How to improve the speed of binding data into MSHFlexGrid

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.
tanghsAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
ajexpertCommented:
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
 
tanghsAuthor Commented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
TimCotteeHead of Software ServicesCommented:
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
 
AmitabhCommented:
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
 
TimCotteeHead of Software ServicesCommented:
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
 
tanghsAuthor Commented:
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
 
ajexpertCommented:
How about using a Stored Procedure.  See if it helps
0
 
DanRollinsCommented:
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
 
SpideyModCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.