asp.net worker process grow tremendously

[Guest]

Hi experts,

I have an application which will retrieve data from database and extract the
data to Excel file.

My working mechanism is to create a while loop to loop at every criteria/key
which will execute a MSSQL stored procedure to retrieve the data based on
particular key/criteria. The results will be returned to my application once
the stored procedure is finish executed.

Problem happens when results to be returned consist of many rows (says few
tens of thousand rows). The asp.net worker process will grow tremendously
until reaching 800++MB and the CPU usage hits 90% which cause the server in
crawl.

Any workaround I can take to cope this problem? Any help would be much
appreciated. Thanks

 

[Guest]

Are you loading Excel on the server? If so that may be the problem. I would
try to isolate the problem by commenting out different things in the process
until you notice the increase then it would help to determine the next course
of action.

 

[Guest]

Thanks Robert. The Excel file is directly opened at client site. Usually the
asp.net worker process will just increase in great scale when the records to
be returned are reaching 10,000 rows. But my case is even worse, the records
might reach 80,000 - 90,000 rows in normal query. I'm keen to know is there
any way to avoid the worker process grows like crazy.

 

[Brock Allen]

Consider disabling buffering when generating the response.

 

[jasonkester]

Are you sure you're disposing of your connections properly? Since
you're tasking the CPU so hard, it may just be waiting around for a
good lull in the action in which to garbage collect. You can help it
along with using{}'s , .Dispose()s and a small Thread.Sleep() in your
loop.

This has worked for me in the past when I've hit the problem you
describe.

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/