Many thanks for all the tips.... what I have been doing is creating a view then using this in a query- which think applies the same logic as the qiq? Do you think using views creates additional overhead (excluding the fact I have to create them first)? Darren
> Date: Thu, 16 Aug 2007 14:46:10 -0400> From: [EMAIL PROTECTED]> To: > [email protected]> Subject: Re: [dba-users] trouble with large > MS-Access (mdb) database> > OK - I have to say this:> > I have good news and > bad news.> > First the good news.> > Darrent and Jens I found a way to get > the queries running even faster - as> in MS Access speeds. That's the good > news.> > Here is what you do.> > ARRRGH - this is getting very very > frustrating> > Decided to make one more test before I finished entering this > message -> created a new query that joined 4 tables and then tried to save it > - save> it, save it NOT run it...that was over 5 minutes ago and the> > soffice.binprocess is still pegging the cpu meter..> > But non the less, > whilst it is off doing it's thing let me continue.> > As I said, breaking my > three table query into 2 queries managed to speed the> execution time up - to > approx 11 seconds - not really good, but not so bad> one can't live with it. > But here is the kicker. If I create another query> and this one only includes > the query made in the last step - it doesn't join> anything to it, just wraps > it if you will in another select statement the> execution time drops to under > 2 seconds - every time. I can save it, close> OOo reopen and run it by double > clicking...3 seconds - that had a little> overhead to open the tables - after > that execution time is back under 2> seconds and that is acceptable in > anyones book. Also, I tried that trick> with all three Base files and it > works for all of them.> > But here is the bad news.> > Using either of the > Base files connecting via OLE db, that is the MS Access> connection and the > ADO connection.> > When I added the qiq query into a new query not all of the > columns are> displayed in the desgner. They are all there in the result set, > but not in> the designer.> > So in my case when I tried to go one level > deeper and create a qiq query> that would let me link in the products table I > could not, because the added> sub_query did not offer the ProductID FK to > link to.> > If I try this in the Base file that uses ODBC, then the problem > does not> manifest itself.> > A second problem that is most likely related to > the first ( and again only> happening in the OLE DB connections ) is that the > data type for the foreign> key field is changed. It appears to be changing > from a long integer to a> string. ( sorry, can't double check that right now, > because OOo is still> pegging the CPU trying to save that final query....can > I swear on this list> .. *chuckle* . . and as I said with the ODBC connection > the data type> returned is correct.> > I can't say for sure, but I wouldn't > be surprised if this has something to> do with this column being a lookup > field in the MDB file.> > Anyway - I am going to walk away for a while, let > OOo finish saving, and> then try to reproduce the whole thing again. If I > find the same problems> I'll check Issue tracker and enter a new issue if > needed.> > But Darren and Jens try using the Query-in-Query feature and see > if it> doesn't solve your problems - then - try to wrap the working query one > more> time in a new query and see if the performance doesn't increase again - > and> good luck.> > On 8/16/07, Andrew Jensen <[EMAIL PROTECTED]> wrote:> >> > > Alright here is the first observation> >> > If I use QiQ to control the > process I can fix the problem> >> > First I created a query that joins > Customers and Oreders> > Saved it as Customer_Orders> >> > Then created a > second query that uses Customer_Oders and joins> > OrderDetails to this.> > > Selected all fields from both objects and GREAT - the query runs in a just> > > a few seconds> >> > On 8/16/07, Andrew Jensen < [EMAIL PROTECTED]> wrote:> > > >> > > Oh yes - XP SP 2 all updates as of yesterdays release from MS> > > OOo > 2.3m_1> > >> > > On 8/16/07, Andrew Jensen < [EMAIL PROTECTED] > wrote:> > > > >> > > > You don't need an 80 meg database.> > > >> > > > You can see an > example of this with any of the example databases> > > > supplied by > microsoft.> > > >> > > > The problem seems to be when you link in the third > or greater table.> > > >> > > > For instance - using the file QrySampl.mdb ( > available from the MS web> > > > site - or ask and I;ll send it along it is > only ~670K ) I have done the> > > > folloiwng:> > > >> > > > Connected using > MS Access> > > > Connected using ADO exclusive mode> > > > Connected using > ODBC> > > >> > > > I am using the following three tables only> > > > > Customers ( 91 records )> > > > Orders ( 830 records )> > > > OrderDetails ( > 2155 records )> > > >> > > > Now if I create a query that joins Customers to > Orders - no problem> > > > But - add OrderDetails to the query and BAM takes > about 2 minutes to> > > > run and the CPU is at 100% for the whole time.> > > > > ADO exclusive is slightly faster - but not enough to make it usable> > > > > ODBC is just the same> > > >> > > > anyway - I am not done just yet with > this..so I will put up more as I> > > > have it.> > > >> > > >> > > >> > > > > On 8/16/07, Regina Henschel <[EMAIL PROTECTED]> wrote:> > > > >> > > > > Hi > Jens,> > > > >> > > > > Jens Ansorg schrieb:> > > > > > hi,> > > > > >> > > > > > > I try to use OOO Base 2.2.1 as a front end to a MS-Access mdb> > > > > > file.> > > > > >> > > > > > The database file is about 80Mb in size.> > > > > > >> > > > > > I sucessfully created the link to the database using the> > > > > > "Microsoft> > > > > > Access" Connector.> > > > >> > > > > Do you have > tried an ADO connection too?> > > > >> > > > > >> > > > > > I can see all > contained tables and also access all data within.> > > > > >> > > > > > Ok.> > > > > > >> > > > > > Trouble starts when I try to create Queries across more > than one> > > > > table.> > > > >> > > > > > No matter whether I enter the > SQL manually or use the wizard or> > > > > the> > > > > > design view: when I > try to view the query result - and even when I> > > > > just> > > > > > try > to save the query without viewing the result first - OOO Base> > > > > > starts> > > > > > to use 100% CPU and that's it ... forever until I kill it > :(> > > > >> > > > > Can you please post an example SQL statement?> > > > > > >> > > > > >> > > > > >> > > > > > I tried to do queries across several > tables with a small sample> > > > > access> > > > > > database and this works > so I suspect that OOO has trouble dealing> > > > > with> > > > > > the huge > amount of data here?> > > > >> > > > > I would like to try it on my PC, but > how to get a 80MB access> > > > > database file?> > > > >> > > > > kind > regards> > > > > Regina> > > > >> > > > >> > > > > > ---------------------------------------------------------------------> > > > > > To unsubscribe, e-mail: [EMAIL PROTECTED]> > > > > For additional commands, > e-mail: [EMAIL PROTECTED]> > > > >> > > > >> > > >> > >> > _________________________________________________________________ Feel like a local wherever you go with BackOfMyHand.com http://www.backofmyhand.com
