Solved

Speed of Access DB...

Posted on 2001-07-03
18
270 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: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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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 50 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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