-1

i have stock ohlc data the total record count is 4 million records but when i load data into datatable from data adapter its gives me an error of out of memory.

Query

SELECT company_id,trading_Date,trading_open,trading_high, trading_low,trading_close,trading_vol 
FROM company_trading1sIntl 
WHERE company_id = 'YM' 
ORDER BY trading_Date

Function

public DataTable FillDT(string query, SqlConnection conn)
{
    SqlCommand sqlcmd = new SqlCommand(query,conn);
    SqlDataAdapter sqlda = new SqlDataAdapter(sqlcmd);
    DataTable dt = new DataTable();

    try
    {
        sqlda.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            return dt;
        }
        else
        {                   
            return null;
        }
    }
    catch(Exception ex)
    {
        return null;
    }        
}
maccettura
  • 10,514
  • 3
  • 28
  • 35
user1485785
  • 11
  • 4
  • 1
    4 million records is a lot.. – BugFinder Oct 01 '19 at 13:58
  • try running the query limiting rows to TOP 100000 and then work your way up to 4 million to check your limit – demoncrate Oct 01 '19 at 13:59
  • Take a look at [this SO](https://stackoverflow.com/a/20912869/11482040) and [Microsoft reference](https://learn.microsoft.com/en-us/dotnet/framework/configure-apps/file-schema/runtime/gcallowverylargeobjects-element) – André Sanson Oct 01 '19 at 14:00
  • You are trying to load 4 m rows of data into memory? That is most likely why you get an out of memory exception. This means that the program cannot allocate more memory than you're trying to use. If you have 1 kb of memory per row - you need about 1kb * 4000000 = 4 gigabytes of ram to store each line. This is likely too much. You can open the windows task manager and check your memory usage under the performance tab. I think you should not try to load 4 million records at once. Instead try whatever you are doing in SQL, since a database server is designed to work with these kind of numbers. – sommmen Oct 01 '19 at 14:23
  • If you are only presenting your data, try removing irrelevant rows by using where or limit the amount of rows by using TOP (amount) like suggested in the comments. – sommmen Oct 01 '19 at 14:23
  • What is the business case for loading 4 million records? Even if you can get to load by adding more RAM why are you presenting this number of rows to a user? – JazzmanJim Oct 01 '19 at 16:36

1 Answers1

0

The first answer you need to ask yourself is, why load all that records inside a datatable? What about doing a SqlDataReader for reading data, edit it, and evry (for example) 100.000 rows do a SqlBulkCopy to re-insert them inside your table, is faster than adapter.Fill() and maybe you can avoid storing all 4.000.000 records inside memory.

IF you need to handle all the data in a single "select" you've no chance to edit your web.Config like below:

<runtime>
    <gcAllowVeryLargeObjects enabled="true" />    
</runtime>

And compile a x64 binary.

Legion
  • 760
  • 6
  • 23