Post reply

Strange Behavior of Query Optimizator

  • September 8, 2006 at 12:46 am

    #170244

    Well, some days ago I found one strange thing in SQL Server's Query optimization behavior.

    The only way I can explain this behavior is that SQL Server doesn't optimize simple Queries . Sounds stupid but that's all a can say. May be I'm missing something and you will help? So here it is.

    I have a Test table with 3 fields, one of them is PK.

    CREATE TABLE Test (

                            id int IDENTITY(1,1),

                            field1 int,

                            field2 datetime,

                            PRIMARY KEY CLUSTERED (id)

                      )

    I need to find minimal value of id when field1 equals to something, so the best way to do that is

     

    <Q1>

    SELECT TOP 1 id FROM Test WHERE field1 = <something> ORDER BY id

     

    The Execution Plan for this is:

     

    SELECT (Cost:0%) -> TOP(Cost 0%) -> Clustered Index Scan (PK)

     

    Now comes the interesting thing. Let’s assume that I don’t want to use “TOP”. In this case I must use MIN function and so I must write this

     

    <Q2>

    SELECT MIN(id)

    FROM Test

    WHERE field1 = <something>

     

    Now look at the Execution Plan.

    SELECT (Cost:0%) -> Steam Aggregate(Cost 0%) -> Clustered Index Scan (PK)

    There is not more top, instead a Steam Aggregate appeared, but even so it looks right.

    But what if we add something stupid to query?

     

    <Q3>

    SELECT min(id)

    FROM Test

    WHERE field1 = <something>

    GROUP BY field1

     

    This is stupid because logicaly there is no need of Group by because the WHERE clause already filters by field1 and this means that there will be only one group.

    But what happens?

    SELECT (Cost:0%) -> TOP(Cost 0%) -> Clustered Index Scan (PK)

    Strange. Looks like SQL Server understood what I called stupid. So in case when we add this “stupid” GROUP BY. SQL Server transforms the Query to the TOP Version above. But when I have only MIN without GROUP BY it can’t optimize?

    So now the Question. Can anybody explain how this can happen, that with realy simple Query like <Q1>, SQL Server doesn’t know how to optimize, but in Q3 with additional grouping it can transform the Query to realy optimized way.

     

  • September 8, 2006 at 3:28 am

    #659296

    Anybody?????

     

    ---------------------------------

Viewing 2 posts - 1 through 2 (of 2 total)

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