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
Solved

How to improve the speed of binding data into MSHFlexGrid

Posted on 2002-07-17
10
375 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

860 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