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 [email protected] Tue Jun 9 17:40:48 +0000 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: [email protected] For additional commands, e-mail: [email protected]
