April 21, 2011 at 8:16 am
I read 197 million rows of data, make modifications, then write the 197 million rows to a file. The process is kicked of from a Windows 2003 64 enterprise server using an asp.net web interface as the front end. The database is on a separate Windows 2003 64x enterprise server. The process times out at 200 million rows when writing the file.
There are no timeout errors in the SQL Server log orthe application event log. I just get and error returned from the asp.net front end indicating it timed out.
Is there a recordset limitation and is there a configuration option to get around it.
April 27, 2011 at 8:00 am
Can you chunk this out into batches? It would probably make it run a ton faster if you handle this in batches instead of all at once. I don't know of anything that says 200 million rows is the max but that is crap load of data to try to hold all in memory. It sounds like it the asp.net that is timing out. There are many reasons this could be happening. There is an execution timeout in the web config that might be exceeded. Depending on how you are getting the data it could be connection timeout. My guess is that the only way you will be able to fix this long term is batching. Anything you do now to increase the timeout will work until the data size is bigger than the new limits. If there is one thing consistent about data it is that it grows exponentially over time.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 28, 2011 at 9:16 am
You need to set the timeout of your connection object AND your command object, default timeout is 30 seconds
e.g.
DIM cn as NEW adodb.connection
DIM cmd as NEW ADODB.command
cn.connectionstring = cnstr
cn.connectiontimeout = 300
cn.commandtimeout = 300
cmd.commandtimeout = 300
--
JimFive
April 28, 2011 at 9:19 am
It may very well be an IIS execution timeout given the amount of data and the time it takes since this probably called during a post back. If the original poster can provide the actual message we can figure out where the error is originating.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply