Here are the outputs from the SHOW_STATISTICS command:

IX_Podrobnosti_SubPhylum_5000Feb 23 2016 10:51PM7071513285100YESNULL70715

10SubPhylum_5000

NULL07071501

Sorry about the ugly output - if there is a tidy way to post table output, I don't know how...

ScottPletcher (2/23/2016)

---

I think this will identity whether or not a row exist and will probably run much more quickly, since no massive join is involved, although a huge sort will...

Jacob Wilkins (2/23/2016)

---

Ah, interesting. Estimating 1240 rows and getting an actual 25 million is certainly problematic 🙂

I must have misunderstood you earlier, though. I thought the stand-alone COUNT queries were...

Here is one queryplan that takes forever and returns a count of 0.

Jacob, I think this article that you posted does, in fact, pinpoint the trouble: http://blogs.msdn.com/b/bartd/archive/2012/03/14/row-goals-gone-rogue.aspx

It matches what I recall, in that the most troublesome queries were those that returned zero...

I wound up putting a single-field index on every column that is used in this circus, about twenty of them. That has made the performance acceptable. I'll put up some...

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 -...