Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-12-09
7
Medium Priority
?
212 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 668 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 1332 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 1332 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

718 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