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]='1stA
id'" 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!