Issue 102625 - Table Data View + ODBC issues select * from table to get only key data
Summary: Table Data View + ODBC issues select * from table to get only key data
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOO300m9
Hardware: PC Windows XP
: P4 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2009-06-09 18:40 UTC by ludob
Modified: 2013-08-07 15:45 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
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 2 ludob 2009-06-14 09:09:38 UTC
Same as issue 98548 ??
Comment 3 ludob 2009-06-18 15:29:48 UTC
Same as issue 101280?
Comment 4 ludob 2009-06-19 07:22:50 UTC
Same as Issue 66846?
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.


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:

[g:\ooo\dev300_m92\connectivity\source\drivers\odbcbase\opreparedstatement.cxx @
[g:\ooo\dev300_m92\dbaccess\source\core\api\preparedstatement.cxx @ 227]
[g:\ooo\dev300_m92\dbaccess\source\core\api\rowset.cxx @ 1681]
[g:\ooo\dev300_m92\dbaccess\source\core\api\rowset.cxx @ 1805]
[g:\ooo\dev300_m92\dbaccess\source\core\api\rowset.cxx @ 1588]
[g:\ooo\dev300_m92\forms\source\component\databaseform.cxx @ 1261]
[g:\ooo\dev300_m92\forms\source\component\databaseform.cxx @ 2919]
[g:\ooo\dev300_m92\forms\source\component\databaseform.cxx @ 2702]
[g:\ooo\dev300_m92\dbaccess\source\ui\browser\brwctrlr.cxx @ 733]
[g:\ooo\dev300_m92\dbaccess\source\ui\browser\unodatbr.cxx @ 2333]
[g:\ooo\dev300_m92\dbaccess\source\ui\browser\unodatbr.cxx @ 2612]
[g:\ooo\dev300_m92\dbaccess\source\ui\browser\unodatbr.cxx @ 2389]
[g:\ooo\dev300_m92\dbaccess\source\ui\browser\unodatbr.cxx @ 3160]
[g:\ooo\dev300_m92\dbaccess\source\ui\browser\genericcontroller.cxx @ 408]
[g:\ooo\dev300_m92\dbaccess\source\ui\browser\dbloader.cxx @ 320]
[g:\ooo\dev300_m92\dbaccess\source\ui\misc\databaseobjectview.cxx @ 164]
[g:\ooo\dev300_m92\dbaccess\source\ui\misc\databaseobjectview.cxx @ 119]
[g:\ooo\dev300_m92\dbaccess\source\ui\misc\databaseobjectview.cxx @ 106]
[g:\ooo\dev300_m92\dbaccess\source\ui\app\appcontroller.cxx @ 1926]
[g:\ooo\dev300_m92\dbaccess\source\ui\app\appcontroller.cxx @ 1827]
[g:\ooo\dev300_m92\dbaccess\source\ui\app\appcontroller.cxx @ 1787]
[g:\ooo\dev300_m92\dbaccess\source\ui\app\appdetailpagehelper.cxx @ 1060]
[g:\ooo\dev300_m92\dbaccess\source\ui\app\appdetailpagehelper.cxx @ 1057]
[g:\ooo\dev300_m92\dbaccess\source\ui\control\dbtreelistbox.cxx @ 468]

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.


- 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

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
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 :-)


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.