Solved

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

Posted on 2013-12-09
7
203 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 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 our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

713 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