• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

Speed of Access DB...

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
sageryd
Asked:
sageryd
  • 9
  • 4
  • 3
  • +2
1 Solution
 
Mohammed NasmanSoftware DeveloperCommented:
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
 
sagerydAuthor Commented:
I have already.
0
 
ziolkoCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
sagerydAuthor Commented:
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
 
FelixinCommented:
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
 
sagerydAuthor Commented:
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
 
TheNeilCommented:
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
 
sagerydAuthor Commented:
Running locally, single user at the moment, usually I extract a maxumum of ten records per query, but normally just one. Compacted yes.


:)
0
 
Mohammed NasmanSoftware DeveloperCommented:
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
 
sagerydAuthor Commented:
It's a CGI.
0
 
ziolkoCommented:
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
 
sagerydAuthor Commented:
I was considering that, but I thought it might get too complicated, does it?
0
 
Mohammed NasmanSoftware DeveloperCommented:
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
 
sagerydAuthor Commented:
Thanx! I'll review that in a couple of days. But right now I'm heading for the swedish archipelago! ahh :)
0
 
ziolkoCommented:
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
 
sagerydAuthor Commented:
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
 
sagerydAuthor Commented:
Sorry! My error. :)  Works now. And it's a lot faster.

Thanx all! :o)
0
 
ziolkoCommented:
Glad to be helpful
ziolko.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 9
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now