I think it has to do with the fact that using queryparam makes CF generate a prepared statement. If you do a profile of what statements actually get sent to the database when using queryparam you'll notice a lot more than just sql in it. On sqlserver what gets passed is actually t-sql where parameter values are declared by type and then the sql statement is passed and then the parameter values.
On 11/14/05, Ryan Guill <[EMAIL PROTECTED]> wrote: > Hey guys, > > This isn't so much a question as a discussion. We are using cfmx 7 > with an iseries (v.5r2) as/400 box, through a client access odbc > driver. Now, this isn't your run of the mill iseries, its a very > beefy box with quad processors and a ton of memory (5gigs I think). > We have used cfqueryparam everywhere throughout our applications with > no problems, its always very fast. > > But today we were working on just going through some of the sql > statements, especially ones that are going to be run more frequently > and seeing if we can clean them up any. There is a tool that comes > with client access that is a query analyzer that gives us times and > other information about each query. so we were copying the queries > into the analyzer and replacing the queryparams with hardcoded values > and just tweaking on things. We started to find some queries that > were running considerably faster in the analyzer than they were > actually running the cfquery in coldfusion. Now granted, were talking > about tens of milliseconds here, but it was a noticable difference in > averages of those times. > > What we found was that some of our simpler statements, for instance > where we were pulling from a single file, we're sometimes 20 > milliseconds faster on average without the queryparam than with it. > Now if we ran the same thing comparing some more complex statements > the difference switched, the cfqueryparam was considerably faster than > without. But for simple statements, it seems like not using > cfqueryparam is much faster. > > This isn't a big deal, but just wondered if anyone had any ideas why > we are seeing those differences. I have always heard and understood > that anytime you can use cfqueryparam that you should. And I do > realize that the queryparam also does type checking on the variables > being passed through it and protects against sql injection and all > that, but thats not really worry for this application. > > Any thoughts anyone? > -- > Ryan Guill > BlueEyesDevelopment > [EMAIL PROTECTED] > www.ryanguill.com > (270) 217.2399 > got google talk? Chat me at [EMAIL PROTECTED] > > The Coldfusion Open Application Library - COAL - http://coal.ryanguill.com > > www.ryanguill.com/ > The Roman Empire: www.ryanguill.com/blog/ > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:224086 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
