0

I have a table that has 500K rows (10 fields) on SQL Server 2008. I need to query them all. please dont ask why should i need to query them all as that is outside of my question. my question is that my coldfusion server freezes everytime i run the coldfusion page that has a query in it.

<cfquery name='' datasource=''>
SELECT one,two,three,four,five,six,seve,eight,nine,ten
FROM tblname;
</cfquery>

what bugs me is that the same condition (500K rows, 10columns) of a table on my MySQL server only needs about 20secs to finish the query on the same coldfusion page.

any tips?

kapitan
  • 2,008
  • 3
  • 20
  • 26
  • 1
    I guess the first step I would take is caching the query depending on how often it is used. Can you do that, or possibly break it up into a few different queries that don't all run at the same time? – TRose Dec 16 '15 at 02:46
  • 2
    Check your exception log. You probably have a java heap space error. Whatever it is you are looking to achieve, pulling that much data into your application server is probably not the best way to go about it. – Dan Bracuk Dec 16 '15 at 03:57
  • 2
    500k rows is a LOT of data. What are you doing with the data when you get it back from the DB? Could just be that ColdFusion is simply chugging along processing it all. – Scott Stroz Dec 16 '15 at 04:20
  • 1
    If this for an export/download, CF is ill suited for that type of task. [Use db tools such as sqlcmd or bcp](http://stackoverflow.com/questions/14212641/export-table-from-database-to-csv-file), which are specifically designed for those kinds of jobs. – Leigh Dec 16 '15 at 17:12
  • 1
    You say that ColdFusion freezes every time that you run the page with the query on it. Have you verified that the query is the piece of the page that is running slowly? Using getTickCount() plus some logging can establish exactly where on the page the slow-running code is. – Steve Bryant Dec 17 '15 at 21:29
  • thank you for all of your answers. the thing is, it is a bad designed query which checks the balances of input-output items. the page was poorly done by someone who resigned from my company. i rewrite the routine that every output of each item, the system would calculate the qty balance. so that when i run the report page (the page that i have a problem), only those who have remaining qty balance would be queried. thanks to all who gave time. i have upvoted your comments. – kapitan Apr 11 '16 at 06:57

0 Answers0