I hit a weird situation last week. We had a report (Windward, not SSRS) that was pretty basic, 9 pages long, and it was taking over 1 minute to run. I ran it under a profiler and 92% of the time is in the ADO.NET connector (i.e. SQL Server was slow to respond). And then about 1 time out of 20, it would be the expected 2 seconds. Something weird was going on…
To cut to the chase, by changing the selects, I got it down to 2 seconds. I did this by crafting better selects. But the thing is, the amount of data was so small, it shouldn’t have made a difference.
“…by changing the selects, I got it down to 2 seconds.”
Here is what we had (these are distinct selects because they’re used to build out the report, in places by sections, then rows, then columns):
- Weeks loop: Select builds & returns a temp table that has 28 sequential dates. It uses every 7th entry.
- Days loop: Select builds & returns a temp table that has 7 sequential dates, starting with the date on from weeks loop. Used for the table header.
- Name loop: Select returns a result set of each distinct name in the database. This is 29 names (from a total of 94 rows in the table).
- Sum: For the name & week, a sum of all values in the cost column.
- Days loop: Select builds & returns a temp table that has 7 sequential dates, starting with the date on from weeks loop.
- Times: Read a single value from the DB – a column specified by the name & day of the week
Keep in mind this table had just 94 rows in it. 16 columns that are date, varchar(50) and int. This is nothing. And while there’s a fair number of calls to get the individual values, that innermost reading a single value from the DB occurs just 4 * 7 * 29 = 812 times. That’s nothing. As demonstrated by the fact that occasionally the whole thing runs in 2 seconds. Also as an experiment, I removed the Sum and Times calls, expecting a significant increase in speed. That dropped it to 52 seconds – which is not much.
So what did I change?
- Indexed the 2 columns used in where clauses.
- Weeks loop: Made it a set operation. Basically, a select that creates results set on the fly. Also, had it return every 7th day so we were having to skip 6 rows (days) after reading each row.
- Name loop: Created a temp table with the name and sum. Used a temp table instead of a set operation because it’s a row per distinct name and the cost is per date range.
- Sum: handled by #3.
- Days loop: Creates a temp table of all the dates (not needed but useful for debugging) and the time’s value for each date. This could probably be a (not so simple) select to return the result set. But I was down to 2 seconds this way so called it good.
- Times: handled by #5.
And now it consistently runs in about 2 seconds (actually less as that time includes the initialization, launching the generated report, etc.). But the above changes shouldn’t count for dropping from 1:21 to 0:02. The data is just too small. And I didn’t change any of the selects so much as combine some to return them together in result sets. It could be flat files under the covers and still be faster.
So what gives?
My guess is that SQL Server is optimized for standard ways of pulling down selected data from large databases. And optimization is trade-offs where you build up caches, structures, etc. to then be able to handle 100,000 queries against a terabyte of data. And for our case, that was just a ton of unnecessary overhead. Or maybe we just hit some very weird combination of calls that it handles poorly.
We could have told our customer that it’s SQL Server, but I don’t like doing that when the selects all look fine. Besides, this bugged me as it should have been fast. I wanted to solve this. SQL Server was making our program look bad!
And it was a nice sense of accomplishment when I saw it run in 2 seconds.
Author: David Thielen
Dave, Windward's founder and CEO, is passionate about building superb software teams from scratch and dramatically improving the productivity of existing software teams. He's really proud that he once created a game so compelling (Enemy Nations) that a now-professional World of Warcraft player lost his job for playing it incessantly on company time. You can read more from Dave on his personal blog, and at Huffington Post.
Other posts by David Thielen