I’m often guilty of taking the “get it done quick and move on to the next thing” approach to developing. I don’t do it because I’m lazy but in the interest of saving time. When developing an application, events often collide, and that's when the inline SQL “get it done quickly” data access approach has a very strong possibility of making it into a final production application.
While inline SQL does work, it really isn’t very efficient, especially when SQL statements are embedded in loops, because each time the SQL command is executed, the database must parse it to determine its validity and then compile or interpret it. This isn’t the case with stored procedures because they’re already parsed and compiled. But because it takes time to create or modify stored procedures solutions, incorporating them has a tendency to take longer to complete than solutions using inline SQL.
What you need is an intermediate step between inline SQL and stored procedures in case time doesn’t allow you to return and make stored procedures to replace inline SQL statements at a later date. Precompiled SQL provides this step by offering the flexibility of inline SQL and, when used correctly, speed approaching that of stored procedures.
Just things you normally have lying around
While the code in Listing B is more verbose than that in Listing A, the precompiled example can provide substantial speed improvements over the inline example when used in a loop placed around the setting of the parameter (e.g., prmADO.Value) and the execution of the command (e.g., cmdADO.Execute).The more loop iterations, the greater the speed advantage you’ll see.
Not without its share of opportunities
Precompiled SQL is not without some problems. The first and foremost is that an application needs to hang on to command objects for as long as they can possibly be of use to keep the precompiled SQL available for use. While on the surface this doesn’t seem bad, if you’re dealing with lots of command objects, you can wind up short on resources fairly quickly.
The second problem is that, unlike stored procedures, precompiled SQL parameters aren’t referred to by name. Instead, they are always referred to with the question mark placeholder (?) and are dependent upon their position in both the SQL command and the corresponding code. It is also important to remember that the sequence of table columns must be the same as the sequence of command parameters appended to the command. This restriction requires some meaningful names for variables in the code to keep things straight. With this in mind, consider the code snippet in Listing C.
Without the meaningful variable names, prmADOaaaa, prmADObbbb, and prmADOcccc, the example in Listing C would be rather obscure, and the meanings of the parameters used would be difficult to decipher.
Not just for SELECTs
Precompiled SQL’s usefulness isn’t limited to only SELECT queries. The same technique can also be applied to SQL INSERT, UPDATE, and DELETE statements.
One last persistent opportunity
There is one additional issue that precompiled SQL presents that may or may not affect you. I genuinely love persisting an ADO Recordset as XML and will do so whenever I can. Sometimes I’ll do this in preparation for dynamically creating an HTML object, other times for building an XML data island. I discovered that attempting to invoke the Save method of a Recordset object created from a precompiled SQL statement save method causes problems. My first attempt resulted in the following error:
Microsoft OLE DB Provider for Oracle error '80040e25'
Row handles must all be released before new ones can be obtained.
/127.0.0.1/server.asp, line 191
Being the savvy developer that I am, I knew immediately what was wrong. The Recordset’s CursorType was set to the default, adOpenForwardOnly,which doesn’t allow persisting a Recordset as XML. I then tried setting a dynamic cursor type using the following code:
rstADO.CursorType = adOpenDynamic;
rstADO = cmdADO.Execute(lintRows);
Feeling pretty confident that my change would do the trick, I gave it another shot. And I got the exact same error message as before. I was reminded that there is often a vast gap between knowing what the trouble is and knowing how to fix it.
After uttering a few choice words and fruitlessly searching the Internet, I decided it was time to bring out the heavy artillery. I cracked open O’Reilly’s ADO: ActiveX Data Objects and it pointed me in the right direction. By invoking the command object’s Execute method and assigning the result to the Recordset object, I was essentially creating a new Recordset and destroying any properties that were previously assigned. The devilishly simple solution is using the Recordset.Openmethod, passing the Command object as the source, as shown in the following snippet:
rstADO.CursorType = adOpenDynamic;
Finally, I was able to persist my Recordset as XML.
While not as efficient as stored procedures or quite as flexible as inline SQL, precompiled SQL captures some of the best features of both and represents a fair compromise in terms of performance and flexibility for little extra effort. From now on, when developing an application, I’ll use precompiled SQL instead of inline SQL for performing repetitive data access tasks, and I think you should, too.