The joy of subqueries

So a thing I’ve recently discovered: mastering previously cut code. I never thought I’d get the satisfaction that I’m currently enjoying when I return to previously written code, tweaking and adjusting it to my heart’s content. Invariably, I end up with better code. For instance, in the CMS that I’m developing (currently called ‘The Pencilcase’), I’ve cut the average page-load time in half from version 1.0. An how? mostly through writing better queries, combining queries, etc. More intelligent looping also. I have eliminated 5 whole loops from the code, subsuming all that those loops did into other bits of code, which has greatly improved efficiency. Yesterday, I had a crise de conscience about my ‘security manager’, a plugin module that manages all permissions in the system: getting user permissions, discovering objects’ required permissions, comparing the two, etc. It had been horribly inefficient. At one point, I looped over a recordset, and for each iteration, ran two further queries to determine and compare permissions. It ended up that for one particular instance, I ran 897 queries for 1 request. Which was just horrible. Page load time was still under 1000 milliseconds, but not by much. So I spent some time yesterday rewriting. I added a subquery to the query that gathered all the particular object’s information. Suddenly, rather than running 2 further queries for each record, there’s now a simple BitAnd() check. Which has dropped average load time from roughly 950 ms to about 200 ms.

So yay!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: