Hello, I need some advice about how to best deal with large amounts of data.
We are using a security model that implements object level security (down to buttons and links in a page) for this project, and the maintenance screen to mange the object level security is causing us some problems. This page filters all of our object security data (over 4 million records and expected to grow) and allows an administrator to mange roles that are allowed access to all of our existing objects (as well as add new objects, and assign role based permissions). We know it will be a slow page, but as this page is not access by a lot of people, speed is not crucial, but it does need to work. Currently I am getting a "500 null" response in the browser when I am running a stored procedure that filters the data, when the data returned is returning just over a million records. Several tests have shown the same procedure runs and results are returned to the browser when results are less than 1 million (our current data has results of 1,028,848 / 2,882,429 / 904 / 2,452 records and we fail on the first two result sets, but the second two can be output to the browser without any problem). Just running the query (via a cfstoredproc call) and trying to access the record count (not even looping over the query) results in the same "500 null" error in the browser when the parameters passed cause the larger dataset to be returned, so I can only imagine this is an issue where CF and/or the web server are having trouble retrieving/outputting the data. The stored procedure runs on the database in roughly 1:30 minutes (with the parameters that cause the 1 million result set) but when the results are being outputted to the page, I get the error. Same issue occurs on the record set for over 2 million records. I have tried to improve the stored procedure with the use of a view, and that barely speeds up my database response (when stored procs are called directly in the enterprise manager); regardless of response time I still have the same amount of data being returned that cf/iss cannot seem to handle. It seems that cfflush would be the solution to my problem, but we cannot use cfflush as we are using Fusebox3 for this application, therefore we have incompatibility with cfsavecontent. Have tried implementing the max value on blockfactor on the cfstoredproc tag; this does not improve the situation. Our environment is as follows: Running CFMX 6.1 with Fusebox 3 methodology MS SQL Server 2000 Windows Server 2003 and IIS Suggestions?? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294845 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
