Monday, 30 July 2012

Ah, SQL performance issues...

Ah, SQL performance issues. If only there were some way of retrieving data other than a single row at a time in a massive loop ... *headdesk*

(Contractor-supplied code; trying to convince contractor to up his game now. "Joins, m'dear, might want to look into them"... Trouble is, on his little test setup with the web server and database server on the same unloaded machine, it's not such an obvious bottleneck - then when deployed, it's a complete dog.)


  1. welcoem to my world.... queries running against 3 or 4 tables joined together where each table has ~2m rows.

  2. This one's about 100k of data. Which makes it all the more impressive it takes over a minute for some operations, of course.

    Last week, I showed him the code got much faster if you pre-fetched some of the stuff that was being requested inside the loop. Today, he has presented me with a version which dumps the output to a static file, returning that instead for future requests. Still the same braindead dog-slow loop kludging the data together a few bytes at a time, but hopefully not being called quite as often now with the manual caching layer. (There is, of course, built-in caching available with dependency tracking too. Using that would have been nice.)

  3. data partitioning also helps with speed when you have multiple simultaneous threads reading and or writing data.

  4. The tragedy here? It's 100k of data. Concurrency: essentially none, it's an ultra-low traffic niche web app. Hell, you could probably assemble the data using a CGI shell script and flat file data storage faster than this.

    Instead, it's had a committee of architecture astronauts each throw their pet buzzword in. Every db access is obfuscated - er, "abstracted" - using linq to get another object - only to throw most of it away again and dump a few strings out as json.

    Oh, and just why are there 4 different copies of jquery in there? Then a couple of pages tried to use option #5 instead, breaking until I stuck an extra copy in the place actually referenced in the script tag. Yes, jquery 1.3 - one of the plugins used is abandonware which wont work on anything newer...

    Gene pool needs more chlorine.

  5. This is a general principle, not specific to your complaint.