Solved

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

Posted on 2014-10-10
5
569 Views
Last Modified: 2014-10-12
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
0
Comment
Question by:murbro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 40373877
if you run that query in sql server on your desktop  is it returning in a reasonable amount of time ?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 40373886
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40374290
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
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 40374556
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
 

Author Closing Comment

by:murbro
ID: 40375844
Thanks for the advice
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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