rebuild sql to increase query speed? or other way maybe?

P

pat12

Hello everyone

Table1 contain car journal with nb of km on Counter after each journey
To calculate the distance of one trip I would like to have the Counter
status from the previous one.

Here is what I did:

SELECT a.Order, a.Date, a.Place, a.CarNb, a.Counter, b.Counter
FROM Table1 AS a LEFT JOIN Table1 AS b ON (b.Data<a.Data) AND
(a.CarNb=b.CarNb) AND (a.Order<>b.Order) 'could be more than one record
for one trip but counter is filled in the last one'
WHERE (b.Date = (SELECT Max(c.Date) FROM Table1 AS c WHERE a.Order <>
c.Order AND a.CarNb=c.CarNb AND c.Date<a.Date ) or b.Date is Null);


and its works fine with few hundreds records. But when nb of records
is more than 5000 Its 10 minutes to have results. The problem is that
the Table1 is to have about 200 000 records and than this is useless.


What should I do to increase query speed (except indexing of course).
Rebuild it? How? Maybe some better (different) solution?

Any help appreciated
PAT
 
G

Guest

Indexing of course! Without indexing the query will do a full table scan. Do
you have indexes and on what fields.

Even if you have an index on the Date field, the Is Null in the last Where
clause will stop it from being used. In these cases I often use a bogus value
for the field so that there won't be any nulls. Something like #1/1/1950# so
that the Where clause would be or b.Date = #1/1/1950# . Set the default
value to #1/1/1950# and update the existing nulls to #1/1/1950#.

What are the primary keys and foriegn keys of these two tables and do you
have referiential integrity turned on?

The biggest problem I see is that you are doing two different self joins on
the same table. That is often an indication that your data isn't normalized
properly and few things harm database speed more than a poor design.

Seems like you might already have a Car table and and Order table. You may
well need a Counter table with the Date in it.
 
G

Guest

You seem to be struggling with a poorly designed table. If each record
represents a journey, then all information for that journey should be on that
record: should have begin_Counter and end_Counter fields.
If you can edit this table, I would write small code to add and populate the
begin_counter field.
 
M

Marshall Barton

Table1 contain car journal with nb of km on Counter after each journey
To calculate the distance of one trip I would like to have the Counter
status from the previous one.

Here is what I did:

SELECT a.Order, a.Date, a.Place, a.CarNb, a.Counter, b.Counter
FROM Table1 AS a LEFT JOIN Table1 AS b ON (b.Data<a.Data) AND
(a.CarNb=b.CarNb) AND (a.Order<>b.Order) 'could be more than one record
for one trip but counter is filled in the last one'
WHERE (b.Date = (SELECT Max(c.Date) FROM Table1 AS c WHERE a.Order <>
c.Order AND a.CarNb=c.CarNb AND c.Date<a.Date ) or b.Date is Null);


and its works fine with few hundreds records. But when nb of records
is more than 5000 Its 10 minutes to have results. The problem is that
the Table1 is to have about 200 000 records and than this is useless.


What should I do to increase query speed (except indexing of course).
Rebuild it? How? Maybe some better (different) solution?
Click to expand...


I doubt this will help much, but try using:

SELECT a.Order, a.Date, a.Place, a.CarNb, a.Counter,
( SELECT TOP 1 b.Counter
FROM Table1 AS b
WHERE (a.CarNb=b.CarNb) AND (a.Order<>b.Order)
ORDER BY b.Date DESC
) As PreviousCounter
FROM Table1 AS a
 
J

John Spencer

Perhaps the following will work for you.

I'm not sure of your data structure or way your data is entered. For
instance, does counter get set for every segment of a trip or only for the
last segment of the trip? What is Order (is it an order (invoice) number
or is it a segment order for a trip or ???)?

If counter is only entered on the last segment of a trip, then all you would
seem to need would be the Max of counter that is less than the current
value.

SELECT Max(Counter) FROM Table1 as Temp WHERE Temp.CarNb = Table1.CarNb and
Temp.Counter < Table1.Counter

SELECT a.Order, a.Date, a.Place, a.CarNb, a.Counter
, (SELECT Max(Counter)
FROM Table1 as Temp
WHERE Temp.CarNb = A.CarNb
AND Temp.Date < A.Date) as PreviousCounter
FROM Table1 As A
WHERE A.Counter is not null
 
P

pat12

Thank you all for your answers.

I will try to describe a little the project and answers your
suggestions.

To Jerry Whittle:

Unfortunately indexing is not possible because table1 is a combination
of linked tables.

To Marshall Barton and John Spencer

I used your sql and its two times faster but still to slow to accept. I
will have to figure out complete different approach. I think

What it is about:

The project goal is to prepare simple reports for company which:
- has no idea what Access is and how to use it (barely know Excel)
- have no person which could be assign to administrate the reporting
tool I try to build
- data is inputted by 10 people and some data is sent via e-mail (there
is no other solution)

Because of what above I decided to prepare special Excel files with
protected sheets which are automatically saved with csv copy. This csv
copy is source for Access which is just an engine for calculations (200
000 records). In fact users will not even know (in theory) then Access
is involved in calculation process. Access is gathering csv files as
linked tables (those sent via e-mail will be saved in special folder)
and will have number of queries which will be source for excel reports
(taken via MSQuery). In this way it is simple to use and even if
something brakes they will need only to watch out for data excel files
and the rest (Access and Report excel files) could be just restored
from backup any time. I think in this way administration is needless.

I know than this way database data is not optimized but simple queries
with aggregation with 200 000 records of data even with linked tables
are not very slow. My problem is that counter field.
I know that I can force users to input the previous counter field each
time but you know If something can be calculated why typing this two
times - they say. At this moment I try to solve the problem with sql
method :) but maybe this will be the best solution.

To Bruce Meneghin:

I didn't describe all fields of the table because I focused on this
particular problem. Sorry for that. The main use of the table is not
the journey but the time of work of the drivers. One record is one day
of work (start date end date, breaks, sleep, carnb, fuel bought, extra
expenses etc). The counter is to be filled at the end of the trip and
is extra (but useful) info.


Excuse me if now this post is off the group a little.
I am really interested in you opinions as experienced database
creators.

Thank you in advance
PAT
 
Top