SQL Server

Why GraphQL Excels for Querying Large, Complex Datasets: A Real-World Trading API Case Study

Handling large, complex datasets efficiently is a critical challenge in modern APIs. In a real-world project, I built a .NET GraphQL API integrated with SignalR to retrieve and stream thousands of trades from a database in real time. The Trades class, with approximately 100 properties (e.g., trade ID, price, volume, timestamp, counterparty details), represents a rich dataset where different domains (e.g., analytics, reporting, trading desks) require specific subsets of data. Previously, I illustrated GraphQL’s benefits with a simplified task management API using a WorkTasks table. Here, I’ll show how those benefits apply to this real-world trading API, enabling precise, scalable, and flexible querying of trade data.

In a microservices architecture, ensuring each service communicates reliably with databases like SQL Server and Oracle is critical but challenging due to restricted permissions. Health checks monitor service health, and in this post, I focus on advanced checks that execute custom queries to verify table access, beyond standard NuGet packages. I’ve learned to anticipate issues like permission errors, ensuring robust systems for global teams.

I developed a MultiDatabase health check to test specific queries across SQL Server and Oracle, addressing the challenge of limited database access rights that require DBA intervention (e.g., user creation, encrypted connection strings). This check integrates with CI/CD pipelines, catching issues before deployment, complementing my 95% test coverage in projects like RunningTracker.

Inspired by my running hobby, I built a .NET 8 RESTful API to track amateur runs, focusing on testing mastery. Using Dapper, Docker Compose, and SQL Server with XUnit, Reqnroll, and K6, ensuring robust endpoints.

My procurement-honed discipline—20+ years managing complex projects—drove rigorous testing, a skill I apply to production systems.

This API, inspired by my running hobby, honed my testing skills in Unit, Integrated, Functional and Load tests. Streamlined testing process for maintainable code, applicable to production systems.

During the development of this project, I was moving a critical system from .NET Framework to .NET 8 in a move to cloud initiative. The legacy system was not designed to be cloud-native and didn’t have the tests I spoke about in the Running Tracker project.

This new Web API was a part of the monolith responsible for retrieving from database all the negotiations made by a number of clients and confirmed in the B3 (Brazilian Stock Exchange), the data was processed to calculate taxes and commissions.