Query Timeout

  • nice one.

    I've done some testing with clustered and non-clustered indexes and combinations of both and the results are the same.

    I was also thinking this might be a target for a nice article.

    How do you know the constant eval only happens one?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • The reason I ask the above and I guess it's related to the OP.

    for me with a table of 1mil rows I get very much the same results for the below two queries.

    SELECT RowNum,SomeInt

    FROM CTSTest_OR

    WHERE @temp = 0

    OR (SomeInt) = @temp

    SELECT RowNum,SomeInt

    FROM CTSTest_OR

    WHERE (SomeInt) = @temp

    OR @temp = 0

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Test code:

    CREATE TABLE #tmpTab (

    column1 int,

    column2 int

    )

    INSERT INTO #tmpTab

    SELECT A.colorder, B.status

    FROM syscolumns A

    INNER JOIN syscolumns B

    ON A.colorder = B.colorder

    This is what I tried:

    SET STATISTICS IO ON

    SELECT 1

    WHERE EXISTS (

    select 1

    from #tmpTab

    where column1 = column2

    )

    SELECT 1

    WHERE EXISTS (

    select 1

    from #tmpTab

    where 1 = 1

    )

    SELECT 1

    WHERE EXISTS (

    select 1

    from #tmpTab

    where column1 = column1

    )

    And these are the results:

    Table '#tmpTab'. Scan count 1, logical reads 575, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table '#tmpTab'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table '#tmpTab'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Somehow the engine recognises "tautological" expressions and evaluates them only once.

    -- Gianluca Sartori

  • I'm not 100% that that code is indicating what we thinking.

    I tested it on my table:

    where RowNum = SomeInt -- 1678

    where 1 = 1 -- 5

    where RowNum = RowNum -- 5

    where RowNum = 1 -- 3

    So even though I did static verse a static it seems that the last one had the least.

    I wonder however if that is maybe not because there are statistics provided for that column because it in a key in an index so it knows the distribution hence need less reads?!? what do you think?

    OK just did some reading and yeah it seems to be Pages.

    So that doesn't necessarily mean that it's only evaluated once I don't think...

    Wow this is fun he he he

    I'm gonna check it out in profiler and see if there is more light shed on this

    OK I just check it again on a non indexed column and the reads jump up VERY VERY high.

    To be honest I'm not 100% clear if the "Logic Reads" means pages or if it means nodes or rows?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • I can't answer any of your questions, I can only confirm that short circuit is one of the features implemented in sqlserver:

    http://technet.microsoft.com/it-it/cc678236(en-us).aspx

    Search in this page for shor circuit and you'll find this answer by Nigel Ellis, development manager for the SQL Server Query Processor team:

    A: There wasn't any change made in this area; the semantics of SQL does allow short-circuit evaluation, and we take advantage of this in our optimization engine.

    Google returns lots of results for "sql server short circuit". Lots of forums debate this point, with different conclusions.

    -- Gianluca Sartori

  • Gianluca Sartori (5/20/2009)


    Other cases with the same test data:

    Divide by zero error:

    select *

    from @tmpTab

    where (column1/column2 = 1)

    OR column1 = column1

    No error thrown:

    select *

    from @tmpTab

    where (column1/column2 = 1)

    OR 1 = 1

    Conclusions:

    1) Order matters in OR predicates

    2) When constants are involved, the expression is evaluated only once

    Really really interesting! I'll take the time to write an article on this...:-)

    Not always!!, I've found the following piece of code on http://beingmarkcohen.com/?p=62 that does not short circuit.

    CREATE TABLE ShortCircuit (

    ID int )

    GO

    INSERT INTO ShortCircuit

    SELECT 1 UNION

    SELECT 2 UNION

    SELECT 3 UNION

    SELECT 4 UNION

    SELECT 5 UNION

    SELECT 6

    GO

    DECLARE @ID int

    --SET @ID = NULL

    SELECT ID

    FROM ShortCircuit

    WHERE @ID IS NULL OR ID = 1/0

    DROP TABLE ShortCircuit

    --Ramesh


  • This is because you're working with constant values: 1/0 is evaluated before the query is executed, I think during the execution plan elaboration.

    In my test code this works:

    DECLARE @id int

    SELECT 1

    WHERE EXISTS (

    select 1

    from #tmpTab

    where @id is null

    OR column1 / column2 = 1

    )

    and I have lots of column2 = 0.

    -- Gianluca Sartori

  • too_sweeeeeeeet (5/12/2009)


    I have a function that contains the following query which is always timimig out.

    select distinct @Result = sum( column1 )

    from table (nolock)

    where ((column2 = @param1) or (@param1 = 0))

    What you have there is what I call a 'catch-all' query. When the optimiser generates a plan for that, it has to find a safe plan, one that can be reused and will work regardless of what parameters are passed. This tends to result in very poor exec plans.

    Think about it, the optimal exec plan will be completely different in the case where @param1 is 0 from cases where it's something other than 0.

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for your opinion, Gail: I longly wished you came across this thread to shed some light.

    Can you help us with some ideas on the short circuit side?

    Thanks

    Gianluca

    -- Gianluca Sartori

  • I tried the same test code on a SQLServer 2000 box and I get the same results (original tests had been made on a 2005 x64 box).

    No news, just a side note...

    -- Gianluca Sartori

  • awesome Gail found us he he he

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • She didn't actually find us, but she was linked here from this http://www.sqlservercentral.com/Forums/FindPost720460.aspx thread, that apparently has become a sort of "bulletin board" because all major experts are participating...

    Hope you don't mind, Gail.

    -- Gianluca Sartori

  • That one is called "The Thread".

    By the way, while you guys are testing, maybe you should also check out whether short-circuiting also can happen with logical ANDs, i.e. when the first expression evaluates to FALSE?

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Gianluca Sartori (5/20/2009)


    Thanks for your opinion, Gail: I longly wished you came across this thread to shed some light.

    Can you help us with some ideas on the short circuit side?

    Thanks

    Gianluca

    Can the optimizer use short-circuiting optimizations? Yes.

    Will it always? No (this forum is full of examples of folks tripping over it). Sometimes it's more efficient not to. Also, this gets into just exactly what "short-circuiting" means in complex/compound set processing queries. It's not as clear as you might think.

    If it does short-circuit, will it always test the first clause first? No.

    Can I control the order of short-circuiting? Not if the optimizer does it, only if you do explicit short-circuiting yourself(usually involves nested CASE tests).

    This may seem trivial or obvious, but it is by far the most common mistaken assumption (usually implicit) about SQL and short-circuiting. For instance the following code has been seen many times on the boards and forums:

    SELECT *

    From syscolumns

    Where IsNumeric(name) = 1

    And Convert(Float, name) > 1.5

    Invariably, this query will fail with the error "Error converting data type nvarchar to float." Not because IsNumeric doesn't work right (as is often assumed) but because 1) you can't rely on short-circuiting, and 2) the code order does not determine the execution order.

    Now this, usually does work:

    SELECT *

    From syscolumns

    Where IsNumeric(name) = 0

    OR Convert(Float, name) > 1.5

    but it is completely unreliable.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Gianluca Sartori (5/20/2009)


    Can you help us with some ideas on the short circuit side?

    Maybe.

    I've seen SQL short circuit some expressions, emphasis on some. Mostly in cases of expressions based on constants and 'trivial' expressions (1=1, col1 = col1).

    Normally, once the expressions become non-trivial, it's the indexes which affect which operations run in which order.

    ORs are slightly different, because they're almost the same as the union of two resultsets and, depending on how the optimiser decides to behave, order may or may not matter. If it decides to scan an index then the order may matter (especially if one of the expressions is trivial or always true). If it decides to break up the query, seek on two indexes and concatenate the two afterwards, order won't matter.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 16 through 30 (of 57 total)

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