Written by: Dave
Tuesday, March 23, 2010 5:45 PM 

When it comes to writing T-SQL, one of my pet hates is the unnecessary use of loops and cursors.  As we all know, SQL is a set based language and has been designed to work using sets of data.  Far too often do I see people looping unnecessarily in their back-end code resulting in a slow user interface.

With every operation I write in T-SQL I always try to take a set based approach.  Ok, I understand that there are scenarios where a cursor is simply the only option but I tend to think of those as a last resort. 

In a recent project I had to extract a list of email addresses which would be used in the “To” field of an automated email.  The list of addresses had to be separated with a semi-colon.  This can be achieved in many ways but I have a preferred method of extracting data in this fashion.  Firstly, let me show you what I consider to be a bad approach.  The following code uses a cursor to return the email addresses and the iterates through that cursor, concatenating them together with a semi-colon.

--  Variable declarations
    Declare @MailingList VarChar(Max)
    Declare @EmailTemp VarChar(255)
    Declare cur_MailingList Cursor For
        Select Top 100 Email
        From Users

--  Iterate through the email addresses
    Open cur_MailingList
    Fetch Next From cur_MailingList Into @EmailTemp

    While @@Fetch_Status = 0
        Begin

            Set @MailingList = IsNull(@MailingList, '') + @EmailTemp + ';'

            Fetch Next From cur_MailingList Into @EmailTemp

       End

    Close cur_MailingList
    DeAllocate cur_MailingList

--  Return the data
    Select @MailingList

The code above is very inefficient.  If you instruct SQL Server to show you the execution plan for this you can see for yourselves.  Not only is the execution plan HUGE, it also takes the server well over 2 seconds (a long time) to generate the plan.  The following screenshot shows a selection of the generated execution plan.  Notice the size of the vertical scroll bar!

Now that I have shown you the bad code, let me show you what I consider to be the good code.  I hope you will agree that the results speak for themselves.  The following code uses what I can only describe as a SQL magic!  No loops!

--  Declare and instantiate the return variable
    Declare @MailingList VarChar(Max)
    Set @MailingList = ''

--  Build the mailing list as the query is running.
    Select Top 100 @MailingList = @MailingList + Email + ';'
    From Users

--  Return the data without the trailing semi colon
    Select Left(@MailingList, Len(@MailingList) - 1)

This code is very efficient.  The iteration is taking place as the data is being selected out of the table.  Everything is done in one hit on the database.  If you now look at the execution plan for this piece of code, which is generated in a split second I might add, you will see what I mean.

Not only is the code much much quicker, it is less to type, easier to read and what I would consider simpler to understand.  I hope you have found this information useful and I believe that it illustrates how a simple understanding of a set based approach can drastically improve your code.

Trackback Print

Tags: SQL Server , T-SQL , Tips , Loops , Recursion , Iteration

Categories: Database, Development

Location: Blogs Dave Clayton