[AccessD] Help with Query

Just a thought but is the data in the SQL server table indexed? Also maybe the DBA of the SQL server data could build you view of just the data you need. That can be a lot quicker than joining to the entire table.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, February 05, 2009 1:31 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com