Post reply

How SQL-SVR Execute Query? Filter or Join First?

  • December 8, 2003 at 9:06 pm

    #83093

    Hi all, does SQL Server filter records before join or join records before filter when execute query?

    For example:

    select HD.id, HD.dDateTrans, DT.ItemID

    from Header HD,

    Detail DT

    where HD.id = DT.id and HD.dDateTrans Between @dDate1 and @dDate2

    How SQL Server execute this query?

    Filter the record in table HD first and then join the result set with corresponding rows in table DT or Join the tables first and the apply filter to the join-products?

    I need to know this because I always heard how join is such an expensive operation and I'm thinking if SQL Server always filter the records before joins then the query will speed up significantly. Am I right to make this assumption? I'm using SQL7, is it the same applies to SQL2K?

    Is there some kind of query-hints or other ways I can use to ensure SQL-Server always filter the records first before perform the join?

    and oh.., I really could use more reading/reference material on this. Could you give me links to such reading materials/reference? 😉

    Thank you. 🙂

    Edited by - dannyp on 12/08/2003 9:11:45 PM

  • December 9, 2003 at 5:35 pm

    #485443

    Put your query in Query Analyzer.

    Choose "Show Execution Plan" from the "Query" menu.

    Execute the query.

    View the excution plan by clicking the "Execution Plan" tab.

    This will show you the steps SQL Server takes to execute the query.

    Hope this helps

    Phill Carter

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

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • December 9, 2003 at 6:29 pm

    #485448

    Hi Phill, thx for your reply.

    okay, let's say the execution plan tells me that SQL Server will join the 2 tables first before it filter out unwanted records from the result set.

    What can I do to make SQLServer do it otherwise?

  • December 9, 2003 at 6:41 pm

    #485450

    Can you paste your query and table structure so I can base my suggestions on something?

    Hope this helps

    Phill Carter

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

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • December 9, 2003 at 7:04 pm

    #485451

    Actually I'm not referring to any specific query here, Phill.

    I just wanted to know the strategy implemented by SQL when parsing and execute a query in general.

    To join or to filter first ... and if there is some kind of query-hint or other ways I could use to force SQL-Server to always filter first and perform join latter.

  • December 9, 2003 at 8:39 pm

    #485458

    quote:

    I just wanted to know the strategy implemented by SQL when parsing and execute a query in general.

    Ok, take a look at the following topics in Books Online,

    "Query Processor Architecture", "Query Fundamentals" and "Query Tuning"

    Also, a good book for diving into the internals of SQL Server is "Inside SQL Server 2000" by Kalen Delaney

    Hope this helps

    Phill Carter

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

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • December 11, 2003 at 1:16 am

    #485662

    ok, thanks ..

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply