SQL Server Performance

how to Avoid cursor here

Discussion in 'General Developer Questions' started by ranjitjain, Sep 21, 2005.

  1. ranjitjain New Member

    Hi Guys,<br />This is the sample data and<br />appid strttime volume<br />60222005-04-12 00:00:00.000102502<br />60242005-04-12 00:00:00.00027395404<br />60272005-04-12 00:00:00.000122038<br />60582005-04-12 01:00:00.0004735<br />60612005-04-12 01:00:00.00010917<br />60622005-04-12 01:00:00.00030705<br />60222005-04-12 02:00:00.00061682<br />60242005-04-12 02:00:00.00024757602<br />60272005-04-12 02:00:00.000111234<br />60612005-04-12 03:00:00.00048068<br />60622005-04-12 03:00:00.00047148<br />60632005-04-12 03:00:00.00069897<br />61662005-04-12 04:00:00.00067463<br />61682005-04-12 04:00:00.0007056692<br />61742005-04-12 04:00:00.00025223703<br /> from this how to get top 2 highest volume appid based on strttime without using cursor.<br />Thanks for your tries.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  2. ghemant Moderator

    Hi,
    AF in understand i did it as :

    select top 2 volume, strttime,appid from testtime
    order by volume,strttime desc


    which returns
    Volume StrtTime Appid
    ========================================
    1025022005-04-12 00:00:00.0006022
    109172005-04-12 01:00:00.0006061


    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  3. ranjitjain New Member

    Hi Hemant thanx for the try but dear ofcourse i dont need this simpler.
    Sorry i dint posted the desired output.
    I need the resultset as

    6024 2005-04-12 00:00:00.000 27395404
    6027 2005-04-12 00:00:00.000 122038
    6061 2005-04-12 01:00:00.000 10917
    6062 2005-04-12 01:00:00.000 30705
    6024 2005-04-12 02:00:00.000 24757602
    6027 2005-04-12 02:00:00.000 111234
    6061 2005-04-12 03:00:00.000 48068
    6063 2005-04-12 03:00:00.000 69897
    6168 2005-04-12 04:00:00.000 7056692
    6174 2005-04-12 04:00:00.000 25223703
  4. FrankKalis Moderator


    set nocount on
    use northwind
    select
    t1.CustomerID
    , t1.OrderDate
    from
    orders t1
    where
    t1.OrderDate in
    (
    select top 2 --with ties
    t2.OrderDate
    from
    orders t2
    where
    t2.CustomerID = t1.CustomerID
    order by
    t2.OrderDate desc
    )
    order by
    t1.CustomerID
    , t1.OrderDate desc
    set nocount off


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  5. Madhivanan Moderator

    Try this also


    Select appid, strttime, volume From yourTable T
    Where (Select count(*) From yourTable Where strttime=A.strttime and appid <= A.appid)<=2
    Order By strttime, appid Desc




    Madhivanan

    Failing to plan is Planning to fail
  6. FrankKalis Moderator

    Wow, very good! Adapting your statement to the Northwind db results in following figures<br /><pre id="code"><font face="courier" size="2" id="code"><br />Table 'Orders'. Scan count 827, logical reads 269474, physical reads 2, read-ahead reads 3.<br /> <br />------------------------------------------------------ <br />1900-01-01 00:00:01.243<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />Second is execution time. Your statement<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT t1.CustomerID , t1.OrderDate<br /> FROM Orders t1 <br /> WHERE <br /> (SELECT COUNT(*) <br /> FROM Orders <br /> WHERE OrderDate&lt;=t1.OrderDate AND CustomerID = t1.CustomerID) &lt;=2 <br /> ORDER BY t1.CustomerID, t1.OrderDate DESC<br /></font id="code"></pre id="code"><br />results in<br /><pre id="code"><font face="courier" size="2" id="code"><br />Table 'Worktable'. Scan count 823, logical reads 3351, physical reads 0, read-ahead reads 0.<br />Table 'Orders'. Scan count 2, logical reads 44, physical reads 3, read-ahead reads 12.<br /> <br />------------------------------------------------------ <br />1900-01-01 00:00:00.080<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  7. Madhivanan Moderator

    Frank, May I know how you know that Statistics? Which one have I to set?

    Madhivanan

    Failing to plan is Planning to fail
  8. FrankKalis Moderator


    SET STATISTICS IO ON
    DBCC DROPCLEANBUFFERS
    DBCC FREEPROCCACHE
    DECLARE @dt_start1 DATETIME, @dt_start2 DATETIME
    SET @dt_start1 = GETDATE()
    SELECT t1.CustomerID, t1.OrderDate
    FROM Orders t1
    WHERE t1.OrderDate IN
    (SELECT TOP 2 t2.OrderDate
    FROM Orders t2
    WHERE t2.CustomerID = t1.CustomerID
    ORDER BY t2.OrderDate DESC)
    ORDER BY t1.CustomerID, t1.OrderDate DESC
    SELECT GETDATE()-@dt_Start1
    DBCC DROPCLEANBUFFERS
    DBCC FREEPROCCACHE
    SET @dt_Start2 = GETDATE()
    SELECT t1.CustomerID , t1.OrderDate
    FROM Orders t1
    WHERE
    (SELECT COUNT(*)
    FROM Orders
    WHERE OrderDate<=t1.OrderDate AND CustomerID = t1.CustomerID) <=2
    ORDER BY t1.CustomerID, t1.OrderDate DESC
    SELECT GETDATE()-@dt_Start2
    SET STATISTICS IO OFF


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  9. Madhivanan Moderator

    Thats cool Frank<br /><br />One more doubt<br />Whenever I copy the code (written using
    Code:
    ...) and paste it in the Query Analyser, all come as single line without line feed. Is there any trick to avoid this? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  10. FrankKalis Moderator

    That's pretty annoying, isn't it? [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br />No, I haven't found a public workaround. But I use a private one. As moderator you can go and edit the posting which pops up the usual text area. The I copy the code from there to the clipboard and cancel the edit. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  11. ghemant Moderator

    Hi,
    actually thats what i wondering why you have ask this question but now its clear to me ! [:0][V]

    quote:Originally posted by ranjitjain

    Hi Hemant thanx for the try but dear ofcourse i dont need this simpler.
    Sorry i dint posted the desired output.
    I need the resultset as

    6024 2005-04-12 00:00:00.000 27395404
    6027 2005-04-12 00:00:00.000 122038
    6061 2005-04-12 01:00:00.000 10917
    6062 2005-04-12 01:00:00.000 30705
    6024 2005-04-12 02:00:00.000 24757602
    6027 2005-04-12 02:00:00.000 111234
    6061 2005-04-12 03:00:00.000 48068
    6063 2005-04-12 03:00:00.000 69897
    6168 2005-04-12 04:00:00.000 7056692
    6174 2005-04-12 04:00:00.000 25223703


    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  12. ranjitjain New Member

    Thanks All,
    Really Good one.
    Even i've faced the problem of pasting code in QA from here comes in one line.
    It really irritates alot.
  13. ranjitjain New Member

    Hi guys,
    I tried both the solutions but not getting top 2 * for diffrent dates with highest volume
  14. FrankKalis Moderator

    Too lazy today? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />Is this better?<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT t1.appid, t1.strtime, t1.volume<br /> FROM #t t1<br /> WHERE t1.volume IN<br /> (SELECT TOP 2 t2.volume<br /> FROM #t t2<br /> WHERE t2.strtime = t1.strtime<br /> ORDER BY t2.volume DESC)<br /> ORDER BY t1.strtime <br /></font id="code"></pre id="code"><br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  15. ranjitjain New Member

    [<img src='/community/emoticons/emotion-2.gif' alt=':D' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />]Yes i think i need a long weekend[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Mind is not at one place.<br />Anyways Thanx alot Frank.[8D]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Too lazy today? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />Is this better?<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT t1.appid, t1.strtime, t1.volume<br /> FROM #t t1<br /> WHERE t1.volume IN<br /> (SELECT TOP 2 t2.volume<br /> FROM #t t2<br /> WHERE t2.strtime = t1.strtime<br /> ORDER BY t2.volume DESC)<br /> ORDER BY t1.strtime <br /></font id="code"></pre id="code"><br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstí²“íº¥ PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  16. Madhivanan Moderator

    &gt;&gt;Yes i think i need a long weekend<br /><br />So you are doing heavy work<br />your boss must be proud of you [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  17. ranjitjain New Member

    Hey once more i need your valuable posts.
    If the data provided above has two more rows with time,volume,appid appended at end of each distinct starttime and with that data how to get this.

    6024 2005-04-12 00:00:00.000 27395404
    6027 2005-04-12 00:00:00.000 122038
    0 0 sum(all avoided volume and not above)
    6061 2005-04-12 01:00:00.000 10917
    6062 2005-04-12 01:00:00.000 30705
    0 0 sum(all avoided volume and not above)
    6024 2005-04-12 02:00:00.000 24757602
    6027 2005-04-12 02:00:00.000 111234
    0 0 sum(all avoided volume and not above)
    6061 2005-04-12 03:00:00.000 48068
    6063 2005-04-12 03:00:00.000 69897
    0 0 sum(all avoided volume and not above)
    6168 2005-04-12 04:00:00.000 7056692
    6174 2005-04-12 04:00:00.000 25223703
    0 0 sum(all avoided volume and not above)


  18. FrankKalis Moderator

    If I understand this correctly now, one way might be to use a correlated subquery with the same IN() query, but use NOT IN() this. <br /><br />Enough for today, I'm going home now. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  19. ranjitjain New Member

    Hi Frank,<br />I tried subquery with union but it appends them at the end of all of first one.<br />I want <br />0 0 sum(all other volume) at the end of all distinct time.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  20. FrankKalis Moderator

    Nah, was in a hurry yesterday when I typed this. Forget my posting. I would really consider using a report writer for this. Should be much easier. But in the meantime this should do:


    SELECT appid, strtime, Report.Volume
    FROM
    (SELECT TOP 100 PERCENT LTRIM(t1.appid) AS appid, t1.strtime, t1.volume, 1 AS INNERGROUP
    FROM #t t1
    WHERE t1.volume IN
    (SELECT TOP 2 t2.volume
    FROM #t t2
    WHERE t2.strtime = t1.strtime
    ORDER BY t2.volume DESC)
    ORDER BY t1.strtime
    UNION ALL
    SELECT 'Not included', t3.strtime, SUM(t3.volume), 2
    FROM #t t3
    WHERE t3.volume NOT IN
    (SELECT TOP 2 t4.volume
    FROM #t t4
    WHERE t4.strtime = t3.strtime
    ORDER BY t4.volume DESC)
    GROUP BY strtime) Report
    ORDER BY strtime, INNERGROUP


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  21. Twan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />Thats cool Frank<br /><br />One more doubt<br />Whenever I copy the code (written using
    Code:
    ...) and paste it in the Query Analyser, all come as single line without line feed. Is there any trick to avoid this? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Paste it into WordPad or MsWord first, then copy and paste it into QA.  WordPad will recognise the line feeds, and MSWord recognises the line feeds and the white space<br /><br />Cheers<br />Twan
  22. FrankKalis Moderator

    A kludgy wordaround, but still nice. Thanks! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  23. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Twan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />Thats cool Frank<br /><br />One more doubt<br />Whenever I copy the code (written using
    Code:
    ...) and paste it in the Query Analyser, all come as single line without line feed. Is there any trick to avoid this? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Paste it into WordPad or MsWord first, then copy and paste it into QA.  WordPad will recognise the line feeds, and MSWord recognises the line feeds and the white space<br /><br />Cheers<br />Twan<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Well. Thats nice<br />But other way is to click Reply with Quote Button and then copy the text and paste it in QA will work well<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  24. ranjitjain New Member

    Yes Madhivanan,
    I tried the same by replying and working the same way the code was pasted.
    Good Stuff.
    Thanks.

Share This Page