asked
updating temp table takes forever
I have a procedure that basically populates a one-row temp table and then runs a report from it. The table is populated with the aggregates from a very large and bulky table. So it's just several statements like this: UPDATE #temp_table SET count1 = ( SELECT COUNT(DISTINCT value1) FROM MainTable (NOLOCK) WHERE date BETWEEN @Date1 AND @Date1 ) The proc used to do its job in some 10-12 minutes. Recently it started taking forever. SSRS just throws an exception after 1 hour. Not sure what happened. But what's particularly interesting: if to exclude the updating part and leave just select statements, e.g.: SELECT COUNT(DISTINCT value1) FROM MainTable (NOLOCK) WHERE date BETWEEN @Date1 AND @Date1 all of the same the SELECT statements are getting executed in the same 10-12 min. What is the deal with the updating of a temp table that makes such a difference in the execution time? (I tried to wrap each update in a separate transaction, this did not improve the running time).
You really need to get hold of the execution plan so that you can see what's happening. In case you don't know, here's how to do that: Open a new query window in SSMS. Type in `set statistics time, io on;`. Paste in the query. Press Ctrl+M to enable the query plan capture. Execute the query. Once it has finished, you'll get a load of text in the Messages tab. This is the output of the time and i/o statistics. It tells you where the most reads were and where the time was spent but it's hard to read. Go to
http://statisticsparser.com/, paste the text in and hit the Parse button. You'll see a much clearer display. Where the numbers are the biggest is probably your problem area. In the query plan, there are several things to look for. It might recommend missing indexes that would help. Try them on a test copy of the database. Look for exclamation marks on the operators. These indicate possible problems. There's an excellent article to get you started on reading execution plans here:
https://www.simple-talk.com/sql/performance/execution-plan-basics/. Knowing this stuff will be a huge help to you so it's well worth learning about it.
18 People are following this question.
