Forum for any topic of interest to the Qcodo community, including questions about the framework itself.
Qcodo queries take a long time in version 0.4.x as compared to that of version 0.3.x. Any ideas?
not understand the problem, it is a matter of performace are you saying?
Have you done any profiling to see where the problem is coming from?
It is a matter of performance.
I have 2 versions of a code with slightly different schema.
Version 1 is using qcodo 0.3.x.
Version 2 is using qcodo 0.4.x
I have made a sql patch to migrate version 1 to version 2.
Now, in version 2, qcodo queries of the form QueryArray run a lot slower than than that in version 1.
Same questions apply.
From your post, it's clear that it's not just the exact same code in v0.4.x vs v0.3.x, since you're using a slightly different schema.
Also, what sort of SQL patch did you apply?
Anyway, bottom line is that the same questions asked above still apply. Have you profiled, what specific queries are slow, where is the performance bottleneck (on the App layer or in the MySQL layer), etc.
The SQL patch just converts the old code(Qcodo 0.3.x) database structure to the new code(0.4.x) database structure. I did this by first examining a diff between the schema structure of both the codes. Then, I wrote queries to convert the old code schema to the new code schema and executed the same.
The query which takes about 15s is:
$objDocuments = ComplianceDocuments::QueryArray(
When I profile the code, the following is noticed(time in milliseconds):
Function InvocationCount TotalSelfCost TotalInclusiveCost
ComplianceDocumentsGen::InstantiateDbRow 2916 4051 10817
QMySqliDatabaseRow->GetColumn 85299 2634 7100
QType::Cast 34953 446 2608
QType::CastValueTo 34585 1070 1098
ComplianceDocumentsGen::InstantiateDbResult 35 1069 12171
QDateTime->__construct 8752 969 1793
php::mysql_query 530 444 444
php::DateTime->__construct 17506 310 310
UsersGen::InstantiateDbRow 140 279 462
QQNode->GetColumnAlias 1197 209 475
GroupsGen::InstantiateDbRow 250 209 383
QQueryBuilder->AddSelectItem 5036 174 233
QApplicationBase::SetErrorHandler 8751 169 189
QApplicationBase::RestoreErrorHandler 8751 164 165
php::date 8751 133 133
DashboardForm->Form_Create 1 111 15793
QMySqliDatabaseResult->GetNextRow 3637 102 182
UsersGen::QuerySingle 105 101 67
As we can see, InstantiateDbRow, QMySqliDatabaseRow->GetColumn and InstantiateDbResult are taking too much time?
The old code (qcodo 0.3.x) which runs faster has InnoDB tables. The new code(qcodo 0.4.x) has MyISAM tables. This doesnt matter as I checked by running the new code (qcodo 0.4.x) on InnoDB tables
So just to confirm: the query itself is not necessarily taking a long time, but it's the instantiation of the objects?
If unsure, I would recommend querying (e.g. within the MySQL console itself) to ensure that the query itself isn't taking too much time. Taking a look at your query, it does look like you are doing quite a bit of logic across a fairly large hierarchy... so depending on how many rows you are doing -to-Many joins across, MySQL could be bottlenecked.
Regardless, if you are sure that the raw query itself is not a bottleneck, then the next question would be: how many rows are being returned by your query, and what are you doing with all those objects?
So for example, if you are returning 10000 rows (and thus instantiating 10000 objects), and all you are doing is something like a foreach on each object to perform something quick, then you might want to consider using QueryCursor and InstantiateCursur instead of QueryArray.
This forum post on QueryCursor should hopefully be of help.
The instantiation of the objects take time.
Using query cursor instead of query array does not reduce the time significantly.
I fit in with your conclusions and looking forward to your updates.