Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 1.0 and 1.1 > ASP.NET 1.0 and 1.1 Professional
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 1.0 and 1.1 Professional For advanced ASP.NET 1.x coders. Beginning-level questions will be redirected to other forums. NOT for "classic" ASP 3 or the newer ASP.NET 2.0 and 3.5
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 Professional section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 15th, 2005, 04:51 PM
Friend of Wrox
Points: 2,876, Level: 22
Points: 2,876, Level: 22 Points: 2,876, Level: 22 Points: 2,876, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Denver, CO, USA.
Posts: 428
Thanks: 57
Thanked 2 Times in 2 Posts
Default System.OutOfMemory Exception

I have a program that retrieves around 45M of SQL Server data, builds an internal table, and then reduces the retrieved data to around 5M to produce a report. Every so often, when running the report multiple times simultaneously, it throws this exception. Most frequently, it appears to be getting it from the SQL Adapter, but every so often it comes from an attempt to establish a filter on the data once it's been associated with a DataView.

Microsoft insists it's a code problem and has pointed me to a Performance Enhancements Tricks and Tips link that only wasted my time determining its suggestions didn't apply. I could probably trap for the error and tell the user to try again later, but that wil result in as many unhappy users as we have when the error pops up now. I don't understand why, in a virtual environment, it's hitting this error to begin with. Is there some trick to avoiding this altogether, short of reducing the data retrieved? Can anybody point me to something which might actually help rather than just passing the buck the way Microsoft did?
  #2 (permalink)  
Old November 16th, 2005, 03:44 PM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Question: Why do you retrieve so much data, only to reduce it? My suggestion, create a stored procedure. In the stored procedure only return the data you will actually display.

Hope this helps...
Jim

  #3 (permalink)  
Old November 18th, 2005, 05:32 PM
Friend of Wrox
Points: 2,876, Level: 22
Points: 2,876, Level: 22 Points: 2,876, Level: 22 Points: 2,876, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Denver, CO, USA.
Posts: 428
Thanks: 57
Thanked 2 Times in 2 Posts
Default

The reason for all these gyrations is that the data is very normalized. The process in question is denormalizing it for reports. Here's the query being passed to the server by the Data Adapter:

Select * from SurveyResults with (NOLOCK) where SurveyID=33 AND ( QuestionID=1119 OR QuestionID=1114 OR QuestionID=1115 OR QuestionID=1116 OR QuestionID=1117 OR QuestionID=1118 OR QuestionID=881 OR QuestionID=916 OR QuestionID=882 OR QuestionID=884 OR QuestionID=883 OR QuestionID=885 OR QuestionID=886 OR QuestionID=887 OR QuestionID=888 OR QuestionID=889 OR QuestionID=903 OR QuestionID=906 OR QuestionID=907 OR QuestionID=908 OR QuestionID=909 OR QuestionID=910 OR QuestionID=911 OR QuestionID=912 OR QuestionID=913 OR QuestionID=914 OR QuestionID=915 OR QuestionID=901 OR QuestionID=902 OR QuestionID=890 OR QuestionID=904 OR QuestionID=899 OR QuestionID=891 OR QuestionID=892 OR QuestionID=898 OR QuestionID=1110 OR QuestionID=1111 OR QuestionID=1112 OR QuestionID=1113 OR QuestionID=-5) AND ((UserID like '%IMPORT%') ) Order by UserID, SurveyTimeStamp, QuestionID

Each record associated with a QuestionID contains ONE Field for which I need the value. Multiple QuestionIDs associated with the same values for UserID and SurveyTimeStamp are then written to a single record constructed in an internal table. Here's an example of the data retrieved to produce only ONE denormalized record:

118049 IMPORT 33 -5 7/21/2005 7:33:33 AM 7/26/2005 1:12:28 PM NY\513670 Y 7/21/2005 7:33:33 AM
118045 IMPORT 33 881 7/21/2005 7:33:33 AM 7/26/2005 1:12:28 PM NY\513670 001 7/21/2005 7:33:33 AM
118047 IMPORT 33 882 7/21/2005 7:33:33 AM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220431 IMPORT 33 883 7/26/2005 1:21:51 PM 7/26/2005 1:12:28 PM NY\513670 Verification 7/21/2005 7:33:33 AM
118048 IMPORT 33 884 7/21/2005 7:33:33 AM 7/26/2005 1:12:28 PM NY\513670 Internet 7/21/2005 7:33:33 AM
220432 IMPORT 33 885 7/26/2005 1:21:51 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220433 IMPORT 33 886 7/26/2005 1:21:51 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220434 IMPORT 33 887 7/26/2005 1:21:51 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220435 IMPORT 33 888 7/26/2005 1:21:51 PM 7/26/2005 1:12:28 PM NY\513670 No 7/21/2005 7:33:33 AM
220436 IMPORT 33 889 7/26/2005 1:21:51 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220450 IMPORT 33 890 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220453 IMPORT 33 891 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220454 IMPORT 33 892 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220455 IMPORT 33 898 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220452 IMPORT 33 899 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220448 IMPORT 33 901 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 No 7/21/2005 7:33:33 AM
220449 IMPORT 33 902 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220437 IMPORT 33 903 7/26/2005 1:21:51 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220451 IMPORT 33 904 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220438 IMPORT 33 906 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 Yes 7/21/2005 7:33:33 AM
220439 IMPORT 33 907 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 N/A - Not Verified 7/21/2005 7:33:33 AM
220440 IMPORT 33 908 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220441 IMPORT 33 909 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220442 IMPORT 33 910 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220443 IMPORT 33 911 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 No 7/21/2005 7:33:33 AM
220444 IMPORT 33 912 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220445 IMPORT 33 913 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220446 IMPORT 33 914 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220447 IMPORT 33 915 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
118046 IMPORT 33 916 7/21/2005 7:33:33 AM 7/26/2005 1:12:28 PM NY\513670 080-18-5156 7/21/2005 7:33:33 AM
220456 IMPORT 33 1110 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220457 IMPORT 33 1111 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220458 IMPORT 33 1112 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
220459 IMPORT 33 1113 7/26/2005 1:21:52 PM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7:33:33 AM
118039 IMPORT 33 1114 7/21/2005 7:33:33 AM 7/26/2005 1:12:28 PM NY\513670 F01 7/21/2005 7:33:33 AM
118040 IMPORT 33 1115 7/21/2005 7:33:33 AM 7/26/2005 1:12:28 PM NY\513670 Boston 7/21/2005 7:33:33 AM
118041 IMPORT 33 1116 7/21/2005 7:33:33 AM 7/26/2005 1:12:28 PM NY\513670 A03 7/21/2005 7:33:33 AM
118042 IMPORT 33 1117 7/21/2005 7:33:33 AM 7/26/2005 1:12:28 PM NY\513670 001 7/21/2005 7:33:33 AM
118043 IMPORT 33 1118 7/21/2005 7:33:33 AM 7/26/2005 1:12:28 PM NY\513670 001 7/21/2005 7:33:33 AM
118044 IMPORT 33 1119 7/21/2005 7:33:33 AM 7/26/2005 1:12:28 PM NY\513670 7/21/2005 7/21/2005 7:33:33 AM
118060 IMPORT 33 -5 7/21/2005 7:33:34 AM 7/26/2005 1:04:00 PM NY\513670 Y 7/21/2005 7:33:34 AM

Sorry it's not very well formatted, but this was the best I could do in this tiny input window. The questionid is brown, TimeStamp is green, userid is blue, and the answer red. Hopefully the columns will line up in full screen. Anyway, once the data is denormalized, with all the answer values from all these records written to a single internal record, I then apply a filter to the dataview associated with the constructed record set to further reduce the records to just those with the answer values needed for reporting. IOW, a second query is then run against the denormalized records by applygin a filter to a data view pointing to the constructed, denormalized table. I can think of no other way to do this with the normalized data above, especially when the user wants only the denormalized records where the Answer for Question 1118 equals "Y", "P" OR "Z", Question 1132 is less than 15, Question 1195 is between 52 and 55, and Question 1209 is not equal to "Denver". It seems to me the Select statement would be so incredibly complex to construct and so long, it might not be processible.

It's also important to know that the list of Questions/Answers to be written to the report may vary from run to run. So the user may want the QuestionIDs listed in the query above today, and a whole different list of QuestionIDs on the next run. So any SP must be able to accommodate a potentially infinite number of combinations of values for the QuestionID field in order to be of any use. I am not particularly experienced with SPs, and when I have attempted to use them in the past, found them difficult to debug when problems arose.

Can you suggest a way to write a very generic SP that would be of any value to this process? Otherwise I'm back to solving this OutOFMemory exception.
  #4 (permalink)  
Old November 19th, 2005, 01:13 AM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Construct a SP with parameters for UserID, TimeStamp and a Varchar parameter for possible QuestionIDs. Then you can use an "IN" clause for QuestionID instead of all of the ORs.


  #5 (permalink)  
Old November 21st, 2005, 04:13 PM
Friend of Wrox
Points: 2,876, Level: 22
Points: 2,876, Level: 22 Points: 2,876, Level: 22 Points: 2,876, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Denver, CO, USA.
Posts: 428
Thanks: 57
Thanked 2 Times in 2 Posts
Default

This might help if the problem was performance related. But it'll still be returning the same set of data whether a stored procedure or simple select is used. So, I don't see how this change will help with this problem.

Does somebody have a clue how to correct the OutOfMemory exception>?
  #6 (permalink)  
Old November 22nd, 2005, 12:41 AM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Have you tried running it on a different server? Could it just be simply you need more memory on the server? Maybe the server needs to be wiped and started from scratch.

  #7 (permalink)  
Old November 23rd, 2005, 01:14 PM
Friend of Wrox
Points: 2,876, Level: 22
Points: 2,876, Level: 22 Points: 2,876, Level: 22 Points: 2,876, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Denver, CO, USA.
Posts: 428
Thanks: 57
Thanked 2 Times in 2 Posts
Default

We've tried running it on several different servers with different loads and it's consistently getting this error when running 7 or more simultaneous requests. It refers me to the Event Log for more info, but there's nothing there I can find to help explain it. When this happens, it then starts throwing APPLICATION SERVER UNAVAILABLE and I have to stop and restart IIS Admin Service (and its dependencies) in order to get to the site once more. So it's evidently taking down the web server when it hits this and therefore pernicious. More memory might delay getting it, but I'd bet it will still come up eventually.
  #8 (permalink)  
Old November 30th, 2005, 10:08 AM
Friend of Wrox
Points: 2,876, Level: 22
Points: 2,876, Level: 22 Points: 2,876, Level: 22 Points: 2,876, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Denver, CO, USA.
Posts: 428
Thanks: 57
Thanked 2 Times in 2 Posts
Default

For enquiring minds, here's what MicroSoft has to say about this problem:

> Your observation seems correct about OOM exceptions. These
> exceptions are usually caused when any specific heap reaches
> 64 segments. All the heaps once spawned can have maximum of
> 64 segments. Combination of very large and small allocations
> on such heaps would cause heap fragmentation. All the heap
> managers have an algorithms to reduce the fragmentation
> (scheduled heap clean up, GC in example of managed heap).
> These algorithms kick in at certain time intervals. In a case
> where the large/small allocations are happening way too fast
> even before next cycle of algorithm triggers, we run into OOM
> exceptions. If any code is making virtual memory allocations
> directly then this goes to RAM directly and VM does not have
> any heap fragmentation algorithms. Some code use VM for
> faster processing.

IOW, space allocation for datatables does NOT use VM management and any attempt to load very large record sets results in the Out Of Memory exception. What's worse: repeated OOMs in a short time locks up IIS resulting in SERVER APPLICATION UNAVAILABLE errors in all future attempts to access the site (and probably all other applications in the same pool). The only way I have found to get IIS to deliver pages once more is to restart the service and dependents.

There appears to be no programming solution to the problem - just ways to mitigate it. I've reduced the number of records being retrieved at any one time to a much smaller number. For large reports this results in slower response times, and it seems likely with our database growth that we will eventually run into the problem again, but that's the best advice MS can offer. There's the /3GB switch which increases the total memory for IIS applications from 800MB to 1.8GB, but MS says that won't help much in this instance. We can throw more servers at the problem to balance load better. And there's the promise that a 64bit OS will give us more breathing room, too. But the real solution, it appears to me, is for MS to eliminate this 64 segment ceiling and fix .NET so it uses VM management when RAM is insufficient. No promises on that yet, though.
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can handle OutOfMemory Exception jazzydonald Pro Visual Basic 2005 2 October 5th, 2007 07:10 AM
System out of memory exception cmrhema Visual Studio 2005 0 August 21st, 2007 08:38 AM
How to handle OutOfMemory Exception FileFound Visual Studio 2005 4 July 3rd, 2007 07:23 AM
System.Exception .aspx website dephjam C# 3 April 13th, 2005 07:59 PM
System.TyepInitialization.Exception w connection fig000 Pro VB.NET 2002/2003 1 July 18th, 2003 08:37 AM



All times are GMT -4. The time now is 11:07 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.