snow-raven
Registered User.
- Local time
- Today, 06:54
- Joined
- Apr 12, 2018
- Messages
- 48
Hello kind forum users, I have a query in a new database I'm developing that persists in being slow, in spite of all I've tried to address it. I've gone through the "100 Tips for Faster Microsoft Access Databases", "The Twenty-Something Rules For Access Development", and all of Allen Browne's relevant tips, to no avail. Maybe someone here can spot my problem.
The query is intended to evaluate sets of sample gradation data. It is the third of four queries that must pass data from a table to perform necessary calculations to classify the sample. The first two queries are fast - even zippy!
Things I've tried:
-I have made each of those queries as streamlined as possible (removing joins, excess fields) while maintaining key fields and indices, and
-I've connected them in as straightforward a manner as I can in the Relationship window.
-My database is normalized as well as I know how.
-I rewrote several of the queries to follow suggestions about subqueries, nz() functions, nested IIf statements, etc.
- I've tried shifting subqueries between the four sequential queries to speed and/or simplify. I think I've isolated the slowness to this third query. I can't shift any more pieces, as this query refers to sorted/summed subqueries in the previous one.
It's much slower now. Constructive suggestions would be much appreciated:
Additional information about the general structure:
Table: Sample_Intervals (holds all sample-specific data)
Table: Sample_Gradation_Intervals (hold test data, generally 14 records per sample)
Query: Sample_Intervals_Gradation_Query (queries Sample_Intervals for data entry form)
- five calculations are performed, two with IIf statements to avoid DIV#0 errors
- two fields are sorted, no aggregates
Query: Sample_Gradation_Query (Queries Sample_Gradation_Intervals for data entry subform, allows data entry)
- two fields are sorted, no aggregates or calculations
Query: Sample Gradation_Retained (Queries Sample_Intervals_Gradation_Query and Sample_Gradation_Intervals to perform calculations on gradation interval data. Separated from previous query as this one would not allow data entry.)
- two calculations are performed, one with IIf statements to avoid DIV#0 errors
- no aggregates or sorting
Query: Sample Gradation_Run_Sum (Queries Sample Gradation_Retained)
- two running total calculations are performed
- one subquery "Previous_Sieve" references the previous record to pull a value to allow calculations between subsequent records in the next query (compare data from sieve size 2 to data from sieve size 1)
Query: Sample Gradation_Row_Math (Query in question - Queries Sample Gradation_Run_Sum)
- performs calculations to determine the slope/intercept defined by the Sieve_Log10 and Percent_Pass of two adjacent records, as defined by the "Previous_Sieve" relationship.
- performs four calculations to determine slope/intercept, including two SELECT subqueries that pull the values from the previous record
- also performs four IIf calculations that use the slope/intercept values. IIf statements are to prevent DIV#0 errors where slope = 0. I orginally had these in a separate query, but later combined them in the hopes of increasing efficiency overall
Query: Sample_Ds (Queries Sample Gradation_Row_Math to select minimum value)
- four queries that select the minimum value from the four IIf statements in previous query, meeting a WHERE clause of greater than a constant value
Further queries need to use these Sample_Ds values!
The query is intended to evaluate sets of sample gradation data. It is the third of four queries that must pass data from a table to perform necessary calculations to classify the sample. The first two queries are fast - even zippy!
Things I've tried:
-I have made each of those queries as streamlined as possible (removing joins, excess fields) while maintaining key fields and indices, and
-I've connected them in as straightforward a manner as I can in the Relationship window.
-My database is normalized as well as I know how.
-I rewrote several of the queries to follow suggestions about subqueries, nz() functions, nested IIf statements, etc.
- I've tried shifting subqueries between the four sequential queries to speed and/or simplify. I think I've isolated the slowness to this third query. I can't shift any more pieces, as this query refers to sorted/summed subqueries in the previous one.
It's much slower now. Constructive suggestions would be much appreciated:
Code:
SELECT DISTINCT Sample_Gradation_RunSum.Grad_ID, Sample_Gradation_RunSum.Samp_ID, Sample_Gradation_RunSum.Sieve_Size, Sample_Gradation_RunSum.Sieve_Log10, Sample_Gradation_RunSum.Percent_Pass, Sample_Gradation_RunSum.Previous_Sieve,
(SELECT (Percent_Pass)
FROM Sample_Gradation_RunSum AS Pass1
WHERE Pass1.Sieve_Size = Sample_Gradation_RunSum.Previous_Sieve
AND Pass1.Samp_ID = Sample_Gradation_RunSum.Samp_ID) AS Previous_Pass,
(SELECT (Sieve_Log10)
FROM Sample_Gradation_RunSum AS Sieve1
WHERE Sieve1.Sieve_Size = Sample_Gradation_RunSum.Previous_Sieve
AND Sieve1.Samp_ID = Sample_Gradation_RunSum.Samp_ID) AS Last_Sieve_Log10,
([Percent_Pass]-[Previous_Pass])/([Sieve_Log10]-[Last_Sieve_Log10]) AS Grad_m,
[Percent_Pass]-([Grad_m]*[Sieve_Log10]) AS Grad_b,
IIf([Grad_m]=0,CDbl(0),(0.6-[Grad_b])/[Grad_m]) AS aD60,
IIf([Grad_m]=0,CDbl(0),(0.5-[Grad_b])/[Grad_m]) AS aD50,
IIf([Grad_m]=0,CDbl(0),(0.3-[Grad_b])/[Grad_m]) AS aD30,
IIf([Grad_m]=0,CDbl(0),(0.1-[Grad_b])/[Grad_m]) AS aD10
FROM Sample_Gradation_RunSum;
Additional information about the general structure:
Table: Sample_Intervals (holds all sample-specific data)
Table: Sample_Gradation_Intervals (hold test data, generally 14 records per sample)
Query: Sample_Intervals_Gradation_Query (queries Sample_Intervals for data entry form)
- five calculations are performed, two with IIf statements to avoid DIV#0 errors
- two fields are sorted, no aggregates
Query: Sample_Gradation_Query (Queries Sample_Gradation_Intervals for data entry subform, allows data entry)
- two fields are sorted, no aggregates or calculations
Query: Sample Gradation_Retained (Queries Sample_Intervals_Gradation_Query and Sample_Gradation_Intervals to perform calculations on gradation interval data. Separated from previous query as this one would not allow data entry.)
- two calculations are performed, one with IIf statements to avoid DIV#0 errors
- no aggregates or sorting
Query: Sample Gradation_Run_Sum (Queries Sample Gradation_Retained)
- two running total calculations are performed
- one subquery "Previous_Sieve" references the previous record to pull a value to allow calculations between subsequent records in the next query (compare data from sieve size 2 to data from sieve size 1)
Query: Sample Gradation_Row_Math (Query in question - Queries Sample Gradation_Run_Sum)
- performs calculations to determine the slope/intercept defined by the Sieve_Log10 and Percent_Pass of two adjacent records, as defined by the "Previous_Sieve" relationship.
- performs four calculations to determine slope/intercept, including two SELECT subqueries that pull the values from the previous record
- also performs four IIf calculations that use the slope/intercept values. IIf statements are to prevent DIV#0 errors where slope = 0. I orginally had these in a separate query, but later combined them in the hopes of increasing efficiency overall
Query: Sample_Ds (Queries Sample Gradation_Row_Math to select minimum value)
- four queries that select the minimum value from the four IIf statements in previous query, meeting a WHERE clause of greater than a constant value
Further queries need to use these Sample_Ds values!
Last edited: