Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

Is there a vb.net / system.data Grid that can browse a 10 million row database efficiently?

Currently, I use the C1FlexGrid for all of my griddling in vb.net. I create System.Data.DataTables and feed them to the grid. This works well for much of what I need to do.

It doesn't work well if I want to browse a giant table. I end up loading millions of rows into memory and it takes forever.  I understand that System.Data is memory based and I appreciate many of the benefits that brings but I was hoping to find a way to be able to page through the table and didn't want to re-invent any wheels if I could help it.

Is there a capable Grid out there that will virtualize giant tables (or Sql queries) for me so my app could provide users with the ability to browse them without having to load the entire thing into memory?

My development environment is VS 2010 and I'm creating standard WinForms applications.
0
ou81aswell
Asked:
ou81aswell
  • 4
  • 2
4 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
I never tried that much number of rows but I am aware of http://www.telerik.com/help/winforms/gridview-virtual-mode.html
0
 
Jacques Bourgeois (James Burger)Commented:
I always ask myself the following question when I see a question such as this one:

Who is the user that is interested in browsing through millions of lines?

You should create a form that would request what the user needs, bring only that in your grid. This will be a lot faster, will solve your problem, and give the user something that is manageable.
0
 
ou81aswellAuthor Commented:
Thanks.

The end users are forensic accountants & auditors. They like to look at the top and bottom of sorted columns to see the outliers and like doing it in an ad hoc manner and with it presented to them as if it were an Excel spread sheet. I cant ask the user ahead of time what they might want to look at because they don't know yet... they want to see it all.

Currently, we just load the entire query (usually a Select * From table type of query) into the grid, which takes around  45 seconds to load a million rows with about 8 columns on them. Once loaded, sorting can take up to 30 seconds (slower if it's on long text columns) but the end user understands that they're dealing with a large chunk of data.

Besides the slow performance on grid loads, I'm also concerned with available ram as it has been suggested that they would like to have a look at a file with over 17 million rows in it but they are running out of memory.  The software will be both x86 and x64 once C1 get their grid working in x64 mode for VS 2012 but I wanted to see if there were any simpler solutions available.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Jacques Bourgeois (James Burger)Commented:
I don't think so. Think of the memory that a million rows take, specially since you say that you are dealing with long text. In most instances, even more so when dealing with the memory limits imposed by x86, that requires a lot of swapping on the hard disk.

Equipping their PCs with the maximum amount of memory possible, as well as fast hard disks that are regularly defragmented is probably the best that can be done in such a situation.

An alternative to consider might be to switch from ADO.NET to classic ADO. The old ADODB library can be accessed through .NET, but since it does not require all the data to be in memory on the client, leaving the work, including sorting, to the server, it might be faster if you have a good server.

I have not worked with ADODB on .NET however, so I cannot say for sure. It's just that the way ADODB handles things is theoretically more interesting when you have such a big amount of data.
0
 
ou81aswellAuthor Commented:
I had the same problem with an earlier version of the c1flexgrid and ADO back in the vb6 days. Although the grid promised a virtual mode, I could never get it to work properly so it would load the entire table. I can't go back (you never can) to ADO although I still use it (even in x64 mode) via Interop for catalog work and compacting mdb /accdb files.

I'm going to study Adapter.Fill(dataTable, startRecord, maxRecords)

It looks like a way to control how much of the data is read and combined with some grid event handling, might be a way of loading rows on demand.

But here I go, re-inventing another wheel.
0
 
ou81aswellAuthor Commented:
With a million row accdb table, Adapter.Fill(dataTable, startRecord=999000, maxRecords=1000) filled my grid in about a second so this looks like it might be a promising approach. I would have imagined that there would be a grid out there somewhere that would take advantage of this.
0
 
ou81aswellAuthor Commented:
My solution to explore Adapter.Fill worked out well and was inspired by some of the other answers.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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