Post reply

Issue with CTE

  • March 12, 2009 at 9:34 am

    #958439

    Hey G, how about testing this on your machine so we have apples to apples?

    declare @N int;

    set statistics time on;

    with Tally (N) AS (SELECT TOP 100000 ROW_NUMBER() over (order by sc1.id) FROM Master.dbo.SysColumns sc1 CROSS JOIN Master.dbo.SysColumns sc2)

    select @n = n from tally;

    set statistics time off;

    select @N

    I'm also curious about whether or not some of them perform quicker for a smaller number of rows. When doing string parsing and stuff like that, a million rows is rarely needed.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • March 12, 2009 at 9:46 am

    #958450

    31 milliseconds CPU and total for that one. Five runs, no real variation (32 ms one time out of five).

    Selecting from an actual Numbers table, 0 CPU, 14 total. Five runs, back and forth between 0 CPU and 15 CPU, 14-16 total.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • March 12, 2009 at 9:47 am

    #958452

    Bump it up a notch. The version I sent you was set to 100 thousand rows, not a million.

    But yeah, having the tally table already generated SHOULD be quicker.

    Then I stop and ponder and realize we are talking about fractions of a second to produce a million rows. I think we've caught Moden's Millisecond Madness.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • March 12, 2009 at 9:57 am

    #958465

    At 1-million, the syscolumns cross join took 297 milliseconds CPU, 291 total.

    Created a million-row BigNumbers table. 235 CPU, 243 total, using that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • March 12, 2009 at 10:00 am

    #958468

    Thanks G... or do you prefer G2 ?? 🙂

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • March 12, 2009 at 10:52 am

    #958509

    Bob Hovious (3/12/2009)

    Thanks G... or do you prefer G2 ?? 🙂

    Doesn't matter. Gus, G, G2, GSquared, even Hey You. It all works. 🙂

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • March 14, 2009 at 6:12 pm

    #959812

    thanks everyone for the contribution.

    the issuse came as I changed the @string parameter from Varchar(8000) to Varchar(Max) and the solution was posted here where the start was casted into int. But I am not able to understand why the same casting was not required when data type of inout parameter was varchar(800)...

    cheers

    Siddarth

  • March 14, 2009 at 8:28 pm

    #959842

    siddartha pal (3/12/2009)

    I am not able to complie the function because of Varchar(max) for @string parameter.

    Siddartha,

    Check the compatibility level of the database... if it's set to "8", then the database is SQL Server 2000 compatible and things that require SQL Server 2005 will simply not work.

    --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)

  • March 14, 2009 at 8:59 pm

    #959844

    Heh... stupid me... of course it's set to 9 or you'd have gotten a message about the CTE. Sorry.

    Can you post your final code? I just wanna make sure that you didn't get stuck using the very slow recursive CTE method.

    --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)

  • March 14, 2009 at 9:05 pm

    #959845

    Bob Hovious (3/12/2009)

    I think we've caught Moden's Millisecond Madness.

    Heh... you must have... you're getting good at it. 😉

    The performance key here isn't the number of milliseconds of difference between the various good methods of generating a Tally table or it's equivalent... in this case, it's how many times those numbers of milliseconds are going to be used as part of a function. Consider this... if the difference between two functions is only a tiny 1/2 of a millisecond, why worry? The answer is because if it's used against even a relatively small table of only 10 million rows, the performance difference between the two functions will weigh in at a rather hefty 5000 seconds. For those that need help on that, that's a whopping 83 minutes and 20 seconds difference.

    BWAA-HAA!!! And I guess it might be time for a new Avatar... something with [font="Arial Black"]M3[/font] in it, eh? 🙂

    --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)

  • March 15, 2009 at 11:25 am

    #959935

    Stick with your RBAR animation. It's extremely cool.

    how many times those numbers of milliseconds are going to be used as part of a function

    This is one of the reasons that inline table valued functions are becoming my new best friend.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 11 posts - 16 through 25 (of 25 total)

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