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.