Tuesday, August 03, 2004

Query Bottlenecks

One of the biggest barriers to scalability and long-term performance is how we logically grab the flow of data from the database.

So I am creating a list of things to not do, and to do.

1. Do not loop thru cfquery, because each cfquery creates a seperate jdbc/odbc connection which takes additinal memory/processing power etc. So look at the different queries and see if you can combine them.

But it depends on the relationships, if for example you have two queries and the second is looped over by the first one, perhaps you can do a join.

Remember the power of group by in the cfoutput statements....

You can use this technique, based on the join you use, to combine the queries and still get the same results.

Remember the issue is to reduce the number of cfqueries, as much as possible.

2. Do not ever, ever do select *. It's always best as a standard and for performance, to specify in the correct order, the exact field you need for your query.

For example if you have a table of 40 fields, and you run a query that needs only five, think of how much wasted memory/cpu, you are using improper coding.

If coldfusion is going to be the best language, then we have to always code for longterm, scalable performance, even if it takes us longer to type in.

So once you have performance nailed down, then it is a matter of improving upon it.

1. Such as moving complicated, highly used joined queries into views or stored procedures.
2. Review the performance of the page, by debugging, look at the cfincludes and cfqueries for those that take too long.
3. Learn all about caching, when and where.

Cachedwithin, Cachedafter, or cfqueryparam

CFQUERYPARAM is a very powerful tool, both to help validate incoming url/form data to a specific data type. Which is great for preventing cross-scripting, and errors.

For oracle databases, it actually improves performance, because of something called bind-variables. Basically if you have 1 query running dozens of times, but just a different value in the where clause, then bind-variables, allow the database to treat the queries as the same query, which caches it and improves performance.

In the end, query bottlenecks, are caused by poor planning, and poor coding. These can be overcome, and they should be overcome.

Coldfusion has so much potential to be the most scalible web development language/server, if we code that way.


No comments:

Post a Comment