Trying to speed up a report that takes freaking forever

I'll try to keep this as short as I can without losing context - we have a system for online education, where presentations often have a test.

Management has access to a report used for measuring compliance. The report has a few paramaters that need to be selected:

• A date range to look in (e.g. From 11 March 2010 to 11 March 2011)

• One or more online tests (it's a hospital, so some topics have a clinical and a non-clinical version)

• One or more departments (so VPs can see all their departments, managers can see just their department, etc.)

• There are a couple of options for filtering the results (showing all results, showing only "missing" results - kinda broken at this point, excluding "missing" results)

• The report can be pulled as an Excel spreadsheet or as a web page (The Excel option is going to go away due to some issues with Excel 2007, I just haven't had time to remove it)

2011-03-17 Addendum:

As per munificent's advice (and apparently an earlier me's notes) I used http://aspprofiler.sourceforge.net/ to get a little more hard data.

Getting the data from SQL Server Express 2005 (which is running on the same machine) seems to be the biggest culprit.

Set objResultsRS = objSQLDB.Execute(,,adCmdText) has taken anywhere from 33% to 50% of the processing time.

If Not objResultsRS.BOF Then objResultsRS.MoveFirst takes another 20% - 26% of the time - I'll be damned if I can figure out why its even running... since the data is getting dumped into an array, I probably don't need to check BOF and EOF to see if I've got an empty recordset - I just need to check if the array is empty. I'll test to see if that yields any improvements worth reporting.

Nothing else is taking more than 3% of the processing time. I've run a fairly typical report checking system-wide compliance with two online test from 11 March 2010 to 11 March 2011. (a report that is being run constantly this month by just about every VP, and the source of half the support calls I've bee

It makes me sad how few people seem to know this basic advice: If you have a program that's slow profile it. It takes five minutes to download a trial of a profiler, hook it up to your app, and see exactly where the bottlenecks are. Computers are much too complicated to reason about performance from first principles anymore.

As long as you have the right algorithm (which you do need to reason about before you get this far) you'll make progress much faster with a profiler than you will just trying to guess at stuff. Almost every time I've had a performance problem, a profiler has revealed it wasn't at all where I thought it was.

StackExchange Code Review Q#1298, answer score: 6