How to increase speed

manishgore · February 27th, 2004, 01:04 AM

Hi !

Could any one tell me how to increase the speed while displaying the records from MS-Access database. I am using VB as front end. In access I have around 9 Lacs of records out of this I want access specific no. of records by giving criteria. But the speed varies from M/c to M/c i.e in 256 MB RAM it is taking 1.5 Min., 128 MB RAM it is taking 3.5 Min. I want to increase the display speed in 128 MB RAM. I am using only SELECT statement with thos columns I want to access.But user is not accepting the speed.

Could any one help me out to resolve this issue.

Thanks in advance.

Regards,
Manish.

tbriers · February 27th, 2004, 09:50 AM

When you open a recordset you might want to consider using
rsDb.Open strSql, adoConn, adOpenForwardOnly, adLockPessimistic, adCmdText

the forward only might give you a little speed gain but when you are querying 90.000 (Or is one Lac 100.000 ,it's been a while since i've been in india) records it will always take some time.

Faster Proecessor and more Ram will always increase the speed.

Greetz

stephanel · February 27th, 2004, 11:34 AM

Also try avoiding the use of RIGHT JOIN in your query. I added a query that toke about 5 to 10 min to execute with RIGHT JOIN. I rewrite the query using only LEFT JOIN and it toke about 30 sec to execute

StÃ©phane Lajoie

manishgore · March 1st, 2004, 12:12 AM

Thanks for the reply. But I am using the same statement what u've mentioned.
rsDB.open sSql,oCon,adOpenForwardOnly,adLockPessimistic
And my query is simple and straight forward no joins require. The thing is that I've 8-9 lacs of records.

Regards,
Manish

xgbnow · March 1st, 2004, 09:43 AM

Is the query dynamic or static. If the query is static you could increase the speed of the query by writing the query in Access. If your query is dynamic you will more than likely need to build it in your VB app.

Also, it has generally been my experience a query that takes that long to run, in the scenario you described, is returning too much data. You could create a query that limits the amount of data returned.

I think you probably have several options available but I would need some more information.

At the risk of sounding ignorant what do you mean by "9 Lacs of records"?

Larry Asher

dodorisio · March 2nd, 2004, 05:17 PM

Highly agree with what Larry just posted, i was just looking for some info on the same thing, i had a query that took roughly 20 secs to process when the query was in the code, i switched the query to be on the server.. now it takes 3 seconds!!!

things slip the mind sometimes, thanks for the help larry, even though i didnt ask:)

/d