?
Solved

Speed of Access DB...

Posted on 2001-07-03
18
Medium Priority
?
275 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
[X]
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
  • 9
  • 4
  • 3
  • +2
18 Comments
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 6250235
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
ID: 6250349
I have already.
0
 
LVL 21

Expert Comment

by:ziolko
ID: 6251322
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:sageryd
ID: 6251799
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
ID: 6252210
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
ID: 6252260
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
ID: 6252265
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
ID: 6252269
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:Mohammed Nasman
ID: 6252290
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
 
LVL 1

Author Comment

by:sageryd
ID: 6252307
It's a CGI.
0
 
LVL 21

Accepted Solution

by:
ziolko earned 200 total points
ID: 6254317
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
ID: 6254559
I was considering that, but I thought it might get too complicated, does it?
0
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 6254602
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
ID: 6254866
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
ID: 6257386
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
ID: 6276661
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
ID: 6278252
Sorry! My error. :)  Works now. And it's a lot faster.

Thanx all! :o)
0
 
LVL 21

Expert Comment

by:ziolko
ID: 6283296
Glad to be helpful
ziolko.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month13 days, 14 hours left to enroll

800 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