Display number of records in table
Display number of records in table
My issue seems to be pretty basic here.
I just started using Base. Version is shown as OOO330m20(Build:9567).
I created my first table, and inserted rows from a CSV file (by all means not an easy task, to mention this).
I now have 20000+ records in the table.
However when I open the table, bottom line is always something like "Record 1 of 26*" or similar. I can't easily get Base to show me the actual and true number of records in the table.
MS Access (the product that I want to replace by Base) would immediately show me that information.
Any way this can be configured or otherwise achieved?
I know of course of "select count(*) from tablex" ... my question relates to the standard table view when opening a table in Base.
Thanks
Oliver
I just started using Base. Version is shown as OOO330m20(Build:9567).
I created my first table, and inserted rows from a CSV file (by all means not an easy task, to mention this).
I now have 20000+ records in the table.
However when I open the table, bottom line is always something like "Record 1 of 26*" or similar. I can't easily get Base to show me the actual and true number of records in the table.
MS Access (the product that I want to replace by Base) would immediately show me that information.
Any way this can be configured or otherwise achieved?
I know of course of "select count(*) from tablex" ... my question relates to the standard table view when opening a table in Base.
Thanks
Oliver
OpenOffice 3.3 on Windows 7
Re: Display number of records in table
Hello
I think use the navigation toolbar or a navigation control and go to the last record.
Romke
I think use the navigation toolbar or a navigation control and go to the last record.
Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Re: Display number of records in table
True, finding and pressing navigation keys will eventually trigger Base to display the number of records in the table. My suggestion would be to display that info right away - since this is what "Record x of y" suggests to the user.
OpenOffice 3.3 on Windows 7
- Hagar Delest
- Moderator
- Posts: 32657
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Display number of records in table
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Display number of records in table
If the need is critical, it is possible to over-rule the defaults used by Base for the number of records in the initial record-set by setting the '.FetchSize' property of the data form. For example, when that property is set to any number in excess of the total number of records, the effect will be similar to that seen in MS Access.My suggestion would be to display that info right away - since this is what "Record x of y" suggests to the user.
MS Access (the product that I want to replace by Base) would immediately show me that information.
See: http://api.openoffice.org/docs/common/r ... #FetchSize
I suggest where communication between the Client and the Server is uncertain or slow such as some of us enjoy, there is merit in the Base defaults which download no more records than what might be initially required. IMHO Base in this respect is more flexible and preferable to Access, and with time adjustment to the change-over becomes more acceptable.
Last edited by Arineckaig on Wed Mar 07, 2012 6:16 pm, edited 1 time in total.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Re: Display number of records in table
The Base was designed to show limited number of rows, due to database performance problems. If you load all data into memory when you open odb file it takes more time, and consume more system memory, nowdays the later is not a big problem for most of the computers, but in older slower computers can cause problems. OOo needs to run on all system, if you rise the minimal system requirement, more users will be unsatisfied, than you when you need to click to one button more.oliveiro wrote:True, finding and pressing navigation keys will eventually trigger Base to display the number of records in the table. My suggestion would be to display that info right away - since this is what "Record x of y" suggests to the user.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Hungarian forum co-admin
Re: Display number of records in table
With 20,000 records, you'll need to become familiar with some performance tweaks and perhaps my signature links or other external database solution:
Slow database remedies:
Compact your HSQL database
Adjusting HSQLDB Cached Table Memory for Large Databases (>3 MB) (key if you're experiencing slow database response)
Adjusting Java Memory for Very Large Databases (>50 MB) (in conjunction with HSQLDB Cached Memory settings)
HSQLDB 1.8 Image storage issue; HSQLDB 2.x to the rescue (maintain performance despite large VARCHAR or LOB storage)
...
Slow database remedies:
Compact your HSQL database
Adjusting HSQLDB Cached Table Memory for Large Databases (>3 MB) (key if you're experiencing slow database response)
Adjusting Java Memory for Very Large Databases (>50 MB) (in conjunction with HSQLDB Cached Memory settings)
HSQLDB 1.8 Image storage issue; HSQLDB 2.x to the rescue (maintain performance despite large VARCHAR or LOB storage)
...
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Re: Display number of records in table
SELECT COUNT(*) FROM CONTACTSoliveiro wrote:MS Access (the product that I want to replace by Base) would immediately show me that information.
...I know of course of "select count(*) from tablex" ... my question relates to the standard table view when opening a table in Base.
I just ran Count(*) in Base on an "embedded database" table with 25,000 records (4MB file; all records filled with bogus contact information). The result "25000" was instantaneous.
But "instantaneous" is subjective, so I ran the same command on the same table in 'server mode' (HSQLDB 1.8) using SQL Workbench/J. The reported time was 0.02 seconds to return the "25000" result. I then quadrupled the records to 100K and the "100000" count was reported in 0.07 seconds.
These times are relative to my laptop with mid-range mobile CPU, which is quite similar to an average dual-core desktop sold over the past 6 years or so.
So I'm guessing that Microsoft's developers considered a few hundredths of a second to be worth the wait, while Base developers were simply aghast!
All kidding aside, it probably came down to a philosophy on reporting the number of records loaded in memory, versus the total number of records in the table. I'm with you...I think it's a design flaw in Base. But we all share the guilt of not reporting these things to the devs.
So we may have to convince the devs that we really want a record count in Table view, but there is a clunky workaround for Base Forms which can host Table Control Grids similar to the standard Table view:oliveiro wrote:Any way this can be configured or otherwise achieved?
Code: Select all
Sub InitializeForm 'Form > Event > When Loading
Dim oForm As Object
oForm = ThisComponent.DrawPage.Forms.GetByIndex(0) '.GetByName("<form name here>") may be easier to troubleshoot
IF oForm.isloaded = false THEN Wait 100 'allows faster CPUs to continue while waiting for slower ones
oForm.Last
oForm.First 'this along with .Last above leaves the total number of records in nav bar
' oForm.MoveToInsertRow
End Sub
Last edited by DACM on Tue Nov 25, 2014 5:28 pm, edited 1 time in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Re: Display number of records in table
I think we should not forget that Base can be used as a frontend for many different database engines -- with several server based amongst them. Surely MS Access has the paradigm to have the frontend and the backend on the same client computer, but the embedded HDBSQL database was added to Base not before OOo Version 2.x. The initial concept was different.
If you have ever worked in a client server constellation where you only have a 128 kBit connection to the server, you will surely value that Base is not retrieving all the rows of the result set at once (leaving the GUI non-responsive for several minutes), but only that many as defined in the pre-fetch size. Even if you have enough memory on the client with Base to hold the complete 20000 rows result set in memory, the network bandwidth will be the bottleneck in this case.
If you have ever worked in a client server constellation where you only have a 128 kBit connection to the server, you will surely value that Base is not retrieving all the rows of the result set at once (leaving the GUI non-responsive for several minutes), but only that many as defined in the pre-fetch size. Even if you have enough memory on the client with Base to hold the complete 20000 rows result set in memory, the network bandwidth will be the bottleneck in this case.
Edit: Compared to retrieving the complete result set a separate SELECT count(*) FROM table_a doesn't cost you anything in terms of network bandwidth (it's a 1x1 result set!). Of course it will force the database engine into an additional full table scan, but usually the server is a machine that doesn't suffer from CPU and RAM capacity. Typically there is a break even somewhere between 200 and 2000 lines in the result set/table. Below the processing of the additional count() query needs more resources then simply retrieving the complete result set and evaluating the number of rows in the set. Of course if you have many columns/fields in your query the amount of data/bytes per row increases and the break even might be reached already at 300 and not at 1000 rows. |
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
Re: Display number of records in table
I come back to this topic after so many years and several new versions of OOBase because apparently the situation has not changed and I fear it can become definitive.
Former contributors have discussed the relative merits of a) a certain delay in the opening of a long database against b) the unreliability of the figure provided for the size of such a database (slightly mitigated by the cryptic and idiosyncrasic appendage of an asterisk to such a figure to denote its very unreliability).
I want to introduce a new consideration that in my opinion puts the question clearly on the *buggish* side.
Take a database of enough size to be opened partially by Base. Perform a search for some datum that you are sure is in the database but not in the initial recordset. The search will fail, which I consider to be an unacceptable behavior of the application.
I hope that some solution can and will be found to change this state of affairs, improving as much as possible the celerity of the application but without compromising in its name the essential reliability of its behaviour.
As long as this situation continues, I will always attach to the opening step of my databases a macro that performs the suggested initial down-up walk of the database. (I am accessing the tables through a form. I first observed this anomaly with version 3.4 but have later learned that it has a much longer tradition.)
Regards
Former contributors have discussed the relative merits of a) a certain delay in the opening of a long database against b) the unreliability of the figure provided for the size of such a database (slightly mitigated by the cryptic and idiosyncrasic appendage of an asterisk to such a figure to denote its very unreliability).
I want to introduce a new consideration that in my opinion puts the question clearly on the *buggish* side.
Take a database of enough size to be opened partially by Base. Perform a search for some datum that you are sure is in the database but not in the initial recordset. The search will fail, which I consider to be an unacceptable behavior of the application.
I hope that some solution can and will be found to change this state of affairs, improving as much as possible the celerity of the application but without compromising in its name the essential reliability of its behaviour.
As long as this situation continues, I will always attach to the opening step of my databases a macro that performs the suggested initial down-up walk of the database. (I am accessing the tables through a form. I first observed this anomaly with version 3.4 but have later learned that it has a much longer tradition.)
Regards
Windows 7 + Apache OpenOffice 4.1.3.
Re: Display number of records in table
paco:
Welcome to the OpenOffice / LibreOffice Base forum.
You do have several options:
Welcome to the OpenOffice / LibreOffice Base forum.
You do have several options:
- As mentioned earlier, file a bug / suggestion , per the instructions at the link below, and, please remember, the users of this forum are NOT developers, and, rarely do developers read this forum:
viewtopic.php?f=6&t=13490 - If you use an HSQL External database ( database Version 2.2.? and later ) RATHER THAN the Embedded Database ( HSQL 1.8.0.10 ) . . . you can use the following Query ( or create a VIEW for it ), and, get instantaneous results of the number of records in each of your tables.
Or, if prefer, similar to above Query, but, add a number as the first column ( starting with 1 ) . . . using HSQL function ROWNUM() :Code: Select all
Select INFORMATION_SCHEMA.SYSTEM_TABLESTATS.TABLE_NAME as TABLE_NAME, INFORMATION_SCHEMA.SYSTEM_TABLESTATS.CARDINALITY as NUM_RECORDS From INFORMATION_SCHEMA.SYSTEM_TABLESTATS Where INFORMATION_SCHEMA.SYSTEM_TABLESTATS.TABLE_SCHEMA = CURRENT_SCHEMA Order By UPPER(INFORMATION_SCHEMA.SYSTEM_TABLESTATS.TABLE_NAME) ASC
This way, you can create a FORM from it, if you wish.Code: Select all
Select ROWNUM() as "#", A.* From (Select INFORMATION_SCHEMA.SYSTEM_TABLESTATS.TABLE_NAME as TABLE_NAME, INFORMATION_SCHEMA.SYSTEM_TABLESTATS.CARDINALITY as NUM_RECORDS From INFORMATION_SCHEMA.SYSTEM_TABLESTATS Where INFORMATION_SCHEMA.SYSTEM_TABLESTATS.TABLE_SCHEMA = CURRENT_SCHEMA Order By UPPER(INFORMATION_SCHEMA.SYSTEM_TABLESTATS.TABLE_NAME) ASC ) as A