SQL Statement runs slow

[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.

Thanks for any help you can give me

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

http://www.davidpkuhn.com

 

[Golom]

Its slow because the EXISTS clause is a correlated sub query that is run for every record in the outer query. You may get better results if you try running it as a join

INSERT INTO FurnaceUsage ( JobNum, Process, ProcessNum, Temperature, CustCode, MatCode, Status, ProcessID, Hardness, JobComms )

SELECT Trim(P.job) AS jobnum, Trim(P.description) AS process, Trim(P.SeqNo) AS ProcessNum, Trim(P.Temperature) AS Temperature, Trim(J.CustCode) AS CustCode, Trim(J.MatCode) AS MatCode, 'WAITING' AS Status, Trim(P.ID) AS ProcessID, Trim(J.rc) AS Hardness, C.COMMENTS AS JobComms 

FROM ((Jobs J 
INNER JOIN processes P ON (J.job = P.job AND J.Status=P.Description)) 
LEFT JOIN JobComments C ON (J.Comments = CStr(C.ID) ))
LEFT JOIN  FurnaceUsage F ON F.JobNum=P.Job 
AND F.ProcessNum=P.SeqNo 
AND J.Custcode=F.custcode and J.matcode=F.matcode

WHERE (((P.description)='TEMPERING' or trim(P.description)='CYROGENIC TREATING -300') 

AND ((P.WhereRun)='" & GlobalWHERERUN & "') 

AND F.JobNum IS NULL

 

[dpk136]

This gives my program an error

Run-time error '-2147467259 (80004005)':

Query input must contain at least one table or query.

When i put this into access i get the error

Join expression not supported.

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

http://www.davidpkuhn.com

 

[dpk136]

do you not have an answer for this?

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

http://www.davidpkuhn.com