[AccessD] Help with Query

How about instead of the IN and with the Distict in the select
statement, what if you created a temp table of those job numbers with
a make table query over the results from the first query with group
by. Then I would set the job number as a KEY field too. Then run you
second query with a join between the job number in your temp table and
on the source system. If this proves to be satisfactory performance
wise you might want to change the make table to an append query and
run a delete query on the temp file first to clean it out. That way
the key field stays there.

I do this kind of stuff and have had good results but am using Oracle
database tables as source data.

GK

On 2/4/09, Rojas, Joe <joe.rojas at symmetrynb.com> wrote:
> 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
>


-- 
Gary Kjos
garykjos at gmail.com