SQL Perfromance Reference: Joins vs. Seperate Queries
time to read 6 min | 1134 words

I need to find the reference / reasoning for the following argument:

A single query using a join will be more efficent in server resources than several queries to get the same information.

For instance, consider this query:

SELECT

      Users.Name,

      ProductLicenses.StartDate,

      ProductLicenses.EndDate

FROM Users JOIN ProductLicenses

ON Users.Id = ProductLicenses.UserId

WHERE Users.Id = @UserId

Versus this:

SELECT

      Users.Name

FROM Users WHERE Users.Id = @UserId

And then this:

SELECT

      ProductLicenses.StartDate,

      ProductLicenses.EndDate

FROM ProductLicenses

WHERE ProductLicenses.UserId = @UserId

I know that the first one should be more efficent, but I can't find out the documentation for it. Server round trips is one consideration (although it can be mitigated with multiply result sets), but the concern here is the performance of the server only.

Note: I'm talking here about 2 - 4 tables joins, top, properly indexed.

Tweet Share Share 0 comments
Tags: