• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 631
  • Last Modified:

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
0
Murray Brown
Asked:
Murray Brown
1 Solution
 
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
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks for the advice
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now