Looking for better performance

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

I have a table with two fields: Month and Plan
- month is a date and plan is an Integer

I want to show an accumulating sum witch this query does, but it is VERY
slow.

How can I improve the performance?

SELECT tblStaffing.Month,
DSum("[Plan]","tblStaffing","[month] >= forms!frmMain!txtstart and
[Month]<=#" & [Month] & "#") AS PlanCum
FROM tblStaffing
WHERE (((tblStaffing.Month) Between [forms]![frmMain]![txtstart] And
[forms]![frmMain]![txtend]));
 
Hi Ed

Accumulations are slow (because they are calcuated on each row) but the
domain aggegate functions like DSum() are *very* slow. If you don't mind a
read-only result, consider using a subquery in place of DSum():

SELECT tblStaffing.Month,
( SELECT Sum(Plan) AS SumOfPlan
FROM tblStaffing AS Dupe
WHERE Dupe.Month >= [forms]![frmMain]![txtstart]
And Dupe.Month < tblStaffing.Month ) AS PlanCum
FROM tblStaffing
WHERE ...
 
Thanks!
That was very FAST. But, the first month's plan value is blank (no
accumulation?)
Any ideas?

ed
Allen Browne said:
Hi Ed

Accumulations are slow (because they are calcuated on each row) but the
domain aggegate functions like DSum() are *very* slow. If you don't mind a
read-only result, consider using a subquery in place of DSum():

SELECT tblStaffing.Month,
( SELECT Sum(Plan) AS SumOfPlan
FROM tblStaffing AS Dupe
WHERE Dupe.Month >= [forms]![frmMain]![txtstart]
And Dupe.Month < tblStaffing.Month ) AS PlanCum
FROM tblStaffing
WHERE ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ed said:
I have a table with two fields: Month and Plan
- month is a date and plan is an Integer

I want to show an accumulating sum witch this query does, but it is VERY
slow.

How can I improve the performance?

SELECT tblStaffing.Month,
DSum("[Plan]","tblStaffing","[month] >= forms!frmMain!txtstart and
[Month]<=#" & [Month] & "#") AS PlanCum
FROM tblStaffing
WHERE (((tblStaffing.Month) Between [forms]![frmMain]![txtstart] And
[forms]![frmMain]![txtend]));
Click to expand...
Click to expand...
 
Hi Ed,
I think just forgot an "=" sign.

And Dupe.Month <= tblStaffing.Month ) AS PlanCum



Ed said:
Thanks!
That was very FAST. But, the first month's plan value is blank (no
accumulation?)
Any ideas?

ed
Allen Browne said:
Hi Ed

Accumulations are slow (because they are calcuated on each row) but the
domain aggegate functions like DSum() are *very* slow. If you don't mind a
read-only result, consider using a subquery in place of DSum():

SELECT tblStaffing.Month,
( SELECT Sum(Plan) AS SumOfPlan
FROM tblStaffing AS Dupe
WHERE Dupe.Month >= [forms]![frmMain]![txtstart]
And Dupe.Month < tblStaffing.Month ) AS PlanCum
FROM tblStaffing
WHERE ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ed said:
I have a table with two fields: Month and Plan
- month is a date and plan is an Integer

I want to show an accumulating sum witch this query does, but it is VERY
slow.

How can I improve the performance?

SELECT tblStaffing.Month,
DSum("[Plan]","tblStaffing","[month] >= forms!frmMain!txtstart and
[Month]<=#" & [Month] & "#") AS PlanCum
FROM tblStaffing
WHERE (((tblStaffing.Month) Between [forms]![frmMain]![txtstart] And
[forms]![frmMain]![txtend]));
Click to expand...
Click to expand...
Click to expand...
 
That was it, thanks.
ed

Gary Walter said:
Hi Ed,
I think just forgot an "=" sign.

And Dupe.Month <= tblStaffing.Month ) AS PlanCum



Ed said:
Thanks!
That was very FAST. But, the first month's plan value is blank (no
accumulation?)
Any ideas?

ed
Allen Browne said:
Hi Ed

Accumulations are slow (because they are calcuated on each row) but the
domain aggegate functions like DSum() are *very* slow. If you don't mind a
read-only result, consider using a subquery in place of DSum():

SELECT tblStaffing.Month,
( SELECT Sum(Plan) AS SumOfPlan
FROM tblStaffing AS Dupe
WHERE Dupe.Month >= [forms]![frmMain]![txtstart]
And Dupe.Month < tblStaffing.Month ) AS PlanCum
FROM tblStaffing
WHERE ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table with two fields: Month and Plan
- month is a date and plan is an Integer

I want to show an accumulating sum witch this query does, but it is VERY
slow.

How can I improve the performance?

SELECT tblStaffing.Month,
DSum("[Plan]","tblStaffing","[month] >= forms!frmMain!txtstart and
[Month]<=#" & [Month] & "#") AS PlanCum
FROM tblStaffing
WHERE (((tblStaffing.Month) Between [forms]![frmMain]![txtstart] And
[forms]![frmMain]![txtend]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top