There Must Be 15 Ways To Lose Your Cursors… Part 2: Just Put It in a S

  • I'm a newbie at this but here is my solution to the second tougher example. I think it's right. I get the same results back.

    Select h.SalesOrderID AS OrderNo, d.SalesOrderDetailID AS LineNum, h.CustomerID AS CustomerID, d.ProductID AS ProductNo

    From Sales.SalesOrderHeader h JOIN Sales.SalesOrderDetail d on h.SalesOrderID = d.salesorderid

    WHERE h.CustomerID = d.ProductID

    Order By d.SalesOrderID, d.SalesOrderDetailID, d.ProductID

    The difference in processing is

    CpuMsLogRdsElapsed

    6032 8348386089

    CpuMsLogRdsElapsed

    62 292 110

  • Andy DBA (4/27/2009)


    Goldie Graber (4/27/2009)


    Thomas (4/27/2009)


    Note that the comma-delimited list is sorted alphabetically. I'm aware that there is a hack using Replace, Stuff and For Xml to achieve this but it is pretty slow especially as the resultset (outer or inner) gets large.

    Strange. I use STUFF & FOR XML for this all the time and it's actually pretty fast.

    The only time I had a problem was when operating on tables with 10 million+ records.

    Any amount smaller than that worked pretty quick.

    I'd be interested in seeing the STUFF and FOR XML solutions. This might be hidden RBAR, but here's an old school udf approach (based on learnings from this forum!) that works on older SQL versions. I'm betting performance takes a nose dive as the list length grows, but look, No "Replace, Stuff, or For Xml"!

    create function [dbo].[ufn_territorylist](@salesPersonID as int) returns varchar(max) as

    begin

    declare @territory as varchar(max)

    select @territory = Coalesce(@territory + ', ', '') + Temp.TerritoryName

    from

    (select distinct ST.Name as TerritoryName

    from Sales.SalesTerritoryHistory As SH

    Inner Join Sales.SalesTerritory As ST

    On ST.TerritoryID = SH.TerritoryID

    where salesPersonID = @salesPersonID) Temp

    order by TerritoryName

    return @territory

    end

    go

    --This lists all salesperson regardless of whether or not they have a territory

    Select C.FirstName + ' ' + C.LastName, dbo.ufn_territorylist(C.ContactID)

    from Person.Contact As C

    Order By C.LastName, C.FirstName --I think this is the order you meant

    --This lists only the ones that have at least one territory

    Select C.FirstName + ' ' + C.LastName, dbo.ufn_territorylist(C.ContactID)

    from Person.Contact As C

    where ContactID in

    (Select SalesPersonID from Sales.SalesTerritoryHistory)

    Order By C.LastName, C.FirstName --I think this is the order you meant

    Great example!

    ---------------------------------------------------------------------
    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

  • Steve Jones - Editor (4/27/2009)


    For the email solution, we run a .NET app here to send the mail, offloading the load of XP_sendmail from SQL Server. We grab a bunch of items to send using a set-based solution to merge the recpients with the email and replace some tokens. The app sends, and then once it gets a sent OK, it updates a single row in the DB. It can retry emails with issues, without loading the db.

    As someone has posted already - operations like emailing should be done externally from the database, using SSIS or in your case a .NET application.

    Defeats the purpose of the article though as it avoids the central premise of the piece which is to replace cursors with set based SQL

    From Part one of the series


    What I will be showing you in this series of articles is how to use the new and old features of Transact-SQL to both create and convert SQL routines that are faster, smaller, cleaner, clearer and more supportable without the use of Cursors or While loops.

    Any cursor based operation can be replaced with an external application 😛

  • Ditto on the fact people think this kind of article is even needed/scarey (at least so far). I can't imagine ever writing the cursors for the examples the author has shown. WOW.

  • Here is an example of how to use the STUFF...FOR XML using the example from AdventureWorks:

    Set NoCount On;

    Declare @cpu_ int;

    Declare @lreads_ int;

    Declare @eMsec_ int;

    Select

    @cpu_ = cpu_time,

    @lreads_ = logical_reads,

    @eMsec_ = total_elapsed_time

    From

    sys.dm_exec_requests

    Where

    session_id = @@spid;

    --======

    with TerritoryHistory (

    SalesPersonID,

    Territories

    ) as (

    select distinct top 100 percent

    sth.SalesPersonID,

    stuff((select ', ' + st.Name from Sales.SalesTerritoryHistory sth1 inner join Sales.SalesTerritory st on (sth1.TerritoryID = st.TerritoryID) where sth.SalesPersonID = sth1.SalesPersonID order by st.Name for xml path('')),1,2,'')

    from

    Sales.SalesTerritoryHistory sth

    order by

    sth.SalesPersonID

    )

    select

    c.FirstName + ' ' + c.Lastname as SalesPerson,

    th.Territories

    from

    Person.Contact c

    inner join TerritoryHistory th

    on (c.ContactID = th.SalesPersonID)

    order by

    c.FirstName,

    c.LastName

    --======

    Select

    cpu_time-@cpu_ as CpuMs,

    logical_reads- @lreads_ as LogRds,

    total_elapsed_time - @eMsec_ as Elapsed

    From

    sys.dm_exec_requests

    Where

    session_id = @@spid

    GO

  • Samuel Vella (4/27/2009)


    Steve Jones - Editor (4/27/2009)


    For the email solution, we run a .NET app here to send the mail, offloading the load of XP_sendmail from SQL Server. We grab a bunch of items to send using a set-based solution to merge the recpients with the email and replace some tokens. The app sends, and then once it gets a sent OK, it updates a single row in the DB. It can retry emails with issues, without loading the db.

    As someone has posted already - operations like emailing should be done externally from the database, using SSIS or in your case a .NET application.

    Defeats the purpose of the article though as it avoids the central premise of the piece which is to replace cursors with set based SQL

    From Part one of the series


    What I will be showing you in this series of articles is how to use the new and old features of Transact-SQL to both create and convert SQL routines that are faster, smaller, cleaner, clearer and more supportable without the use of Cursors or While loops.

    Any cursor based operation can be replaced with an external application 😛

    Just because any cursor based operation can be replaced with an external application doesn't mean it should be (and yes I noticed the emoticon, but others might not).

  • Try this code in MSSql 2005 or 2008...

    [font="Courier New"]

    /****** Object: StoredProcedure [dbo].[util_Create_Temp_Table_Iterator] Script Date: 04/27/2009 09:45:34 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[util_Create_Temp_Table_Iterator]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [dbo].[util_Create_Temp_Table_Iterator]

    GO

    /****** Object: StoredProcedure [dbo].[util_Create_Temp_Table_Iterator] Script Date: 04/27/2009 09:40:42 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    /************************************************************

    Utility to make a temp table iterator rather than use a cursor

    Param:@TableName - the name of the table you wish to iterate

    Comment: See comments in code about inserting and doing work

    Created:Nick Stein11-14-2005

    ************************************************************/

    CREATE PROCEDURE [dbo].[util_Create_Temp_Table_Iterator]

    @TableName varchar(75)

    AS

    DECLARE @SSQL varchar(8000)

    SELECT @SSQL = '/****** BEGIN ' + @TableName + ' LOOP ******/' + char(10)

    SELECT @SSQL = @SSQL + '-- Temp table columns as variables' + char(10)

    -- Declare the variables

    SELECT @SSQL = @SSQL + 'DECLARE @' + Replace(S.[NAME],' ','_') + ''

    + CASE S.system_type_id

    WHEN 34 THEN ' [varchar] (2000)' + CHAR(10)

    WHEN 35 THEN ' [text]' + CHAR(10)

    WHEN 36 THEN ' [uniqueidentifier]' + CHAR(10)

    WHEN 48 THEN ' [tinyint]' + CHAR(10)

    WHEN 52 THEN ' [smallint]' + CHAR(10)

    WHEN 56 THEN ' [int]' + CHAR(10)

    WHEN 58 THEN ' [smalldatetime]' + CHAR(10)

    WHEN 59 THEN ' [real]' + CHAR(10)

    WHEN 60 THEN ' [money]' + CHAR(10)

    WHEN 61 THEN ' [datetime]' + CHAR(10)

    WHEN 62 THEN ' [float]' + CHAR(10)

    WHEN 98 THEN ' [sql_variant]' + CHAR(10)

    WHEN 99 THEN ' [ntext]' + CHAR(10)

    WHEN 104 THEN ' [bit]' + CHAR(10)

    WHEN 106 THEN ' [decimal]' + CHAR(10)

    WHEN 108 THEN ' [numeric]' + CHAR(10)

    WHEN 122 THEN ' [smallmoney]' + CHAR(10)

    WHEN 127 THEN ' [bigint]' + CHAR(10)

    WHEN 165 THEN ' [varbinary] (' + CAST(S.max_length AS VARCHAR(20)) + ') ' + CHAR(10)

    WHEN 167 THEN ' [varchar] (' + CAST(S.max_length AS VARCHAR(20)) + ') ' + CHAR(10)

    WHEN 173 THEN ' [binary] (' + CAST(S.max_length AS VARCHAR(20)) + ') ' + CHAR(10)

    WHEN 175 THEN ' [char] (' + CAST(S.max_length AS VARCHAR(20)) + ') ' + CHAR(10)

    WHEN 189 THEN ' [timestamp]' + CHAR(10)

    WHEN 231 THEN ' [nvarchar] (' + CAST(S.max_length AS VARCHAR(20)) + ') ' + CHAR(10)

    WHEN 239 THEN ' [nchar] (' + CAST(S.max_length AS VARCHAR(20)) + ') ' + CHAR(10)

    ELSE ' Unknown system_type_id ' + CAST(S.system_type_id AS VARCHAR(10)) + char(10)

    END

    FROM SYS.Columns S

    WHERE object_id = (SELECT object_id FROM SYS.Objects WHERE NAME=@TableName)

    ORDER BY column_id

    SELECT @SSQL = @SSQL + CHAR(10)

    SELECT @SSQL = @SSQL + '-- Iterator variables ' + CHAR(10)

    SELECT @SSQL = @SSQL + 'DECLARE@I_' + @TableName + ' INT' + CHAR(10)

    SELECT @SSQL = @SSQL + 'DECLARE@M_' + @TableName + ' INT' + CHAR(10)

    SELECT @SSQL = @SSQL + CHAR(10)

    -- Make the Create Table

    SELECT @SSQL = @SSQL + 'CREATE TABLE #' + @TableName + ' (' + char(10) +

    '[IDENT] int IDENTITY, ' + CHAR(10)

    SELECT @SSQL = @SSQL + '[' + Replace(S.[NAME],' ','_') + ']'

    + CASE S.system_type_id

    WHEN 34 THEN ' [varchar] (2000) NULL,' + CHAR(10)

    WHEN 35 THEN ' [text] NULL,' + CHAR(10)

    WHEN 36 THEN ' [uniqueidentifier] NULL,' + CHAR(10)

    WHEN 48 THEN ' [tinyint] NULL,' + CHAR(10)

    WHEN 52 THEN ' [smallint] NULL,' + CHAR(10)

    WHEN 56 THEN ' [int] NULL,' + CHAR(10)

    WHEN 58 THEN ' [smalldatetime] NULL,' + CHAR(10)

    WHEN 59 THEN ' [real] NULL,' + CHAR(10)

    WHEN 60 THEN ' [money] NULL,' + CHAR(10)

    WHEN 61 THEN ' [datetime] NULL,' + CHAR(10)

    WHEN 62 THEN ' [float] NULL,' + CHAR(10)

    WHEN 98 THEN ' [sql_variant] NULL,' + CHAR(10)

    WHEN 99 THEN ' [ntext] NULL,' + CHAR(10)

    WHEN 104 THEN ' [bit] NULL,' + CHAR(10)

    WHEN 106 THEN ' [decimal] NULL,' + CHAR(10)

    WHEN 108 THEN ' [numeric] NULL,' + CHAR(10)

    WHEN 122 THEN ' [smallmoney] NULL,' + CHAR(10)

    WHEN 127 THEN ' [bigint] NULL,' + CHAR(10)

    WHEN 165 THEN ' [varbinary] (' + CAST(S.max_length AS VARCHAR(20)) + ') NULL,' + CHAR(10)

    WHEN 167 THEN ' [varchar] (' + CAST(S.max_length AS VARCHAR(20)) + ') NULL,' + CHAR(10)

    WHEN 173 THEN ' [binary] (' + CAST(S.max_length AS VARCHAR(20)) + ') NULL,' + CHAR(10)

    WHEN 175 THEN ' [char] (' + CAST(S.max_length AS VARCHAR(20)) + ') NULL,' + CHAR(10)

    WHEN 189 THEN ' [timestamp] NULL,' + CHAR(10)

    WHEN 231 THEN ' [nvarchar] (' + CAST(S.max_length AS VARCHAR(20)) + ') NULL,' + CHAR(10)

    WHEN 239 THEN ' [nchar] (' + CAST(S.max_length AS VARCHAR(20)) + ') NULL,' + CHAR(10)

    ELSE ' UNKNOWN system_type_id ' + CAST(S.system_type_id AS VARCHAR(10)) + ' ' + CHAR(10)

    END

    FROM SYS.Columns S

    WHERE object_id = (SELECT object_id FROM SYS.Objects WHERE NAME=@TableName)

    ORDER BY column_id

    SELECT @SSQL = @SSQL + 'CONSTRAINT [PK_IDENT_' + @TableName + '] PRIMARY KEY CLUSTERED

    (

    [IDENT]

    ) ON [PRIMARY] )

    '

    -- Print the results so far

    PRINT @SSQL

    SELECT @SSQL = ''

    -- Make the iterator

    SELECT @SSQL = @SSQL + '

    -- Insert into temp table

    INSERT INTO #' + @TableName + CHAR(10)

    SELECT @SSQL = @SSQL + 'SELECT '

    PRINT @SSQL

    SELECT @SSQL = ''

    SELECT @SSQL = @SSQL + '[' + S.[NAME] + '],' + CHAR(10)

    FROM SYS.Columns S

    WHERE object_id = OBJECT_ID(@TABLENAME)

    ORDER BY column_id

    -- Clip the CHAR(10) and ,

    SELECT @SSQL = LEFT(@SSQL, LEN(@SSQL)-2) + CHAR(10)

    SELECT @SSQL = @SSQL + ' FROM ' + @TableName + '

    --WHERE -- SET WHERE CLAUSE

    --ORDER BY -- SET ORDER BY

    '

    PRINT @SSQL

    SELECT @SSQL = '

    -- For Debugging uncomment the next line

    -- SELECT * FROM #' + @TableName

    -- Print the results so far and start a new string

    PRINT @SSQL

    SELECT @SSQL = '

    -- Initialize iterator variables

    SELECT@I_' + @TableName + ' = MIN(IDENT) FROM #' + @TableName + '

    SELECT@M_' + @TableName + ' = MAX(IDENT) FROM #' + @TableName + '

    -- Loop through temp table doing work

    WHILE@I_' + @TableName + ' <= @M_' + @TableName + '

    BEGIN

    '

    -- Print the results so far and start a new string

    PRINT @SSQL

    SELECT @SSQL = 'SELECT ' + CHAR(10)

    -- make the select into variables

    SELECT @SSQL = @SSQL + '@' + Replace(S.[NAME],' ','_') + ' = [' + Replace(S.[NAME],' ','_') + '],' + CHAR(10)

    FROM SYS.Columns S

    WHERE object_id = (SELECT object_id FROM SYS.Objects WHERE NAME=@TableName)

    ORDER BY column_id

    -- Clip the CHAR(10) and ,

    SELECT @SSQL = LEFT(@SSQL, LEN(@SSQL)-2) + CHAR(10)

    SELECT @SSQL = @SSQL + 'FROM #' + @TableName + ' WHERE IDENT = @I_' + @TableName + char(10)

    PRINT @SSQL

    -- increment and end the iterator

    SELECT @SSQL = '-- Add work here for ' + @TableName + ' Loop

    SELECT @I_' + @TableName + ' = @I_' + @TableName + ' + 1

    END

    DROP TABLE #' + @TableName + CHAR(10) +

    '/****** END ' + @TableName + ' LOOP' + ' ******/' + char(10)

    PRINT @SSQL

    GO

    [/font]

  • Samuel Vella (4/27/2009)


    Jeff Moden (4/27/2009)


    Heh... in SQL Server 2005, even that can be done without a cursor. We're just gonna have to wait for it in Barry's series of articles.

    OK it *can* be done without a cursor... I have seen some dirty work arounds to get stored procedure calls inside select statements using linked servers but they need certain security features turned on and would still end up as a row by row operation when processed within the database engine (not to mention all the other overheads associated with opening new DB connections) so no performance benefit.

    IMO a last resort technique (even after the possibility of SSIS has been excluded).

    Nah... no such dirty work arounds are necessary. No security violations, either. I do agree that a Cursor won't hurt in such a situation, but they're not necessary if you don't want them to be. Still, I'm not going to steal Barry's thunder on this set of articles. I don't actually know if he's planning on showing the technique, but I don't mind waiting to see.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Samuel Vella (4/27/2009)


    Any cursor based operation can be replaced with an external application 😛

    Absolutely true. But it's still amazing to me what people actually think they need a cursor for. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Andy DBA (4/27/2009)


    Thanks for the post! I bet that blows the function based approach out of the water!

    Like anything else, it depends... I've seen it go both ways. Knowledge of both methods is a great thing to know.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Coming from a novice point of view, I can't understand why cursors are really even used? They seem so much more complicated than anything set-based in SQL. I'd never even seen actual cursors before - heard about them in some of the posts on this site - reading this article. Having now seen them and gone through the explanation in this article of their structure, they seem about twice as complicated as anything set-based. Glad the references, mentors, and instruction I've had to this point in using SQL Server hadn't mentioned cursors, as they seem thoroughly more confusing!

    Great article

  • I've always found cursors to be a pain and set-based to be fun. So, I'm no fan of cursors. Also, as soon as I was able to use For XML, I jumped on it and use it whenever the need arises.

    I say all that as background so that you can understand the context in which the next comment is made: The For XML is not an ideal replacement for a true "List" function as someone else posted about. The For XML is about making XML text and the encoding that it does can be a real problem. For some data sets, it is no big deal. But having to do Replace() function to deal with the coding can be a pain.

    If MS is not going to implement the List function and instead reply on the Stuff/For XML statements, then I'd like to see some kind of function called "DeEncode" or something that will take care of all the Replace needs at once.

    Otherwise, I might still be tempted to use Cursors sometimes. It would depend on the data. I haven't hit such a situation yet, but I'm not going to rule it out as the best solution in some cases.

  • @mark-3: Developers use cursors & loops because they are used to thinking procedurally and not set-based. They write loops because that mirrors their thought process.

    @Barry: Awesome article! I sent both parts out to my team at work. Hope you feel better soon!

  • Just stepped in for a quick read up on the article...good article, however the examples are a bit naive and are not applied toward anything I would have ever thought someone might use a cursor for - why would anyone use a cursor to loop over a table when the same result is created with a simple select statement. I understand the examples are made simple to make a point, but the only time I have ever needed a cursor is to achieve some sort of lookahead in the result set, such that data in in row 1 depended on knowing the value of some data in row 10 and the only way to get that (without building an external app/script) was with a cursor.

    Thanks for the article just the same.

  • I forgot to add that I think this is a well written article. It would be a great intro for beginners who are cursor prone. It is great because of the method you give for "converting" cursors. I like that. It is the first time I have seen something concrete that a beginner could really stick his/her teeth into.

Viewing 15 posts - 31 through 45 (of 316 total)

You must be logged in to reply to this topic. Login to reply