Mark A Matte
markamatte at hotmail.com
Tue Jan 25 11:18:57 CST 2005
Tom, Thanks for the feedback. I don't have access to build the tables on the informix box. The best speed I seem to have found with A97 and ODBC connections is using an append (but not a make-table) query and loading the data into an UN-indexed table. Any additional Ideas/tips would be greatly appreciated. Thanks, Mark >From: Tom Bolton <tom.bolton at donnslaw.co.uk> >Reply-To: Access Developers discussion and problem >solving<accessd at databaseadvisors.com> >To: "'Access Developers discussion and problem >solving'"<accessd at databaseadvisors.com> >Subject: RE: [AccessD] Data from Informix to Access - dirty reads? >Date: Tue, 25 Jan 2005 15:48:29 -0000 > >Hi Mark > >As outlined in Roz's original post (I'm the developer on her team building >this Informix/Access monster) there aren't really any other methods than >either using temp tables in your FE (as Jim helpfully suggested earlier >today, and as you're doing by the sounds of it) or the method I'm using. >No >disrespect to Jim's ideas (check his link out, lots of useful info) but it >suits my purposes better to use ADO to build tables on the Informix box, >swap the data I need into them, and pull it back to the FE using >pass-through queries. > >Be aware though, Informix temp. tables only persist as long as an Informix >session, i.e. as long as you have an ODBC connection open - otherwise you >have to explicitly build the tables in order to persist them between >sessions. For some reason best known to someone, there's no SELECT INTO as >with every other database server on the planet. > >HTH, if you need any more info I have it by the truckload ;-) >Tom > > >-----Original Message----- >From: Mark A Matte [mailto:markamatte at hotmail.com] >Sent: 25 January 2005 14:49 >To: accessd at databaseadvisors.com >Subject: RE: [AccessD] Data from Informix to Access - dirty reads? > >Hello All, > >Every week I do pulls from Informix to A97 using ODBC. 1 of my pulls now >is > >around 1 million records. I use temp tables and append queries. This can >take a few hours. Some of the replies in this thread imply there is a much >'better/faster' way. Without changing versions of Access...is there a way >to 'tweak'/'speed up' this data pull? > >Thanks, > >Mark > > >From: Roz Clarke <roz.clarke at donnslaw.co.uk> > >Reply-To: Access Developers discussion and problem > >solving<accessd at databaseadvisors.com> > >To: "'Access Developers discussion and problem > >solving'"<accessd at databaseadvisors.com> > >Subject: RE: [AccessD] Data from Informix to Access - dirty reads? > >Date: Tue, 25 Jan 2005 09:52:19 -0000 > > > >Informix doesn't support XML afaik >< > > > >We did think about this as a way around connection issues for SQL Server > >when we thought that we would have our main db replicating to SQL Server > >rather than Informix - our recordsets are going to be up to about 50k >rows > >I > >think. I don't know how XML would perform with that volume of data but >it's > >academic for now. > > > >Roz > > > >-----Original Message----- > >From: John W. Colby [mailto:jwcolby at colbyconsulting.com] > >Sent: 24 January 2005 20:58 > >To: 'Access Developers discussion and problem solving' > >Subject: RE: [AccessD] Data from Informix to Access - dirty reads? > > > > > >How much data? Can it export it to an XML file in a location where your >fe > >can use it? XML has a high overhead but can be useful in cases like >this. > > > >John W. Colby > >www.ColbyConsulting.com > > > >Contribute your unused CPU cycles to a good cause: > >http://folding.stanford.edu/ > > > >-----Original Message----- > >From: accessd-bounces at databaseadvisors.com > >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Roz Clarke > >Sent: Monday, January 24, 2005 10:23 AM > >To: 'Access Developers discussion and problem solving' > >Subject: [AccessD] Data from Informix to Access - dirty reads? > > > > > >Hi all > > > > > >We have a bit of a problem with getting data out of our Informix server > >since we recently turned on transaction logging for replication. When > >extracting data with an Access XP MDE using pass-through queries, Access > >locks entire tables in the Informix database, which causes transaction > >errors and makes the Informix database scarily unstable. > > > >Our Informix suppliers told us that the way to avoid these locking issues > >was to set the connection to 'dirty read' before running the SQL. >However, > >Access cannot execute 2 statements in a pass-through query and it does >not > >hold the connection open between the execution of 1 statement and the >next. > >Thus when the query is processed the 'dirty read' setting is no longer in > >effect. > > > >We have been racking our brains trying to come up with a workaround. Some > >further options that we have considered are: > > > >* Stick the data in a temp table in Informix. This is no good because > >the temp table is destroyed automatically when the connection is closed >and > >there's no way to make it persist long enough to bind it to a report. > >* Use a view in Informix. This is no good because views in Informix > >cannot be set read-only. > >* Create a permanent table in Informix and destroy it when finished > >with it. This is far from ideal because Informix does not support SELECT > >INTO and therefore a table would have to be explicitly constructed with > >names columns etc. We really need the system to be flexible so that the > >queries can be easily changed. > >* Use an ADO recordset. This is a PITA because you cannot bind a > >report to a recordset in an MDE and we cannot build the report on the fly >- > >we are a Terminal Services site so we will have up to 20 users in one FE. > >* Create a table in Access and destroy it when finished with it. Can't > >think of a better way to induce bloating... > > > >Has anyone faced this kind of problem before? Any bright ideas? Our > >foreheads are starting to bleed... > > > >TIA > > > >Roz (and Tom) > > > > > >-- > >AccessD mailing list > >AccessD at databaseadvisors.com > >http://databaseadvisors.com/mailman/listinfo/accessd > >Website: http://www.databaseadvisors.com > > > >The contents of this message and any attachments are the property of >Donns > >Solicitors > >and are intended for the confidential use of the named recipient only. > >They may be legally > > privileged and should not be communicated to, or relied upon, by any > >other party without > >our written consent. If you are not the addressee, please notify us > >immediately so that we > >can make arrangements for its return. You should not show this e-mail to > >any person or > > take copies as you may be committing a criminal or civil offence for > >which you may be > > liable. The statement and opinions expressed in this e-mail message >are > >those of the > >writer, and do not necessarily represent that of Donns Solicitors. > >Although any files attached > > to this e-mail will have been checked with virus protection software > >prior to transmission, > >you should carry out your own virus check before opening any attachment. > >Donns Solicitors does not accept any liability for any damage or loss >which > > >may be caused > >by software viruses... > >-- > >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 > >The contents of this message and any attachments are the property of Donns >Solicitors >and are intended for the confidential use of the named recipient only. >They may be legally > privileged and should not be communicated to, or relied upon, by any >other party without >our written consent. If you are not the addressee, please notify us >immediately so that we >can make arrangements for its return. You should not show this e-mail to >any person or > take copies as you may be committing a criminal or civil offence for >which you may be > liable. The statement and opinions expressed in this e-mail message are >those of the >writer, and do not necessarily represent that of Donns Solicitors. >Although any files attached > to this e-mail will have been checked with virus protection software >prior to transmission, >you should carry out your own virus check before opening any attachment. >Donns Solicitors does not accept any liability for any damage or loss which >may be caused >by software viruses... >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com