We’ve got a large solution using tabpanels to view child record information about people (e-mail addresses, citizenships, degrees, notes, etc.).
From the people form, we’d like to display the child counts on each tab like this: “Degrees (2)”, and “Email (3)” as a GUI aid for the endusers.
We’ve tried form.relations.parent_to_child.getSize(), but this does not issue a count(*) statement which should be most efficient. Instead, getSize seems to be creating:
“select addresses_email.id_addresses_email, addresses_email.id_people, addresses_email.id_t_addresses_email, addresses_email.address, addresses_email.note, addresses_email.test from addresses_email where addresses_email.id_people = ? order by addresses_email.id_addresses_email, addresses_email.id_people”
Is this intended, or a possible bug?
If a fix isn’t warranted, what is the most efficient way to provide the child ccounts on tabs in a tabpanel? (Is an implementation of database_manager.getFoundSetCount() recommended instead?)
getSize() doesn’t create that query.
the lookup of the foundset creates that query
so ‘form.relations.parent_to_child’ does that.
if you don’t want to load any data yet. then i think doing youre own select count(*) query through the databasemanager.getDataSetByQuery() would be the fasted.
I’ve learned from our DBA that count(*) is slower, because it will somehow also look at all the column in the table, whereas count(1) doesn’t look at the columns.
Maybe it’s a myth, as the website you found states.
Running both queries consequtive and comparing the execution time is not a foolproof way of figuring out which is faster, because the DB can cache the execution plan of the first query, making the second run faster.
why whould the db read all youre columns?
It knows what it should return that is only an int.
It should read ANY columns, if it read one column i would say that is a bug or very bad implementation.
If it is a select with the where on an index. Then it shouldn’t touch the data in the table at any place. Because the index can return the size.
If it where part isn’t on an index column it needs to read in the column that is used in the part. But it shouldn’t read in any other columns, why should it?
We’re talking an Oracle Db here. I’ll ask the DBA who tought me this what his effidence is for his statement that count(1) would be faster than count(*)
Intregued by the quest, the DBA went on a sprawl to find the truth and here it is.
We work on Oracle DB’s only and our DBA has a long time experience with this DB. It turns out Oracle also concluded that it was poor design that count(*) would be more costly than count(1), so as of version 8, they made the behavior of both queries equal.
So, Johan, as far as Oracle and Sybase DB’s are concerned, you’re right. Maybe also right about all the other DB’s around, but I do not have any experience in that area, but a major DB vendor like Orcale only “solved” this in their version 8.
Thanks for the response re: what is returned from getSize().
If possible having getSize() create select count(*) SQL even when used on related records (rather than loading related ata) would be an advantage over creating out own SQL to do the same.
(Or at the least a bit in the sample code explaining the behavior as it’s got performance implications.)
Just fyi with our solution which has 10 tabpanels on one core form, doing a getSize() seems to essentially load all the tab panel(s) data when that record is displayed. This does seem to be a considerable amount of work for the DB…
It’s 9 extra queries w/ data when the user might only want to see one tabpanel.