Solved

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

Posted on 2013-12-09
7
190 Views
Last Modified: 2014-01-09
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
Comment
Question by:ou81aswell
  • 4
  • 2
7 Comments
 
LVL 69

Assisted Solution

by:Éric Moreau
Éric Moreau earned 167 total points
ID: 39707515
I never tried that much number of rows but I am aware of http://www.telerik.com/help/winforms/gridview-virtual-mode.html
0
 
LVL 40

Assisted Solution

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

Author Comment

by:ou81aswell
ID: 39709750
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 40

Assisted Solution

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

Author Comment

by:ou81aswell
ID: 39710588
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
 

Accepted Solution

by:
ou81aswell earned 0 total points
ID: 39718197
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
 

Author Closing Comment

by:ou81aswell
ID: 39767529
My solution to explore Adapter.Fill worked out well and was inspired by some of the other answers.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now