Apache OpenOffice (AOO) Bugzilla – Issue 12747
re-fetch of every row in a DB-Table
Last modified: 2006-05-31 14:29:06 UTC
Hi Frank,*, I have made some tracing with hsqldb, so their are some strange things: if I select a table in the DB-Explorer, OOo does first a: select <ColumnName> from <Tablename> where 0=1 , and this for every Column and than for every row of the resultset a: SELECT * FROM "OOTable1" WHERE "OOTable1"."OOID" = 9 where 9 is only a single primaryKey. For me it seems that OOo builds the resultsets by iterate thru this by singele sql-statements. If I go with basic to the db, all works fine. BTW: If anybody is looking as this, perhaps someone can point me to the steps what OOo does by clicking on a table in the db-explorer
*** Issue 12748 has been marked as a duplicate of this issue. ***
confirming at least the first part, which is calling a "SELECT <column> FROM <table> WHERE 0 = 1" for every column. For the second part: I am not yet clear about this, normally, I'd expect that there is only one statement/result set needed for retrieving all the data. Ocke, can you please investigate both parts? For the first, I think we should see if we can reduce the number of statements - if they're all triggered from the same place (the query composer), the we (try to) should optimize this place. For the second, I think we should first investigate what's causing this ... Thanks :)
Hi Ocke, I found out, that with SAPDB the refetch of every row not happens. So I wondered and looked around in the Metadata. With Basic I have a Property FetchSize in the ResultSet-Object of OOo. In Mysql this is 0, in hsqldb 1 and in sapdb 30000. Perhaps this could be a reason. But I dont find out where OOo has this info from. Do you know this? Because than I could change the driver :-)
Hi Hermann, what does you baisc marco do? If it creates a RowSet the fetchsize defines the size of the cache which holds the row currently fetched. 1 means that only one row will be hold, could be used when only updates and new rows are going to be inserted otherwise I would prefer 50. The data source browser use the size of the window and calculates it's size automaticly. But I don't know where this 30000 from SAPDB comes from. Best regards, Ocke
my Basic Macro creates only a com.sun.star.sdb.DatabaseContext and loops thru the registered Databases. Then I get a Connection and create a statement. After doing a: Select * from <Tablename> I got a com.sun.star.sdb.OResultSet back and this Resultset has some Properties including fetchSize. I dont change them and I dont know where the values come from. BTW: with this Basic-Script the refetch does not happen. This is only if I go with the DataSource Browser. I thought this could be a starting-Point to look for
I reduced the calls for "select name from 0=1" to a call like "select * from 0=1", so this should be fixed in the next version. The FetchSize is initial set by the driver itself. You may have a look at the ResultSet method "getFetchSize". HSQLDB returns always 1. What we do in the RowSet is to fetch every row with their primary key. So we have a cache which holds only the primary key and when the data is needed we use it to fetch the whole row. When you set the FetchSize for the RowSet, it should hold excatly FetchSize rows and for the other it holds only the primary key. Best regards, Ocke PS: I set this one to fixed. If there exists still a bug with the FetchSize please submit a new issue, Thx.
Changed target.
Hi Ocke, I can switch the Fetchsize to whatever I will, OOo creates a select-Statement for every row in the Table. And if I understand you right, than OOo fetch every row with primary key again. Why does OOo not use the resultset from select * ... What is if I have a Table with 30000 rows over network? is this performant and necessary? What is anout MySQL? it gives 0 as FetchSize back. And as Frank told, another User have the Problem that OOo is very very slow with great Tables.
First of all be have to decide between the RowSet and a normal ResultSet directly created from the Statement. The FetchSize of the ResultSet depends on the driver and depends on the preson developed it. The FetchSize for the RowSet only defines the size of rows which will be currently cached. When it is set to 50 then only 50 rows will be in memory. So the RowSet will "only" call 50 times the prepared statement. If the cache (window) moves to the 51 row it will use the ResultSet of the statement to fetch the primary key for the 51 row and then fetch the row with all columns. The problem with the very very slow tables was a problem that a select statement 0=1 was sent to the database for every column in the table, but this is fixed.
Hi Ocke, now I see that this is true and 'only' for the rowset every row is fetched again. But now when I work with great tables, I see in the Database Browser not the available Rows, only the number of rows that are in cache. And scrolling works very slow too. But If this is right so, I changed back to fixed (hope it works).
Please verify.
What ?
For example enable tracing for ODBC and count how often the call SQLColumns is called when selecting one table in the beamer. Should around 3-4 times. BEst regards, Ocke
Hi, I found the call SQLColumn 4 times in the log file when open a ODBC table on a MS SQl Server. So the bug is fixed. Bye Marc
verified in internal development build
Closing now.
change subcomponent to 'none'