Solved

Speed of Access DB...

Posted on 2001-07-03
18
269 Views
Last Modified: 2010-04-06
I'm using the ADO components to access an MS Access database. The big problem is that it is extremely slow, it executes like 100 queries in 10 seconds. How can I speed this up?

j
0
Comment
Question by:sageryd
  • 9
  • 4
  • 3
  • +2
18 Comments
 
LVL 22

Expert Comment

by:mnasman
Comment Utility
in the select statment, try always to use the field that requred not all the fields, use "select field1,field2" instead of "select * "

use the indexes for the fields that ur queries use for searching.

many times writing the sql statment in right way, can increase the speed, so try to enhance ur sql statments
0
 
LVL 1

Author Comment

by:sageryd
Comment Utility
I have already.
0
 
LVL 21

Expert Comment

by:ziolko
Comment Utility
In .UDL file try not to use Microsoft OLD DB provider for ODBC drivers, use JET 4.0 OLE DB Provider or any other for MS Access. (but the best way is give up with MS Access and go for real RDBS like MSSQL, Oracle, Interbase,...)
ziolko.
0
 
LVL 1

Author Comment

by:sageryd
Comment Utility
I'm using JET 4.0. I don't want to go over to SQL Server or similar, maybe I will later, but I need it to be simple right now as I'm moving it around alot.
0
 
LVL 2

Expert Comment

by:Felixin
Comment Utility
How big is your database?
How are your queries?
How fast is your disk driver?

I have queries that take more than a second to run, and I think they are fast. You ought to see the sentences to understand it.

Thing like this should taken into account to say that it is slow.

Give us a small detail.


Felixin
0
 
LVL 1

Author Comment

by:sageryd
Comment Utility
My db is NOT at all big, it contains liek five or six tables and has hardly no data in it at all because it's still in "testing mode". My queries ar like "select field1, field2 from table1, table2 where field1=field2", simple. Diskdriver....dunno.

Anyway, I think I've solved my initial problem now, it may be a bit slow still, I thought the original problem was due to some timeout becuase it was too slow, but it wasn't. It was..something else. Don't know what yet though. Weird.

I'll leave the Q open for a while, might get some good tips on the way (and already have!)

thank you all!

j
0
 
LVL 5

Expert Comment

by:TheNeil
Comment Utility
Are you running your database across a network or on a local machine? Multiple users or single user? Are your queries extracting a big number of records? Is your database compacted?

So many possibilities, so many ways for it to go wrong

The Neil =:)
0
 
LVL 1

Author Comment

by:sageryd
Comment Utility
Running locally, single user at the moment, usually I extract a maxumum of ten records per query, but normally just one. Compacted yes.


:)
0
 
LVL 22

Expert Comment

by:mnasman
Comment Utility
r ur quries execute when ur program run? or when u click on the button?, look for other possibles in the loading ur project, for 10 records i think it must too fast, so try to find something slow ur program between the lines, and the ado fast more than the bde as i tested before

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:sageryd
Comment Utility
It's a CGI.
0
 
LVL 21

Accepted Solution

by:
ziolko earned 50 total points
Comment Utility
saqeryd > CGI is very slow. each time request comes to CGI new process is loaded into memory!! and after processing request whole proccess is removed from memory, so every time request comes to CGI You have to establish new connection with database, check user privilages etc., try ISAPI/NSAPI not CGI, once ISAPI is loaded it stays in memory till WebServer is downed, so database connection remains active then only first "call" to database maybe little bit slow then everything should go nice and smooth.
ziolko.
0
 
LVL 1

Author Comment

by:sageryd
Comment Utility
I was considering that, but I thought it might get too complicated, does it?
0
 
LVL 22

Expert Comment

by:mnasman
Comment Utility
Hello

 ISAPI is better and faster than than CGI, and delphi made easy to use, approximately, ISAPI is similar to CGI in delphi, you don't change alot for that, if you need some info about the ISAPI see this link

look at this link, i post some links there
http://www.experts-exchange.com/jsp/qShow.jsp?ta=delphi&qid=20100317
Mohammed
0
 
LVL 1

Author Comment

by:sageryd
Comment Utility
Thanx! I'll review that in a couple of days. But right now I'm heading for the swedish archipelago! ahh :)
0
 
LVL 21

Expert Comment

by:ziolko
Comment Utility
sageryd > like mansman said ISAPI is a piece of cake with Delphi! for samples search EE site or visit: http://www.matlus.com also https://sik.petronaft.com.pl is written completly useing ISAPI all data comes from RDBS (I mean ALL!!! even simple pages are stored in DB!!!)  
ziolko.
0
 
LVL 1

Author Comment

by:sageryd
Comment Utility
Like you said, ISAPI is a piece of cake. But I get this error:

The Microsoft Jet database engine cannot open the file 'c:\com\db.mdb'. It is already opened exclusively by another user, or you need permission to view its data.


Why? My TADOConnection is set to connection type "ShareDenyNone", not exclusive.

I'm just executing an SQL to get this error, like "select * from Users". What am I doing wrong?


Johan
0
 
LVL 1

Author Comment

by:sageryd
Comment Utility
Sorry! My error. :)  Works now. And it's a lot faster.

Thanx all! :o)
0
 
LVL 21

Expert Comment

by:ziolko
Comment Utility
Glad to be helpful
ziolko.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now