SQL Server Performance

Better way to perform a query than using cursors

Discussion in 'T-SQL Performance Tuning for Developers' started by AnimalMagic, Nov 7, 2007.

  1. AnimalMagic New Member

    Morning all,
    I have a situation where i have to put a couple of tables into a flat file output. the tables are designed as below

    Table1
    AppID, Name, address
    Table 2
    Appid, DeclineReason
    They are joined by AppID. Table 2 can have multiple (up to 10) decline reasons for 1 application id.
    My task was to put the output into one file, so what i did was setup 10 variables for the 10 possible decline reasons then a cursor to get the distinct appID's from table 1, then within the cursor loop i declared another cursor which started with @DeclineReason1 and went through each Decline reason for that appid. At the end it did an insert into a table which looked like this...
    AppID,Name, Address, DecReason1,DecReason2,DecReason3,DecReason4.....
    Is there a better way than doing these nested triggers? Im sure there must be, but i couldnt think of any at the time. Your advice would be appreciated.

    Thanks
    John



  2. Adriaan New Member

    If an 8000 character restriction is not a problem (4000 if DeclineReason is a unicode column) ...
    DECLARE @CheckId INT, @Output VARCHAR(8000)
    SELECT @CheckId = AppId
    FROM Table1
    WHERE xxxxxxxxxxxxxxxxxxx
    SET @Output = CAST(@CheckId AS VARCHAR(100))
    SELECT @Output = @Output + ',' + DeclineReason
    FROM Table2
    WHERE AppId = @CheckId
    ORDER BY DeclineReason
    SELECT @Output
  3. AnimalMagic New Member

    Hi Adriaan,
    Thanks for the reply, however this method will not work as each decline reason has to be in a seperate field, not all in one. the output has to be like
    AppID, Name, Addy, Declinereason1, declinereason2....
    not
    Appid,Name, Addy, declinereason
    ps, anandchatterjee i will check out the link - thanks

  4. Adriaan New Member

    Denormalizing data is a common cause for bad headaches. Don't you love it.
  5. AnimalMagic New Member

    yeah its just fantastic :)
    The solution i presented in my first post does work, i just thought that there would be a quicker, or better written solution. Maybe there isnt, hence the post :)

  6. Adriaan New Member

    Did you check out if they would accept XML?
    You can't do much with XML in SQL 7.0 or 2000, but it's quite simple: you add a FOR XML clause to a regular SELECT query. Check it out in Books Online.
  7. Madhivanan Moderator

    Search for Dynamic Cross tab in google
  8. AnimalMagic New Member

    Madhivanan - cheers, ill check that out.

    Adriaan - Would be great if they did accept XML, however i tried that one already and they cant. they wont even accept a csv!!
  9. Adriaan New Member

    By the way, my code actually does generate a comma-separated list with as many commas as are needed for the DeclineReason values.
    The only thing it doesn't do is create a header row with field names ...
  10. anandchatterjee New Member

  11. jdaman New Member

    AM,
    you could always use a PIVOT operator as follows:
    declare @t1 table (
    i int)

    insert @t1 (i)
    select 1 union
    select 2 union
    select 3 union
    select 4

    declare @t2 table (
    i int, val int)

    insert @t2 ( i, val )
    select 1, 0 union
    select 1, 1 union
    select 1, 2 union
    select 2, 0 union
    select 2, 1 union
    select 3, 0 union
    select 3, 1 union
    select 3, 2 union
    select 3, 3 union
    select 3, 4 union
    select 3, 5 union
    select 4, 0 union
    select 4, 1 union
    select 4, 2 union
    select 4, 3 union
    select 4, 4

    select * from
    (
    select t1.i, t2.val from @t1 t1
    join @t2 t2 on t1.i = t2.i
    ) a
    pivot ( count(val) for val in ( [0], [1], [2], [3], [4], [5] ) ) as p
    Results:
    i 0 1 2 3 4 5
    ----------- ----------- ----------- ----------- ----------- ----------- -----------
    1 1 1 1 0 0 0
    2 1 1 0 0 0 0
    3 1 1 1 1 1 1
    4 1 1 1 1 1 0
  12. jdaman New Member

    Using your example:
    declare @t1 table (
    AppID int, Name char(25), address char(30))

    insert @t1 (AppID, Name, address)
    select 1, 'Mark', '123 main' union
    select 2, 'Shannon', '111 field st' union
    select 3, 'Carl', '325 james ave' union
    select 4, 'Pete', '677 436th st'

    declare @t2 table (
    AppID int, Declinereason char(25))

    insert @t2 ( AppID, Declinereason )
    select 1, 'Declinereason0' union
    select 1, 'Declinereason1' union
    select 1, 'Declinereason2' union
    select 2, 'Declinereason0' union
    select 2, 'Declinereason1' union
    select 3, 'Declinereason0' union
    select 3, 'Declinereason1' union
    select 3, 'Declinereason2' union
    select 3, 'Declinereason3' union
    select 3, 'Declinereason4' union
    select 3, 'Declinereason5' union
    select 4, 'Declinereason0' union
    select 4, 'Declinereason1' union
    select 4, 'Declinereason2' union
    select 4, 'Declinereason3' union
    select 4, 'Declinereason4'



    select * from
    (
    select t1.AppID, t1.Name, t1.address, t2.Declinereason from @t1 t1
    join @t2 t2 on t1.AppID = t2.AppID
    ) a
    pivot ( count(Declinereason) for Declinereason in ( [Declinereason0], [Declinereason1], [Declinereason2], [Declinereason3], [Declinereason4], [Declinereason5] ) ) as p

  13. jdaman New Member

    oops... sorry for the 2005 answer... was in the wrong forum.
  14. ScottPletcher New Member

    Please try this: it might perform better; usually even temp tables do better than cursors [:)].
    CREATE TABLE #DeclineReasons (
    id INT IDENTITY(1, 1),
    Appid INT, --<<-- chg to match original datatype on table
    DeclineReason VARCHAR(30), --<<-- chg to match original datatype on table
    PRIMARY KEY (Appid, id) --NOTE: do not remove, needed for performance
    )
    INSERT INTO #DeclineReasons (Appid, DeclineReason)
    SELECT Appid, DeclineReason
    FROM [Table 2]
    ORDER BY Appid, DeclineReason --NOTE:do not remove, needed for performance [and accuracy]
    SELECT t1.Appid, t1.Name, t1.Address,
    MAX(CASE WHEN dr2.id = dr1.firstId THEN dr2.DeclineReason ELSE '' END) AS DecReason1,
    MAX(CASE WHEN dr2.id = dr1.firstId + 1 THEN dr2.DeclineReason ELSE '' END) AS DecReason2,
    MAX(CASE WHEN dr2.id = dr1.firstId + 2 THEN dr2.DeclineReason ELSE '' END) AS DecReason3,
    --...4 thru 9 same general approach as above
    MAX(CASE WHEN dr2.id = dr1.firstId + 9 THEN dr2.DeclineReason ELSE '' END) AS DecReason10
    FROM [Table 1] t1
    INNER JOIN (
    SELECT Appid, MIN(id) AS firstId
    FROM #DeclineReasons
    GROUP BY Appid
    ) AS dr1 ON dr.Appid = t1.Appid
    INNER JOIN #DeclineReasons dr2 ON dr2.Appid = t1.Appid
    GROUP BY t1.Appid, t1.Name, t1.Address

Share This Page