ChrisM@Work (2/23/2016)

---

Your two queries are logically different - sorry if I was ambiguous, what I meant was remove the NULL checks on the join columns only, like this:

SELECT COUNT(1)

FROM...

Auto Create Statistics and Auto Update Statistics are both on, Auto Update Statistics Asynchronously is off. I tried creating an index for all the affected fields, naturally it was too...

Just tried an alternate version, using subqueries with their own WHERE clauses. No improvement.

if exists (select 1

from (select Family_13000, Suborder_11000

from PaleoData.Tax.PodrobnostiTmp

where Family_13000 is not null and Suborder_11000 is...

Nope - EXISTS gives the same behavior - all four cores at 100%, grinding away.

The estimated number of rows was almost a million, the ACTUAL was over 59 million. Seems clear what he problem is, but WHY is it doing that? Apparently it is...

I've got it, sort of. I've been single-stepping through it in debug mode, and it appears to be the counts WITHIN THE IFs that are causing the problem. Here is...

Looking at the query plans - some of the estimates are wildly off. Estimated rows 331,232, actual rows 597.

Sean - no, not using transactions. Just started it with execution plans enabled, but since there are several hundred independent SELECT and UPDATE statements in the procedure, I suspect the...

Nope -...

GilaMonster (12/14/2015)

---

Nested loop joins may fetch the same row many times from the outer table, and if there are functions there they might get evaluated before the join. Also you...

Why would a UDF run more than once per row, unless it's used more than once per row?

Erland Sommarskog (11/16/2015)

---

You can sign the procedure with a certificate. From the certificate you create a user which you grant the permissions needed.

I describe this technique in detail in an...

Erland Sommarskog (11/16/2015)

---

I can see two possible explanations:

1) It is not the way you think it is, but procedure and table have different owners. Check the two objects in sys.objects,...

They do, sort of, and office politics are not a problem. There is no management between us...

I'm familiar with that site. However, it has nothing to say about the situation I described.