Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB Databases
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 27th, 2004, 01:04 AM
Registered User
 
Join Date: Feb 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to increase speed

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.
Reply With Quote
  #2 (permalink)  
Old February 27th, 2004, 09:50 AM
Authorized User
 
Join Date: Aug 2003
Location: , , Belgium.
Posts: 44
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old February 27th, 2004, 11:34 AM
Authorized User
 
Join Date: Jun 2003
Location: , Quebec, Canada.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old March 1st, 2004, 12:12 AM
Registered User
 
Join Date: Feb 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default


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

Reply With Quote
  #5 (permalink)  
Old March 1st, 2004, 09:43 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: McPherson, Kansas, USA.
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old March 2nd, 2004, 05:17 PM
Authorized User
 
Join Date: Dec 2003
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
increase DB size RinoDM SQL Server 2000 1 December 7th, 2006 01:25 AM
increase DB space... RinoDM SQL Server 2000 5 October 12th, 2006 11:07 PM
text increase/decrease ... jstewie Javascript How-To 0 July 12th, 2005 12:13 PM
How to Increase page width rajukurian Crystal Reports 2 June 10th, 2005 01:53 AM
How to Increase Report width Kiransuthar BOOK: Professional Crystal Reports for VS.NET 1 January 28th, 2005 07:07 AM



All times are GMT -4. The time now is 03:12 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.