Editorials

Set Logic

In procedural programming we tend to think in terms of properties and methods. Sometimes those properties may contain collections of properties, which is the closest we generally get to the concepts of programming in SQL.

By contrast, SQL programming begins with sets, the procedural concept of collections. While you can work with sets having only a single record, the language is still optimized to work on sets of records in the most efficient way. This dissonance of programming perspectives is what makes it more difficult for a procedural programmer to transition to working with SQL.

Often you will find code from procedural programmers that manipulates one record at a time in SQL. You will see this when they use a CURSOR to perform on individual records the same process that could have been applied to a complete set of data. Another way you see this is with the use of a loop that once again manipulates one record on each iteration. While these techniques of looping are not always wrong, they are often misused by individuals who have not made the transition to thinking in set logic.

Another example of procedural type processing is to return many records from a parent table. Then, for each record returned from the parent table, run another query against a child table to get dependent data. For example, get a list of people. Then,, for each person, get a list of their phone numbers. These two sets of people and their phones can be returned in a single stored procedure as two sets that may be joined on the client side as the data is being adapted to native objects. Or, you could return them as two separate calls, one to return the people, and the second to return all of the phones for the selected people, and then combine the two sets in the client code into people having a collection of phones. What you don’t want to do is get a list of people, and then, for each person make a separate call to get their phones. While this does work, and is fairly efficient for smaller sets, when you are working with large sets of data, this process is sub-optimal.

There is a difference in the kind of system you are working with. Because OLTP systems tend to work with small sets, the impact of set logic is not as great as when you are working with systems requiring large sets of data. This is demonstrated by the use of ORMs designed to work with less set logic; the performance is often quite adequate. It is when you get to systems where you work with transactions containing multiple objects that the influence of SET logic makes a big difference.

For those of you entering into the world of SQL, you’ll find it valuable to understand the power of SET logic in your queries. Think of ways you can impact as many records as possible without resorting to procedural constructs like loops and Cursors.

Cheers,

Ben