RSS Feeds

A quick point about timing

I've just deployed my V3 blog engine to live.  In doing so, the time per query jumped from <10 mSec per query to ~2.5s — which meant that the typical page response time for non-cached pages increased from an average 0.25s to nearly 3s.  (Cached pages are loaded from a static copy, so the render time is typically under 0.2s.  This was odd as the timing for V3 remained much the same as the V2 engine (< 10 mSec) for my development and test versions.  After quick 'binary chop' through the code, looking at the micro-timing (I have debug routines to do this), I found that entire increase was down to a single MySQL query which I used in the initialisation of my extension to the mysqli class:

SELECT TABLE_NAME AS name
FROM information_schema.tables
WHERE TABLE_SCHEMA = '<DBname>'
AND TABLE_NAME LIKE '<TablePrefix>%'

and this was taking 2.3 – 2.4 seconds to run, so I replaced this by a functional equivalent which ran in < 2 mSec:

SHOW TABLES LIKE '<TablePrefix>%'

I picked the former because I wanted to explicitly name the column in the result set.  However, coding around with the second query only adds an extra line of code.   For this extra line,  my script time drops by 100x and the user response at the browser decreases by 10x.  This all goes to show you can spend ages worrying about whether this particular way of coding (say select case vs. if esleif  chains) which only changes runtimes by µSec, but if the runtime suddenly increases in a bizarre manner, then its usually some bizarre quirk that you hadn't anticipated, and you should do a timing drill-down to work out why.


Post a comment

Please note that your name is required and that all posts will not be visible until authorised by an administrator.

  Required
  A valid mail address must be supplied
A cookie will store your name/url for three months
 Sorry, but you must answer this easy sum as a SPAM prevention measure.
 
 

You should be aware that all information on blog site is © Terry Ellison 2010 and made open access under the Creative Commons Artistic Licence.

Your comments will only publicly available after you have carried out email confirmation. Your email address will only used for this purpose and is not made public.

Comments that are not confirmed will be automatically deleted after 7 days.

The blog author reserves the right to delete comments which breach copyright or the rules of site etiquette as he determines (such as unnecessary use of obscenity or spam content).