[AccessD] Help with Query

I have the same issue when pulling data via ODBC...I have found it is much quicker to NOT join a local table to a linked ODBC...just my opinion.  Any reason you can't do the filtering in one SQL statement.(see sample below)
 
Good Luck,
 
Mark A. Matte
 
 
SELECT PUB_JobOper.Company, PUB_JobOper.JobNum, PUB_JobOper.OprSeq,
 PUB_JobOper.OpCode, PUB_JobOper.SubContract, PUB_JobOper.ActBurCost,
 PUB_JobOper.ActLabCost
 FROM PUB_JobOper,PUB_LaborDtl
 WHERE PUB_JobOper.JobNum=PUB_LaborDtl.JobNum
 and PUB_JobOper.Company = 'SNB'
 and ((PUB_LaborDtl.ClockInDate) Between [dteStart] And [dteEnd]);
 

----------------------------------------
> Date: Wed, 4 Feb 2009 12:06:09 -0500
> From: joe.rojas at symmetrynb.com
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Help with Query
>
> Hi All,
>
> I am trying to pull data into Access from our ERP system that runs off
> of a Progress OpenEdge database.
> The problem I'm having is that the query takes a long time to
> run...around 20 mins.
> I think the problem is related to how I've designed the query and I'm
> hoping someone can point out my error.
>
> I'm connecting to OpenEdge via ODBC. I've linked to the tables I need in
> Access.
> I've pasted the SQL for the queries below.
> The first query runs pretty quick. It pulles data from one of the tables
> based on date range and inserts the records into a local table in
> Access.
> The second query is the one that has the problem. I created a query in
> Access that pulls data from another table in OpenEdge that has a WHERE
> clause that uses the IN statement. The IN statement uses a sub query to
> get the distinct job numbers from the local table that was created in
> the first query.
> The sub query could return many job numbers depending on the span in the
> date range for the first query.
> By many, I mean it could be 10, 100, 1000, or more.
>
> My guess is that the sub query could be the problem.
> When I run the query, the CPU spikes to 100% for MSACESS but the network
> traffic indicates that it's not actually pulling data the whole time.
>
> Is there an alternate method to achieving my goal?
>
> Tables in my query that have a 'tbl' prefix are local tables. Tables
> with a 'PUB' prefix are link ODBC tables.
>
> Query 1 (runs in seconds)
> INSERT INTO tblLaborDtl ( Company, EmployeeNum, JobNum, OprSeq,
> ResourceGrpID, ScrapQty, ResourceID, ClockInDate, LaborDtlSeq,
> ScrapReasonCode )
> SELECT PUB_LaborDtl.Company, PUB_LaborDtl.EmployeeNum,
> PUB_LaborDtl.JobNum, PUB_LaborDtl.OprSeq, PUB_LaborDtl.ResourceGrpID,
> PUB_LaborDtl.ScrapQty, PUB_LaborDtl.ResourceID,
> PUB_LaborDtl.ClockInDate, PUB_LaborDtl.LaborDtlSeq,
> PUB_LaborDtl.ScrapReasonCode
> FROM PUB_LaborDtl
> WHERE (((PUB_LaborDtl.ClockInDate) Between [dteStart] And [dteEnd]));
>
> Query 2 (take 20 mins)
> INSERT INTO tblJobOper ( Company, JobNum, OprSeq, OpCode, SubContract,
> ActBurCost, ActLabCost )
> SELECT PUB_JobOper.Company, PUB_JobOper.JobNum, PUB_JobOper.OprSeq,
> PUB_JobOper.OpCode, PUB_JobOper.SubContract, PUB_JobOper.ActBurCost,
> PUB_JobOper.ActLabCost
> FROM PUB_JobOper
> WHERE PUB_JobOper.Company = 'SNB' AND PUB_JobOper.JobNum In (SELECT
> DISTINCT tblLaborDtl.JobNum FROM tblLaborDtl);
>
> Joe
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
_________________________________________________________________
Windows Live™: Keep your life in sync. 
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t1_allup_explore_022009