SQL or maybe a hardware issue for finding lowest Repair order date or mileage

[ksquared]

I’m hoping someone has an idea regarding this issue



I have data set of repair orders identified by the dealer code that did
the service and the VIN (Vehicle Identification Numbers) serviced. The
table is 1.3 million records and covers 850 dealers with data over a 2
year period. I am trying to select the lowest date or mileage for each
unique dealer VIN combination. For me to be successful I need to
identify the 400,000 – 600,000 repair order records with the lowest
mileage so they can be associated with the selling dealer. I am running
Acess 2007 on a XP Centrino lap top with maybe 1 – 1.5 megs of RAM.



I am using two instances of the same table. The table contains (Vehicle
Identification Numbers and RO numbers dates and mileages etc. This query
as a select query runs slowly 10 – 15 minutes but runs. When I go to
sort the result set my PC freezes. Is there a better way to write the
query?. Or is it possible my Centrino based lap top is lacking in
processing power or memory? I am trying to run it for one unique dealer
NY075. I don’t have an easy way to run this on another PC since I don’t
have another one with Acess 2007 and more processor power





SELECT A.RPRDLR, A.RPSEQN, A.RPWODT, A.RHWOML, A.RPFVIN, A.RHCTTA,
A.RHWTTA, A.RHITTA, A.RHCPAY, A.RHIPAY INTO [First Oil Change]

FROM [First Service Data table] AS B, [First Service Data table] AS A

WHERE (((A.RPRDLR)="NY075") AND ((A.RPWODT) In (Select Min(B.RPWODT)
from [First Service Data table] B where B.RPFVIN=A.RPFVIN)));

 

[John Spencer]

It would help if you posted the query you are currently using.

Two query approach (First query get the earliest service date by VIN)
SELECT Dealer, VIN, Min(ServiceDate) as FirstService
FROM [SomeTable]
GROUP BY Dealer, Vin

Then you can use that query to get all the other data
SELECT [SomeTable].*
FROM [SomeTable] INNER JOIN QueryFirstService
ON [SomeTable].Dealer = QueryFirstService.Dealer
AND [SomeTable].Vin = QueryFirstService.Vin
AND [SomeTable].ServiceDate = QueryFirstService.FirstService

Make sure you have indexes on Dealer, Vin, and ServiceDate.

If you wish to do this for just one dealer you can speed things up quite a bit
by changing query one to limit its results to just the one dealer.

SELECT Dealer, VIN, Min(ServiceDate) as FirstService
FROM [SomeTable]
WHERE Dealer = "NY075"
GROUP BY Dealer, Vin

With table and field names that consist of only letters, numbers, and the
underscore character (and names are not a reserved word) this can all be done
in one query.

I’m hoping someone has an idea regarding this issue



I have data set of repair orders identified by the dealer code that did
the service and the VIN (Vehicle Identification Numbers) serviced. The
table is 1.3 million records and covers 850 dealers with data over a 2
year period. I am trying to select the lowest date or mileage for each
unique dealer VIN combination. For me to be successful I need to
identify the 400,000 – 600,000 repair order records with the lowest
mileage so they can be associated with the selling dealer. I am running
Acess 2007 on a XP Centrino lap top with maybe 1 – 1.5 megs of RAM.



I am using two instances of the same table. The table contains (Vehicle
Identification Numbers and RO numbers dates and mileages etc. This query
as a select query runs slowly 10 – 15 minutes but runs. When I go to
sort the result set my PC freezes. Is there a better way to write the
query?. Or is it possible my Centrino based lap top is lacking in
processing power or memory? I am trying to run it for one unique dealer
NY075. I don’t have an easy way to run this on another PC since I don’t
have another one with Acess 2007 and more processor power





SELECT A.RPRDLR, A.RPSEQN, A.RPWODT, A.RHWOML, A.RPFVIN, A.RHCTTA,
A.RHWTTA, A.RHITTA, A.RHCPAY, A.RHIPAY INTO [First Oil Change]

FROM [First Service Data table] AS B, [First Service Data table] AS A

WHERE (((A.RPRDLR)="NY075") AND ((A.RPWODT) In (Select Min(B.RPWODT)
from [First Service Data table] B where B.RPFVIN=A.RPFVIN)));

--

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Keith K]

Thanks I'll start with adding indexes which I have never used before
and

see what happens. Also not sure about your question about posting my
query. Do you mean the query that created the table [First Service Data
Table]??


It would help if you posted the query you are currently using.

Two query approach (First query get the earliest service date by VIN)
SELECT Dealer, VIN, Min(ServiceDate) as FirstService
FROM [SomeTable]
GROUP BY Dealer, Vin

Then you can use that query to get all the other data
SELECT [SomeTable].*
FROM [SomeTable] INNER JOIN QueryFirstService
ON [SomeTable].Dealer = QueryFirstService.Dealer
AND [SomeTable].Vin = QueryFirstService.Vin
AND [SomeTable].ServiceDate = QueryFirstService.FirstService

Make sure you have indexes on Dealer, Vin, and ServiceDate.

If you wish to do this for just one dealer you can speed things up
quite a bit by changing query one to limit its results to just the one
dealer.

SELECT Dealer, VIN, Min(ServiceDate) as FirstService
FROM [SomeTable]
WHERE Dealer = "NY075"
GROUP BY Dealer, Vin

With table and field names that consist of only letters, numbers, and
the underscore character (and names are not a reserved word) this can
all be done in one query.

I’m hoping someone has an idea regarding this issue








SELECT A.RPRDLR, A.RPSEQN, A.RPWODT, A.RHWOML, A.RPFVIN, A.RHCTTA,
A.RHWTTA, A.RHITTA, A.RHCPAY, A.RHIPAY INTO [First Oil Change]

FROM [First Service Data table] AS B, [First Service Data table] AS A

WHERE (((A.RPRDLR)="NY075") AND ((A.RPWODT) In (Select Min(B.RPWODT)
from [First Service Data table] B where B.RPFVIN=A.RPFVIN)));

 

[John Spencer]

OUCH! No wonder that is slow. You have a correlated subquery (runs once for
every record in the table) combined with a cartesian join. That means if you
have 10,000 records, the query will build 10,000 * 10,000 records
(1,000,000,000 records) to work with and then for every one of those 1 billion
records it will run the subquery in the where clause.

So I would rewrite that query. The spaces in your table name means that
Access will have to "stack" queries to handle the subquery in the FROM clause.

Save this query as qFirstDate - it should return the first service date for
each VIN plus dealer combination
SELECT RPFVIN, RPRDLR, Min(B.RPWODT) as FirstDate
FROM [First Service Data table]
WHERE RPRDLR ="NY075"
GROUP BY RPFVIN, RPRDLR

Now build a query with your table and this saved query.
SELECT A.RPRDLR, A.RPSEQN, A.RPWODT
, A.RHWOML, A.RPFVIN, A.RHCTTA,
A.RHWTTA, A.RHITTA, A.RHCPAY, A.RHIPAY
FROM [First Service Data table] AS A INNER JOIN qFirstDate as Q
ON A.RPFVIN = Q.RPFVIN
AND A.RPWODT = Q.FirstDate
AND A.RPRDLR = Q.RPRDLR

 

[ksquared]

OUCH! No wonder that is slow. You have a correlated subquery (runs
once for every record in the table) combined with a cartesian join.
That means if you have 10,000 records, the query will build 10,000 *
10,000 records (1,000,000,000 records) to work with and then for every
one of those 1 billion records it will run the subquery in the where
clause.

So I would rewrite that query. The spaces in your table name means
that Access will have to "stack" queries to handle the subquery in the
FROM clause.

Save this query as qFirstDate - it should return the first service
date for each VIN plus dealer combination
SELECT RPFVIN, RPRDLR, Min(B.RPWODT) as FirstDate
FROM [First Service Data table]
WHERE RPRDLR ="NY075"
GROUP BY RPFVIN, RPRDLR

Now build a query with your table and this saved query.
SELECT A.RPRDLR, A.RPSEQN, A.RPWODT
, A.RHWOML, A.RPFVIN, A.RHCTTA,
A.RHWTTA, A.RHITTA, A.RHCPAY, A.RHIPAY
FROM [First Service Data table] AS A INNER JOIN qFirstDate as Q
ON A.RPFVIN = Q.RPFVIN
AND A.RPWODT = Q.FirstDate
AND A.RPRDLR = Q.RPRDLR

Thanks just got back from a trip so I need to read your information and
digest many thanks Keith