Solved

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

Posted on 2013-12-09
7
208 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
[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
  • 4
  • 2
7 Comments
 
LVL 70

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
How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

627 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