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] > > > > > > > > > > > > > >
