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 |
|
Soulweaver
Starting Member
18 Posts |
 Posted - 2004-10-13 : 13:48:34
|
| HelloWe have a strange thing happening in one of our stored procedures. When doing a estimated execution plan for the stored procedure, the primary of the one table being joined keeps on showing missing statistics and giving an estimated cost of 142 for the subtree.The stored procedure ran for about 5 seconds at a time.The join causing the issue looks like this :...LEFT JOIN Element el on (pl.Freq<>5 and pv.Planno=el.Planno and pv.Elno = el.Elno) OR (pm.Planno=el.Planno and pl.Freq=5)...The primary key for the Element table is Planno,ElnoI updated the statistics using 'update statistics', that didn't change the missing statistics. I then continued and created a new statistic with 'create statistics' on the fields of the primary key. that didn't help. lastly, i did a DBCC DBREINDEX on the primary key. That also didn't help.Out of desperation I created a 2nd, non-clustered Index with exactly the same fields as the primary key. The stored procedure is suddenly running at well below subsecond times (16-20ms vs 5000ms and cost 0.142 vs 142) and is using my new index happily.has someone come across this behaviour? i think it might be due to the OR in the join.any experiences / info would be useful.thanksTiaan-----------------------Black Holes exist where God divided by Zero----------------------- |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-13 : 13:54:10
|
| I've never looked into it closely to know, but for the table alias "el" does the inital reference to "pl" ( pl.Freq<>5 ) get in the way of anything?Kristen |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-13 : 14:14:27
|
| Obivously i have no idea of the soundness of the logic behind your join, but i try to avoid OR's in joins at all costs. if your join requires more than 1 possibility, then join to that table multiple times and then use the one you need.i.e.,replace:LEFT JOIN Element el on (pl.Freq<>5 and pv.Planno=el.Planno and pv.Elno = el.Elno) OR (pm.Planno=el.Planno and pl.Freq=5)with: LEFT OUTER JOIN Element el on (Planno=el.Planno and pv.Elno = el.Elno)LEFT OUTER JOIN Element e2 on(pm.Planno=el.Planno)and in the SELECT portion of the query, use a CASE to determine which copy of the Element table (e1 or e2) to pull from:CASE WHEN pl.Freq<>5 THEN e1.<some column> ELSE e2.<some column> ENDDoes this make sense? Logically, JOINING on an OR clause probably should very rarely be done. Multiple joins to multiple alias's of the same table makes a little more logical sense and should be much more efficient.- Jeff |
|
|
|
Soulweaver
Starting Member
18 Posts |
Posted - 2004-10-18 : 09:16:51
|
| Thanks guysI have handed the comments over to our developers to see if the breakup of the OR in the join is feasible.Will give feedback as soon as they give feedback.Tiaan-----------------------Black Holes exist where God divided by Zero----------------------- |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-18 : 09:23:37
|
quote:
Originally posted by Soulweaver
Thanks guysI have handed the comments over to our developers to see if the breakup of the OR in the join is feasible.Will give feedback as soon as they give feedback.Tiaan-----------------------Black Holes exist where God divided by Zero-----------------------
If they feel it's not, send them here -- because that's the best way to handle this situation. We'll set them straight !  - Jeff |
|
|
|
|
|
|
|
|
Strange behaviour on Join with OR