SQL Server Performance Forum – Threads Archive
WHERE or INNER JOINI have a query that takes over 6 minutes to fetch 4500 records. It has several INNER JOINS. If I replace them with WHERE clause, will it be faster? I am using MS SQL Server 7.0 and SQL 2000. How does OPTION (LOOP JOIN) affect the performance of a query? The query looks like:- SELECT table1.* FROM
((((table1 INNER JOIN table2 ON table1.a=table2.b ) INNER JOIN table3 ….. )))
ORDER BY x,y,z
OPTION (LOOP JOIN)
If you join two tables, and SQL Server optimizer choose Nested-Loop join, then one table will be selected as outer table and the other as inner table. SQL Server will scan the outer table row by row and for each row in the outer table, it will scan the inner table, looking for matching rows. Nested-Loop join will be chosen if one of the tables is small and the other table has an index on the column that joins the tables. The query optimizer usually selects the best execution plan for a given select statement, it is not necessary to change the kind of join, but sometimes it can be useful. You can enforce the desirable join type with OPTION clause. http://www.sql-server-performance.com/hints_join.asp
Fyi. Satya SKJ
Contributing Editor & Forums Moderator
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
To the best of my knowledge, changing from INNER JOINS to a series of where statements will not improve performance (i believe INNER JOIN is the ANSI standard) Regarding the Loop Join- personally, i would leave it up to SQL server to decide what kind of join to use. It might be a good idea to make sure you have good indexes on the tables you are joining to help improve performance ‘I reject your reality and substitute my own’ – Adam Savage
SQL Server will treat
WHERE a.col = b.col and
INNER JOIN b
ON a.col = b.col equally and will produce identical execution plans. Both are valid statements and comply to the ANSI standard. Personally I favor the explicite INNER JOIN variante. —
Microsoft SQL Server MVP
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)
Another side effect of specifying an option for the join type is I believe it could also change the order in which sql server tries to join the tables, which could have a negative impact on performance as well. I know this to be true when you use join hints to change the join type in this manner…
inner loop join tableB on(…)
inner loop join tableC on (…) …but I’m uncertain if this is true for specifying join type w/option. Regardless, take out the option and compare the exec plans, and see if the qry runs any faster.
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=11286 Madhivanan Failing to plan is Planning to fail
6 minutes to complete a query for that few rows seems like an exceptionally long time so I don’t want to miss the obvious, you need to make sure that you have indexes on the fields being joined and any fields that you are using in WHERE clauses to speed things up. Have they been defragmented and or rebuilt lately, or are they a mess?