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?

 

use perl/php and mysql/postgres on unix/linux/bsd

 

not acceptable!

 

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

 

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.

 

personally i prefer postgres for all applications.

 

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.