EasyCFM.COM ColdFusion Forums / MySQL Related Issues / <cfqueryparam> and MySQL

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: <cfqueryparam> and MySQL

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

beranza
07-03-2005 @ 8:17 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 5
Joined: Jul 2005

Can Anyone confirm if CFQUERYPARAM actually improves performance when using MySQL.

In debugging mode I noticed queries were a lot slower when i used CFQUERYPARAM...

Opinions are apprectiated

Thanks

Jorge Beranza

GrowlyBear
07-08-2005 @ 12:25 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1314
Joined: May 2004

I'll make a wild guess.
anytime you use debugging you will "see" things "slower"
Why? because it take more time to process the page AND do debugging as compared to processing a page alone.

anyone got ideas?

~~~~~~~~~~~~~~~~~~~
There is no place like 127.0.0.1

JJfutbol
07-08-2005 @ 12:44 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1250
Joined: Nov 2004

I agree with GrowlyBear. Don't use debugging. Just use the GetTickCount function. Read up on it in DWMX2004, just hit CTRL + F1 and the CF Docs will popup. Use that function at the very very top of your cfm page and then another tick function (forgot the name) at the very very bottom. Add them together and display the value. It will give you the time in milliseconds. Do that, as its much more reliable. I definitely think its the debugging slowing it down. It would be hard to convince the preacher of cfqueryparam otherwise. Smile

----------------------------------------------------
Need FREE CF applications?? Then my site has it all, http://www.javier-julio.com/coldfusion/downloads/ In time I will be including many more free CF apps as you will see many listed up top on that page.

This message was edited by JJfutbol on 8-15-05 @ 12:29 PM

CJ
07-08-2005 @ 12:59 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4262
Joined: Oct 2002

um...the one at the bottom is the same as the one at the top Smile

<cfset start = getTickCount() />
    code here
<cfset end = getTickCount() />

<cfset processingTime = end - start />

<cfoutput>the code executed in: #processingTime# ms</cfoutput>

-CJ-
@ #coldfusion/DALNet
http://charlie.griefer.com

JJfutbol
07-08-2005 @ 1:04 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1250
Joined: Nov 2004

Aye it is, I just stumbled across the tick count code I used in my old forum app and it uses both same functions. When I responded I only remembered the first bit of code not the last but I find it very very useful. I have never used the debugger before for timing, its much easier to just use that.

----------------------------------------------------
Need FREE CF applications?? Then my site has it all, http://www.javier-julio.com/coldfusion/downloads/ In time I will be including many more free CF apps as you will see many listed up top on that page.

This message was edited by JJfutbol on 8-15-05 @ 12:29 PM

Lola
08-06-2005 @ 12:50 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 363
Joined: Feb 2005

The
<cfqueryparam>
tag does not increase performance for the MySQL database server in the current stable versions, this is due to the fact that MySQL does not have bind variables. Bind variables are basically a compiled statement stored on the server that tell what type of field is being used in the sql statement, therefore, the engine doesn't have to check the field, see what datatype it is, etc etc, each and every time.

Use
<cfqueryparam>
to make sure that the data being passed isn't escaped from the quotes surrounding it.

I think the tag only works with Oracle, and maybe SQL Server.

This message was edited by Lola on 8-6-05 @ 12:50 AM

beranza
08-22-2005 @ 12:35 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 5
Joined: Jul 2005

Lola,

Thank you for the concise and well articulated answer. Your insight has confirmed my hunches as well as the various whispers online.  You are a true scholar..

Jorge Beranza

Lola
08-22-2005 @ 5:32 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 363
Joined: Feb 2005

Thanks,

Basically, to summarize what I said, <cfqueryparam> doesn't apply to MySQL. If you are using MySQL, the only good reason to use <cfqueryparam> is to make sure that any data being passed doesn't escape from the value field.

Therefore, with MySQL, you should be using <cfqueryparam> such as: <cfqueryparam value='...'>, without specifying any of the other attributes. The datatypes provided by <cfqueryparam> are in complete different ranges than the ones accepted MySQL, and will decrease performance, as every field you pass, will have to be checked to make sure it matches the datatype.

For instance, in cfqueryparam, you can choose INTEGER as a datatype, but the INTEGER in cfqueryparam will have a different number range than INT in MySQL.

And here are a few articles on bind variables:

http://www.rittman.net/archives/000832.html

http://www.akadia.com/services/ora_bind_variables.html

http://12.46.245.173/help/wvtwbind.htm

This is what <cfqueryparam> is performing on Enterprise database platforms (not in MySQL).

This message was edited by Lola on 8-22-05 @ 5:44 PM


Website Designed and Developed by Pablo Varando.