Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Which WHERE clause would be faster?

    Hypothetically,

    If I had a WHERE clause that had to compare a string to another string would it be faster one way or another if I broke it down to three different, smaller searches?

    An example:

    Code:
    WHERE a = 'abc'
      OR  a = 'def'
      OR  a = 'ghi'
    
    as opposed to:
    
    WHERE a = 'abcdefghi'
    ddave

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    As given, these two where clauses would give you very different results. When thinking of an index, think of a phone book. You can think of a phone book as a table that is indexed on last names. If you were searching for all people with a last name of "Anderson", you would have an easy time doing that. You would look at a few pages at the front of the phone book, and be done with it. If you were told to look for all people with a first name of "Dave", then it would get considerably harder. In this case, the index does not help you whatsoever. The search would take you through every single page of the phone book.

    This is the basic principle of indexing, and why queries should ideally be using an indexed column in the where clause.

    In most RDBMSs, a query written like
    Code:
    where a = 'z'
       or a = 'y'
       or a = 'x'
    would generate the same query plan as
    Code:
    where a in ('x', 'y', 'z')
    In short, take a peek at the estimated execution plan for your two queries. If you see a lot of arrows going straight down, then the indexes are not getting used. If you see many arrows that have a kink in them, then the indexes are getting used. Of course, sometimes, you just have to bite the bullet, and accept a monster query like searching for all "Dave"s in the phone book. Some queries just can not be helped. Hope this helps.

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    Thanks for the reply. For those interested I found an interesting link that covers SET SHOWPLAN_ALL to analyze a query's execution and its use of resources.

    http://doc.ddart.net/mssql/sql2000/h...t-set_365o.htm

    ddave

Posting Permissions

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