Home Forums SQL Server 2008 SQL Server 2008 Performance Tuning tuning query using max() over(partition by) clause RE: tuning query using max() over(partition by) clause

  • February 1, 2014 at 1:10 am

    #1685281

    venus.pvr (1/31/2014)

    anyone can think of anything for this one. I have n't been able to figure it. Thanks

    Your query almost certainly isn't doing what you are expecting. The column Usr is unique. There's no point in looking for three rows for the same usr and different values of val1 and full-outer-joining them because there will always be only one, even in 60 million rows. Your query is equivalent to this:

    SELECT

    val1 = CASE WHEN val1=1 THEN val1 END,

    val2 = CASE WHEN val1=3 THEN val2+1 END,

    val3 = CASE WHEN val1=0 THEN val3 END,

    MinSimilarity = CASE WHEN val1=1 THEN val1 WHEN val1=3 THEN val2+1 WHEN val1=0 THEN val3 END,

    MaxSimilarity = CASE WHEN val1=1 THEN val1 WHEN val1=3 THEN val2+1 WHEN val1=0 THEN val3 END,

    maxval = CASE WHEN val1=1 THEN val1 END

    FROM TestExecPlans

    - which is a heck of a lot cheaper, but still pointless.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]