Apache OpenOffice (AOO) Bugzilla – Issue 73245
Base 2.1 odbc access to as/400 slower than in 2.0.4 (one SQLTables/SQLColumns call for *every* table)
Last modified: 2009-07-20 14:53:17 UTC
I updated from 2.0.4 to 2.1 and found that the first access to as/400(iseries) databases is much slower than before. OS/400 release is V4R5 ODBC driver is IBM iseries Acces v.10.00.05.00 It happens on different machines and it is repeatable. The reason seems to be that on accessing a library not only SYSTABLES, but also SYSCOLUMNS are read from the system catalog. There are 1001 relevant records in SYSTABLES and 24838 in SYSCOLUMNS. I checked the job protocol (QZDASOINIT job) and found that it had to allocate 15 x 16MB additional storage and the ended with an error message. I don't know if this is a problem of os/400 or the ODBC driver, but it definitly happens with 2.1 and not with 2.0.4. So does anybody know what has been changed in the Base module? Or how to solve the problem? Tilmann Brenk
clu->fs: may you have a look at that
Citing myself from http://dba.openoffice.org/servlets/ReadMsg?list=dev&msgNo=2448: > I updated from 2.0.4 to 2.1 and found that the first access to as/400 > (iseries) databases is much slower than before. With "first access", I assume you mean the first connection to the database? ... Do you have changes to find out what information from SYSCOLUMNS is accessed? Alternatively/Additionally, could you switch on ODBC tracing, and search the logs for suspicious (read: happening way too often) calls related to columns? Can you compare the ODBC logs from 2.0.4 and 2.1? ... Sounds like a side effect of some other change, however, I cannot imagine which one. All changes I can think of were already in 2.0.4 or before. Please try to find more information about what exactly is accessed in SYSCOLUMNS, as described above. This might help to track when and were this happens, and why.
no respond
-> close do not hesitate to reopen this issue if it still exist in a current version and you have anything to add to make it better repro
I have now tested this issue with 2.0.4 and 2.2.1 and it still exists. I have created odbc traces for small database with only one table and 3 logical files (indexes/views) and I will attach them to this issue. You will see that in 2.0.4 'SQLColumnsW' is not called, but 8 times in 2.2.1. This is not much, but shows, that 2.2.1 (or 2.1) does access the columns when connecting to the database and that 2.0.4 does not. In our production database 'SQLColumnsW' was call 1046 times. All this happened when I just clicked on 'Tables' after opening the testoobase.odb file.
Created attachment 47724 [details] 2 odbc trace logs (2.0.4 and 2.2.1)
will have a look at this
side note: SQLColumnsW is called 4 times, but there's two entries in the log for every call, one for ENTERing and one for EXITing the call.
Okay, seems we do an SQLColumn for every table in the database, right? (You mention three "logical files", but the logs suggests there are tables named AGDBPRL0, AGDBPRL1, AGDBPRL2 and AGDBPR.) Besides this, it seems we do a SQLTablesW for each and every table in the DB, followed by an SQLColumnsW. I don't know which of those is worse, but can easily imagine that this adds to quite some time when the number of tables increases. Tilman, just to get an impression how this adds in your particular scenario: do you have numbers to quantify the "much slower than before"? I.e., how long does it take in 2.0.4, and how long in 2.1?
1) AGDBPR is the table (physical file in as/400 terminology) AGDBPRL0 to L2 are indexes/views (logical files). Logical files can be accessed like views (you can do a select on them) and they are used as indexes by the system. If you create views or indexes using sql statements, the system will create logical files. 2) I did a performance comparison. We have a similar database (240 tables, 804 logical files) on two machines, one development machine with OS/400 V4.4 and a newer, faster production machine with OS/400 5.3. I have OpenOffice 2.0.4 on one pc and 2.2.1 on another. I just open the .odb file, click on 'Tables' and stop the time until I can see the tables in the bottom subwindow. development production OO 2.0.4 5 sec 1 sec OO 2.2.1 4 min, 15 sec 27 sec Let me know if you need more info.
I just checked this issue with OOo 2.3.0. No change, as slow as before.
Okay, having seen another log from another user, where we also do one SQLTables and one SQLColumns for each table in the DB (and there were 756 of them), I think we should at least eliminate this tremendous amount of superfluous calls. I strongly suppose this will fix the problem, but cannot know until I get your feedback on the fix.
fixed in CWS dba24c find more information about this CWS, like when it is available in the master builds, in EIS, the Environment Information System: http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=SRC680%2Fdba24c
fs->tbrenk : If I would provide you a build of the child workspace where (I hope) this issue is fixed, would you be willing to test it and give me feedback?
tbrenk->fs yes, I'd be glad to test it.
targeting to 2.4, since the fix is part of a CWS aiming for this release
fs->tbrenk: Please find a snapshot of the CWS where this is fixed at http://ooo.services.openoffice.org/pub/OpenOffice.org/cws/upload/dba24c. This version will install in parallel to existing OOo releases (but will conflict with intermediate developer snapshots, in case you have installed them). If you could verify that this version solves your problem, I'd be most grateful. Thanks in advance. Please be aware that this is a version which is more advanced than the recent 2.3 release, which means there's some more features, and probably some more bugs therein - don't use it for production, please.
I tried to test the new build, but got an error: Could not load the program library ODBC32.DLL or it is corrupted. The Data source selection is not available. This happens not only for the selection, but also if I enter the data source manually. How can I fix this?
Argh. This is an issue introduced by somebody else on the main trunk, and the CWS inherited it. It's not fixed, yet, as far as I know. Can you try simply opening the .odb created with 2.3? You should not need the data source administration at all. Just open the existing database document on the same system, and see how long connecting takes. If you do not want to use the dev snapshot on the same system as 2.3, then you need to start the ODBC data source administration via the control panel. Then, in Base, just enter the ODBC data source name (without using the "Browse" button).
I tried both to open the existing .odb and enter the data source manually, but I always get some error with ODBC32.DLL in it.
Ah, yes, the driver itself (not only the ODBC UI) has this error I mentioned. Sigh. I am going to attach a fixed driver.
Created attachment 49525 [details] fix for the ODBC connection problem in the developer snapshot
fs->tbrenk: please extract the attached zip into your installation's "program" directory, it will overwrite the existing odbc2.dll. After this, ODBC connections should work, again.
I copied the odbc2.dll to the program subdirectory, but this did not help. Same error messages as before.
Ehm - sure? I did the same thing here, and it worked. Hmm. Try copying the odbc32.dll from your windows\system32 directory to the "program" folder. (Not sure if odbc32.dll is self-contained to allow this, but it's worth a try.)
Congratulations! After I copied odbc32.dll to the program subdirectory, I was able to test the issue and it's fine. Times are like they were in 2.0.4. Thank you very much.
Okay, that's great to hear. Thanks for trying this out.
This issue is closed automatically and wasn't rechecked in a current version of OOo. The fixed issue should be integrated in OOo since more than half a year. If you think this issue isn't fixed in a current version (OOo 3.1), please reopen it and change the field 'Target Milestone' accordingly. If you want to download a current version of OOo => http://download.openoffice.org/index.html If you want to know more about the handling of fixed/verified issues => http://wiki.services.openoffice.org/wiki/Handle_fixed_verified_issues