SQL Server Performance

A question about joins.

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by mhenderson, Nov 7, 2006.

  1. mhenderson New Member

    I have a best practices question regarding table joins.

    I am looking at a query where the tables are joined similar to the example below:

    SELECTA.ColumnA, B.ColumnB

    FROM Customers C JOIN
    Addresses A ON A. (C.CustomerID = A.CustomerID) AND (A.DefaultAddress = 1)

    WHERE(A.EffectiveDate < GETDATE())


    Seems the default address flag should go in the where clause.

    Does it really matter? Are there performance advantages one way or another?

    Thanks,

    Mike

    The revolution is just a t-shirt away.
  2. satya Moderator

    That depends whether you can agree the performance of query, have you checked the estimated execution plan in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. Adriaan New Member

    Also, are you sure there is only one row in Addresses, for each CustomerId, where DefaultAddress=1?
  4. mhenderson New Member

    I have not looked closely at the estimated execution plan, but the actual query is pretty slow. I dropped the elements into the where clause and that at first appeared to speed up the query.



    The revolution is just a t-shirt away.
  5. satya Moderator

    How about indexes for the involved tables?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  6. mhenderson New Member

    --Also, are you sure there is only one row in Addresses, for each CustomerId, where DefaultAddress=1?

    There are some cases where a customer has more than one default address listed. I initally started looking at the query to see if I could eliminate the duplicates.

    Mike

    The revolution is just a t-shirt away.
  7. Roji. P. Thomas New Member

    I can almost guarantee you that, you will get the same execution plan(which actually depends on the indexes you have), regardless whether you put condition "A.DefaultAddress = 1".

    But as a best practice, for INNER JOINs, I'd recommend putting the JOIN condition(s) in the ON clause and the FILTER condition(s) in the where clause. Anyway the optimizer is going to rearrange them. With outer joins, its a totally different story.



    Roji. P. Thomas
    http://toponewithties.blogspot.com

Share This Page