Shoot...how do you speed up Cold Fusion queries?
06-05-2001, 07:54 PM
#1
Registered User
Thread Starter
Join Date: Oct 2000
Location: Baltimore
Posts: 10,134
Likes: 0
Received 0 Likes
on
0 Posts
CFM query through ODBC (non-MS DB). I thought about having it call stored procedures...but the store procedures option is not available...
any suggestions?
06-05-2001, 09:33 PM
#4
Registered User
Join Date: Apr 2001
Location: Boulder Creek
Posts: 111
Likes: 0
Received 0 Likes
on
0 Posts
Mingster:
The best way to speed up your queries is to cache them of course.
Also ColdFusion 5.0 uses Merant ODBC drivers, greatly improving the speed for non-MS DB's.
CF Enterprise has native DB drivers for most non-MS DB's. I believe they allow you to use stored procedures.
I believe non-MS ODBC drivers also allow execution of stored procedures (Oracle for example) using CFSTOREDPROC, but my experience is primarily with MS-SQL server so...
You might want to try posting your question to the list server at http://www.bacfug.net .
I'll help you figure it out for a free X-brace
Seriously, if you need more help I'll turn you over to my husband who is a Certified Allaire Developer.
-Pam
The best way to speed up your queries is to cache them of course.
Also ColdFusion 5.0 uses Merant ODBC drivers, greatly improving the speed for non-MS DB's.
CF Enterprise has native DB drivers for most non-MS DB's. I believe they allow you to use stored procedures.
I believe non-MS ODBC drivers also allow execution of stored procedures (Oracle for example) using CFSTOREDPROC, but my experience is primarily with MS-SQL server so...
You might want to try posting your question to the list server at http://www.bacfug.net .
I'll help you figure it out for a free X-brace
Seriously, if you need more help I'll turn you over to my husband who is a Certified Allaire Developer.
-Pam
06-05-2001, 09:39 PM
#5
Registered User
Join Date: Apr 2001
Location: Boulder Creek
Posts: 111
Likes: 0
Received 0 Likes
on
0 Posts
Josh:
I wasn't aware that ColdFusion server could interpret perl/php!
BTW: mysql, while free, isn't as robust as MS-SQL or Oracle and so isn't recommended for Enterprise level applications.
I wasn't aware that ColdFusion server could interpret perl/php!
BTW: mysql, while free, isn't as robust as MS-SQL or Oracle and so isn't recommended for Enterprise level applications.
06-05-2001, 10:23 PM
#7
Registered User
Join Date: Nov 2000
Location: SF
Posts: 1,001
Likes: 0
Received 0 Likes
on
0 Posts
using 'robust' and 'sql server' in the same sentence is dangerous at best.
regardless (however) the advice to go with native drivers is correct. this will not only allow you to use stored procedures but will also have a direct effect on performance of a basic query.
in many cases, however, the best way to speed these operations up (assuming you can't move to a more 'robust' platform and toolset) is to cache not just the query, but also the result set. this obviously is of little value when you don't have common or repeated queries (or where your server has limited memory).
a robust and mature database engine will always cache queries once interpreted and processed, but this is within the database engine. optimizing the query within the database engine can only solve part of the problem. as much of the query time in a situation like this occurs in communication with the database (as opposed to within the database) if you have commonly repeated queries (or a single query that is repeated a couple times in a short period of time) you can see performance increases of an order of magnitude by caching the result set in a write through cache within your application logic layer.
hmmm... now that i re-read this i realize i have made some unconcious assumptions. before doing any of this you need to make sure that your DB is well architected (at a data level and at a physical level) and well optimized and that your queries are well designed. thinking back, most people i've helped who have had poor DB query performance have been forced into doing hideous inner joins or the like due to bad DB design. buy the CJ Date book.
regardless (however) the advice to go with native drivers is correct. this will not only allow you to use stored procedures but will also have a direct effect on performance of a basic query.
in many cases, however, the best way to speed these operations up (assuming you can't move to a more 'robust' platform and toolset) is to cache not just the query, but also the result set. this obviously is of little value when you don't have common or repeated queries (or where your server has limited memory).
a robust and mature database engine will always cache queries once interpreted and processed, but this is within the database engine. optimizing the query within the database engine can only solve part of the problem. as much of the query time in a situation like this occurs in communication with the database (as opposed to within the database) if you have commonly repeated queries (or a single query that is repeated a couple times in a short period of time) you can see performance increases of an order of magnitude by caching the result set in a write through cache within your application logic layer.
hmmm... now that i re-read this i realize i have made some unconcious assumptions. before doing any of this you need to make sure that your DB is well architected (at a data level and at a physical level) and well optimized and that your queries are well designed. thinking back, most people i've helped who have had poor DB query performance have been forced into doing hideous inner joins or the like due to bad DB design. buy the CJ Date book.
Thread
Thread Starter
Forum
Replies
Last Post
integrate
Off-topic Talk
47
03-12-2002 08:27 PM