Wednesday, November 15, 2006

Tell me why...

For the second time this week I was asked to take a look at a query that's used to generate a report. Problem is, when generating these web based reports, the sessions are timing out. The query I got on Monday ran in 15 minutes resulting in almost 2 million rows. Yes, 2 million! The query under investigation today ran in about three minutes resulting in about 160,000 rows...

Call me crazy, call me mad, but somehow I don't see the point. These queries would result in reports of thousands of screens long! I can't believe that's really what the business users want. So apart from trying to speed up the queries, we'll ask those business users what it is they really want. Chances are, we can add some default where clauses to these queries so the result sets will end up being a bit more sensible...

Jan van Mourik

3 comments:

Karen Morton said...

It's amazing to me how people "think" they really need something (like an endless report like the one you mention) when in the end, they don't. The habit of having to have something you've always had is often hard to break. But this is a great example of doing more work than has to be done. Waste...waste...waste!

Jeff Holt said...

I suppose the web page could have executed the query and closed the cursor after some predetermined number of rows.

But even if it did, and the execution plan didn't was "all rows" optimized or anything worse than "first rows" optimized, then server capacity will definitely be wasted.

Ric Van Dyke said...

To quote the Eagles
"I can't tell you why..."
Folks sure do some odd things.