I have a stored procedure which pulls a large SQL blob from a table. That SQL blob returns tens of thousands of records and then the SP performs certain actions on those records (inserts into different tables) . Essentially, the blob looks something like this:
select a.accountID from #filter f join account a (nolock) on f.accountID = a.accountID join car c (nolock) c.carID = a.carID
The temp table #filter is populated in the beginning of the sproc by filter out the millions of accounts in the account table, essentially limiting the scope. It looks like this:
select accountID into #filter from account where fileSetID = @fileSetID
This weekend, we got a new file set from a client. For all other file sets, the SP and the SQL blob works, but it hangs on the new one. I dropped and re created the procedure to force a refresh of the execution plan. When I do that, the procedure won't hang over this new file set but it will hang for everyone else.
Any ideas to make this SQL blob work for all filesets?