[dba-issues] [Issue 102625] Table Data View + ODBC is sues select * from table to get only key data
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=102625 User oj changed the following: What|Old value |New value Status|UNCONFIRMED |RESOLVED Resolution| |FIXED --- Additional comments from o...@openoffice.org Mon Dec 6 08:55:44 + 2010 --- 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. - Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification - To unsubscribe, e-mail: issues-unsubscr...@dba.openoffice.org For additional commands, e-mail: issues-h...@dba.openoffice.org - To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org
[dba-issues] [Issue 102625] Table Data View + ODBC is sues select * from table to get only key data
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=102625 --- Additional comments from lu...@openoffice.org Mon Dec 6 10:22:08 + 2010 --- 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...). - Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification - To unsubscribe, e-mail: issues-unsubscr...@dba.openoffice.org For additional commands, e-mail: issues-h...@dba.openoffice.org - To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org
[dba-issues] [Issue 102625] Table Data View + ODBC is sues select * from table to get only key data
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=102625 User ludob changed the following: What|Old value |New value Status|RESOLVED |UNCONFIRMED Resolution|FIXED | --- Additional comments from lu...@openoffice.org Fri Dec 3 16:55:58 + 2010 --- 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
[dba-issues] [Issue 102625] Table Data View + ODBC is sues select * from table to get only key data
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=102625 User oj changed the following: What|Old value |New value Assigned to|dbaneedsconfirm |oj Target milestone|--- |OOo 3.4 --- Additional comments from o...@openoffice.org Thu Dec 2 12:17:22 + 2010 --- Grabbing - Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification - To unsubscribe, e-mail: issues-unsubscr...@dba.openoffice.org For additional commands, e-mail: issues-h...@dba.openoffice.org - To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org
[dba-issues] [Issue 102625] Table Data View + ODBC is sues select * from table to get only key data
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=102625 User oj changed the following: What|Old value |New value Status|UNCONFIRMED |RESOLVED Resolution| |FIXED --- Additional comments from o...@openoffice.org Thu Dec 2 12:25:18 + 2010 --- Fixed in cws dba34c. Now SELECT * from xx where key = ? will only be called when the view jumps from last to first row. - Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification - To unsubscribe, e-mail: issues-unsubscr...@dba.openoffice.org For additional commands, e-mail: issues-h...@dba.openoffice.org - To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org
[dba-issues] [Issue 102625] Table Data View + ODBC is sues select * from table to get only key data
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=102625 User atjensen changed the following: What|Old value |New value CC|''|'atjensen' - Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification - To unsubscribe, e-mail: issues-unsubscr...@dba.openoffice.org For additional commands, e-mail: issues-h...@dba.openoffice.org - To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org
[dba-issues] [Issue 102625] Table Data View + ODBC is sues select * from table to get only key data
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=102625 --- Additional comments from lu...@openoffice.org Fri Jun 19 06:22:50 + 2009 --- Same as Issue 66846? - Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification - To unsubscribe, e-mail: issues-unsubscr...@dba.openoffice.org For additional commands, e-mail: issues-h...@dba.openoffice.org - To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org
[dba-issues] [Issue 102625] Table Data View + ODBC is sues select * from table to get only key data
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=102625 --- Additional comments from lu...@openoffice.org Thu Jun 18 14:29:48 + 2009 --- Same as issue 101280? - Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification - To unsubscribe, e-mail: issues-unsubscr...@dba.openoffice.org For additional commands, e-mail: issues-h...@dba.openoffice.org - To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org
[dba-issues] [Issue 102625] Table Data View + ODBC is sues select * from table to get only key data
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=102625 --- Additional comments from lu...@openoffice.org Sun Jun 14 08:09:38 + 2009 --- Same as issue 98548 ?? - Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification - To unsubscribe, e-mail: issues-unsubscr...@dba.openoffice.org For additional commands, e-mail: issues-h...@dba.openoffice.org - To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org
[dba-issues] [Issue 102625] Table Data View + ODBC is sues select * from table to get only key data
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=102625 User ludob changed the following: What|Old value |New value Summary|Table Data View + ODBC iss|Table Data View + ODBC iss |ues select * from table to|ues select * from table to | get only key data| get only key data --- Additional comments from lu...@openoffice.org Wed Jun 10 08:27:43 + 2009 --- same behavior in OOO310m11 - Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification - To unsubscribe, e-mail: issues-unsubscr...@dba.openoffice.org For additional commands, e-mail: issues-h...@dba.openoffice.org - To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org
[dba-issues] [Issue 102625] Table Data View + ODBC is sues select * from table to get only key data
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=102625 Issue #|102625 Summary|Table Data View + ODBC issues select * from table to g |et only key data Component|Database access Version|OOO300m9 Platform|PC URL| OS/Version|Windows XP Status|UNCONFIRMED Status whiteboard| Keywords| Resolution| Issue type|DEFECT Priority|P3 Subcomponent|none Assigned to|dbaneedsconfirm Reported by|ludob --- Additional comments from lu...@openoffice.org Tue Jun 9 17:40:48 + 2009 --- 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 - Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification - To unsubscribe, e-mail: issues-unsubscr...@dba.openoffice.org For additional commands, e-mail: issues-h...@dba.openoffice.org - To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org
