Queries

The twin goals of performance and supportability help drive ArgentSea’s approach to SQL queries. One of the areas in which ArgentSea’s design is most distinctive is its attempt to avoid tight coupling between the application and the database.

Tight Coupling

Tight coupling describes when a system’s integration with a second system depends on the internal implementation of the other system. Often the result of haphazard design, this interdependency makes it nearly impossible to change systems or even improve the implementation due to the difficulty of fully accounting for potential inter-system impact.

When your database client code depends upon how tables and columns are implemented, the application layer and database layer have a type of tight coupling. ArgentSea tries to minimize this by ensuring that SQL is segregated and explicit.

Loose Coupling

Loosely coupled systems have well-defined interfaces. Because of this, you can change the implementation as long as you maintain the interface “contract”. These systems are more robust, testable, and manageable. Because of the well-defined interface between services, systems can be optimized without concern about inadvertent downstream impacts.

ArgentSea’s use of static, parameterized queries are intended to make the data “contract” as explicit as possible, and managed independently of application code. The goal is to enable the changing of database structures — you can rewrite queries, partition tables, add hints, materialize views, and more — and as long as the same parameters are provided and the same results returned, the application should not break.

To keep database interactions as manageable as possible, ArgentSea seeks to keep SQL static, consolidated, and segregated.

Static

ArgentSea discourages dynamic SQL queries. Dynamic SQL may have slightly poorer performance due to the overhead of a SQL compiler, and the potential for SQL injection may make some dynamic SQL less secure. Primarily, though, it can be difficult to parse, understand, and — especially — improve SQL created from a C# object model. This is why ArgentSea is intended to work with static SQL and parameters.

Consolidated

Large applications routinely have “orphaned” procedures, tables, views, etc. As functionality changes, once necessary objects are no longer required. Application developers often have no easy way to confidently drop these objects, yet there is a continuing maintenance cost to preserving them. By consolidating SQL queries, ArgentSea helps provide visibility to query usage. Especially helpful is Visual Studio’s reference count: if the count is zero, the query is no longer in use.

Segregated

ArgentSea avoids compiling SQL implementation code into application binaries. Well-written application code, running in the same environment, will likely continue to run well indefinitely; however, a query that initially worked efficiently may need to be rewritten as the data set grows or cardinality changes. By separating SQL and C# files, queries can be adapted as data changes over the lifetime of an application.

This also encourages SQL for data-domain work, and .NET for application work. People expert at optimizing SQL do not always welcome dealing with C# projects and concomitant build processes.

Next: Creating SQL Queries