?
Solved

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

Posted on 2014-10-10
5
Medium Priority
?
587 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:Murray Brown
[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 2000 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:Murray Brown
ID: 40375844
Thanks for the advice
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

771 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