Solved

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

Posted on 2014-10-10
5
531 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
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 69

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 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

7 Experts available now in Live!

Get 1:1 Help Now