Monitor Your Query Performance

"I click the button and wait maybe 5, sometimes 10 seconds before I see my results. It's just too slow!"

The above sentence and its many variations are familiar to every developer. As programmers, we are at the mercy of so many variables outside our control, yet we are held accountable for them all. Before griping and complaining, make sure you've fulfilled your end of the bargain, which is to ensure the programming of our applications is performing optimally. Like everything else, the best way to optimize and maintain the performance of an application is through proper measurement and active monitoring of unhealthy conditions.

Periodic reporting and analysis is equally important for a larger-scale application, and if reader response is favorable, I will follow-up with an informative article on effective reporting.

This article demonstrates a technique using VBScript (ASP) code. None of what is written below is language or technology dependent and can be adapted into your platform of choice. If you undertake a re-write into PHP or other scripting language, please send it to me, so I can share it with our readers as an external link.

All of us are intimately familiar with the following line of code:

rs.open("SELECT COLUMN FROM TABLE WHERE CONDITION"), conn

While this is syntactically correct and will return your expected output without complaint, it leaves nothing to be measured. A simple and more attractive solution in terms of measurement and monitoring would be to replace this line with the following:

query("SELECT COLUMN FROM TABLE WHERE CONDITION", -1)

The SQL query you intend to execute is the first argument, while the second argument allows you to specify a response time threshold the query is expected not to exceed. The workings of the query() function are described below:

SUB query(byval query_sql, byval query_timer_threshold)

  '--DEFAULT timer threshold is .5 seconds (make whatever you like depending on the speed and performance of your database and typical query)

  dim query_timer_threshold_default

  query_timer_threshold_default = .5

  '--IF timer threshold is not specified

  IF (query_timer_threshold <= 0) THEN

    '--SET timer threshold to default

    query_timer_threshold = query_timer_threshold_default

  END IF

  '--START the clock

  dim start_time

  start_time = timer

  '--EXECUTE the query and return the results – rs and conn are defined in global scope outside of this function

  rs.open(query_sql), conn

  '--END the clock – two significant digits is fine for our purposes

  dim total_time

  total_time = round(cdbl(timer - start_time), 2)

  '--IS total time a negative number? – whew! that was fast! – actually the timer resets on the server each day at midnight so if your query starts before midnight and ends after midnight, it will be negative

  IF (total_time < 0) THEN

    '--FIX time – this is easy to fix by subtracting the number of seconds in a day's timer

    total_time = (total_time + 86400)

  END IF

  '--IF query time is greater than specified (or default) threshold?

  IF (query_timer_threshold > total_time) THEN

    '--SET email parameters to define the query and nature of the possible performance problem

    dim email_subject

    dim email_body

    email_subject = "Notification: Possible Performance Issue"

    email_body = ("This query exceeded its execution threshold:" & vbcrlf & vbcrlf & _

                  query_sql & vbcrlf & vbcrlf & _

                  "Total Time to Execute: " & total_time & " secs" & vbcrlf & _

                  "Specified Threshold: " & query_timer_threshold & vbcrlf & vbcrlf & _

                  "As most server or database outages are precipitated by slowed queries and poor performance, it is imperative that this query receive proper troubleshooting immediately.  If an outage does ensue, the above query may be at least partially responsibley, having unexpectedly exceeded its allotted execution time.")

    '--SEND email notification to admin - if you don't have a comparable function like this that can be used quickly, you should make one!

    send_admin_email email_subject, email_body

  END IF

END SUB

In this example as long as your rs and conn variables are in a global scope, you have a simple and elegant solution. You might even escape with a global find and replace on your existing code!

Find: rs.open(
Replace: query(

Find: ), conn
Replace: , "")

If you've found (like many before you) that the Windows find and replace tool isn't worth the development environment in which it was written, try using an upgraded version.

You will learn to tune the queries that consistently fail or to specify a higher threshold for the queries that are not meant to respond at or below your default. You cannot know which queries are your problems until measurement has been implemented.

The use of this simple architecture shift will pay off in stress relief immediately! Imagine all the worry and stress melting away as you hit "Replace All" and dispatch your newly written queries out into the wide world of the web, each coupled to a closely monitored alert system waiting to warn you if a problem arises. Go relax - but keep a watchful eye on your inbox!

As I stated at the beginning of this article, adding reporting is the next logical step for a large-scale implementation that needs constant tuning. By charting your results over time, you will notice both positive and negative trends in the performance of your queries. You can start becoming "proactive" instead of "reactive".

Note to readers: I choose to format my variables in an unconventional style. While you may be used to camel-casing (dateTime) and type-fixing (objRS), I'm not keen on any of the usual styles. While many of you have your own predilection for variable naming and may detest such a simplistic (and possibly confusing) style, I present mine as an alternative. This is part of the freedom that defines our profession.