Improving performance on joins to large tables.

Total article views: 495 | Views in the last 30 days: 39

 Rate this |   Join the discussion |  Briefcase

In your tenure as a DBA/developer, you've probably been asked to write dozens of stored procedures similar to this:

CREATE PROCEDURE ContactInfo
@ContactID int
AS
SELECT c.Name, a.Address, p.Phone
FROM Contact c
LEFT JOIN ContactAddress a
ON c.ContactID = a.ContactID
LEFT JOIN ContactPhone p
ON c.ContactID = p.ContactID
WHERE c.ContactID = @ContactID

But there is a more efficient way to write such a query.....particularly if you are joining onto very large tables.  It involves using derived tables with the stored procedure parameters imbedded within them, as the following script shows.  In my tests, I ran this query with STATISTICS TIME set on, and it reduced the CPU Time and Elapsed Time from 16 ms to 0 ms, and 263 ms to 15 ms, respectively.

(Note: if you're running version 2000, you can improve this even more by rewriting the derived tables as UDFs accepting a @ContactID parameter.)

Total article views: 495 | Views in the last 30 days: 39