Run Faster?

[dpk136]

How can i get this to run faster, i'm accessing a access database and running this.
INSERT INTO FurnaceUsage ( JobNum, Process, ProcessNum, Temperature, CustCode, MatCode, Status, ProcessID, Hardness, JobComms )SELECT Trim(Processes.job) AS jobnum, Trim(processes.description) AS process, Trim(processes.SeqNo) AS ProcessNum, Trim(processes.Temperature) AS Temperature, Trim(Jobs.CustCode) AS CustCode, Trim(Jobs.MatCode) AS MatCode, 'WAITING' AS Status, Trim(processes.ID) AS ProcessID, Trim(JOBS.rc) AS Hardness, JobComments.COMMENTS AS JobComms FROM (Jobs INNER JOIN processes ON (Jobs.job = processes.job AND Jobs.Status=Processes.Description)) LEFT JOIN JobComments ON (JOBS.Comments = CStr(JobComments.ID)) WHERE (((processes.description)='TEMPERING' or trim(processes.description)='CYROGENIC TREATING -300') AND ((processes.WhereRun)='" & GlobalWHERERUN & "') AND ((Exists (Select JobNum FROM FurnaceUsage WHERE FurnaceUsage.JobNum=Processes.Job AND FurnaceUsage.ProcessNum=Processes.SeqNo AND Jobs.Custcode=furnaceusage.custcode and jobs.matcode=furnaceusage.matcode))=False));

It grabs all rows from one table that are not in a 2nd table and places them in the 2nd table. For some reason this takes a ton of time.

Please help!



David Kuhn
------------------

http://www.davidpkuhn.com

 

[Zathras]

Since no one else seems interested in this, I'll take a stab.

The WHERE clause is using a function. This prevents the compiler from being able to use an index (assuming you have an index on the DESCRIPTION column of the PROCESSES table).

You should get some improvement if you clean up your data so that the descriptions are trimmed in the table. Then your statement can look like this:
[tt]
INSERT INTO ... SELECT ... FROM ... WHERE processes.description in ('TEMPERING','CYROGENIC TREATING -300') ... AND ...
[/tt]
Create an index on the FURNACEUSAGE table that consists of the four columns you are referencing: JOBNUM, PROCESSNUM, CUSTCODE and MATCODE. This should speed up your EXISTS test.

Try creating an index on the JOBS table that has both JOB and STATUS columns in it to facilitate a match on the INNER JOIN.

The LEFT JOIN ON JobComments is not able to use an index efficiently because you have a function applied to the ID column. Better to de-normalize the JobComments table to have a char version of the ID field to use for joining (and create an index on it). Alternatively, change the Comments data type in the JOBS table to match the data type of the ID column in the JobComments table. Either way, by eliminating the CStr( ) the compiler will be able to use an index on the ID column (assuming one exists).

Remove the Trim( ) functions unless absolutely necessary. Clean up the data instead. This won't have much effect but should help a bit.