Where clause with nested loop join

Question

Post reply

Where clause with nested loop join

arup chakraborty

Hall of Fame

Points: 3640
More actions
September 1, 2010 at 4:43 am

#223291

Hi everybody,
I have a SQL SELECT query which has more than 200 line. Due to performance issue, I was trying to convert the hash join to nested loop join with OPTION (LOOP JOIN). Without WHRE clause, there is no syntax error, but while using WHERE clause, its throwing error.
Could you plz tell what may be the syntax?
My suntax:
select * from A inner join B on
(A.col1= B.col1) OPTION (LOOP JOIN) --Working fine
where a.col2=0 --Not working
Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 1

HI All, Got it.

It should be

select * from A inner join B on

(A.col1= B.col1)

where a.col2=0 OPTION (LOOP JOIN)

Thanks.

Now I am coming ot the original query. This is working fine in Oracle but create a time out in SQL server. The SELECT query is more than 200 lines. The execution plan is very much gibberish. Any idea how to proceed to this performance problem?

Thanks in advance.

HowardW One Orange Chip Points: 29109 More actions · Answer 2

It's not gibberish, if you post the execution plan XML along with the actual query people will likely be able to suggest improvements - for a start, why are you using join hints? In nearly every situation, assuming statistics are up to date the optimiser will pick the best plan on its own.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

arup_kc (9/1/2010)
Any idea how to proceed to this performance problem?

First things first, remove the hint.

If SQL is using a hash join it's because it thinks that there are too many rows for a loop join to be optimal. Take out the hint, then please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

p.s. http://sqlinthewild.co.za/index.php/2009/11/24/the-most-optimal-join-type/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass