Slow query

[Leslie Isaacs]

Hello All
I have a query based on 2 tables = [stafs] and [x confirmed].
Essentially, [staffs] contains standing data (like staff names, hourly rates
etc) and [x confirmed] contains monthly data - this is added to every month.
Some of the monthly data is in fact just a record of the standing data for
that month - i.e. some data from [staffs] is appended to [x confirmed].
I need a query that will tell me where this data has changed - i.e. where
the value of certain fields in [staffs] is not the same as the corresponding
fields in [x confirmed] for the current month (the current month is given at
[Forms]![frm x main]![prev month]).

I have the query below, which works, but takes 30-40 seconds to run. All the
relevant fields are indexed. If anyone can see how I could speed it up I
would be very grateful.

Many thanks
Les

The query I have:

SELECT [x confirmed].practice, staffs.name, [x confirmed]![Tax code no] & [x
confirmed]![Tax code ltr] & [x confirmed]![mth 1 basis] AS Expr1, [x
confirmed]![Tax code no] & [x confirmed]![Tax code ltr] & [x confirmed]![mth
1 basis] AS [prev tax code], [staffs]![Tax code no] & [staffs]![Tax code
ltr] & [staffs]![mth 1 basis] AS [new tax code], [x confirmed]![hourly rate]
AS [prev hourly rate], [staffs]![hourly rate] AS [new hourly rate], [x
confirmed]![ni code] AS [prev NI code], [staffs]![ni code] AS [new NI code]
FROM staffs INNER JOIN [x confirmed] ON staffs.name = [x confirmed].name
WHERE ((([x confirmed].practice)=[Forms]![frm x main]![prac name]) AND (([x
confirmed]![Tax code no] & [x confirmed]![Tax code ltr] & [x confirmed]![mth
1 basis])<>[staffs]![Tax code no] & [staffs]![Tax code ltr] & [staffs]![mth
1 basis]) AND (([x confirmed].[month name])=[Forms]![frm x main]![prev
month])) OR ((([x confirmed].practice)=[Forms]![frm x main]![prac name]) AND
(([x confirmed]![hourly rate])<>[staffs]![hourly rate]) AND (([x
confirmed].[month name])=[Forms]![frm x main]![prev month])) OR ((([x
confirmed].practice)=[Forms]![frm x main]![prac name]) AND (([x
confirmed]![ni code])<>[staffs]![ni code]) AND (([x confirmed].[month
name])=[Forms]![frm x main]![prev month]));

 

[John Spencer]

When you concatenate the values together the query can no longer use indexes
on the fields. If you need to see the concatenated values then leave them
in the SELECT clause but break them out in the WHERE clause to individual
fields. Try changing this part of the WHERE clause

([x confirmed]![Tax code no] & [x confirmed]![Tax code ltr] &
[x confirmed]![mth 1 basis])<>[staffs]![Tax code no] &
[staffs]![Tax code ltr] & [staffs]![mth 1 basis])

to the following

NOT ([x confirmed].[Tax code no] = Staffs.[Tax Code No]
AND [x confirmed].[Tax code ltr] = Staffs.[Tax Code Ltr]
AND [x confirmed]![mth 1 basis]) = Staffs.[Mth 1 Basis])

Of course, if some of the values could be nulls then you will need to handle
that in the query.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello All
I have a query based on 2 tables = [stafs] and [x confirmed].
Essentially, [staffs] contains standing data (like staff names, hourly
rates
etc) and [x confirmed] contains monthly data - this is added to every
month.
Some of the monthly data is in fact just a record of the standing data for
that month - i.e. some data from [staffs] is appended to [x confirmed].
I need a query that will tell me where this data has changed - i.e. where
the value of certain fields in [staffs] is not the same as the
corresponding
fields in [x confirmed] for the current month (the current month is given
at
[Forms]![frm x main]![prev month]).

I have the query below, which works, but takes 30-40 seconds to run. All
the
relevant fields are indexed. If anyone can see how I could speed it up I
would be very grateful.

Many thanks
Les

The query I have:

SELECT [x confirmed].practice, staffs.name, [x confirmed]![Tax code no] &
[x
confirmed]![Tax code ltr] & [x confirmed]![mth 1 basis] AS Expr1, [x
confirmed]![Tax code no] & [x confirmed]![Tax code ltr] & [x
confirmed]![mth
1 basis] AS [prev tax code], [staffs]![Tax code no] & [staffs]![Tax code
ltr] & [staffs]![mth 1 basis] AS [new tax code], [x confirmed]![hourly
rate]
AS [prev hourly rate], [staffs]![hourly rate] AS [new hourly rate], [x
confirmed]![ni code] AS [prev NI code], [staffs]![ni code] AS [new NI
code]
FROM staffs INNER JOIN [x confirmed] ON staffs.name = [x confirmed].name
WHERE ((([x confirmed].practice)=[Forms]![frm x main]![prac name]) AND
(([x
confirmed]![Tax code no] & [x confirmed]![Tax code ltr] & [x
confirmed]![mth
1 basis])<>[staffs]![Tax code no] & [staffs]![Tax code ltr] &
[staffs]![mth
1 basis]) AND (([x confirmed].[month name])=[Forms]![frm x main]![prev
month])) OR ((([x confirmed].practice)=[Forms]![frm x main]![prac name])
AND
(([x confirmed]![hourly rate])<>[staffs]![hourly rate]) AND (([x
confirmed].[month name])=[Forms]![frm x main]![prev month])) OR ((([x
confirmed].practice)=[Forms]![frm x main]![prac name]) AND (([x
confirmed]![ni code])<>[staffs]![ni code]) AND (([x confirmed].[month
name])=[Forms]![frm x main]![prev month]));