QListBox holds all objects making it slow in certain situations - possible performance improvement?

ADDITION:

I have now extracted the Query and applied it manually and measured it..

This is the complete code it took

*QueryArray TEST: 2.3054418563843
*LoadAll TEST: 2.2675719261169
*PLAIN PHP TEST: 0.036140918731689
* in phpmyadmin it took only 0.0050 but that might be automatically restricted to the first 30 rows.

protected function Form_Create() {


//-----QueryArray TEST-----------------------

QApplication::$Database[1]->EnableProfiling();
$start1 = microtime(true);
$test1= Geschaefte::QueryArray(
                                QQ::All(),QQ::OrderBy(QQN::Geschaefte()->Id,true),array(QQ::LimitInfo('10000')));    
        
print 'QueryArray TEST: ';
echo microtime(true)-$start1;
print '<br />';
            
            
            
//------LoadAll TEST------------------------------
            
$start2 = microtime(true);
$test2= Geschaefte::LoadAll();
print 'LoadAll TEST: ';
echo microtime(true)-$start2;
print '<br />';        

// output profiling for the 2 above..
                QApplication::$Database[1]->OutputProfiling();
                
//------Plain old PHP TEST---------------------------------
            
    
$link = mysql_connect("mysql.asdfasdf.info", "43_61", "1234");
mysql_select_db("34_61", $link);
$start3 = microtime(true);        
            
$query = '
    
SELECT
    `t0`.`id` AS `a0`,
... and so on ...
    `t0`.`modified` AS `a139`,
    `t0`.`created` AS `a140`
    
        FROM
            `Geschaefte` AS `t0`
            
        ORDER BY
            `t0`.`id` ASC
            
            LIMIT 0,100000000
            ';
            
            
$result = mysql_query($query);
$array = mysql_fetch_assoc($result);


print 'PLAIN PHP TEST: ';
echo microtime(true)-$start3;
print '<br />';            
            
            
}    // end form create

Cheers
tronics

From collegue Akrohn:

Generated around 4000 rows for the projects table of the sample database and was curious what the profiler would say. (used QSqlServer2005Database)

For a Project::LoadAll() it got me this for a total time of 21.569 ms:

- first for “Avg. self” was ProjectGen::InstantiateDbResult (1206 ms for 1 call)
- first for “Total self” by a large margin was ProjectGen::InstantiateDbRow (11.115 ms for 4006 calls)
- first for most called function besides php internals is GetColumn() (around 56000 times, total self is 3.102 ms)

So I guess these 3 functions should be the first ones to look into for optimization.

It comes down to 2 problems:

1) Qcodo ORM is generating a lot of objects when doing eg. a LoadAll?()
This has also proven to hamper performance on other ORMs like Doctrine.

2) QListBox is hardly usable for large datasets - regarding usability & performance
What works really good on small datasets makes for a very lagging system as soon as you have a couple of hundred entries.

Performance is hampered in several ways:

• Using the Qquery LoadAll?() method is really very, very slow

• The more data there is in the set the larger the Formstate is on the clientside. I have one form where the HTML hits 2,7 MB because of this reason

• The selection of this UI Element for a large dataset is poor. Looking at advanced listbox replacements from other frameworks it is obvious we have some room to catch up: http://www.grails.org/screencast/show/7 (I know it might look like autocomplete at first sight but this or something similar is also a high performance UI replacement for Listboxes)

I submitted those as 2 separate tickets:

ORM performance
http://www.qcodo.com/issues/view.php/43/lastpage

QListBox enhancements
http://www.qcodo.com/issues/view.php/44/lastpage

All the Best,
tronics

Hello Mike,

Thank you.
Yes you are totally right that the datastructure used in that application should be improved.
I posted it because it was the first application where I found this issue and I was unsure if it was only having to do with my setup.

--

But then I found actually the performance issue has nothing to do with that structure, it also shows with your recommended sample database.

Akrohn tested with just 4000 rows (which is not very much) with the projects table of the sample database and that took 5 seconds.
Avoiding GetColumn() calls in InstantiateDbRow() did get him down to 3.2 seconds

And he had the same issues that make an application too slow for nowadays standards just because of one generated QListBox.
Or better to say, not because of the QListBox but because of one ->LoadAll() statement

Quote from Akrohn:

Generated around 4000 rows for the projects table 
of the sample database and was curious what the 
profiler would say. 

--

Hey, I've got to add, that it needed 21 seconds 
only because the xdebug-profiler was enabled, without 
it needs only around 5 seconds.

Avoiding GetColumn() calls in InstantiateDbRow() did 
get me to 3.2 seconds for a 6 column table. 
So this is a lot for such a little change.

Related to this is avoiding of "Select *" discussion, 
that should enhance performance too.

--

Apart from the problem that ->LoadAll() is slow, there should be a different UI element to address large datasets.

QListBox containing more then say 100 entries, cannot be selected or sorted by the user from the UI aspect plus without the performance issue above.

So giving QListBox as the default is a choice that will make the developer thiknk it is ok to start with that and to use ->SelectedValue and having an application rely on QListBox.

There is no indication on the code or website, that this is not a good choice if you are going to have more than 100 rows . But there is probably no application that is intended to have less then 100 rows.

I'm sure there are workarounds for example to use a Qdialog/Qdatagrid combination or pagination.
Qcodo does not give any indication how to use that.

Most useful would be to have optimal UI elements autogenerated in the drafts already.

Cheers,
tronics

The standard QFormStateHandler is not usable in pages with big controls or lots of controls, for the reason you're exposing. Use the QFormSessionStateHandler or QFormFileStateHandler, so the entire state is not transfered to the client but cached ni the server.

The GRails control you reference is not a listbox, it's clearly an autocomplete box. Not the same functionality. And don't trust in the fantastic performance seen in a demo, as it's done with just a couple of records.

I faced the same problem you're facing, and I'll explain the solution I implemented:

For the QQuery part, optimize the database (indexes, number of records per table, number or fields per record...). Currently there's no other way.

For the HTML part, a listbox with tenths of values is simply not usable (I can't imagine how does it work with thousands of them). This is a bit complicated to solve in a generalized manner, but it's doable (in fact I did it for my own framework):

- Implement popup pages that serve only for record selection (using a data grid that allows a JavaScript action when you click on a row, for instance).

- Implement a listbox replacement control that simlpy opens that popup and receives the result.

The complex part is that the popup must return a value or some values, so the control must specify in the URL it opens where to put them, and the popup must put that values in the parent window when they're selected. It's a bit hard to do it right, cross browser and in a generalized manner, but we've faced more complicated JavaScript wars.

Maybe it's less complicated if you use a panel or a QDialogBox instead of a popup. I haven't tried those.