[AccessD] Help with Query

Does anyone know what might be behind this slowness?  I have an Access application that is required 
to link to data from SQL Server, and it is indeed very slow, a source of annoyance to my client.  I 
do have to filter the data, currently using an inner join.  I really do not want to just copy the 
data into a local MDB table though I will if that will help (hadn't thought much about doing that 
till now).

John W. Colby
www.ColbyConsulting.com


Rojas, Joe wrote:
> 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