VB.net Error loading 2 million records into a DataGridView from SQL on my desk top

Hi

I am using the following code to load a DataGridView. It works fine for aup to around a million records
from a SQL database on my local machine. I need to load 2 million records, but when I try I get the following error message. Is there a better  way to do this?

Error  Dim dbadp2 As SqlDataAdapter
    Dim dTable2 As New DataTable

                dTable2 = New DataTable 'to make sure that columns from previous query are wiped
                Dim connection As New SqlConnection(CS)
                dbadp2 = New SqlDataAdapter(sSQL, connection)
                dbadp2.Fill(dTable2)
                Me.DataGridView1.DataSource = dTable2
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plusone3055Commented:
if you run that query in sql server on your desktop  is it returning in a reasonable amount of time ?
0
Éric MoreauSenior .Net ConsultantCommented:
why is there a need to load 2M rows in a grid? Nobody can process that much information. You better offer your user a way to enter filter criteria and have your query returning only those records. I even add a "top 1000" or so clause at the top of my query to be sure that not too much data is returned from the database.
0
Anthony PerkinsCommented:
I need to load 2 million records, but when I try I get the following error message. Is there a better  way to do this?
As indicated previously:  Why?
0
Jacques Bourgeois (James Burger)PresidentCommented:
A better way to do this is to analyze your needs.

As Éric said, there is no way for a human being to deal with that amount of information. So if the data needs to be manipulated by a human being, first ask them what information they need to work on and retrieve only that information, not the whole table.

If the work to do is processing that is done through code, then do not use the DataGridView. It has an overhead that requires a lot of memory over the data, and is useless unless you need to display the data. See if the job could not be done in a stored procedure. If not, and if it can be done by processing the records one by one, use a DataReader. If not, try a DataTable, that requires less memory than a DataGridView. If not, see if the work can be done in chunks instead of in one big batch processing (processing one month at a time in a sales record as an example). Using a custom class can also take up less memory than using a DataTable.

You see that there are many different solutions. But they require that you look carefully at what you need to do and then maybe test a solution against another.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks for the advice
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.