Query efficiency

Question

I am wondering which is the best way to query data from a large database.

Say I have a requirement to get all a list of all users who live in the United States, along with their orders and the products belonging to their orders. For simplicity, we have a user table, which has a CountryId in that table... and then an Order table, with a userId.. then maybe an OrderProduct table to list many products to an order (and many orders can contain the same product).

My question is, would it be better to maybe create a temp table by

SELECT userId FROM dbo.User WHERE countrId = @CountryId

We now have the relevant users in a temp table.

Then, do a

Select p.ProductDescription ... 
From @TempTable tmp 
INNER JOIN Order o 
ON o.UserId = tmp.UserId 
INNER JOIN OrderProduct op
ON op.OrderID = o.OrderId
INNER JOIN Product p
ON p.ProductId = op.ProductId

So, what I am doing is getting the users I need.... and moving that into a temp table, then using that temp table to filter the data for the main query.

Or, is it as efficient, if not more, to just do it all in one...

Select ... from User u
INNER JOIN Order o
....
WHERE u.UserId = @UserId 

?

Answer 1

In general you want to write your entire request in one query, because that gives the database query optimizer the best possible chance of coming up with the most efficient possibility. With a decent database, it will generally do a wonderful job at this, and any effort on your part to help it along is more likely to hurt than help.

If the database is insufficiently fast, first look into things like whether you have the right indexes, tuning your database, etc. Those are the most common causes of problems and should clear up most remaining problems quite promptly.

Only after you have given the database every chance to get the right answer in the right way, should you consider trying to use temp tables to force a particular query plan. (There are other reasons to use temp tables. But for getting good query plans, it should be a last resort.)

There is an old pair of rules about optimization that applies here in spades.

1. Don't.
2. (For experts only.) Not yet.

Answer 2

You could create a view that holds the data you need.

A view is created like this:

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

You can then query the view you created just as you would query a table.