Solved

How to improve the speed of binding data into MSHFlexGrid

Posted on 2002-07-17
10
365 Views
Last Modified: 2012-06-21
I'm trying to bind the recordset returned by SELECT query into the MSHFlexGrid. It worked fine for a small amount data. However, it took a long time when there were more than 10000 records.

Dim Rs As New Recordset
Rs.Open MySelectSQL, objConn
Set MSHFlexGrid1.DataSource = Rs

The program stopped at this line for more than 30 seconds:
Set MSHFlexGrid1.DataSource = Rs

Is that possible to improve the speed of binding data into MSHFlexGrid ? Thanks.
0
Comment
Question by:tanghs
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 7158893
Hi,
    Well, use indexes on the table.  Definitely it will increase the retrieval speed. Also modify the SELECT command to retrieve only those columns which are needed to display in the grid.

CHeers!
0
 

Author Comment

by:tanghs
ID: 7158912
Thanks for your advice.
Yup, I have set indexes on tables. And my SELECT command only retrieve the columns I need. I did use timer to get the query execution time and I can see the time spent is considered "acceptable".  Just wondering if the problem occurred when binding the recordset into the MSHFlexGrid.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 7158917
AngelIII is quite correct in his suggestions, however these only address part of the issue. When you use the statement Set MSHFlexGrid1.DataSource = Rs then all rows/columns in the grid need to be updated, for each updated cell the grid needs to repaint all of this takes time so it is important when doing something like this that you set .Redraw to False before doing massive updates to the grid so that repainting is disabled whilst you are making changes to cells, then set .Redraw = True afterwards to force a single repaint for the whole operation:

Dim Rs As New Recordset
Rs.Open MySelectSQL, objConn
MSHFlexGrid1.Redraw = False
Set MSHFlexGrid1.DataSource = Rs
MSHFlexGrid1.Redraw = True

This in addition to any optimisation that you can do at the database level will improve the population speed of the grid no end!
0
 

Expert Comment

by:Amitabh
ID: 7158921
As ajexpert has suggested. this is more on the query optimization and database design side than MSHFlexgrid. you can get better results if u focus on ur database and query designs to make data retrieval faster.

One out of track suggestion, may be u can find it helpful in urr code. use do events so that while the control is taking time to get populated the screen gets refreshed quickly.

regards
A.Pandey
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 7158930
ajexpert it was, now why did I think it was Guy (angelIII) that had commented first, probably haven't had enough caffeine yet this morning, off to rectify that deficiency now!
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:tanghs
ID: 7159023
Thanks, experts.

Hi Tim, I've tried your suggestion but seems like there's no different after executing the following command :

MSHFlexGrid1.Redraw = False.
Set MSHFlexGrid1.DataSource = Rs
MSHFlexGrid1.Redraw = True

It's especially slow when I do data binding the second time where the MSHFlexGrid has already contained data loaded by my first query.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 7159194
How about using a Stored Procedure.  See if it helps
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 150 total points
ID: 7161006
You need to ask yourself why you really need to load 10,000 rows in the first place.  If it is for a user: Consider that no user in his/her right mind is going to wade through that many records.  If this is the case, it may be time to re-think and perhaps you filters to get the rows down to a more manageable 100 or maximum 200 rows.

Just my 2 cents worth.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 8013059
Hi tanghs,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Split points between: ajexpert and acperkins@devx

tanghs, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 

Expert Comment

by:SpideyMod
ID: 8096038
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange

ajexpert, points added to unclaimed points at:
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20519695.html
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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

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

18 Experts available now in Live!

Get 1:1 Help Now