SQL long waits for the query

[markhan]

Hi all!

Well lets see if someone could help me a little bit and give me some guidelines about what can i do...

Database: Access 2000
Rows: 1.8Millions
LD = is a Date
There are no relationships.

The database is like this:

SN GN VN LD LV LQ LA
-----------------------------------------------
E 1M AA X 0 0
E 1M AB X - 23712
T 10S BB Y 0 20400
E 1M AB Y 0 1

Index:

GN GN Ascending
LD LD Ascending
SN SN Ascending
VN SN Ascending

SQL:

SELECT SN, GN, VN, LD, LQ 
FROM L 
WHERE VN='%s' AND SN='%s'AND GN='%s' 
AND LQ >= 20480 AND (De < #%s#) ORDER BY LD

Where %s is a String that is going to change with the DB values

example:

Select SN,GN,VN,LD,LQ 
FROM L 
WHERE VN = 'AA' AND SN = 'E' 
AND GN = '1M' AND LQ >= '20480' AND DE < 'Y' 
ORDER BY LD

VN has many different values, SN and GN only has a few different values, LD is a date so has many different and duplicates.


My problem is that This Query sometimes needs like 15 secs to finish it and thats not viable for the program. I need it faster
What i can't really understand is that sometimes the query for the same amount of data needs like 15 secs and other times 1 or less. :/

What should i do? :S

Thanks in advance,

kind regards.

 

[Golom]

As a first step you may want to create indexes for each of the fields in the WHERE clause including the LQ and DE fields. Note however that having many indexed fields can significantly slow add, delete and update operations on the table.

Second, if you are using tests involving LIKE and wildcards the system may not be able to use indexes and that will slow execution.

Third, inequalities (i.e. <, >, <=, >=) can slow execution and a not equal test (i.e. <>) forces a table scan because it cannot use an index.

You didn't mention the environment in which this is running (i.e. stand-alone; over a network). Many delays experienced in DBMS performance can be traced to the speed of the communications system in addition to the efficiency of the SQL.

 

[MrDataGuy]

-Somethings to consider

-As Golom mentioned where the database exists can be significantly change the running time. If the data is not local you may want to make a local copy to do testing/optimizing.

-If the data is remote I suggested that you write some test code that runs the query several times in a row and display the results. Run this every few mins ( A couple of times an hour ) and record the results. Watch it over the day. If you see significant changes in running time then chances are the speed issues are external to the application (E.g. Server being used bother apps, network speed etc.) Now if the issue are external to your app, you may still need to speed up your app, but realize the variable running times may be out of your hands.

-Since the Query is returning all of the columns (or at least most of them) try using the “SELECT *” syntax.

-Speed may be effected by the order of the Where clause elements. Try different orders:
Maybe: VN = 'AA' AND SN = 'E' AND GN = '1M' AND DE < 'Y' AND LQ >= '20480'

-Is LQ always an integer? What about switching from character to integer?

-Try removing parts of the Where Clause and watch the resolting time. (Yes I am aware more records will be returned but what if the current query returns 100 records in 10 seconds, but removing the LQ >= '20480' returns 1000 records in 0.1 seconds. One can conclude the ‘issue’ is with the LQ >= '20480' and adjustments to that part of the SQL needs to be made.

-Try removing all indexs and see the results. Add back them in one at a time and watch the results. Try different combinations. Sometimes having an index causing more issues then not having it.

-Is ‘LQ >= 20480’ hardcoded? E.g. the value 20480 stays the same for every run of the query? Might a new Boolean column be added to the table, e.g. Less_20480 and put an index on that, and change the query to use, might be helpful. (Maybe I have made things to complex, can a index be put on the current column for just >= 20480??)

-Make sure to compact & repair the database between tests.
-
-BTW what version of access is being used?

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[markhan]

Thanks both for the replies

The main program is a real time app updating a database each 5 secs.

The application I have to do has to obtain data from the database and process it.

Right now Im just using my app, the database is locally.
Database: ACCESS 2000

Talking about the query:

The main problem is that the most important value in this case is LQ, because I need to get all the rows that have LQ >= 20400.

In addition Is mandatory all the conditions i wrote in WHERE.

Im going to try some of the advices, the others i already tried them/knew them

Thanks a lot, later I will reply with more info.

Regards