Hmm. Are you saying that

$start = microtime(true);
$asdf=Business::LoadAll();
echo microtime(true)-$start.'
';

Is taking 1.8 seconds, but a SELECT * is taking a few microseconds? That makes no sense whatsoever. Can you do a dump of the SQL statement that is getting executed here, and run it directly against the database?

Actually, this makes perfect sense, and is something I see in my code profiling all the time. Basically our object instantiation code involves quite a lot of overhead. We should probably look into any fat we can trim off of that.

One thing I've done in my own project is to keep a cache of instantiated objects, and just return a reference to the existing object if they try and instantiate an object with that ID again. This is particularly effective when doing expands when the expanded objects may be the same for different base objects. (eg: lots of people belong to the same group. Instantiated separate group objects for each of those is a lot of unneeded overhead, so I just return a reference to the first one that was created.)

This introduces problems when trying to re-load the original DB contents (you still get the cached object, which may have been altered), and you also have to take into account different expands (eg: object A expanded on B with condition C is different than object A expanded on B with condition D, because object B may be different).

I guess I can provide my template changes if someone wants, but really we should be optimizing the core instantiation code, not just reducing the number of times it's called.

Thanks to our previous discussions on caching I use QQueryCached in almost any project.
When turning on the caching the generated caching file for this loadAll above are 9MB. Yet for just 1 query it is even slower then the direct use.

It might be that we have a drawback in our paradigma here introducing too much overhead prefetching everything. With a reflection based ORM like www.phpactiverecord.org we would probably not experience this at all.

The machine is not slow - a dual core i7 with 6 GB RAM - no load, not virtualized, PHP5.29

I wonder in which ways could the current architecture be improved?

At first I was thinking about making a new QListBox that does not hold all the objects but instead loads these informations only on demand by caching one query and instantiating objects only when the data is needed.

But in fact the factors that produce overhead lie much deeper.
We have had discussions about improving performance on a very low level, say using different php constructs that save a little on the compiler.
Changing some smart stuff on the architecture might bring much more.

At least if we are not able or willing to change at the current stage.
There should be some informations that produce awareness which constructs are likely to produce lag.

For example the loadAll() method never came to my mind as something troublesome. But then I was waiting 4,5 seconds for an AJAX call to complete.

When I stressed the other ORM I also did that because Qcubed/Qcodo could be ORM agnostic (given even that currently all parts are codegenerated, using activerecords for other parts would not at all make us suffer) and this is not against our project here, instead rather some radical thought towards possible enhancment.

I cannot post the database at the moment, as I would have to get a lot of data anonymized which takes some time. Yet it should not be a big problem to produce a sample where one can see the differences.

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.

Here is the complete code, I do not think I have made an error here that affects the result.

Cheers
tronics

Now I also tried QueryArrayCached..

It is slower then this $result = mysql_query($query); $array = mysql_fetch_assoc($result);

1 time (not cached yet): slower then all of the above by at least .3 seconds
QueryArrayCached TEST: 2.5453979969025

2nd time and subsequent (cached): 16 times slower then Plain PHP
QueryArrayCached TEST: 0.56282591819763

//------QueryArrayCached TEST--------------

$start4 = microtime(true);
$test1= Geschaefte::QueryArrayCached(
QQ::All(),QQ::OrderBy(QQN::Geschaefte()->Id,true),array(QQ::LimitInfo('10000')));
print 'QueryArrayCached TEST: ';
echo microtime(true)-$start4;
print '
';

And last but not least stepping through all of the rows (I only stepped through one of them in the previous example..):

PLAIN PHP TEST: 0.066386938095093 very fast

All right, this is all very interesting. What concrete suggestions do we have for improving the performance?

Thoughts:
1) Finishing up the "selectively picking columns" in QQuery - then, a smaller query will get issued for listboxes, and there will be less overhead converting DB results to our objects.
2) Doing custom work to make QListBox - specifically - faster.

Other thought:

Integrate the framework from phpactiverecord.org as the most cutting edge ActiveRecord ORM with qcubed.
And make stuff like this work via reflection and not codegeneration.

Both ORMs exist peacefully next to each other without hampering performance.

See Plugin - http://trac.qcu.be/projects/qcubed/ticket/402

Cheers,
tronics

To be clear, I think our performance is mostly dropping in the instantiation methods. If we could find a faster way to convert from the returned row arrays to objects, I think we'd be fine.

Now, whether this is possible, I'm unsure. But I'm hopeful.
It may also be possible to simply have our normal data classes wrappers (which should require little to no instantiation overhead) for a more quickly instantiated DB-filled object, thereby leaving everything higher level than the data object itself blissfully unaware of the switch.

Ok, so I tested with this:

And got a run time about 1/3 that of LoadAll. I think this means there should be lots of room for improvement in our InstantiateDbRow function. Though without changing our approach, we still can't hit the 1/50th run time we would get by skipping object creation.

Tronics - the ActiveRecord plugin is extremely interesting; let's continue discussions about it in the ticket that you opened.

Vexed - do you want to spend some time on investigating performance improvements in the instantiation methods?

My biggest concern here is expands. I'm seriously unsure how to go about acheiving expands without manually parsing the row at object instantiation time. So I don't think we can switch approaches and have a wrapper around the db row object without losing expands. And I have a feeling the remaining 2/3 of the performance hit is exactly this, figuring out expands. Which may be unavoidable.

Sigh. This performance hit may be an unavoidable consequence of this ORM approach.

Ok, I ran some more tests. I commented out all the expansion code, and still got about 2/3 the runtime. So 1/3 of the overhead actually seems to be related to our DB adapter itself. Using GetColumn etc must be introducing the hit vs utilizing mysql_fetch_assoc. I'll try and test further, but this really isn't looking promising.

(We can't rely on things like mysql_fetch_assoc because it's not DB agnostic enough.)

Vexed - in the example that tronics posted above - the LoadAll() example - we saw that our stuff takes a few order of magnitudes longer than just a direct MySQL query. How about we optimize just that for now? Expands seem to be dramatically more difficult.

Ok, more testing shows that this alone is 10x slower than the raw SQL query:

So that's taking ~1/4 of our runtime all on its own.
Expantion checking is taking ~1/3
Object creation is taking ~1/3

So that's what I'm saying. So far there's nothing really _to_ optimize. We're already at the bare bones without tossing out features.

My only hope is working to reduce the Query call overhead, and regaining 1/4 our performance. That would still leave us at something like 60x slower than raw SQL though.

Thanks for investigating, Vexed.

OK; let's start with Query Call overhead. This will be a pretty significant improvement if we can get there. Plus, in cases when there is no Expansion (a significant chunk of cases in my opinion), this will deliver the best bang for the buck. Wanna look into that, Vexed?

I don't think I was clear. None of my tests actually included an expand clause.

The performance hit was due to _checking_ for any expanded tables. I imagine actually doing an expand would just slow things down more.

Interesting discussion. Maybe a profiler could give more detail about where the time is spent.

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.

Further analysis...

The only difference between the database adapters using GetColumn() is the handling of boolean values. So maybe one could call GetColumn() only if it's a boolean in InstantiateDbRow().

Otherwise the value of the column could be determined in InstantiateDbRow() like that:

Thanks @akrohn for checking with the profiler.

--

The main problem is "having objects" look down to the ORMs Propel and Doctrine performance tests.. phpactiverecord is pretty new but has a small codebase and clean code, there is no test out there yet, would be cool to include it here.

You can clearly see:
1) object generation is taking a lot of performance and should be avoided
(comparison: doctrine, hydration as array - doctrine, hydration as object)
2) how much we are lagging behind other mature ORMs like Propel and Doctrine (the later as it gives the option to not use objects).

---

ORM comparison:

Main table has 16 fields and 3578 records, related table has 25 fields and 599 records. Main table has two foreign keys to related table.
In tests with pager, paging to 10′th page with 50 record’s per page.

Select * from main table
propel 0.65384 sec.
doctrine, hydration as array 1.24309 sec.
doctrine, hydration as object 5.49193 sec.

Select * from main table with pager
propel 0.02704 sec.
doctrine, hydration as array 0.08439 sec.
doctrine, hydration as object 0.07002 sec.

Join 2 tables
propel 0.63045 sec.
doctrine, hydration as array 2.2832 sec.
doctrine, hydration as object 8.84054 sec.

Join 2 tables with pager
propel 0.01358 sec.
doctrine, hydration as array 0.1838 sec.
doctrine, hydration as object 2.43772 sec.

Find by PK
propel 0.00004 sec.
doctrine, hydration as array 0.0067 sec.
doctrine, hydration as object 0.01825 sec.

A potential "overhead" suggestion:

I have been having problems with file size being sent to the client browser, so the largest amount of data that was sent was the form state value. I moved that to a File Handler. I noticed on some files the file is only 2k (no Look-ups), but on a file with a number of lookups, the file is 100k. Is this not the place where a lot of the "overhead" is working (in the generation of the form state id)?

Regards
Corra

Well this is in any case an additional problem, eventually it is the actual reason why the orm steps through all columns, I don't know.
Nowadays a website should not take more then 100-200kb as a maximum.

The formstate adds up to 2,5 MB on my form with no particular benefit/reason eg. complexity of the relations.
That needs to be adressed also.
It seems to grow remarkably also with an increasing size of the listbox entries.

So Back- _and_ Frontend do not scale properly.
As long as you build the product it works well - as soon but as a couple of hundred dataentries flow in...

Folks - let's keep our discussion focused. Form State and the amount of stuff sent over the wire is a separate problem; I believe it's actually solved by using a File Form State Handler. If you disagree, let's open a separate thread.

A few things for you to consider:
- I don't think we should be thinking about "throwing away" the code generation aspect of QCubed and be considering replacing it with Doctrine/etc. We can create alternate ORM plugins; those are fine. One of the fundamental strengths of this framework, though, is indeed its object-relational mapping and scaffolding; so let's think about how we can improve on what WE have.
- Option for not using objects, like Propel or Doctrine: interesting; let's explore it!
- List boxes: can one of you put together a ticket with a prototype of mods we need to make the listboxes in particular faster/better? This keeps coming up again and again.

Generated 2 tickets that address these problems:

ORM performance
http://trac.qcu.be/projects/qcubed/ticket/412

QListBox enhancements
http://trac.qcu.be/projects/qcubed/ticket/413

I also posted a copy of some of these informations to the qcodo forum, hoping we would get some architecture feedback from Mike to improve on both sides.

http://www.qcodo.com/forums/forum.php/2/3969/

I think there is a way to optimize a way QListBox works. Problem with QListBox is the way it adds items. QListBox goes two times through entire list. First time to add every single item to objItemsArray, and second time to render all those items from the list.

Here is what I did. I introduced a new properties:
- DataSource (like it is for QDataGrid) - it is simply a property that wraps objItemsArray
- NameField - here is stored a name of filed that will be used to display text for every option tag. If nothing is passed, __toString() of object is called
- ValueField - a value that will be passed when SelectedItem/s is called
- intSelectedItemsArray - as I don't use QListItem at all, I have to store selected items somewhere. Those will be stored in this array. This way where is no need to go through entire list of items to mark some items selected (more speed improvement)

So, when you need to add items, you simply assign an array to DataSource. Here is simple code for creating a QListItem in MetaControl:

In 4 column table, with 7100+ rows, I got next results for standard qcubed implementation:

- 1.03804802895 for initialization of form elements
- 1.6108288765 for additional rendering of all elements (this listbox, empty textbox and buttons).

With change of code:

- 0.814762115479 for initialization of form elements
- 1.30934596062 for additional rendering of all elements (this listbox, empty textbox and buttons).

I will post my code on ticket. It is simply a prove of concept, so insert item at, option groups, and maybe some other stuff doesn't work. But it can be fixed easily.

One more think you may like. With this implementation it is possible to use Type tables as well (:

Ok, here's a really dumb question... Why are you using a listbox for more than a few entries? Who wants to put a user through selecting from such a frustrating list?

In my app, I've moved to a QDialog with a filtered datagrid in it, and a "select" column. The render simply spits out the __toString of the selected object, and a "choose" button.

No long setup, since it's a paged datagrid, no long render since it's a single entry.

And much much much easier for the user to pick the object they want.

I'm quite surprised at all the activity going on with performance in QCubed.

I've looked at a bunch of ways we could try to improve performance for 2.0, but i've never really touched on the QListBox issue, seems interesting.

>Why are you using a listbox for more than a few entries? Who wants to put a
>user through selecting from such a frustrating list?

Exactly. Why is our framework generating a UI element that is frustrating (bad User Interface choice when > then 100 rows) and slow (->LoadAll() using objects) in the first place?
And leaving no remark at all that this implicit choice will slow down your application up to x,xx seconds for every occurrence (because of ->LoadAll() using objects uncached).

Also as soon as one relies on QListBox when building the app the developer may be tempted to use ->SelectedValue and generate a lot of code that that takes serious effort to adapt to your QDialoge/QDatagrid workaround that needs to be implemented sooner or later.

Cynically you could say that Qcubed as its draft generated forms ship today shows the programmer the direct way to its poorest performance aspects instead of showing its best performance aspects through best practice choices. ;)

However on the bright side, this can be improved and hopefully it will :)

Of course that for that number of rows QAutoComplete or some other control is much better choice for use... but I see it as a challenge to optimize a way how QCubed works. (: