chrisleonard (8/12/2008)

---

Well, I'm not hand-waving; just type "TPC cursor" into Google and you will immediately find, as I did, a post by the wonderful Linchi Shea that announced to the...

Heh. You know, if you actually type "TPC Cursor" (with the quotes) into Google, the result is pretty funny.

pino_daddy (8/12/2008)

---

YOU HIT THE NAIL MRIGHT ON THE HEAD, Thank you so much.

I removed the "UN-NEEDED" UPPER clauses and BINGO, query runs in 3 seconds. Returns the...

No, because you did not (and still...

Looking through the Execution Plan again, it does look like the UPPER() functions are the cause all those scans.

Well that's case-insensitive (accent sensitive) , so you should be able to take the UPPER's off (though it...

Hopefully, its the same as Management Studio on 2005? Create the Query Plan output, then right-click in the output window and pick "Save As..."

Also, this does not make a lot of sense to me:

Why make a CROSS JOIN, call it an OUTER JOIN...

What COLLATION are you using? Is it the same in both environments?

I ask because those UPPER functions on your joins are likely killing any chance of using the indexes.

Thanks for the feedback, Mark.

Varchar(MAX) is one of the best things about SQL Server 2005, completely gets rid of the need for the BLOB's (Text, NText, etc.), meaning no more...

Since Views can have CTE's and CTE's can use Views, there is a lot of overlap between them. The real difference between them is where the SQL code resides.

If...

khushbu.kumar (8/11/2008)

---

I have tried modifying the cursor to loop but could not change much ...

some developer has written it .. and it has come to me for optimization.

still the query...

This is the solution that you want to use. VARCHAR(MAX) can be as large as any TEXT datatype and is superior in virtually every respect. You can add...

You do...

SQL Server 2005 Developer Edition should give you all of this (the DB stuff) and it is a great deal.