SQL FTW

One of the slower parts of the .Net app I part-support finally became enough of a squeaky wheel to get some grease: I replaced it with a single SELECT query in a little piece of PHP.

Before: c 13.2 seconds.

After: 151 milliseconds.

(Yes, it's retrieving the same fields from the same database, without any caching or other tricks involved - just replacing the craptacular C# dreck which goes through rehydrating objects with LINQ then spitting it all out as JSON with a straight SQL->json_encode.)

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.)

SQL 101: when storing daily figures...

SQL 101: when storing daily figures in weekly batches, you stash half the data in one table and number all the days from 0 to 7 inclusive. (Yes, an eight day week.) Then you store the other half of each week's data in an almost identical table, but number those days NULL+0-6 inclusive. For added variety, try changing which day 0 refers to mid-dataset.

NULL. It's not just for breakfast any more.