Fix ColdFusion Database Problems & Be A Hero [Part 2]

The least favorite thing to hear from a support person is that you need to tune your database or code. Let’s be honest, it’s a roadblock. For the busy developer, free time to tune things isn’t even on the radar. Maybe you’re a one person team, and don’t have support to shine a light on the problems you’re facing. Read on – there’s hope!

What Causes ColdFusion Performance Problems?

ColdFusion runs considerably well when everything is working and running well. It doesn’t do so well to isolate a single problem from the other apps that are just fine. Conversely even if the problems are the ONE application you have, ColdFusion misbehaving at random times is no fun.

What Are The MAJOR Causes?

Select * queries, numerous joins and frequently accessed large tables with no index. Period. If these areas are managed well, you’ll have very little trouble. Yes there’s more, but if this area isn’t managed, you’ll have major trouble seeing gains from tuning anything else.

There’s a great post by Ben Nadel many years back called “ColdFusion Session Management And Spiders / Bots“. This deserves to be read and re-read also. It could save you from having problems with ColdFusion due to sheer annoyances caused by bots/spiders.

[Tweet “Problems in ColdFusion apps? Usual causes – bad db queries, numerous joins & missing indexes.”]

How Do I Find And Fix Slowness Related To Databases?

The first step is to discover which pages contain costly queries (costly = wasteful and slow). Start with this post on ColdFusion performance problem discovery. Using easy to find and install tools anyone can find problems, on the surface.

Got Tools Installed, What’s Next?

Just finding the page requests that’s taking > 500ms might be easy – fixing can take a bit of work. But it’s worth it. Amazon found every 100ms of latency cost them 1% in sales.

1. Locate the query causing you trouble (see first post).
2. Create a query with real data. Using the same sql statement, that’s slowing you down. It will look something like “Select * from tbl_employees”.
3. Use a SQL Tool to run this query (PHPMyAdmin for MySQL and SQL Management Studio for SQL for example).
4. **TO GET HELPFUL INTEL** Before executing the query:
   1. MSSQL. Use Management Studio. Start a new query, tick the menu item “Include Actual Execution Plan” under the “Query” menu. This StackOverflow page has some helpful info.
   2. MySQL. Preface your query with EXPLAIN. See the MySQL docs about EXPLAIN.
5. Run the query.

The first time you look at this, you might glaze over. Don’t worry. What we’re trying to do is get an understanding about why this slow query is taking longer than normal to complete.

Quick Improvements

If you are using Select *, you might be able to stop before doing any of the above. Simply specify the table and columns – and if possible in your query reference the primary or secondary key which should be indexed.

Deeper Performance Tuning

• Simplifying SQL query logic. As mentioned above, and there’s TONS of resources out there to read up on how to create efficient queries. Now you’ll know where to apply your time.
• Create appropriate indexes. I found this article quite enjoyable about MySQL Explain and optimizing queries. And this for MSSQL – which is lengthy but does a good job of reminding you to eliminate the unnecessary and create indexes in the right places.

It doesn’t have to be complicated. Honestly, you can make really big improvements in a short period with a little dedicated time performance tuning.

You will get monumental wins, and drive down page response times.

ColdFusion Performance Consulting

The Hostek team has the ability to take a look at all aspects. From the type of hosting, Shared or ColdFusion VPS Hosting. Quantity and resources of each server.

Have problems you’re facing now? We’d love to speak with you. Schedule a free consultation, and we promise not to try selling you ANYTHING. We’ll discuss improvements. If Hostek is a good fit, we’ll discuss available platforms.

I hope this helps you have the courage to crack open some tools and make significant improvements to performance for your app!

Categories: Developers, Hosting