It might help the process to have a value other than Null in the field. And
if you only have two values for mth 1 Basis, the presence or absence of the
index is probably not going to make a lot of difference in the speed of the
query. If all the fields in the WHERE clause and the JOIN clause have
indexes, there is not a lot more you can do to speed up the query.
AND in answer to your other questions. The use of aliases for the table
should make no real difference in the performance of the query. In this
instance, the use of aliases just makes it easier to enter and edit the SQL.
After all it is much easier to type X (one character) than it is to type
[x confirmed] (13 characters)
One thing that sometimes speeds queries is to use nested queries.
You might build a query that returns the records and fields you are interested
in the table [X Confirmed]. AND then join that query to staffs instead of the
table. I have seen dramatic improvements in performance in some cases with
this technique. I think that the first query would look something like the
following. You might build this and see how fast it runs and how many records
it returns. If it is fast then try the next step of joining it to table Staffs
PARAMETERS Forms![frm x main]![prac name] Text ( 255 )
, Forms![frm x main]![prev month] Text ( 255 );
SELECT X.practice
, X.[Tax code no]
, X.[Tax code ltr]
, X.[mth 1 basis]
, X.[hourly rate]
, X.[ni code] AS PrevNIcode
FROM staffs AS S INNER JOIN [x confirmed] AS X
ON S.name = X.name
WHERE (X.practice=[Forms]![frm x main]![prac name]
AND X.[month name]=[Forms]![frm x main]![prev month])
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hello John
Many thanks for your reply.
I have substituted both of your suggested final AND expressions, but
unfortunately the resulting queries took pretty much the same time to run as
the original!
I should add that the [mth 1 basis] field has two valid values - "M", or
null. Might it be better to alter this to "M" or "X"? Other than that, can
you think of anything else that would speed up this query?
Thanks again for your help.
Les
John Spencer said:
PARAMETERS Forms![frm x main]![prac name] Text ( 255 )
, Forms![frm x main]![prev month] Text ( 255 );
SELECT X.practice, S.name AS StaffName
, X.[Tax code no] & X.[Tax code ltr] & X.[mth 1 basis] AS PrevTaxCode
, S.[Tax code no] & S.[Tax code ltr] & S.[mth 1 basis] AS NewTaxCode
, X.[hourly rate] AS PrevHourlyRate
, S.[Hourly rate] AS NewHourlyRate, X.[ni code] AS PrevNIcode
, S.[NI code] AS NewNIcode
, S.hasLEFT
FROM staffs AS S INNER JOIN [x confirmed] AS X
ON S.name = X.name
WHERE (((X.practice)=[Forms]![frm x main]![prac name])
AND ((X.[ni code])<>.[ni code])
AND ((S.hasLEFT)=False)
AND ((X.[month name])=[Forms]![frm x main]![prev month]))
OR (((X.practice)=[Forms]![frm x main]![prac name])
AND ((X.[hourly rate])<>.[hourly rate])
AND ((S.hasLEFT)=False)
AND ((X.[month name])=[Forms]![frm x main]![prev month]))
OR (((X.practice)=[Forms]![frm x main]![prac name])
AND ((S.hasLEFT)=False)
AND ((X.[month name])=[Forms]![frm x main]![prev month])
AND (([X].[Tax code no] & [X].[Tax code ltr] & [X].[mth 1 basis])
<> .[Tax code no] & .[Tax code ltr] & .[mth 1 basis]));
That last AND is the culprit. Indexes are cannot be used there.
PERHAPS you can use
NOT ([X].[Tax code no] = S.[Tax Code No]
AND [X].[Tax code ltr] = .[Tax code ltr]
AND [X].[mth 1 basis] = .[mth 1 basis])
Or the equivalent
([X].[Tax code no] <> S.[Tax Code No]
OR [X].[Tax code ltr] <> .[Tax code ltr]
OR [X].[mth 1 basis] <> .[mth 1 basis])
Of course, if you have nulls in any of those fields then the expression would
be more complex. Since concatenating the three fields together will eliminate
null values as long as at least one of the fields has a value.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Jeff Boyce wrote:
One down, ...
Is the query as slow if you try 'hard-coded' parameters instead of prompting
for them?
Regards
Jeff Boyce
Microsoft Office/Access MVP
Hello Jeff
Thanks for your reply.
Yep - all 'indexed - Duplicates OK'.
What next?!
Les
Leslie
You may have already addressed this ...
Are all fields used in your query for selection criteria, joins and
sorting
indexed in their underlying tables?
Regards
Jeff Boyce
Microsoft Office/Access MVP
Hello All
Why is the query below so slow - it normally takes 30-45 seconds. There
are
~4000 records on table [staffs] and ~100,000 records in table [x
conformed]:
surely these aren't large tables? To be honest I don't really
understand
why
the two tables are 'aliased' in the query, or whether that makes a
difference.
Hope someone can help.
Thanks as ever
Leslie Isaacs
PARAMETERS Forms![frm x main]![prac name] Text ( 255 ), Forms![frm x
main]![prev month] Text ( 255 );
SELECT X.practice, S.name AS StaffName, X.[Tax code no] & X.[Tax code
ltr]
&
X.[mth 1 basis] AS PrevTaxCode, S.[Tax code no] & S.[Tax code ltr] &
S.[mth
1 basis] AS NewTaxCode, X.[hourly rate] AS PrevHourlyRate, S.[Hourly
rate]
AS NewHourlyRate, X.[ni code] AS PrevNIcode, S.[NI code] AS NewNIcode,
S.hasLEFT
FROM staffs AS S INNER JOIN [x confirmed] AS X ON S.name = X.name
WHERE (((X.practice)=[Forms]![frm x main]![prac name]) AND ((X.[ni
code])<>.[ni code]) AND ((S.hasLEFT)=False) AND ((X.[month
name])=[Forms]![frm x main]![prev month])) OR
(((X.practice)=[Forms]![frm
x
main]![prac name]) AND ((X.[hourly rate])<>.[hourly rate]) AND
((S.hasLEFT)=False) AND ((X.[month name])=[Forms]![frm x main]![prev
month])) OR (((X.practice)=[Forms]![frm x main]![prac name]) AND
((S.hasLEFT)=False) AND ((X.[month name])=[Forms]![frm x main]![prev
month])
AND (([X].[Tax code no] & [X].[Tax code ltr] & [X].[mth 1
basis])<>.[Tax
code no] & .[Tax code ltr] & .[mth 1 basis]));
