So many DB queries

Permalink
Why did the system generates so many hits to the database when you load the main page. Receives about 3 pages of requests under the built-event log.

DreamCast
View Replies:
andrew replied on at Permalink Reply
andrew
Certain blocks, like the autonav block and the page list block, generate a lot of queries, as they not only run queries to grab the page list, but also run queries on instantiating the Page object (to get its name, all its data, etc...)

Additionally, there are multiple permissions checks going on, and things of that nature.

C5 will soon be featuring an object cache for this very reason.
DreamCast replied on at Permalink Reply
DreamCast
great idea? i`ll be waiting4 that
ScottC replied on at Permalink Reply
ScottC
If it is core and soon then I will wait, if I have to buy it i might just make a bounty, is that allowed?
andrew replied on at Permalink Reply
andrew
that it will make it into the core. I will take to Franz about bounties - although I think this is low level enough that we might keep it in house.
frz replied on at Permalink Reply
frz
Yeah we're thinking about this as part of the core stuff in 09. It's not a huge priority for us right now because while we certainly admit there's a lot of DB queries for some of those blocks, decently configured webservers don't seem to have an issue with it on anything but the highest traffic'd websites. If you're building something that's getting national TV spots, you would probably have enough unique needs and budget that you would make a custom version of the auto-nav that didn't have as much flexibility, and as many queries.

I agree with Andy tho, this is something we want to do, and we want to do it right, so we'll probably keep it internal. There's some thoughts around making views/feeds so javascript/actionscript developers can get at nice XML about data in areas or from a collection down, and I see that work as overlapping the caching work.
macgillivary replied on at Permalink Reply
macgillivary
I'm not complaining - really liking what I'm seeing so far with the functionality - still getting around it all - had been toying with typolight for a bit.

Coming from a somewhat big company where although adding more resources was possible and frequently done, there were a few folks who really pushed efficient use of sql.

I have noticed that in some places (haven't dug too deep yet) that the practice of
select ... from
for each row in select query
insert into ... values
end for

is utilized instead of one query of
insert into ... select from...

Example:
line 185 or so of ./concrete/models/page.php

could be replaced with something like:
$v = array($this->cID, $this->getPermissionsCollectionID());
$q = "insert into PagePermissions (cID, uID, gID, cgPermissions, cgStartDate, cgEndDate) select '?', uID, gID, cgPermissions, cgStartDate, cgEndDate from PagePermissions where cID = ?";
$r = $db->query($q, $v);


Its a small example that illustrates the point. It may be that the data abstraction layer dictates the earlier method.

For my purposes on some very low volume pages all served by a dedicated mysql machine, I'm not too worried about it. If I was running a wicked popular site I would hunt down these types of queries. Then again, its in the backend so perhaps not much of an issue at all.

Keep up the good work.