Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    76

    Unanswered: Query Otimization (Urgent)

    Hi All,

    I am new to SQL Server. I want to know about SQL Query execution and compilation sequence.

    Consider the following case:

    SELECT A.COL1, B. COL2
    FROM TAB1 A
    INNER JOIN TAB2 B
    ON A.COL5 = B.COL5
    WHERE A.COL3 = 'A' AND B.COL3 = 'B'

    Now as per my records into TAB1 and TAB2, condition B.COL3 = 'B' can reduse more number of rows then condition A.COL3 = 'A'.
    So, please let me know above way to write the query is proper or not in given senario.

    It would be great if one can provide the details how Sql Server 2000 compile and execute the query and some relavent data to optimize the performance while writting SQL query.

    Thanks in Advance,
    Jai

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Create an index on TAB1 (COL5, COL3, COL1) and an index on TAB2 (COL5, COL3, COL2)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2004
    Posts
    76
    Thanks for your reply.

    My main query is,

    Suppose there are three conditions in WHERE clause like...
    SELECT .....
    FROM ....
    WHERE
    CONDITION1 AND CONDITION2 AND CONDITION3

    Now as per the data in my DB I know that CONDITION2 will reduce more number of rows then CONDITION1 AND CONDITION3. As will CONDITION1 will reduce more number of rows then CONDITION3 but NOT more then CONDITION2.

    So, in such senario, the sequence of the condition mentioned above is proper or not? Is there any specific rule to mention condition in the specific sequence while writting WEHRE clause in T-SQL?

    Please help asap.

    Thanks in advance.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    This is not your father's oracle

    SQL Server does not parse the query from bottom to top like some other products. Simply stated, the optimizer reviews many possible paths, looking at indexes, primary keys, statistics, and a whole slew of other variables and then designs an execution plan.

    An article I read recently stated that prior to SQL 7, if the optimizer came up with alternate plans for a given query, it was considered a bug. With the advent of 7 though, the optimizer became nuch more complex.

    So ... code your queries using ANSI standard joins, place your conditions in the predicate, and leave the optimization to SQL Server!

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are you simply looking to see the order in which the query parts are processed (which you can see via the show plan), or are you looking to actively change the order (which is rarely a good idea)?

    -PatP

Posting Permissions

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