Personally, I am about convinced that this is an Optimizer bug of some kind, and that it is also the direct cause of the bad plan in this case.

Paul White (3/2/2010)

---

Barry,

Are you asking about the Row Goal thing I said I'd have a look for? I didn't look yet :blush:

But it really is just a case of...

And you know, this would all be a lot easier and simpler if you did this in a set-based way, instead of using a CURSOR and WHILE loops.

Adi already answered your question. Did you do what he said?

Paul, did you come with anything on the bad rowcount issue? I'm afraid it's really bugging me... :unsure:

rbrenan (3/2/2010)

---

I have a statement similar to:

SELECT

column1_output, column2_output

FROM

table1 t

INNER JOIN

containstable(table1, column1_search, '"searchterm"') ct ON ct. = table1.id

I have a nonclustered index on column1_search with...

whoa! Did you know that this is embedding subordinate elements inside of text content? I'm not even sure if that's valid XML...

you'll probably get more answers to this question in one of the five different SSRS forums, here: http://www.sqlservercentral.com/Forums/Group416.aspxp

RBarryYoung (3/1/2010)

---

agustingarzon (3/1/2010)

---

Dear Barry, the exec plan for your suggestion is the same as NewExec I just posted.

The approach is ok, but it's making a clust-ix-scan that scans through the...

Most of...

agustingarzon (3/1/2010)

---

Dear Barry, the exec plan for your suggestion is the same as NewExec I just posted.

The approach is ok, but it's making a clust-ix-scan that scans through the first...

Paul White (3/1/2010)

---

RBarryYoung (3/1/2010)

---

True, but I prefer to try to get the optimizer to "do...

Yes, that's the big question here, why is it doing that? It's RowCount estimate is ...

True, but I prefer to try to get the optimizer to "do the right thing"...

Hmm, I thought that...