Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Speed of Access DB...

Posted on 2001-07-03
18
Medium Priority
?
278 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

636 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