Display number of records in table

[oliveiro]

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

[RPG]

Hello

I think use the navigation toolbar or a navigation control and go to the last record.

Romke

[oliveiro]

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.

[Hagar Delest]

-> [Tutorial] Reporting bugs or suggestions.

[Arineckaig]

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.

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.
See: http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/ResultSet.html#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.

[r4zoli]

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.

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.

[DACM]

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

[DACM]

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.

SELECT COUNT(*) FROM CONTACTS

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.

Any way this can be configured or otherwise achieved?

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:

Code: Select all   Expand viewCollapse view

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

[rudolfo]

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.

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.

[paco]

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

[Sliderule]

paco:

Welcome to the OpenOffice / LibreOffice Base forum.

You do have several options:

1. 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:
   
   https://forum.openoffice.org/en/forum/viewtopic.php?f=6&t=13490
   
   
2. 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.
   
   

   Code: Select all   Expand viewCollapse view

   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

   
   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   Expand viewCollapse view

   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

   
   This way, you can create a FORM from it, if you wish.

Sliderule