[AccessD] Help with Query

Found this:
"Heterogeneous joins between local tables and remote tables, or between remote tables in different ODBC data sources. Joins between small local tables and large remote tables, where the join column is indexed, may result in a remote index join. In a remote index join, one query for each row in the local table is sent to the server, and only the joining rows are returned."
 
From:  http://support.microsoft.com/kb/286222
 
Not sure that it solves anything.
 
Any ideas?
 
Mark> Date: Thu, 5 Feb 2009 14:31:29 -0500> From: jwcolby at colbyconsulting.com> To: accessd at databaseadvisors.com> 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
_________________________________________________________________
Windows Live™: E-mail. Chat. Share. Get more ways to connect. 
http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_allup_howitworks_022009