Out of memory web service exception when populating ADO.Net DataSet
1) ClientApp makes an asynchronous call to ASP.Net 2.0 WebService 2) Web services call a SQL Server 2005 stored procedure 3) Stored procedure returns data output, 150MB table
A memory exception is thrown by DataAdapter.Fill (...) when trying to allocate more memory for new rows.
The IIS application pool has no maximum memory limit.
Are there maximum memory usage limits set elsewhere at the IIS level? Should a 150MB db table take up much more space when reading into memory as a DataSet? Is there a scenario (possibly with WCF) where the result of a procedure should never be in the memory of the web server, but will be passed directly to the client?
I would rather not split the request into smaller datasets because the client requests them asynchronously. Collecting all parts must also be asynchronous, and each client will need to implement an asynchronous build for every call.
Any suggestions, recommendations or advice would be appreciated.
a source to share
Yes, using DataSets uses a lot more memory than the actual data. How much is difficult to quantify, but this question on StackOverflow suggests more than 4 times the original data size. Let's assume this is correct. 150 MB of data 4 = 600 MB of memory. When ASP.NET application uses about 800MB of RAM, they will start throwing OutOfMemoryExceptions. I am not sure how this limit matches the application pool memory limit. Have you tried the / 3GB switch in boot.ini? (See this article for instructions)
Also note that if you are serializing your DataSet, the serializer can allocate huge buffers for serialization (up to 10 times the original size, see this article . You indicate that the problem occurs when you read the data, so it probably isn't is the cause of your error (but maybe if you solve the error out of memory and try to send data over the wire).
My experience with DataSets is that they may seem like a good idea at first, but you will run into problems very soon.
Another (probably better) solution would be to use a DataReader and read one line at a time. Return batches of rows (i.e. use some kind of paging for the data) and experiment with the size of each batch to find the sweet spot between performance and memory usage. Streaming WCF can do the trick, but you will need to properly configure WCF to be able to return such huge amounts of data in a single call.
a source to share