[AccessD] fastest way to execute sql

Elam, Debbie DElam at jenkens.com
Thu Jul 13 15:47:59 CDT 2006


Memo/blob searches like this are tough without full text indexing.  I do not
know if Informix has that available, but it would be worth a look.  Of
course there is no way to take advantage of this without using some sort of
pass through query.  I am in an almost pure SQL environment and that is how
I handle it.  I am not sure what may be applied to your situation.

Debbie

-----Original Message-----
From: Mark A Matte [mailto:markamatte at hotmail.com] 
Sent: Thursday, July 13, 2006 3:33 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] fastest way to execute sql


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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
- JENKENS & GILCHRIST E-MAIL NOTICE - This transmission may be: (1) subject
to the Attorney-Client Privilege, (2) an attorney work product, or (3)
strictly confidential. If you are not the intended recipient of this
message, you may not disclose, print, copy or disseminate this information.
If you have received this in error, please reply and notify the sender
(only) and delete the message. Unauthorized interception of this e-mail is a
violation of federal criminal law. 
This communication does not reflect an intention by the sender or the
sender's client or principal to conduct a transaction or make any agreement
by electronic means.  Nothing contained in this message or in any attachment
shall satisfy the requirements for a writing, and nothing contained herein
shall constitute a contract or electronic signature under the Electronic
Signatures in Global and National Commerce Act, any version of the Uniform
Electronic Transactions Act or any other statute governing electronic
transactions.



More information about the AccessD mailing list