Reducing database queries

I found a real badass design flaw on my newly created ORM system the other day. On the register form for a movie, the page had 119 queries taking about 0.085 seconds of running time. Since the site is not finished and the movie I was editing was a simple test, this would have been maybe 250+ queries for one movie, and the data would have been much more on in the tables so the time of each query probably would have been much higher in a real live situation. Have this in mind. What I had done, was to fetch the relations in the many-to-many table with one query each(so that I could use the same get method for every object, including relations). Really dumb, but I did not think it would affect that much in the beginning as long as each of them were fast.

Well, it took time, and I rewrote the system to get every relation in one query per table – reducing the amount of queries to 51 and the time went down to about 0.040 seconds for the page load. That made me think, 51 still sounded a lot to me, so I decided to analyze more. I noticed then that I made the same query many times to some of the tables. For instance, the country table, where I fetched all the objects every time I needed the list. And that list I needed in many places, more precise, in many of the relations. The solution to this began as a crazy simple idea that worked.

What did I do?

In the object that fetched the countries ( SELECT * FROM country ) and made the country object list of it (as mentioned, unfinished and short list), I added a private static $table = array(); at the top. Then I simply used logic from the singleton pattern. I checked if the self::$table['country'] was set with the list, if yes, I used the old list – if no, I created the list.

The amount queries was reduced to 33, and time to 0.035 seconds.

I still need to see if this is a good solution in the long run, as the time to execute the page varied a bit more than before. Can’t wait to see how it performs when the tables gets filled with data.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: