asked on 7/10/2011

Split DB on LAN runs very slow

Access 2003 on a Dell D546, I dunno, 3.4GHz, 4GB RAM.

I put the mdb on the home LAN drive so spouse and I could share access. We run lots of things off the LAN and no performance issues. The mdb was working fine, adequate performance. Then I split the DB so I could work on the front end in another environment also.

Performance went down the drain. It like crawls to refresh the Main form sub-forms...painting the formatting on each continuous record one...at...a...time.

It's a fairly complicated form (seven sub-forms, and lots of cross referencing), but I reran the pre-split version and it still runs much faster. Rebooted the machine, no joy.

I tried moving the front end to my PC instead of leaving it on the LAN, but that didn't improve the performance either.

Recombined the DB, and peformance picked up again, like 4 times faster, as the pre-split.

Any suggestions about this would be appreciated.

Thanks!

Microsoft Access

Last Comment

Scott McDaniel (EE MVE )

7/10/2011

ASKER CERTIFIED SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

7/10/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

SOLUTION

Scott McDaniel (EE MVE )

7/10/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

codequest

7/10/2011

ASKER

Thanks for inputs. By removing elements incrementally I narrowed it down to the attached query, which is *much to the nth* faster if I remove the DLookups.

What I'm doing here is rolling up priorities and due dates from the Action and Task level up to the Activity level, so I can sort for the Activities that have the highest priority Task or Action, etc.

I'll start working on it, however, I'm not that clever with these nested queries, so, any suggestions on how to rewrite this with *additional* sub-queries or otherwise produce the same results would be appreciated.

Thanks.

SELECT [020_Activity].*, DLookUp("[ATVACTPri1]","[044_MaxActionPriority_ATV_01]","ActivityID= " & [020_Activity].ID) AS ATVACTPri1, DLookUp("[ATVTSKPri1]","[032_MaxTaskPriority_ATV_01]","ActivityID= " & [020_Activity].ID) AS ATVTSKPri1, Format(DLookUp("[ATVTSKDueDate1]","[035_MinTaskDueDate_ATV_01]","ActivityID= " & [020_Activity].ID),"mm/dd") AS ATVTSKDueDate1, Format(DLookUp("ATVACTDueDate1","046_MinActionDueDate_ATV_01","ActivityID= " & [020_Activity].ID),"mm/dd") AS ATVACTDueDate1, [008_Program].Type
FROM 010_Project INNER JOIN (020_Activity LEFT JOIN 008_Program ON [020_Activity].ProgramID = [008_Program].ID) ON [010_Project].ID = [020_Activity].ProjectID
WHERE ((([020_Activity].ActivityPriority)>-99))
ORDER BY [020_Activity].ProjAbbrev, [020_Activity].ActivitySeq, [020_Activity].ActivityPriority;

Open in new window

codequest

7/10/2011

ASKER

Just a general approach to writing the necessary queries would be enough.

The subquery 032_MaxTaskPriority_ATV_01 is:

SELECT [031_Tasks_NoSoftDeletes].ActivityID, Max([031_Tasks_NoSoftDeletes].TaskPriority) AS ATVTSKPri1
FROM 031_Tasks_NoSoftDeletes
GROUP BY [031_Tasks_NoSoftDeletes].ActivityID;

where [031_Tasks_NoSoftDeletes] is just the task record with soft deletes excluded.

codequest

7/10/2011

ASKER

I figured out the approach to the queries. A series of outer joins to associated queries for the max priority and min due date values I'm looking for.

Points awarded for response to the original question, and the best value I always get from EE, which is to make me think harder!

codequest

7/10/2011

ASKER

The responses from experts helped eliminate possibilities and narrow down the search for the problem; more helpful to future readers will be to know the impact of the DLookups.

Scott McDaniel (EE MVE )

7/10/2011

I just saw your post, and you're right - a Join is far preferable to a DLookup (or any other UDF in a query).

Split DB on LAN runs *very* slow

Split DB on LAN runs very slow