Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to improve the speed of binding data into MSHFlexGrid

Posted on 2002-07-17
10
Medium Priority
?
386 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
[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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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
 

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 600 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

604 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