You Got Your Query on My Server! You Got Your Temp Table on My Server!
Unlike the similar Reese’s dilemmas, these results were far less favorable…
Reese's are yummy
As many of these issues begin, a developer put together a procedure. When he ran it locally, it ran in a matter of seconds. For some reason, though, he wanted the procedure run from a remote server. When he attempted to run that same procedure from the remote server, it took a number of minutes. It was apparent that it wasn’t the result set being returned, as there were OLEDB waits while the query was processing.
In looking at the query, it wasn’t anything spectacular. It dumped some data into a temp table and then joined that table to an existing table and displayed the result. The problem ended up being the temp tables. While the query was being executed against the remote server, the temp tables were being created on the server the query was running on. So, pulling the data into the temp table and joining the two tables all had to occur with data being pulled across the network.
The fix was pretty simple – change the procedure to create a table on the remote server and drop it once the procedure was finished. The procedure ran in the expected time period.
Ted Krueger
May 26th, 2010 at 10:17 am #
The pain we endure while developers “think”. When those things get by and/or are existing they can be a PITA to track down too.
What We’re Reading This Week – 2010-06-05 | Brent Ozar - Too Much Information
June 4th, 2010 at 9:17 am #
[...] You got your query on my server! You got your temp table on my server! – Lori Edwards discovers why temp tables and linked servers are a lot less appetizing than chocolate and peanut butter. [...]