Query Speed

[StevenH]

I have an Access 97 database and to retrieve 500 records it takes about 6 minutes. This way to slow!

I need to increase the speed of the query. I am using an Access database and I am using stricly code for my query no data control.


Can anyone tell me where I can find some usefull information on query speed? I have a problem that I must fix urgently or I am out of a job!!


If anyone has any strong suggestions on how to improve query speed then please let me know./..

Stevie

[hayessj]

Stevie,
There is something really wrong here because that is way slow.
- Is the database on a network?
- What spec machine are you using?
- Would it be possible to pull down less than 500 records at a go?
- Can you use a readonly recordset?
- Are you using DAO/ADO/RDO ?
- Is the database properly normalized?

If you post a bit more info it might be easier to help ..

[StevenH]

The Database is actually on a web server. I am using strictly code. I am using a Win Nt OS on a Pentium 3.

It just takes way too long....

What I am doing is using Crystal Reports and to run a report on a query I run the query and take the snap shot and place it into a temp.mdb... this is probably what is causing the problem but it should not take this long


Are there ways to improve a sql query...

[StevenH]

Here is the actual sql statement:


db.Execute "SELECT * INTO Times IN '" & strTempDb & "' FROM Times WHERE DatePerformed>=#" & Format(date1.Value, "mm/dd/yyyy") & "# AND DatePerformed<=#" & Format(date2.Value, "mm/dd/yyyy") & "#"

from the db i want to pull out lastname, dateperformed, regularhours, overtimehours fields....


This query takes a snap shot of the selected criteria and places it into a temp mdb...

[ELampman]

You can increase the speed of Crystal Reports by ensuring that the values in your "Where" clause are indexed on the database.

Also, if you're just pulling a report, why write the data to another db?

If you just want to copy from one db to another using DAO, try using a transaction and commit the records about every 100 or so. That should speed it up.

[StevenH]

Thank you for the immediate response.
I am not using a ODBC connection so if i run a query the db will lock. How do I index a db? Can you go into detail?

Transaction I have heard of I will look this up....

[StevenH]

Can anyone please explain indexing a database and how transaction works?

[Edneeis]

He means that the field you are using in the WHERE statement should be an index or when the database was set up this field should have been made into an index, maybe not a primary key or anything but an index none the less.

[StevenH]

Thank you! You have made me a happy man. To increase the speed by 70%, I changed the Index property in Access 97 of the fields that I am using in my query....


Case Closed*****