C#.NET & Oracle database: how to avoid "out of memory" exception when querying large volume data.

Posted on 2009-12-22
Last Modified: 2013-12-17
Hi Experts:

    I am writing a batch process program with C#.NET in VS2005 connecting an Oracle database.
    The basic logic is as the attached code skeleton. The query in DbCommand object object will return a lot of data. In development environment I debug the program and monitored the memory usage. I found that at the first execution of the "dataReader.Read()" command, 700 Mb memory would be used. This would stop my batch program from working, because:
1. in production environment the data volume can be several times larger.
2. the program needs a lot of memory at other places, either another data query at the same time or building up large collection data structures.

    Please give me some opinion how to resolve this issue from programming perspective:
1. is there anyway not to let dataReader.Read() use all the memory at once? I am guessing it buffers all the data from Oracle database at the first execution. Instead of doing that, is there any way to let it buffer part of the data at a time (such as 10 Mb)?
2. any other approaches to reduce the memory usage?

    Many thanks!
DbCommand cmd;

// construct the cmd variable as a query 

// returning large volume of data

using (IDataReader dataReader = db.ExecuteReader(cmd))



   while (dataReader.Read())   // causing a lot of memory

                               // in the first time run


       // process data...



Open in new window

Question by:huangs3
    LVL 16

    Accepted Solution

    LVL 8

    Assisted Solution

    I can't help with your VB code, but you may just have a query that is returning "too many rows" to be practical to process, you may control the memory usage from that side by adding a "where rownum<1e6" or scale that to the maximum memory usage that you find it practical to support, and then try and break your batch job to process in batches of 1e6 records (or whatever the chunk size that your own testing may determine).

    And of course, the other thought is that memory is cheap, and if this is an "off hours" batch there may be plenty of excess memory resources available to just allocate more and let 'er rip through larger batch chunks, but on the other hand you may need to keep the batch chunks small if you are contending with heavy daytime interactive usage.

    Author Comment

    Hi tomcatkev and CuteBug:

        Sorry I found I made a mistake when asking this question. The .Read() function wouldn't cause memory, and the memory curve I observed was from another application. I retested several times to make sure this.
        So there will not be memory problem as long as I process all the data on-the-fly.
        Thank you!

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now