Ask a Jedi: Dealing with an incredibly slow query

Jay asked an interesting question:

I have a question about how to do something for pulling data from a database using CF. I need to run a VERY large query over night and then have the data somewhere I can either query against the next day to get at it in smaller chunks or look at in a more manageable way. Do you have any thoughts on how that might best be accomplished?

Obviously the very first thing you want to do is ensure you have done everything possible to ensure that the initial data is as optimized as possible. I find it hard to believe that one query would take so long. I don’t mean to belittle your SQL skills, but I know mine aren’t that great and this is exactly the case where I’d bring in an experiences DBA.

That being said - let’s assume that there really isn’t anything you can do. I can think of a few ways to handle this.

The easiest solution would be to take your resultant query and insert it into a database. (Or even better, change your initial query so that it doesn’t return the result, but simply save the results to the new table.) Once the data is in your database, you can perform queries against it.

Another option is to store the result in text. This text could be read by another program to perform stats. This will definitely be slower then using the database, but depending on how you want to use the data, it might be an option as well.

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate. He focuses on JavaScript, serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Comments