Well, on Friday I was trying to correct the data that a report produces.

Apparently it was getting the wrong date (or something) and so the finance people didn't like it.

Anyway, this report has been notoriously slow in the past. And when I say slow I mean ridiculously slow, they used to take 5 to 6 hours to run.

Then one of my colleagues (and my manager) had a bash at it and managed to get it to run in under an hour. (The time was the same for a single item or for all items)

Well, I was getting annoyed at having to wait 45 mins to see the results of my changes so I had a look at it myself.

Now if you run the report for a specific item it takes about 5 seconds and if you run it for all items it takes about 2 minutes

I was very chuffed

are you a robot? I have never ever seen an unhappy thread from yourself! all your posts are of a positive/happy nature, I consider you a very intriguing specimen!

You do realise that if you show too much initiative you'll never be promoted as you're too useful.
Still, well done there. Do you know which Muggle produced the original set-up? Sounds like they didn't know their backside from their elbow.

Nicely done Oli.

Was the boss happy, or embarrassed?

Thanks

My boss wasn't in so doesn't know I've done it yet

There are a couple of other reports (all about the same records) that I haven't done yet so I'll do those next

The best bit is that my boss and colleague had both come to the conclusion that a whole new table was required to hold some extra data before it was possible to improve the report any further

OK, I added a new index to a currently existing table but that was all and it was a fairly small addition comparatively

Yeah, it is great, when you can come up with such optimisations.

My first job, I had to collect corporate reporting data from the business units around the world (BASIC program, 300 baud modems and a 4.33Mhz 8088 computer (or 1Ghz Z80 - the program was compiled for CP/M and MS-DOS). I managed to cut the preparation and packing time from 4 hours to under 5 minutes - by reducing the number of screen updates, instead of display each of the thousands of account lines that needed to be processed, I only output every 10, 50 then 100, each giving a significant improvement of speed. The users were a bit wary of that at first, as they couldn't see every account that was being written, but they appreciated, that they didn't have to sit for hours in front of the machine, waiting for it to finish...

My last job, we had an e-Commerce site, which was having problems, the complex query to pull out the top sellers for the home page could take up to 45 seconds to run! And that was for each user that accessed the home page! That, plus some of the shopping basket queries meant that the load-balanced 4-way cluster set-up would keel over and die with around 150 active visitors. When the PayPal newsletters used to go out, the servers and the database server would grind to a halt. Before I started, the head of e-Com would have to restart the MySQL service every couple of minutes during peak times!

I managed to optimise the query, so that it ran in under 1 second! That meant, at peak times, the load balanced set-up was able to cope with nearly 250 active sessions per server, instead of the 150 over all 4!