mark 4643 (9/26/2012)

---

I don't understand why it's doing an implicit conversion between 2 varchars. Is it just because they are different lengths?

Is there an easy way to figure out which node in the exc plan relates to which part of the query?

Is the predicate, the join clause?

Evil Kraig F (9/26/2012)

---

Yep.



Not... exactly.

If you take a look at the second link in my signature for indexes and tuning, it'll walk you through what we'll need to help you decipher what the problems are with your schema and queries. It gets a bit involved.

Evil Kraig F (9/28/2012)

---

Apologies Mark, I've been heavily tied up the last few days and didn't have time to really dig into the information and virus-scan the .zip. I'd hoped someone else would have had time to swing through. Am I correct in that you'd still like assistance with this?

Jeff Moden (9/28/2012)

---

This is a pretty large "all in one" query that needs to be divided up. If you look for the large arrow in the bottom right section of the execution plan, you find it contains a row count of more than 17 million rows which is quite a bit larger than any of the tables involved. That means that you have a many-to-many join going on similar to a small cross join.

One you find the hash join that arrow is coming out of, look at the "residual probe" in the properties window for the hash join. It'll let you know what tables are involved in the join which will help you isolate the code.

My recommendation is to determine what the core of this query actually should be and to quickly isolate that much smaller result set in a Temp Table and then join to that instead of trying to do this query all at once. "Divide'n'Conquer".