The CTableView class - CListView-based grid for really huge datasets

  

Environment: VC6 SP4, Windows 98, 2000

In my last project I had to implement transparent access (via ADO) to huge dataset that had more than 2,700,000 rows (it is displayed on the image above). This table was located on MS SQL Server 6.5 and had size 1.9 GB. I was attempting to use virtual CListCtrl, dynamic cursors, static cursors, multithreading, etc, etc. Really nothing could help me. Response time was endless, or server was hung up because of too heavy loading.

So I made this CTableView class. Access was implemented with really good response time (server platform was very modest - PPro/256MB/SCSI). The CTableView class is derived from CListView and implements simple sequential dataset access algorithm (which can be found in file TableView.cpp, section "Grid engine"). Seeking through dataset is also available. Class dynamically creates corresponding SELECT statements. These statements is used for retrieving data from your dataset. CTableView dynamically inserts these data into embedded CListCtrl and deletes the obsolete data from it. So CListCtrl is not virtual and contains only the items that are visible in the window. Class doesn't use any sizeable internal cache (only tiny map-type collection).

Scrolling through dataset is implemented by scrollbar's substitution - "iterator bar". Really it is right-aligned vertical toolbar with six buttons. Rejection of scrollbar caused by the features of the sequential dataset access.

Of course, for normal response time your dataset (table) MUST be indexed (see documentation).

If you will need to solve similar problems then pay your attention to this class. Maybe it really can help you.

All the documentation is located in file TableView.rtf. I was trying to make documentation as clear as possible, but in some topics it is not enough comprehensible. You may have questions after reading about "ORDER BY value", etc. Please look at example on the last page of the documentation. Then you will make all these questions clear as these topics are NOT really complicated, simply I had difficulties when I was describing them. Good luck!

Downloads

Download demo project (SQLView for MS SQL Server/ADO) - 106 Kb
Download source - 17 Kb


Comments

  • MySQL

    Posted by Bui Tan Duoc on 01/26/2007 03:41am

    Could someone help me to use TableView with MySQL server (instead of SQL)? Thank you very much.

    Reply
  • MS Access Sample

    Posted by fwcetus on 05/02/2005 09:43pm

    Hi, Max. This looks like a very clever approach to browsing a large database. However, I don't have access to a SQL Server database, so I can't run your sample app (and I suspect that I'm not alone in this regard). Do you think you could provide a sample project that would work with a simple MS Access database? That'd make it a lot easier to experiment with your concept. Thank you very much for considering my request.

    Reply
  • This control is neat

    Posted by Legacy on 11/28/2001 12:00am

    Originally posted by: Anthony McKinney

    With the exception of the scrollbar, which I intend to attempt to replace with the standard windows control, this control is great. The previous comment that stated that poor design contributes to the necessity for this control is absolutely true, however, there are circumstances where this statement does not hold water. Look at your checkbook in Quicken sometime. Keep scrolling! It's all there. Granted, there could be a way to pair down your account information by year, but the program works great and I am not going to be the one to inform Intuit of their poor design.

    Reply
  • Why not use a Virtual List Control?

    Posted by Legacy on 11/27/2001 12:00am

    Originally posted by: hector Santos

    I havn't downloaded this code yet, but there is a virtual list control which allows you dynamically fill in a
    view range. As you scroll thru the virtual list,
    it will send you the top and bottom row/index number
    of the view.

    Did I miss the point here with your code?

    ----

    Reply
  • Concerning "dead designers" & network traffic

    Posted by Legacy on 11/02/2001 12:00am

    Originally posted by: Max Kazanowsky

    1) Users now are fully satisfied with this implementation. I think it's the best estimation for usability.

    2) About network traffic.
    It is minimal. You get from a server only those records that you see at the screen. And almost NOTHING more.

    Max.

    Reply
  • Hang the designer

    Posted by Legacy on 11/01/2001 12:00am

    Originally posted by: Tomaz Stih

    Max,

    you should consider hanging the designer of your project to the nearest tree. A grid with millions of records is from usability point of view a(n) ..... [diagnosis of your choice here].

    Then there is the price of executing the query to obtain millions of records and last but not least the amount of network traffic (though this has been optimized by people, smarter then your designer).

    Usually in such case user interface should act in a preventive manner in one of following ways:

    1. It should force user to filter data before coming to the grid,

    2. If the user needs a search it should start up with empty list and fill it on each filter, estimating the filter even before execution and preventing anything that might look like a ten thousands records query...(this can usually be done on business logic level even before it is translated to SQL)

    Sincerely,
    Tomaz

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Event Date: April 15, 2014 The ability to effectively set sales goals, assign quotas and territories, bring new people on board and quickly make adjustments to the sales force is often crucial to success--and to the field experience! But for sales operations leaders, managing the administrative processes, systems, data and various departments to get it all right can often be difficult, inefficient and manually intensive. Register for this webinar and learn how you can: Align sales goals, quotas and …

  • Corporate e-Learning technology has a long and diverse pedigree. As far back as the 1980s, companies were adopting computer-based training to supplement traditional classroom activities. More recently, rich web-based applications have added streaming audio and video, real-time collaboration and other new tools to the e-Learning mix. At the same time, the growing availability of informal learning tools--a category that includes everything from web searches to social media posts--are having a major impact on …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds