OutOfMemoryException when selecting data from sql database

Question

2.00/5 (1 vote)

See more:

SQL-Server-2005

A table contains almost 90 lacks to 1 cr records. I want to save this data to text file. I am trying to select records into datatable, 'OutofMemoryExceptionerror' occurred

code:

VB

Imports System.Data.Sql
Imports System.Data.SqlClient

VB

Dim con As New SqlConnection(Connection)
Dim CategoryAdapter As New SqlDataAdapter("select sserial, spin from tbl_serial_table", con)
Dim SerialData As New DataSet()
CategoryAdapter.Fill(SerialData, "Data")

When I try to fill dataset error occurred.
Please guide...

Posted 8-Feb-12 18:28pm

Harshad-HBK

Add a Solution

Comments

Varun Sareen 9-Feb-12 0:38am

Why you are requiring to fetch such large dataset?

Varun Sareen 9-Feb-12 0:40am

One rule of thumb that I've learnt from my experience is that you NEVER bring ALL the data from the database to your application server. One thing you can do is implement a procedure to page your data.

You can bring one page of data containing around 1000-5000 records, process them, then again fetch the data for the next page.

Sergey Alexandrovich Kryukov 9-Feb-12 0:46am

Of course...
I answered, please see...
--SA

Harshad-HBK 9-Feb-12 5:01am

if so, then please explain in bit detail...

Varun Sareen 16-Feb-12 12:52pm

Dear Harshad, this link will explain you all what we are trying to make you understand http://msdn.microsoft.com/en-us/library/aa479347.aspx

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Alexandrovich Kryukov · Answer 1 · 2012-02-08T18:42:00

What guide can you get if you are trying to use more memory than a system can afford? No matter what is your system, eventually you can always face the situation when your memory is not enough. This is natural and generally unavoidable.

So, there is nothing special to advise. Well, get a client system with more memory, most likely based on one of 64-bit instruction-set architectures, prefer more selective queries, handle out-of-memory exceptions properly.

As to the queries, look at your sample: you are trying to retrieve the whole table at once. Such queries are very unusual and make little sense. Use WHERE clause, etc., remember that using parametrized queries is important.

—SA