SQL Server Performance

SELECT TOP 1 1

Discussion in 'T-SQL Performance Tuning for Developers' started by pssousa, Aug 11, 2006.

  1. pssousa New Member

    Hello!

    I want to check if a given phone number exists in the table. In SQL Server 2000 (or MSDE) which one of these two queries should we use (which is better/faster) or is it the same thing?
    #1 SELECT TOP 1 1 FROM Table WHERE Number='XXX'
    #2 SELECT TOP 1 Number FROM Table WHERE Number='XXX'

    Thank you in advance,
    Pedro
  2. dineshasanka Moderator

    Are sure you are getting same results

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

  3. Jack Vamvas Member

    Assuming you're running an Index Scan , it should be the same
  4. FrankKalis Moderator

    If this is a test for existance, why not use EXISTS()?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  5. pssousa New Member

    Yes, dineshasanka, I just want to check if the number exists in the table.

    No, Jack, I'm not using any indexes.

    I've been told that if you use query #1 in VB to load, e.g., a RdoResultSet, SQL Server creates a temporary table and then a cursor. If you use query #2, it is faster because no temporary table is created. Is this true/does it make sense?

    Thank you once more!
  6. mmarovic Active Member

    As Frank mentioned:


    if exists(SELECT * FROM Table WHERE Number='XXX') return 1
    else return 0

  7. Roji. P. Thomas New Member

    quote:Originally posted by mmarovic

    As Frank mentioned:


    if exists(SELECT * FROM Table WHERE Number='XXX') return 1
    else return 0

    I'd recommend using a constant instead of *

    if exists(SELECT 1 FROM Table WHERE Number='XXX') return 1
    else return 0

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  8. mmarovic Active Member

    There is no difference in execution plans between implementations mentioned.
  9. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />I'd recommend using a constant instead of *<br /><br />if exists(SELECT 1 FROM Table WHERE Number='XXX') return 1<br />else return 0<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />So do I, but the actual difference is hardly measurable. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  10. FrankKalis Moderator

    quote:Originally posted by mmarovic

    There is no difference in execution plans between implementations mentioned.
    You're right there is no difference.

    I think, I've mentioned this here before. The difference happens while parsing a query. The parser always expands a * into the actual column list. So it needs to retrieve this column list from somewhere. This is not needed when using a constant. So using a constant requires less computation and might parse a tick faster than the *.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  11. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"> The difference happens while parsing a query. The parser always expands a * into the actual column list. So it needs to retrieve this column list from somewhere. <hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />And they do call it the "Star Expansion"[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  12. SQL_Guess 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 Roji. P. Thomas</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"> The difference happens while parsing a query. The parser always expands a * into the actual column list. So it needs to retrieve this column list from somewhere. <hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />And they do call it the "Star Expansion"[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />... soon to be renamed to "Disa-star expansion" ?<br /><br />Sorry - couldn't help it [}<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  13. ndinakar Member

    There is no computation as EXISTS merely checks for existence of the record. So it doesnt matter if you use "SELECT * FROM .." or "SELECT 1 FROM .."

    From BOL:

    When a subquery is introduced with the keyword EXISTS, it functions as an existence test. The WHERE clause of the outer query tests for the existence of rows returned by the subquery. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
  14. mmarovic Active Member

    quote:Originally posted by FrankKalis
    The difference happens while parsing a query. The parser always expands a * into the actual column list. So it needs to retrieve this column list from somewhere.
    In mssql server 2000 and probably 7 parser doesn't check for object existence.

    BOL - Create procedure topic:


    quote:
    Referencing Objects
    SQL Server allows the creation of stored procedures that reference objects that do not yet exist. At creation time, only syntax checking is done. The stored procedure is compiled to generate an execution plan when executed, if a valid plan does not already exist in the cache. Only during compilation are all objects referenced in the stored procedure resolved. Thus, a syntactically correct stored procedure that references objects which do not exist can be created successfully, but will fail at run time because referenced objects do not exist. For more information, see Deferred Name Resolution and Compilation.

    Deferred Name Resolution and Compatibility Level
    SQL Server allows Transact-SQL stored procedures to refer to tables that do not exist at creation time. This ability is called deferred name resolution. If, however, the Transact-SQL stored procedure refers to a table defined within the stored procedure, a warning is issued at creation time if the compatibility level setting (set by executing sp_dbcmptlevel) is 65. An error message is returned at run time if the table referenced does not exist. For more information, see sp_dbcmptlevel and Deferred Name Resolution and Compilation.

    Deferred Name Resolution and Compilation
    When a stored procedure is created, the statements in the procedure are parsed for syntactical accuracy. If a syntactical error is encountered in the procedure definition, an error is returned and the stored procedure is not created. If the statements are syntactically correct, the text of the stored procedure is stored in the syscomments system table.

    When a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the syscomments system table of the procedure and checks that the names of the objects used by the procedure are present. This process is called deferred name resolution because objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed.

    In the resolution stage, Microsoft® SQL Server™ 2000 also performs other validation activities (for example, checking the compatibility of a column data type with variables). If the objects referenced by the stored procedure are missing when the stored procedure is executed, the stored procedure stops executing when it gets to the statement that references the missing object. In this case, or if other errors are found in the resolution stage, an error is returned.
    So parsing doesn't expand star to column names because it doesn't even check existence of database object at that time. That's what BOL says. Now, follows speculation, I haven't found proof for my theory yet:

    Star (*) might be expanded during resolution stage. However, for parsing finite automates are usually used. FA are not aware of context, so if * is expanded to column names at that time they wouldn't be aware of difference between regular 'select *' and 'select *' inside 'exists' operator. On the other hand I expect that resolution phase uses context aware algorithm, so it can distinguish and handle properly two cases mentioned. Resolution is kind of compilation. If I remember well (20 years past since I studied computer science mathematic - Formal languages theory) and the same 'grammer' is applied, for translation is used 'push automate' or something with similar name. PAs important characteristic is that they are context aware.

    I believe star expansion myth comes from sql server 6.5 version, where stored procedures where precompiled, so it might be true that parser was responsible for expanding * token then.


  15. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />I believe star expansion myth comes from sql server 6.5 version, where stored procedures where precompiled, so it might be true that parser was responsible for expanding * token then.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />No, I'm afraid, that's not a myth. Well, at least not, if you tend to believe that the dev lead of the query optimization team, Conor Cunningham, knows what he is talking about. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />When the parser faces a * it doesn't have enough context to know that 1, * or whatever doesn't make any difference. And because of that it expands the * into the actual column list. This is still true in current versions of SQL Server. The thread confirming that was in the private MVP newsgroups in December 2005. I'm afraid, I can't just copy and paste the answer as I guessing it is under MVP NDA.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  16. mmarovic Active Member

    <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 /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />I believe star expansion myth comes from sql server 6.5 version, where stored procedures where precompiled, so it might be true that parser was responsible for expanding * token then.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />No, I'm afraid, that's not a myth. Well, at least not, if you tend to believe that the dev lead of the query optimization team, Conor Cunningham, knows what he is talking about. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />When the parser faces a * it doesn't have enough context to know that 1, * or whatever doesn't make any difference. And because of that it expands the * into the actual column list. This is still true in current versions of SQL Server. The thread confirming that was in the private MVP newsgroups in December 2005. I'm afraid, I can't just copy and paste the answer as I guessing it is under MVP NDA.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Well, in that case either BOL is wrong or Conor Cunningham. Once again BOL says database objects are parsed before storing and checked the syntax. Object references are resolved during compilation, which means before stored procedure without exec plan in cache is executed.<br />The contradiction may come from loosely use of the term parsing. Parsing before procedure is stored, according to BOL, doesn't check referenced objects. It might be that microsoft applied another 'parsing' at the moment execution plan is built. <br /><br />The sentence:<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />When the parser faces a * it doesn't have enough context to know that 1, * or whatever doesn't make any difference. And because of that it expands the * into the actual column list. <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />doesn't make a sense to me. Proper implementation (IMO) would be:<br /><br />1. Parsing phase: '*' token is recognized as 'star' token. Period. It is obviously true for parsing that preceds storing the procedure.<br />2. Compilation (translation) phase: Context is known at this moment so 'star' token is not expanded in case it is used in context of exists operator.<br /><br />If it there is another different parsing before compilation that includes star resolution, I don't think it is very smart implementation.
  17. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Well, in that case either BOL is wrong or Conor Cunningham.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Given that alternatives, I would almost always say BOL is "incorrect".<br /><br />But I guess you're right, "parsing" might be used a little bit loosely. In any case, we're guessing about implementation details only MS can answer. And I'm pretty certain, they won't do so in public. <br /><br />For me bottomline is, that star expansion does happen at some point before generating the actual execution plan. This plan, however, is absolute identical for SELECT 1, SELECT * or SELECT 'whatever' when in an EXISTS clause. And we're also guessing about a hardly measurable difference. <br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />If it there is another different parsing before compilation that includes star resolution, I don't think it is very smart implementation.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />The thread I have in mind also mentioned the MS is always looking for smart people to hire. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  18. Roji. P. Thomas 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 FrankKalis</i><br />No, I'm afraid, that's not a myth. Well, at least not, if you tend to believe that the dev lead of the query optimization team, Conor Cunningham, knows what he is talking about. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Infact I had raised this question in the private NG, following a discussion in a public news group, and it was confirmed by Conor that Star Expansion and Shrinking occurs when you use SELECT * in EXISTS. <br /><br />I am not sure whether it actually happens at the parsing stage.<br /><br />Somehow I had lost the thread. Frank, Do you have the link to the public NG posting discussed there?<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  19. FrankKalis Moderator

  20. Roji. P. Thomas 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 FrankKalis</i><br /><br />This is the link you've posted in your question:<a target="_blank" href=http://tinyurl.com/ake27>http://tinyurl.com/ake27</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Well. Thanks Frank. So here is the statement by Conor for brevity.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />It is functionally equivalent from the end plan perspective. <br /><br />For compilation time, it may be ever-so-slightly faster to use "SELECT 1 <br />FROM T" because it does not need to expand the * into a column list and then <br />prune it away. Obviously it would matter more on tables with more columns. <br /><br />Select '1' is also fine. I use SELECT 1 since it's the smallest thing I can <br />do in an exists subquery - fewer allocations <img src='/community/emoticons/emotion-5.gif' alt=';)' />. <br /><br />Hope that helps. <br /><br />Conor Cunningham <br />SQL Server Query Optimization Development Lead <br />Microsoft <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  21. FrankKalis Moderator

    Interesting. I can't reach the URL right now. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />But it appears that indeed "parsing" was used loosely, because now he's mentioning compilation time.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  22. Adriaan New Member

    Obviously, there must be some parsing going on, because if you do

    WHERE EXISTS (SELECT invalid_column_name FROM valid_table_name)

    ... then you do get an error on the invalid column name.

    Other than that, you can use the name of a nullable column, and EXISTS doesn't care whether there's a null or a fixed value on that column - it checks for the row, not for the column.
  23. Roji. P. Thomas New Member

    quote:Originally posted by Adriaan

    Obviously, there must be some parsing going on, because if you do

    WHERE EXISTS (SELECT invalid_column_name FROM valid_table_name)

    ... then you do get an error on the invalid column name.

    Do you know that parser doesnt care about Invalid column names or table names?

    Try the following.

    SET PARSEONLY ON

    SELECT * FROM Customers C WHERE EXISTS(SELECT XYZ FROM Orders O WHERE O.CustomerID = C.CustomerID)

    SELECT * FROM Customers C WHERE EXISTS(SELECT CompanyName FROM Orders O WHERE O.CustomerID = C.CustomerID)

    SET PARSEONLY OFF

    Both the queries will be parsed successfully parsed because of the feature called Deferred Name Resolution.

    If you execute the above queries the first one will fail with an

    Server: Msg 207, Level 16, State 3, Line 1
    Invalid column name 'XYZ'.

    error. But the second query will succeed even when there was no column named CompanyName in Orders. This is called fancy scoping.




    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  24. Adriaan New Member

    Why don't they add a requirement to the SQL standard that as soon as there is more than one (instance of a) table in the query, then you must predicate columns with the table name or an alias?

    There should not be such a thing as fancy scoping, so that people are forced to resolve ambiguities themselves.
  25. Madhivanan Moderator

Share This Page