Solved

Access 2003 Unexpectedly Slow Query Performance

Posted on 2009-05-10
13
977 Views
Last Modified: 2013-11-27
Hi Experts,

I am running a very simple Query in Access 2003 which should run like lightening however is running like a snail in chocolate sauce! It should run in less than a second however takes upwards of 2 minutes to run.

The query is called "VIEW JOBS - FILTER - NEW JOBS" screenshot Q1 below refers.

There are two tables, one containing repair jobs info "MAINTENANCE LOG FAULT CALL (UNDERLYING)" the other one containing job status information "MLFC - JOB STATUS". Each repair job in MAINTENANCE LOG FAULT CALL (UNDERLYING) can be assigned to a repair company indicated by the "Maintainer" field (Text,20, Indexed) and has a unique job number assigned to it "Our Reference" (LongInt, Indexed No Duplicates). Each table has approx 42,000 records, some jobs have no status yet so doing a left join so that all jobs are displayed even they they don't have a status. I have split the DB into Front-End, Back-End, the two B/E tables are in fact in separate MDB files ("MAINTENANCE LOG FAULT CALL (UNDERLYING) - is in FAULTCALL.MDB and "MLFC - JOB STATUS" is in JobStatus.MDB.

The machine they are running on in a brand new Quad Core Xeon Machine with 16Gb RAM, these simple queries should run pretty instantly on this sort of spec.

Basically I am trying to join the two tables together to get the Job Status for all jobs assigned to a particular maintainer (in this example one called '1stAid', easy enuf to do and screenshot Q1 shows the basic query. Problem is this query takes about 1 minute to run just to find 18 records!  (Results shown in screenshot R1). If I change the Maintainer to say 'ssworkshop' it produces 2 results and takes proportionately less time to run however still around 20 seconds or so which is far too low to be usable. If 100 records result it takes upwards of 10 minutes!

If however I modify the query and remove the criteria for the "Maintainer" field the query runs INSTANTLY in less than 1 second! Despite the fact it now produces 160 records (Q2 and R2 refer).

If I remove the Join and just query MAINTENANCE LOG FAULT CALL (UNDERYING) for Maintainer='1stAid' the query again runs instantly.

I have tried splitting the query into two sections, first query would be a straight query just on MAINTENANCE LOG FAULT CALL (UNDERLYING) with Maintaienr = '1stAid" and then use the output of this as the input of a second query on which to then joins with "MLFC - JOB STATUS" on the OurReference field, however still takes 2 minutes to return 16 records. if I again remove the criteria for Maintainer='1stAid" from the first query it runs virtually INSTANTLY again and prouced 160 records.

I have tried using the original query with the Maintainer = "1stAid" taken out again and using as the basis for a DCOUNT("*","VIEW JOBS - FILTER - NEW JOBS","[Maintaiener]='1stAid'" and this time it works INSTANTLY, however as soon as the Maintainer = '1stAid' is put back into the query is runs very slowly again.

I have also tried opening both tables in background before I run the query as I know this can sometimes speed up slow queries hoever no luck this time.

I have also tried repairing the database, I have re-created a blank new database and imported the Table Spec for MAINTENANCE LOG FAULT CALL (UNDERLYING), removed the index on Maintainer field, then ran an Append Query from the original copy of the table to re-create the table and then re-created the index on Maintainer field to see if it was something to do with a corrupt index, however still the same.

Whats going on ??? This has me really stumped! Can anyone shed any light on this one for me, its driving me nuts

Any help very much appreciated!
Q1.jpg
R1.jpg
Q2.jpg
R2.JPG
0
Comment
Question by:MDIRECT
  • 5
  • 3
  • 2
  • +1
13 Comments
 
LVL 84
ID: 24347682
Can you post your query's SQL? In the query designer, click View - SQL.

Why is your Primay Key field in Job Status Codes also the Foreign Key field for the association with Maintenance Log? This is not a normalized table structure; if JobStatusCode needs to be related to Maintenance Log, and if Job Status Codes can have more than one related record, then you really should have a separate Primary Key field.

How may columns are in your two tables?

Indexing can speed up queries, but it can have a detrimental affect on other processes so don't index randomly; you might try adding/removing indexes to see what happens. I'd concentrate on the table structure first, however.
0
 

Author Comment

by:MDIRECT
ID: 24347774
Hi LSM,

SQL for Q1 is as follows...

SELECT [MAINTENANCE LOG FAULT CALL (UNDERLYING)].Maintainer, [MAINTENANCE LOG FAULT CALL (UNDERLYING)].[Our Reference], [MAINTENANCE LOG FAULT CALL (UNDERLYING)].[Is New Call], [MAINTENANCE LOG FAULT CALL (UNDERLYING)].[Our Invoice Number]
FROM [MAINTENANCE LOG FAULT CALL (UNDERLYING)] LEFT JOIN [MLFC - JOB STATUS CODES] ON [MAINTENANCE LOG FAULT CALL (UNDERLYING)].[Our Reference] = [MLFC - JOB STATUS CODES].JobReference
WHERE ((([MAINTENANCE LOG FAULT CALL (UNDERLYING)].Maintainer)="1stAid") AND (([MAINTENANCE LOG FAULT CALL (UNDERLYING)].[Our Reference])>=55269) AND (([MAINTENANCE LOG FAULT CALL (UNDERLYING)].[Is New Call])=Yes) AND (([MAINTENANCE LOG FAULT CALL (UNDERLYING)].[Our Invoice Number])=0 Or ([MAINTENANCE LOG FAULT CALL (UNDERLYING)].[Our Invoice Number]) Is Null) AND (([MAINTENANCE LOG FAULT CALL (UNDERLYING)].[Fault Dudd])=No))
ORDER BY [MAINTENANCE LOG FAULT CALL (UNDERLYING)].[Our Reference];


Table MLFC - JOB STATUS primary key is JobReference, and in MAINTENANCE LOG FAULT CALL (UNDERLYING) the primary key is OurReference.

MAINTENANCE LOG FAULT CALL (UNDERLYING) has 154 fields, MLFC - JOB STATUS CODES has 13
0
 
LVL 84
ID: 24347934
So there can be only ONE record in [MLFC - Job Status] that relates to a record in the Maintenance Log?

Do you have an Index set on the Maintainer column? You might try that ...

0
 

Author Comment

by:MDIRECT
ID: 24347997
Hi LSM,

Yes there is a one-one relationship between the two joined fields, there can be only one records in MLFC - JOB STATUS CODES for each record in MAINTENANCE LOG FAULT CALL (UNDERLYING).

There is already an index on "Maintainer" field in MAINTENANCE LOG FAULT CALL (UNDERLYING).

 :)
0
 
LVL 84
ID: 24348269
What datatype is the Maintainer field?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24349003
How many records in each table?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 44

Expert Comment

by:GRayL
ID: 24349024
How much memory in your machine?  With large (in width) fields and limited memory, you can be paging to virtual memory on your disk which is very time consuming.  If the tables have many records, (hundreds of thousands or millions of records) that just exacerbates the problem.  Easiest way to fix that is to pop in a few more gigs.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24350518
Hi Ray.
Worth asking, though in the question it says "The machine they are running on in a brand new Quad Core Xeon Machine with 16Gb RAM".
I'm guessing throwing more at it isn't going to do much. ;-)
(Also bearing in mind that the 32bit Office isn't going to even be making real use of all that anyway even on a 64 bit machine. Unless we're talking about holding off until Office 14 hits the shelves ;-)

So, what are the issues at hand.  Some good questions have been asked. I'll reiterate so they don't get missed.
How many records?
What data type is the field Maintainer?

That you're only wanting 16 records returned isn't anything like as important as from how many you're selecting.

A slight factor in general can be "the two B/E tables are in fact in separate MDB files".
That can have a subtle bearing (though unlikely to be particularly noteworthy and you're getting good performance at other times).
(FWIW 154 columns in one table sounds much too many in general).
You say you get great performance without the criteria on Maintainer?
What happens when you run the query without an index on Maintainer?
Ultimately - ShowPlan is your primary port of call here.
You need to know what indexes are at play.
It sounds slightly as if an index on Maintainer is being used when the field is available and that index is a poorer choice than ones on the other fields. (I imagine Scott's wondering if Maintainer is a Memo field? :-)
Anyway - switch on ShowPlan logging and watch the query plan and see what the index use is in each scenario.
0
 
LVL 84
ID: 24350553
<I imagine Scott's wondering if Maintainer is a Memo field? :-)>

Ya found me out <g> ...
0
 

Author Comment

by:MDIRECT
ID: 24352611
Hi All,

Many thanks for your input everyone, in answer to your questions...
1. Maintainer is a Fixed Width Text Field, Length, 15, Allow Zero Length: Yes, Unicode Compression: Yes
2. Each table has approx 42,000 records
3. LPurvis: Regards your question "What happens when you run the query without an index on Maintainer?" - I have removed this index and it runs much more quickly, put it back on again and its back to 2 mins run time again!

Whats going on ? :)
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 24353115
Then leave the index off the maintainer field. It looks as if this would be a poor choice for an index anyway, since that field apparently contains often repeated values.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24353680
i.e. it seems to confirm my suggested theory. :-p
42,000 is a decent amount of rows.  Not a lot but enough to make the engine "think".
The thing that makes me wonder most is your point 1. (Well - apart from your confirming point 3 of course ;-)
>> "Maintainer is a Fixed Width Text Field"
Really?
They're not as common in a Jet backend database as in other platforms (primarily because they can't be created through the designer UI).
I'd say that the index upon that may or may not perform well - depending upon the data you're storing within it.
That removing it causes a performance increase strongly suggests that it's the latter.  The fixed width field gives the same index and more to search.  Do you always store 15 characters in there though?
Anyway - without the ability to hint to Jet on the execution to use, removing the index seems best.
If you want an amusing test - time a query selection against just that row from just that table.  Apply no other criteria.
Having an index on it should be much faster than without.
It's just that there are better indexes to use.
As a further test (in your original query with the index in palce) - concatenate that field value with a ZLS and check the results.
(You're trying to force the engine to ignore that index and use the others - which ideally not having to remove the index, which should still be useful in other scenarios).
Cheers.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now