Geek Answers Handbook

Does JOINs make a difference?

Let's say I have a query like below:

SELECT t1.id, t1.Name FROM Table1 as t1 --800,000 records INNER JOIN Table2 as t2 --500,000 records ON t1.fkID = t2.id INNER JOIN Table3 as t3 -- 1,000 records ON t1.OtherId = t3.id

I would see a performance improvement if I reordered my connections to Table2 and Table3. See below:

 SELECT t1.id, t1.Name FROM Table1 as t1 --800,000 records INNER JOIN Table3 as t3 -- 1,000 records ON t1.OtherId = t3.id INNER JOIN Table2 as t2 --500,000 records ON t1.fkID = t2.id

I heard that the query optimizer will try to determine the best order, but it does not always work. Is the version of SQL Server you are using?

+6
sql-server
source share
3 answers

The order of joins does not matter.

What matters is that your statistics are updated.

One way to check your stats is to run the request in SSMS and include the actual execution plan. If the approximate number of lines is very different from the actual number of lines used by any part of the execution plan, then your statistics are out of date.

Statistics are restored when recovering related indexes. If your maintenance window allows, I would update the statistics every night.

This will update statistics for all tables in the database:

 exec sp_MSforeachtable "UPDATE STATISTICS ?"
+6
source share

The order of joins matters only if you specify OPTION (FORCE ORDER) . Otherwise, the optimizer will modify your request depending on what it considers the most effective.

In fact, there are certain cases where I find that I need to use FORCE ORDER , but, of course, they are few and far between. If you are not sure, just SET STATISTICS [TIME|IO] ON and see for yourself. You will probably find that your version is slower than the optimized version in most, if not all cases.

+3
source share

The query optimizer should easily handle them as one and the same query, and best fulfill it.

Most statistics information is greater than the number of entries. For example, if the vast majority of values ​​in t1.fkID are identical, this information can greatly affect QO.

+1
source share

More articles:


All Articles