Skip to main content
S
shoalwater
Participating Frequently
October 5, 2006
Question

CFQUERYPARAM makes it slower??

  • October 5, 2006
  • 3 replies
  • 466 views
Hi,

I'm running some queries on some very big tables and was looking to use cfqueryparams to speed things up a bit. I've used this before with good success, but now I'm seeing an increase in query execution time by double or more.
I'm on CF 7 Standard, Oracle 10g, JDBC Thin driver. Without the CFQUERY param it takes about 2000ms and 4000+ms with it in place. There are indexes on the customer number and id fields...

Here's one example:

This topic has been closed for replies.

3 replies

D
Dan_Bracuk
Inspiring
October 6, 2006
My experience with cfqueryparam is that with Oracle it improves performance, with db2 it doesn't make any difference, and with redbrick, it doesn't work.
S
shoalwaterAuthor
Participating Frequently
October 5, 2006
Before posting I had run it about 3 times each way, but after replacing the like with an = and running it more they were much closer. Still slightly faster (500ms or so) for the query without the cfqueryparam but acceptably close. I suspect the cfqueryparam shines when the application is under load.

Thanks
A
Anonymous
October 5, 2006
With the exact same value of serial_number, run the exact query 20 times without cfqueryPARAM and 20 times with it.

The average times should be about the same or slightly better with cfqueryPARAM.

However, this query is likely to be slow, regardless, because of the like operator.

If you use like with wildcards (mainly leading wildcards), then the database can't use any indices (which you should have on serialnumber). This slows things down a great deal.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices