Unacceptable Form Performance

  • Thread starter Thread starter Cameron
  • Start date Start date
C

Cameron

Hi,

I currently have a form that contains four combo boxes
and a subform. The combo boxes can be used in any
combination to generate a dynamic query which is then
used as the record source for the subform. The main
purpose of this is to filter the subform using a variable
combination of criteria to generate a query.

Right now though, if I try to use just one combo box it
takes about five minutes for the subform to update.
During this period if the user clicks anywhere on the
screen Access crashes. My question is what could be the
possible cause for such poor performance.

An example of the query generated by using one combo box
would be:

SELECT * FROM AssociatesQuery WHERE Region_Category = 2;

Thanks,
Cameron
 
How many records are there being returned by the query AssociatesQuery in
your example, & what is the average row-width?

If you execute your example query directly in the query designer, how long
does it take?
 
Right now though, if I try to use just one combo box it
takes about five minutes for the subform to update.
During this period if the user clicks anywhere on the
screen Access crashes. My question is what could be the
possible cause for such poor performance.

An example of the query generated by using one combo box
would be:

SELECT * FROM AssociatesQuery WHERE Region_Category = 2;
Click to expand...

Yow. How big is AssociatesQuery? What is ITS SQL? Is Region_Category
indexed in its table?
 
Hi James,

Right now there are only about 15 records being returned
by AssociatesQuery. This could eventually get up to a
few hundred which is why I am concerned about the current
performance.

Also, I'm not quite sure what you mean by "row width".

If I attempt to run the above query in the query designer
it takes the same amount of time.

Thanks,
Cameron
 
Hi John,

AssociatesQuery is currently about 15 records in size,
but it will eventually get much larger. Regarding the
SQL, this could be the problem since its about 20 lines
in length. Region_Category is indexed in the original
table. Is there a way to do so in the query itself?

Thanks,
Cameron
 
Hi John,

AssociatesQuery is currently about 15 records in size,
but it will eventually get much larger. Regarding the
SQL, this could be the problem since its about 20 lines
in length. Region_Category is indexed in the original
table. Is there a way to do so in the query itself?
Click to expand...

<g> A hundred lines of SQL is getting pretty big.

You cannot put an index on a Query; but you should put indexes on the
Table fields that are used as criteria and for sorting. Also, if your
Query contains domain functions such as DMax, DLookUp, DCount etc; or
multiple nested IIF functions or the like, you may want to consider
how to remove some of the function calls, as they can be a major drag
on performance.
 
Hi John,

I have indexed the key table fields necessary for my
Query, and I'm pretty sure I haven't used the functions
that you mentioned. Here is the actual SQL since I'm not
sure if it is too big or not. This is merely a
demonstration of its size, since it would be awkward to
try and format it so it is legible.

Thanks,
Cameron

SELECT [Associates Join 1].[Associate ID], Titles.[Title
Name], [Associates Join 1].Last_Name, [Associates Join
1].First_Name, [Associates Join 1].Maiden_Name,
Area_Codes.[Area Code], [Associates Join 1].Phone_Number,
Regions.Name, [Associates Join 1].Background,
Gender.Gender_Name, Years.Year, Year(Date())-[Year] AS
Expr1, Boolean.Type, [Associates Join 1].Street, Cities.
[City Name], [Associates Join 1].Postal_Code, Provinces.
[Province Name], [Associates Join 1].Start_Date,
[Associates Join 1].Police_Check, DateDiff("m",
[Start_Date],Date()) AS Expr2, [Associates Join 1].[First
Name], Certification_Categories.[Cert Name],
Certification_Categories_1.[Cert Name], Degrees.[Degree
Name], Degrees_1.[Degree Name], [Associates Join
1].Admin_Fee, [Associates Join 1].Company, Area_Codes_1.
[Area Code], [Associates Join 1].Cell_Phone_Number,
[Associates Join 1].Email_Address, [Associates Join 1].
[AISI_ Provider_Number], [Associates Join
1].Associate_Password, [Associates Join
1].Coordinator_Category, [Associates Join
1].Area_Code_Category, Area_Codes.[Area Code ID],
[Associates Join 1].Region_Category, [Associates Join
1].City_Category, Boolean_1.Type, Boolean_2.Type,
Boolean_3.Type, Boolean_4.Type, [Associates Join
1].CPI_or_UMVB, Languages.Language_Name, [Associates Join
1].Gender, [Associates Join 1].Active,
Experiences.Experience, Year(Date())-Year([Police_Check])
AS Expr3, [Associates Join 1].Certification_1,
[Associates Join 1].Certification_2, [Associates Join 1].
[Degree/Diploma_1], [Associates Join 1].
[Degree/Diploma_2], [Associates Join 1].Second_Language,
[Associates Join 1].Worked_Mostly_In, [Associates Join
1].Current_Contract, Contract_Periods.Contract_Period
FROM Contract_Periods INNER JOIN (Experiences INNER JOIN
(Languages INNER JOIN (Boolean AS Boolean_5 INNER JOIN
(Boolean AS Boolean_4 INNER JOIN (Boolean AS Boolean_3
INNER JOIN (Boolean AS Boolean_2 INNER JOIN (Boolean AS
Boolean_1 INNER JOIN (Degrees AS Degrees_1 INNER JOIN
(Certification_Categories AS Certification_Categories_1
INNER JOIN (Area_Codes AS Area_Codes_1 INNER JOIN
(Degrees INNER JOIN (Certification_Categories INNER JOIN
(Provinces INNER JOIN (Cities INNER JOIN (Boolean INNER
JOIN (Gender INNER JOIN (Years INNER JOIN (Regions INNER
JOIN (Area_Codes INNER JOIN (Titles INNER JOIN
[Associates Join 1] ON Titles.[Title ID] = [Associates
Join 1].Title) ON Area_Codes.[Area Code ID] = [Associates
Join 1].Area_Code_Category) ON Regions.[Region ID] =
[Associates Join 1].Region_Category) ON Years.Year_ID =
[Associates Join 1].YOB) ON Gender.[Gender ID] =
[Associates Join 1].Gender) ON Boolean.[Boolean ID] =
[Associates Join 1].Ability_to_Travel) ON Cities.[City
ID] = [Associates Join 1].City_Category) ON Provinces.
[Province ID] = [Associates Join 1].Province_Category) ON
Certification_Categories.[Cert ID] = [Associates Join
1].Certification_1) ON Degrees.[Degree ID] = [Associates
Join 1].[Degree/Diploma_1]) ON Area_Codes_1.[Area Code
ID] = [Associates Join 1].Cell_Area_Code_Category) ON
Certification_Categories_1.[Cert ID] = [Associates Join
1].Certification_2) ON Degrees_1.[Degree ID] =
[Associates Join 1].[Degree/Diploma_2]) ON Boolean_1.
[Boolean ID] = [Associates Join 1].Active) ON Boolean_2.
[Boolean ID] = [Associates Join
1].Associate_Information_Form) ON Boolean_3.[Boolean ID]
= [Associates Join 1].Contract_Signed) ON Boolean_4.
[Boolean ID] = [Associates Join 1].
[Harassment_&_Abuse_Policy]) ON Boolean_5.[Boolean ID] =
[Associates Join 1].CPI_or_UMVB) ON Languages.Language_ID
= [Associates Join 1].Second_Language) ON
Experiences.Experience_ID = [Associates Join
1].Worked_Mostly_In) ON
Contract_Periods.Contract_Period_ID = [Associates Join
1].Current_Contract
ORDER BY [Associates Join 1].Last_Name, [Associates Join
1].First_Name;
 
Hi John,

I have indexed the key table fields necessary for my
Query, and I'm pretty sure I haven't used the functions
that you mentioned. Here is the actual SQL since I'm not
sure if it is too big or not. This is merely a
demonstration of its size, since it would be awkward to
try and format it so it is legible.
Click to expand...

Hrm. Well, joining 22 tables (or fewer tables but 22 tables in the
query) is a LOT; I'm not a bit surprised that it's slow. I wonder if
(frex) the Boolean table is necessary? If it's there just to get a
pretty format for yes/no fields, you can do the same thing by simply
including the Yes/No field and setting its Format property. That would
get rid of five tables right there!
 
Back
Top