Facility9

Cursors. Many database developers have to suppress a shudder of horror when they open up a stored procedure only to find a cursor lurking inside. They certainly have their place in the database developer’s arsenal of tools, but they can usually be rewritten as an easily understood set operation.

I’ve spent the first three days at my new client learning the database schema and migrating four load routines from the old to the new database schema. The new schema is, admittedly, easier to understand simply because of the virtue of using design up front principles. But I digress.

These four load processes make use of cursors and, in one instance, nested cursors.

Without divulging too much of the client’s inner workings, the first cursor I encountered looked something like this:

DECLARE @start_date DATETIME;
DECLARE @end_date DATETIME;
DECLARE @end_date2 DATETIME;
DECLARE @account_no INT;

DECLARE my_cursor CURSOR
FOR
  SELECT account_no, start_date, end_date
  FROM Bill;
-- obviously the real query was more complicated than this

OPEN my_cursor;
FETCH NEXT FROM my_cursor
INTO @account_no, @start_date, @end_date;

SET @end_date2 = @start_date

WHILE @@fetch_status = 0
BEGIN
  IF DATEDIFF(dd, @start_date1, @end_date2) > 7
    INSERT INTO some_table (some values);
  END
END;

SET @end_date2 = @end_date;

FETCH NEXT FROM my_cursor
INTO @account_no, @start_date, @end_date;

Essentially, this will insert any records that are at least 7 days older than the previous record. The downside is that, even in my overly simplified example, this isn’t easy to read. When the full query logic is involved this becomes very difficult to read.

I re-wrote the original query using a CTE:

WITH my_cte (id, account_no, start_date, end_date)
AS (
  SELECT
    ROW_NUMBER() OVER (account_no, start_date, end_date, some_values),
    account_no,
    start_date,
    end_date,
    some_values
  FROM Bills
  GROUP BY account_no, start_date, end_date
)
INSERT INTO some_table (some_columns)
SELECT DISTINCT
  account_no,
  start_date,
  end_date,
  some_values
FROM my_cte AS o
INNER JOIN my_cte AS o2 ON o2.account_no = o.account_no
WHERE o2.start_date < o.start_date
  AND DATEDIFF(dd, o2.start_date, o.start_date) > 7

Instead of having to process each record one at a time, the new query is able to process several million records and insert approximately 60,000 records in 11 seconds and is much more readable and reusable as a result.

This CTE pattern has become the basis for several data load procedures that I've re-written from cursors, including a pair of nested cursors.

Special thanks, once again, to my brother for helping me rubber duck my way through this and for providing the initial idea that resulted in using a CTE.

That’s right, now you too can use T-SQL to get dates quickly!

Today at the client I came across the following chunk of SQL in a where clause:

dateadd(m,-24,(cast(datepart(m,getdate()) as varchar(2))+'/1/'+cast(datepart(yyyy,getdate()) as varchar(4))))

It’s used to return the first of the month two years ago. So, for today, this would return 2006-09-01

What’s wrong with this? Well, in this query there are two string concatenations, two explicit conversions from an INTEGER to a VARCHAR, and an implicit conversion from VARCHAR to DATETIME. When run across a large number of dates, this can rapidly become an expensive operation. Especially considering that this was half of a BETWEEN statement that had something similar on the other side of the AND.

Is there a better way to do this? You bet!

SELECT DATEADD(mm, -24, DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))

This involves no implicit or explicit casting, just some basic date math. While I haven’t done any benchmarking on either option, I’m going to go out on a limb and state that the second option is much more efficient and, arguably, a lot clearer.

In addition to being cleaner, using DATEADD and DATEDIFF makes no assumption about the current locale of your database. This could save you if you are dealing with databases that are spread across the globe or if you have to deal with localized tables within a database.

More information can be found either from Pinal Dave or from Shane Cooper. I’ve bookmarked both of these links and, if you’ve ever had to write SQL code, you should to.

N.B. If you’re using this over any more than a few records you will want to create a variable to store the result of the date math since date functions are non-deterministic. Meaning, if you perform a SELECT on 2 million records, the math will be performed at least 2 million times (4 million if you’re using a BETWEEN).

Edit: Special thanks to my brother for coming to my rescue today and helping me track down an easier, SQL-based, way to do this.