Writing queries can be simple, easy and an effective way to get the job done. Whether it is MS SQL, MySQL, T-SQL, SQLite, PostgreSQL or SQL in general, ColdFusion provides many built-in functions to allow you access to your databases with ease. It even gives you the opportunity to create dynamically-generated queries with use of the cfqueryparam tag, and do so with a sense of security. Using this tag can help protect your code and your website against SQL Injection attacks ( such as the sql injection attack that we recently defended against, called “LizaMoon”).
So, you have created a function that queries your database for the information that you need. You’ve taken the time to lay out your code in an organized manner, and add comments wherever they are needed. You’ve created conditional statements in the function, and outside the function wherever it is called. Using these statements, you hope to avoid hang-ups, errors or any other problems associated with not taking every use case into careful consideration.
After carefully revising your code to ensure that there are no nested ColdFusion loops that can hamper performance and you are feeling great about a couple hours worth of work- you decide to test your function. And then it hits, the waiting game. You’re sitting there for 30 seconds, or maybe even longer (depending on the timeout variable set in the coldfusion server) until your page displays an error stating that “The request has exceeded the allowable time limit Tag: cfquery”. After enabling Robust Exception Information and test your function again, you can see that not only does it timeout on the cfquery tag but also the Stack Trace does not include any information that is relevant to what the real problem is.
The answer to the solution, is that the problem may not actually be a real “error”. After some hours of research, I have found that a ColdFusion function I had written earlier passed in a numeric value as a bigint in the cfqueryparam tag. For Example:
<cfquery name="qry" datasource="test_db">
<cfargument name="OrderDate" type="numeric" required="true" default="0">
WHERE OrderDate = <cfqueryparam cfsqltype="cf_sql_bigint" value="#ARGUMENTS.OrderDate#">
This seems like a very simple query and that it should run very fast, which it in fact does when there is a low record count in the query. However, if you are running a complex double query and assigning the results to a ColdFusion structure in one function, and you are pulling hundreds or even thousands of records from a database that is highly normalized, you may have a problem. One of the simplest factors in this believe it or not, is the cfqueryparam tag and what the cfsqltype is set to. In my example above, I used bigint. This was because in the current project I was working on, I was getting a date/time variable from a Flash project that was making a call to my ColdFusion function.
The problem was, Flash was automatically setting the variable to milliseconds, not seconds- which was what date/time values were stored in the database. Originally I passed every date in as a bigint, because each of the date/time variables I was getting were 13 digits long. (Obviously, an easy solution to this would be to divide each date variable by 1000 and pass it in as an integer instead of a bigint, but at the time I did not know what difference it even made, I thought it was just a preference).
After taking the entire page apart as well as the function, I discovered that if I changed the values into seconds and passed them into the cfqueryparam tag as a cfsqltype=”cf_sql_integer” instead of cfsqltype=”cf_sql_bigint”, it dramatically increased performance time and the query started responding within the timeout limit. An article on Microsoft’s MSDN discusses the storage size of int, bigint, smallint, and tinyint. There is a 4 byte difference, regardless of whether or not the number is only 13 digits long. Take a look at this QuickRef to get more of an understanding as to what differences there are between MS SQL datatypes.
Also, even if I kept the bigint cfsqltype and just divided the date (which was still causing extremely slow performance) there is a great chance that the decimal in the new date value would set off a chain reaction that could convince SQL to say “Oh wait, there’s a float here…” and leave you hanging, kind of like a bad ending to a real good movie.
The key points here are as follows:
1. To reduce performance issues, always utilize the smallest datatype possible.
2. If you are going to send a value as a cfqueryparam, use the cfsqltype that best matches what you are sending as a value.
3. If you need a parameter to be dynamic; for example, you need to be able to pass in either a bigint, float OR integer- write conditionals in your ColdFusion code that either clean the data, convert it, or call different query functions based on the data given.