Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||Table Data View + ODBC issues select * from table to get only key data|
|Component:||code||Assignee:||AOO issues mailing list <issues>|
|Status:||REOPENED ---||QA Contact:|
|Priority:||P4||CC:||drewjensen.inbox, issues, ocke.janssen|
|Issue Type:||DEFECT||Latest Confirmation in:||---|
Description ludob 2009-06-09 18:40:48 UTC
Opening a table for viewing issues a few "select * from table where 0=1" to get the table metadata. One should be fine but, unless the underlying database has a long "ping" (over the net), the extra traffic is not problematic. What is a big problem is the "select * from table" followed by as many "select * from table where id='?'" statements as there are lines to display. The first select * is used to get only the primary key or other index. A "select id[,id2,...] from table" should do if one does only a SQLGetData on the key field(s). Or..., since you have the select *, use the data that you just retrieved and forget about the select * where id=?. But not both. Imagine the extra traffic when the table contains blobs: they are not displayed in the Table view and are retrieved from the database twice... Things get even worse when the ODBC driver, for one reason or another, gets all the data into the client's memory when SQLExecute is called on the select * for a large table. Here is how ms access is doing it: "Select id from table" followed by a series of "select field1,field2,... from table where id=? or id=? or [total of 10 ids] or id=?" to get ten rows at a time. The select where 0=1 is done when you create the link, which is OK for how ms access uses linked tables but is in my opinion a minus for ms access. Blobs, Clobs, memos, etc are only retrieved, using a separate select, when the field is visible in the view. Time to first screen and scrolling speed is much faster than for OOO. Overall, the way how OOO handles ODBC databases and what you can do with them is great compared to ms Access. Larger tables, especially on networked databases, can however be a show stopper. Replacing the select * by a select ids should already be a big improvement. Ludo Brands
Comment 1 ludob 2009-06-10 09:27:43 UTC
same behavior in OOO310m11
Comment 5 ocke.janssen 2010-12-02 12:17:22 UTC
Comment 6 ocke.janssen 2010-12-02 12:25:18 UTC
Fixed in cws dba34c. Now SELECT * from xx where key = ? will only be called when the view jumps from last to first row.
Comment 7 ludob 2010-12-03 16:55:58 UTC
I had a look at the changeset for "dba34c: #i102625# only fetch rows when the view moves outside the scope of the rowset window ". Apparently we are talking about different things... When opening a table in a table view, one or more SELECT * FROM table WHERE 1=0 is executed. Then a SELECT * FROM table. Then, to fill the form, a number of SELECT * from xx where key = ?. You addressed a problem with the last part while the problem reported is with the second part (the get the complete table). The complete table needs to be traversed to get the key values but there is no reason to get all the data from the table to use only the key values to re-get everything again one row at the time. I did some debugging: here is the stacktrace for the OPreparedStatement::executeQuery that does the select * from xx: odbcbasemi!connectivity::odbc::OPreparedStatement::executeQuery+0x94 [g:\ooo\dev300_m92\connectivity\source\drivers\odbcbase\opreparedstatement.cxx @ 293] dbami!dbaccess::OPreparedStatement::executeQuery+0xde [g:\ooo\dev300_m92\dbaccess\source\core\api\preparedstatement.cxx @ 227] dbami!dbaccess::ORowSet::impl_prepareAndExecute_throw+0x4ed [g:\ooo\dev300_m92\dbaccess\source\core\api\rowset.cxx @ 1681] dbami!dbaccess::ORowSet::execute_NoApprove_NoNewConn+0xe0 [g:\ooo\dev300_m92\dbaccess\source\core\api\rowset.cxx @ 1805] dbami!dbaccess::ORowSet::execute+0x17f [g:\ooo\dev300_m92\dbaccess\source\core\api\rowset.cxx @ 1588] frmmi!frm::ODatabaseForm::executeRowSet+0x35b [g:\ooo\dev300_m92\forms\source\component\databaseform.cxx @ 1261] frmmi!frm::ODatabaseForm::load_impl+0x388 [g:\ooo\dev300_m92\forms\source\component\databaseform.cxx @ 2919] frmmi!frm::ODatabaseForm::load+0x4f [g:\ooo\dev300_m92\forms\source\component\databaseform.cxx @ 2702] dbumi!dbaui::SbaXDataBrowserController::reloadForm+0xf7 [g:\ooo\dev300_m92\dbaccess\source\ui\browser\brwctrlr.cxx @ 733] dbumi!dbaui::SbaTableQueryBrowser::implLoadAnything+0x5ec [g:\ooo\dev300_m92\dbaccess\source\ui\browser\unodatbr.cxx @ 2333] dbumi!dbaui::SbaTableQueryBrowser::implSelect+0x1bfb [g:\ooo\dev300_m92\dbaccess\source\ui\browser\unodatbr.cxx @ 2612] dbumi!dbaui::SbaTableQueryBrowser::implSelect+0x92 [g:\ooo\dev300_m92\dbaccess\source\ui\browser\unodatbr.cxx @ 2389] dbumi!dbaui::SbaTableQueryBrowser::impl_initialize+0x1e95 [g:\ooo\dev300_m92\dbaccess\source\ui\browser\unodatbr.cxx @ 3160] dbumi!dbaui::OGenericUnoController::initialize+0x51b [g:\ooo\dev300_m92\dbaccess\source\ui\browser\genericcontroller.cxx @ 408] dbumi!DBContentLoader::load+0x1949 [g:\ooo\dev300_m92\dbaccess\source\ui\browser\dbloader.cxx @ 320] fwkmi!framework::LoadEnv::impl_loadContent+0x4b8 fwkmi!framework::LoadEnv::startLoading+0x87 fwkmi!framework::LoadEnv::loadComponentFromURL+0x81 fwkmi!framework::Frame::loadComponentFromURL+0x94 dbumi!dbaui::DatabaseObjectView::doDispatch+0x7a4 [g:\ooo\dev300_m92\dbaccess\source\ui\misc\databaseobjectview.cxx @ 164] dbumi!dbaui::DatabaseObjectView::doCreateView+0x86 [g:\ooo\dev300_m92\dbaccess\source\ui\misc\databaseobjectview.cxx @ 119] dbumi!dbaui::DatabaseObjectView::openExisting+0x4b [g:\ooo\dev300_m92\dbaccess\source\ui\misc\databaseobjectview.cxx @ 106] dbumi!dbaui::OApplicationController::openElementWithArguments+0xefa [g:\ooo\dev300_m92\dbaccess\source\ui\app\appcontroller.cxx @ 1926] dbumi!dbaui::OApplicationController::openElement+0x69 [g:\ooo\dev300_m92\dbaccess\source\ui\app\appcontroller.cxx @ 1827] dbumi!dbaui::OApplicationController::onEntryDoubleClick+0xf7 [g:\ooo\dev300_m92\dbaccess\source\ui\app\appcontroller.cxx @ 1787] dbumi!dbaui::OAppDetailPageHelper::OnEntryDoubleClick+0x76 [g:\ooo\dev300_m92\dbaccess\source\ui\app\appdetailpagehelper.cxx @ 1060] dbumi!dbaui::OAppDetailPageHelper::LinkStubOnEntryDoubleClick+0xf [g:\ooo\dev300_m92\dbaccess\source\ui\app\appdetailpagehelper.cxx @ 1057] tlmi!Link::Call+0x11 dbumi!dbaui::DBTreeListBox::DoubleClickHdl+0x1c [g:\ooo\dev300_m92\dbaccess\source\ui\control\dbtreelistbox.cxx @ 468] svtmi!SvImpLBox::MouseButtonDown+0x18c From what I can see, in ORowSet::execute_NoApprove_NoNewConn in dbaccess/source/core/api/RowSet.cxx, line 1809 impl_prepareAndExecute_throw() causes the "SELECT * from xx" using statementhandle 1, line 1830 m_pCache->setFetchSize(m_nFetchSize) and 1831 m_pCache->createIterator(this); does the "SELECT * from xx where key = ?" using statementhandle 2. The first query is used to provide the key value for the second query. But since all data are already retrieved why not just fetch them instead of re-querying the table one row at a time? Or, as MSAccess does, query 1 does a "select keys from xx", and query 2 a "SELECT * from xx where key = ?". If you are interested I can attach an ODBC trace that shows clearly that data are loaded twice. ODBC traces are lengthy though.
Comment 8 ocke.janssen 2010-12-06 08:55:44 UTC
The reason why we need the first one is to fetch the complete row. The requering of a row should now only happen when you move the cursor to a place where the cursor from the rowset already was. The rowset uses a forward only cursor. The keyset which is used below the rowset fetch rows by key but only when needed. This I changed. In former versions all rows was refetched even the the cursor from the rowset was still valid. So in our case the first fetch like "select * from key = ?" only happen when you move the cursor to a position where the forward-only cursor from the rowset already visited and the position is not in the window (fetch size). A second way to go would be to check if the cursor from the rowset can be moved back and forward. But history shows that some cursor say "yes we can", but the don't ;-) In my test no call was done for "select * from y where key = ?" when displaying the table.
Comment 9 ludob 2010-12-06 10:22:08 UTC
OK, I see where you are coming from. You need to refetch backward cursor positions since some drivers don't implement this correctly or lie about it. So your fix removed "unnecessary" refetching. As long as you scroll down, there should be no refetching. You looked at reducing the "select * from y where key = ?" I look at it from different perspective. I have a problem with the initial "select * from y". Why fetch the full table if you look at the table through a window (limited number of rows at a time)? Why load blobs or binary data when they are not displayed and when you can't do anything with them? No insert, no save. Opening large tables can be really slow (long time to first display) or simply impossible due to an out of memory condition (too many rows, blobs, ...). OOO internally is memorywise OK storing only the data for the window displayed but it forces the driver to fetch the whole lot... An overall minimum memory and bandwidth solution would consist of a "select key(s) from y" followed by a series of "select displayable fields from y where key(s) = ?". To further reduce bandwidth, replace the "where key(s) = ?" by "where key(s) = ? or key(s) = ? ... or key(s) = ?" to retrieve multiple rows in one go. MSAccess uses a fixed number of rows (10), but obviously a variable number (the rows needed to fill the window and not already displayed and not cached in OOO) would be optimal. I'm not going to reopen the issue again as it will be closed by the next reply but you will understand that for me the solution proposed only addresses a small and minor part of the problem (if there is no out of memory before displaying the first line...).
Comment 10 ocke.janssen 2011-01-14 08:05:47 UTC
Please verify. Thanks. repro: - enable ODBC tracing - Open any db with ODBC - Open any table with a primary key - Close OOo - Search for the table name in the log file from ODBC tracing => There less calls for SELECT * from ....
Comment 11 ludob 2011-01-14 08:33:51 UTC
Did anything change from Dec 3? Or is this request just for msc? I did verify the "dba34c: #i102625# only fetch rows when the view moves outside the scope of the rowset window " changes and made my comments. There are indeed less "select * from y where key = ?". If you only scroll forward there are just a few when fetching the last page. That is an improvement. I'm not going to repeat my Dec 6 comments but the main issue is not the "select * from y where key = ?" but the initial "select * from y". Plus, the "select * from y where key = ?" still retrieves blob data while not used in table view.
Comment 12 ocke.janssen 2011-02-25 13:19:40 UTC
Yes I see your concerns. You are right that this should also be adjusted. So only the first part is fixed. I set it to new again. My problem when thinking of the part with select * from vs select vc1,c3,c5,c7 from I today know that we get problems with some drivers which returns column names which can't be fetched :-( There exists always such drivers, that's what the past 12 years told me :-) Regards, Ocke
Comment 13 marc.neumann 2011-03-16 12:45:59 UTC
reassign to oj, because he wants to fix somethings.
Comment 14 ocke.janssen 2011-03-16 12:48:38 UTC
I changed target.