Results 1 to 8 of 8

Thread: Optimal SQL

  1. #1
    Join Date
    Apr 2003
    Location
    CA, East Bay
    Posts
    13

    Question Unanswered: Optimal SQL

    Anybody know why Query B is so much more expensive than Query A? Would you agree that Query C is the best bet?

    Goal:
    Return 0 results

    Given:
    CustomerID will never be '99999'
    CustomerID is the primary key


    A - SELECT * FROM Customers WHERE CustomerID=99999
    B - SELECT * FROM Customers WHERE CustomerID<>CustomerID?
    C - SELECT TOP 0 * FROM Customers

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    in A you are scanning a select range of rows from a clustered index. SQL server is doing a binary search and thus able to eliminate most of the data within a couple of evaluations.

    in B you are scanning all rows of a clustered index. Since you are asking SQL server to compare two attributes of a record, each record must be evaluated.

    in C you are scanning an internal table.

    I personnaly prefer select * from <table name> where 1 = 2.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Apr 2003
    Posts
    18
    HI,
    Agree with Paul .
    select * from <table name> where 1 = 0
    Cheers
    Gola
    Originally posted by Paul Young
    in A you are scanning a select range of rows from a clustered index. SQL server is doing a binary search and thus able to eliminate most of the data within a couple of evaluations.

    in B you are scanning all rows of a clustered index. Since you are asking SQL server to compare two attributes of a record, each record must be evaluated.

    in C you are scanning an internal table.

    I personnaly prefer select * from <table name> where 1 = 2.

  4. #4
    Join Date
    Apr 2003
    Location
    CA, East Bay
    Posts
    13

    Talking Makes Sense

    Thanks Paul. What you said makes perfect sense. I knew how SQL impelemented indexing, but failed to see the obvious need to skip the indexing and go straight to a table scan and scan each record individually when comparing one field to another.

    Gola's example would seem to be more efficient, as would option C or as a friend pointed out, even...

    SELECT * FROM Customers WHERE CustomerID IS NULL.

    ...would work since we are looking at the primary key.

    Thanks guys.

    Moki

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I don't agree with your friend. You will still be scanning part of the index. If the table doesn't have an index you will be scanning the table. My suggestion will always scan an internal table regardless.

    the only diffrence between my suggestion and Gola's is the numbers used to generate a false condition.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Apr 2003
    Location
    CA, East Bay
    Posts
    13
    I'll need to think about that last one...maybe I don't understand MSSQL's implementation like I thought. I would have thought the primary key was always indexed and so checking for NULL would be the same as running thru the B-Tree looking for a value.

    In either case, how about option C, "SELECT TOP 0....."?

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Yes, in SQL server the primary key will have some type of index. My point was that your friends suggestion will cause a partial index scan when a table has a usable index OR a table scan if no index is present or no existing indexes are present to match your where clause.

    Bottom line, C is the best choice of the three.
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Apr 2003
    Location
    CA, East Bay
    Posts
    13

    Thumbs up

    Ok, thanks. I thought about it (Option C) and the suggestion from you (and Gola), and think I like y'alls better. Only because I'm questioning the use of "...TOP 0..." and its conformance (or possible lack thereof) to the ANSI-92 standard...just in case that becomes a customer's constraint in the future.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •