Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    77

    which is faster?

    Hi to all,

    When trying to query for a certain record, which method is usually faster:

    1.) Querying for a record by using a single field in the WHERE clause of your query
    Ex: SELECT * FROM Table1 WHERE col1 = 'a';
    OR

    2.) Querying for a record by adding an AND clause in the WHERE clause of your query
    Ex: SELECT * FROM Table1 WHERE col1 = 'a' AND col2 ='b';

    Is it right to conclude that method 1 is a bit faster than method 2 because it does not have the AND keyword and does not need to do a comparison?

    Thanks and god bless
    Programming is fun!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope. What if 90% of the values for col1 are 'a' and col2 is unique?

  3. #3
    Join Date
    Mar 2007
    Posts
    77
    Could you explain the usefullness of the UNIQUE attribute? How it also affects a certain query?

    thanks
    Programming is fun!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What I meant was query 1 would return 90% of the table and query 2 would return between 1 and 0 rows.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    if you want a specific record/row then the more you can narrow down the rows returned in the SQL the better. using the SQL engine to do the filtering is going to be far quicker than iterating through all the rows returned to find the row you want.

    Why?
    the SQL engine is going to be better optimised for that task
    you are pumping less data up and down the network
    you are doing less work on the client workstation

    If you know that only one item say Col1 will give you the row then there is no need to further specify.. but I doubt it will make a significant difference to performance. other things to think of in performance terms are the trade off between number of indexes, which may slow down inserts, edits and deletes OR may speed up fetches of data. If a column is being heavily used in where clauses (or for that matter Join clauses) then it probably should be indexed
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2008
    Posts
    89
    I think the assumption that the logical AND calculation would reduce performance stems from normal procedural languages, but here also a very antiquated view, as nowadays this doesn't really come into any effect anymore.
    "My brain is just no good at being a relational Database - my relations suck real bad!"

Posting Permissions

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