Display number of records in table

Discuss the database features

Display number of records in table

Postby oliveiro » Wed Mar 07, 2012 10:34 am

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
OpenOffice 3.3 on Windows 7
oliveiro
 
Posts: 3
Joined: Wed Mar 07, 2012 10:17 am

Re: Display number of records in table

Postby RPG » Wed Mar 07, 2012 11:06 am

Hello

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

Romke
LibreOffice 6.1.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2161
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Display number of records in table

Postby oliveiro » Wed Mar 07, 2012 12:24 pm

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
oliveiro
 
Posts: 3
Joined: Wed Mar 07, 2012 10:17 am

Re: Display number of records in table

Postby Hagar Delest » Wed Mar 07, 2012 2:36 pm

AOO 4.1.6 on Xubuntu 19.04 and 4.1.5 on Windows 7 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28458
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Display number of records in table

Postby Arineckaig » Wed Mar 07, 2012 4:06 pm

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.
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
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Display number of records in table

Postby r4zoli » Wed Mar 07, 2012 5:58 pm

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.

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.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Display number of records in table

Postby DACM » Wed Mar 07, 2012 6:32 pm

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)
...
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
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Display number of records in table

Postby DACM » Wed Mar 07, 2012 8:50 pm

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


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! :shock: :bravo:

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

oliveiro wrote: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
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
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Display number of records in table

Postby rudolfo » Wed Mar 07, 2012 11:27 pm

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. 
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.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Display number of records in table

Postby paco » Wed Nov 30, 2016 12:11 pm

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
Windows 7 + Apache OpenOffice 4.1.3.
paco
 
Posts: 3
Joined: Wed Nov 30, 2016 12:01 pm

Re: Display number of records in table

Postby Sliderule » Wed Nov 30, 2016 4:50 pm

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. :bravo:
Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1167
Joined: Thu Nov 29, 2007 9:46 am


Return to Base

Who is online

Users browsing this forum: No registered users and 0 guests