Issue 102625

same behavior in OOO310m11

Same as issue 98548 ??

Same as issue 101280?

Same as Issue 66846?

Grabbing

Fixed in cws dba34c.

Now 

SELECT * from xx where key = ?

will only be called when the view jumps from last to first row. 

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.





 
 

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.

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

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.

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

reassign to oj, because he wants to fix somethings.

I changed target.