[AccessD] fastest way to execute sql

Mark A Matte markamatte at hotmail.com
Thu Jul 13 15:32:59 CDT 2006


Robert,

Thanks for the feedback...I have never used pass-throughs...here is my 
scenario.  Access db connected to Informix via ODBC.  I need to do some 
fuzzy/wildcard searches against a number of  memo/blob fields.  I have tried 
to do these types of searches in the past on the linked tables with no 
success(time outs or just never finishes).  I typically pull ALL(1.2 mil) 
records from the needed table and then do the fuzzy/wildcard searches on 
local tables.  This has been the only way I got the info needed.  In this 
scenario, would your suggestion still apply...and if so, do you mean create 
my fuzzy/wildcard searches as a pass-through...and then use the pass-through 
as the source in another query to append to a local table?

Thanks,

Mark A. Matte


>From: "Robert L. Stewart" <rl_stewart at highstream.net>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: accessd at databaseadvisors.com
>CC: markamatte at hotmail.com
>Subject: Re: [AccessD] fastest way to execute sql
>Date: Thu, 13 Jul 2006 14:10:30 -0400
>
>Mark,
>
>The fastest way is to have your server execute it using pass through SQL.
>You should NEVER pull millions of rows into Access for processing.  That is
>crazy.  It is not designed to do that.
>
>Once you have the pass through query defined, you can use it as a data 
>source to
>append data into a table on the Access side, but don't even think about 
>doing
>millions of rows.
>
>Robert L. Stewart
>The Dyson Group International
>Software for the Non-profit Enterprise
>Expanding your Sphere of Knowledge
>
>
>Quoting accessd-request at databaseadvisors.com:
>
> > Date: Thu, 13 Jul 2006 15:15:26 +0000
> > From: "Mark A Matte" <markamatte at hotmail.com>
> > Subject: [AccessD] (no subject)
> > To: accessd at databaseadvisors.com
> > Message-ID: <BAY121-F8F0B3D7A50E6847BC8D9FD26E0 at phx.gbl>
> > Content-Type: text/plain; format=flowed
> >
> > Hello All,
> >
> > In A97 or A2K is there an speed advantage in using different methods of
> > executing SQL...runsql vs openquery vs CurrentDb.Execute vs 
>qdfCurr.Execute
> > ....or any other method of getting the data.  I'm pulling millions of 
>rows
> > from informix via odbc and appending within Access...the whole point of 
>this
> > is I need to do some wildcard searches on a number of memo/blob 
>fields...and
> > need the records local or it times out.
> >
> > So...bottom line...whats the fastest way to get data from informix via 
>odbc
> > into an access table?
> >
> > Thanks again,
> >
> > Mark A. Matte
> >
> >
> >
> >
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com





More information about the AccessD mailing list