SubQuery performance degrades using SQL Server as the backend

[EuniceH]

This query is slow using MS Access 2000 as a backend, but it gets slower (4 hours or more) using SQL Server 2008 Express as the backend. Is there any way you can see to improving the performance?
The indexes for tblARTransactions are:
composite Primary Key (ARTransDate, ContractID, ARTransTypeID, Sequence)
ARTranactionID
ARTransDate & TimeStamp
ContractID
ContractVehicleID
DeleteDTS


EntryDate

and here's the query
SELECT a2.*
FROM tblARTransactions AS a2
WHERE (( (a2.ARTransDate) > DateAdd("d",-5,Date())) AND ((a2.ContractVehicleID) IN
(SELECT a1.ContractVehicleID
FROM tblARTransactions AS a1
WHERE a2.ARTransDate >= DateAdd("d",-1,a1.ARTransDate) AND a2.ARTransDate <= DateAdd("d",1,a1.ARTransDate)
AND a2.ARTransTypeID = a1.ARTransTypeID
AND a2.ARTransactionID <> a1.ARTransactionID
)))
ORDER BY a2.ContractID, a2.ARTransDate DESC;

[orange]

How many records in the table?
You are doing a lot of Date manipulation in the query.

In plain English what is the selection criteria? Maybe someone will have another method of achieving the query.

[EuniceH]

How many records in the table?

870,000 records

[orange]

In plain English what is the selection criteria? Maybe someone will have another method of achieving the query.

[Eunice.harris]

in english, is there another record in tblARTransactions matching this one that
-is in the last 5 days
-has the same ContractVehicleID
-is within 1 day of the other record
-has the same ARTransTypeID

[orange]

You could try these for comparison -- but you know your data better that I do.

1-

SELECT A2.*
FROM tblArTransactions AS A2 INNER JOIN tblArTransactions AS A1 ON A2.ARTransTypeId = A1.ARTransTypeId
WHERE (((A2.ARTransdate) Between DateAdd("d",-1,[a1].[ARTransDate]) And
DateAdd("d",1,[a1].[artransdate])) AND
((A2.ArTransactionId)<>[a1].[artransactionid]) AND
((A2.ARTransdate) Between DateAdd("d",-5,Date()) And Date()) AND
((A2.ContractVehicleId)=[a1].[contractvehicleid]));

2-

SELECT A2.*
FROM tblArTransactions AS A2
WHERE ((A2.ARTransdate) Between DateAdd("d",-5,Date()) And Date()) AND Exists
( Select "x" From tblArTransactions AS A1
Where A2.ARTransTypeId = A1.ARTransTypeId and
((A2.ARTransdate) Between DateAdd("d",-1,[a1].[ARTransDate]) And DateAdd("d",1,[a1].[artransdate])) AND
((A2.ArTransactionId)<>[a1].[artransactionid]) AND
((A2.ContractVehicleId)=[a1].[contractvehicleid])
);

Good luck.

[EuniceH]

your 1st query works so well, only takes about 5 seconds to run. Thanks, I assume that putting the "((A2.ARTransdate) Between DateAdd("d",-5,Date()) And Date())" in the inside query helps the performance? I intuitively but mistakenly thought it should be in the outside query.
Thanks so much!
EuniceH

[orange]

Glad it worked, but I would expect the second query with the Exists to be faster.

As for the selection, in the first query I didn't use a subquery. The query uses an Inner Join based on ArTransTypeId and resolves the other criteria in the Where clause.

I just looked at it again and think this could be an improvement (Inner Join on 2 fields)

SELECT A2.*
FROM tblArTransactions AS A2 INNER JOIN tblArTransactions AS A1 ON
(A2.ContractVehicleId = A1.ContractVehicleId) AND
(A2.ARTransTypeId = A1.ARTransTypeId)
WHERE
(((A2.ARTransdate) Between DateAdd("d",-1,[a1].[ARTransDate]) And DateAdd("d",1,[a1].[artransdate]) And (A2.ARTransdate) Between DateAdd("d",-5,Date()) And Date()) AND ((A2.ArTransactionId)<>[a1].[artransactionid]));

My gut feeling is that the EXISTS query (#2) should be faster... but you can try it and let us know.

Good luck with your project.