UtterAccess Discussion Forums > Dsum Causing Query to Run Slow

Full Version: Dsum Causing Query to Run Slow

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

ethoemmes

Jul 6 2010, 06:15 AM

Hello,

I am using qryOutstandingWork2 as a recordsource for a form. The form is used to update tasks worked by a department of users. The basic schema is as follows:

tblIncomingWork - one record per task per day
[Work Incoming ID]
[Signon ID]
[Task Name]
[Amount Incoming]

tblWorkCompleted
[Work Completed ID]
[Work Incoming ID] FK to work table
[Work Date]
[Work Amount]

The interface has been designed to allow the users to first input their incoming work for the day. They then open the oustanding tasks form which displays all outstading tasks and allows them to log worked tasks. There may a number of items coming in for each task each day [Amount Incoming] and the users may complete all or some of the oustanding tasks per day [Work Amount. The outstanding tasks form may then hold outstading work for today and tasks from previous days which haven't yet been cleared down.

I am working out what is outstanding using the queries below which do work but are very slow. I expect this is down to the DSum function I am using to grab the sum of the [Worked Field], I resorted to the DSum function as I couldn't find a way to use SQL and still keep the recordset updateable. Maybe this could be done using subqueries but I couldn't figure out how to do this. I also suspect I could combine these queries but again my SQL skills are not up to scratch!

qryOutstandingWork

CODE

qryOutstandingWork2

CODE

SELECT qryOutstandingWork.[Work Incoming ID], qryOutstandingWork.SignonID, qryOutstandingWork.[Team Name], qryOutstandingWork.[Task Name], qryOutstandingWork.System, qryOutstandingWork.[Sub Section], qryOutstandingWork.[Date Allocated], qryOutstandingWork.[Amount Incoming], qryOutstandingWork.Worked, IIf(IsNull([Worked]),[Amount Incoming],[Amount Incoming]-[Worked]) AS Outstanding, qryOutstandingWork.[Worked-Temp]
FROM qryOutstandingWork
WHERE (((IIf(IsNull([Worked]),[Amount Incoming],[Amount Incoming]-[Worked]))>0))
ORDER BY qryOutstandingWork.[Date Allocated];

Can anyone suggest any methods for speeding up these queries? I am experimenting with using Trevor Best's basLookup module which replaces the dSum function with a more efficient tSum function. Up to now though, I have not managed to get this working properly.

Many thanks

Edgar

Doug Steele

Jul 6 2010, 06:33 AM

Yes, using domain aggregate functions such as DSum will usually be slow.

See whether this is any better for qryOutstandingWork:

CODE

SELECT tblWorkIncoming.[Work Incoming ID],
tblWorkIncoming.SignonID,
tblWorkIncoming.[Team Name],
tblWorkIncoming.[Task Name],
tblSystems.System,
tblWorkIncoming.[Sub Section],
tblWorkIncoming.[Date Allocated],
tblWorkIncoming.[Amount Incoming],
Sum([Amount Worked]) AS Worked,
tblWorkIncoming.[Worked-Temp]
FROM tblSystems INNER JOIN tblWorkIncoming
ON tblSystems.ID = tblWorkIncoming.System
WHERE tblWorkIncoming.SignonID=fGetUserName()
GROUP BY tblWorkIncoming.[Work Incoming ID],
tblWorkIncoming.SignonID,
tblWorkIncoming.[Team Name],
tblWorkIncoming.[Task Name],
tblSystems.System,
tblWorkIncoming.[Sub Section],
tblWorkIncoming.[Date Allocated],
tblWorkIncoming.[Amount Incoming],
tblWorkIncoming.[Worked-Temp]

ethoemmes

Jul 6 2010, 06:59 AM

Hi Doug,

Thanks for the quick reply.

I'm assuming you meant I should change code to the below (I added in the tblWorkCompleted table)?

CODE

SELECT tblWorkIncoming.[Work Incoming ID], tblWorkIncoming.SignonID, tblWorkIncoming.[Team Name], tblWorkIncoming.[Task Name], tblSystems.System, tblWorkIncoming.[Sub Section], tblWorkIncoming.[Date Allocated], tblWorkIncoming.[Amount Incoming], Sum(tblWorkCompleted.[Amount Worked]) AS Worked, tblWorkIncoming.[Worked-Temp]
FROM tblWorkCompleted RIGHT JOIN (tblSystems INNER JOIN tblWorkIncoming ON tblSystems.ID = tblWorkIncoming.System) ON tblWorkCompleted.[Work Incoming ID] = tblWorkIncoming.[Work Incoming ID]
WHERE (((tblWorkIncoming.SignonID)=fGetUserName()))
GROUP BY tblWorkIncoming.[Work Incoming ID], tblWorkIncoming.SignonID, tblWorkIncoming.[Team Name], tblWorkIncoming.[Task Name], tblSystems.System, tblWorkIncoming.[Sub Section], tblWorkIncoming.[Date Allocated], tblWorkIncoming.[Amount Incoming], tblWorkIncoming.[Worked-Temp];

This does speed the query up but it prevents the recordsets from being updatable. Any way around this?

Thanks

Doug Steele

Jul 6 2010, 07:43 AM

Sorry, no. As soon as you use a function such as Sum in a SQL statement, the resultant recordset becomes read-only. Given that each row is potentially the sum of multiple rows, how could Access know which row(s) you wanted to update?

ethoemmes

Jul 6 2010, 07:47 AM

That's what I thought and why I used DSum in the first place.

I only ever have to update the left table i.e. tblWorkIncoming though so is there any way of achieving this using sub queries?

Thanks

Doug Steele

Jul 6 2010, 08:01 AM

None that I can think of off the top of my head.

If you're not using this on a continuous form, you could consider setting the ControlSource of the text box to the DSum calculation.

Gustav

Jul 6 2010, 08:36 AM

> This does speed the query up but it prevents the recordsets from being updatable. Any way around this?

Yes. Write the sum results to a temporary table having a unique key; then, in your query, join this to your main table (which you wish to update).

/gustav

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.