I'm doing inserts into a table with no foreign keys from an enormous view (53 tables).
If I do my inserts / lookup with a straight join, it takes a day to run. This is a data-load process, run only one time.
To speed things up a bit I am using a cursor (OH NO!)
At any rate, in the heart of the cusor read is the following:
FETCH FROM #tmptable WHILE @@FETCH_STATUS = 0 BEGIN SELECT ...search variables INSERT INTO ResultsTable(8 fieldlist...) SELECT DISTINCT 8 fieldlist... FROM myView where 6 where clauses... FETCH NEXT FROM ... END
This runs fine for awhile and then seems to freeze. It's not really frozen but it takes many minutes to execute (executes fine for awhile and then hangs).
I am displaying the search criteria so I can tell what it's looking for when it hangs. I can run the above code with the correct where clause and it takes 6 seconds. But in the cursor it just stalls.
There seems to be plenty of space in tempdb.
I have run an execution plan and client statistics. Both are clean as a whistle with no recommendations.
So any ideas would be appreciated.
Oh and PS - I have tried the query without DISTINCT. No joy.