Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2010-07-30 : 09:17:10
|
| Hi,The following sql in the where clause of a select query is causing the query to run very slowly...((@FieldId IS NULL) OR (P.PId IN (SELECT PID FROM @MPs)))I now have the following left join in the select query instead of the above sql to make the query perform better:left join @MPs as mp on p.PId = mp.PIdQuestion1:Notice that (@FieldId IS NULL) OR is missing in the left join queryHow do I include it in the left join?Any thoughts please? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-30 : 09:32:35
|
| [code]SELECT ColumnsFROM MyTableA AS A LEFT OUTER JOIN MyTableB AS B ON B.ID = A.IDWHERE (@FieldID IS NULL OR B.ID IS NOT NULL)[/code] |
 |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2010-07-30 : 09:51:35
|
| Hi,Can you please explain why there is the need for:mp.PortfolioId is not null? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-30 : 10:30:49
|
Because your first way was:P.PId IN (SELECT PID FROM @MPs)means: only matching rows wantedLEFT JOIN gives ALL rows and not only the matching ones.Non matching rows will come up with NULL values.So you can use an INNER JOIN to get only matching rows or you can say LEFT JOIN and WHERE mp.Portfolio is not null.Hope you understand.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2010-07-30 : 11:13:56
|
| Thank you |
|
|
|
|
|
|
performance