SQL Server Performance Forum – Threads Archive

parameters

i notice that on one of my tables if there is no max/min price searched on, the join to that table switches from a nested loop to a hash join. the hash nearly doubles this sp’s exec time. i can see why it does that. any ways to deal with this lack of max/min price?

---

Well, you need to be EXTREMELY careful doing this (test, test, then test some more). You can specify your joins to be INNER LOOP JOIN. That will force a nested loop join, and can result in a significant performance increase sometimes. Usually, the optimizer does a great job; however, we have had to help it in a few places at my current employer. It’s usually at "hot spots" that run thousands of times a day. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

this table is definitely a hot spot. but if i put in the join hint as u suggest, then sql server will be doing a nested loop join where it is costly also because there are so many rows involved. right?

---

It will be costly, but a lot of times it will run a LOT faster. The optimizer generally will pick the least costly plan. That doesn’t make it the fastest option though. Loop joins will almost always beat a hash on speed IF you can obtain a plan. Try it and let us know the results. FYI, the type of system you are on can make differences in the performance of the different types of joins, so that could factor in also. Let us know how it goes though. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Actually the word "hint" is a bit misleading. If you specify such a hint, you really force SQL Server to follow your instructions, no matter if there might be another "better" alternative. I think, this is what Derrick meant with "test, test, then test some more". —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

]]>