CODEWITHSUNDEEP
performancecoldfusioncoldfusion-8
Daniel
Daniel

Reputation: 35674

ColdFusion improving performance of queries within loops

I've got a database setup that is a bit on the complicated side, with several many-many tables.

I'm trying to generate an XML document from this data. There's a bit of checking, like if a name is not defined in one language try to get the name from another language (instead of showing null)

The problem I have that there are a lot of queries within loops.

Are there any guidelines for this, like what stuff to stay away from and what to use, to improve the performance?

cfoutput cfloop cfquery ?

Upvotes: 1

Views: 416

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20794

Regarding, " like if a name is not defined in one language try to get the name from another language (instead of showing null)".

You should be able to do this in your query. Pretty much every db out there has a coalesce function. They all support case constructs as well. You just have to pick the most appropriate method for your situation.

Upvotes: 1

Adam Cameron
Adam Cameron

Reputation: 29870

If the looping logic is basically doing data processing, eg: based on the values from the first query, deciding what to go back to the database with for the next query, the best thing you can do for performance is to take all that logic out of your CF code, and put it into the DB. Use the DB for data processing, use CF for handling the data once it's been processed, and converting it into output.

The only time CF should be doing data manipulation is if you need to process data from differing sources: eg the database, some remote service, the file system, a different database, etc. Basically only if the database can't do the data processing itself should you be involving ColdFusion.

Upvotes: 4

Related Questions