SQL Server Performance

Filtering question

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by wfsharon, Jun 9, 2008.

  1. wfsharon New Member

    I have a question regarding the best placement of filters in a query.
    For example, a number of queries that we deal with each day involve filtering out data by a certain identifier and a most recent date value. Many joins are usually involved, including multiple joins to the same table (to get, say, values from different time periods into the resultset).
    A date filter can be placed in the JOIN (...ON i.ID = p.ID AND p.PricingDate = (SELECT MAX(PricingDate) FROM PriceHistory WHERE ID = p.ID)
    or in the main SELECT's WHERE clause. (WHERE p.PricingDate = (SELECT MAX(PricingDate) FROM PriceHistory WHERE ID = p.ID)
    The base tables are mostly under 1 million rows but a few can get up to the 10-20 million row range. All have proper primary/foreign keys on the join columns.
    I know that SQL performance is rife with variables between hardware and software, but is there a "best practice" that says one way is better that the other in reducing the amount of data consumed as early as possible to arrive at the final resultset?
    Thanks!
  2. MichaelB Member

    Sharon,
    Weclome to the forum!
    The date filter should be in the Select's WHERE statement unless it in a outer join and needs it to filter the outer join table. I hope this answers your question. You say that "All have proper primary/Foreign keys on the join columns" this does not always equate to having indexes on those columns, but normally do. If that is all OK then you should be fine, but I would never suspect that all indexes are perfect. It is a fine art to get everything just right for all queries.

    Mike

Share This Page