[AccessD] Help with Query

Thanks Mark!
Your suggestion leaded me to the answer. Not sure why I didn't just
filter in the query as opposed to linking to a local table.
Forest from the trees syndrome I guess.

Joe

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Wednesday, February 04, 2009 1:49 PM
To: accessd at databaseadvisors.com
Subject: Re: [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(tm): Keep your life in sync. 
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t1_allup_explore_022009
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com