A further update on this one from today's investigation.
I've applied some varring to the Core. To be honest VarScoper is giving me
back a lot so without working out what's persisted and what's not it was
easier to just focus on the main ones I've mentioned already.
We've not applied these yet but we have switch on CF Server Monitoring and
that's brought up some interesting results.
It seems that the database (mySQL) is struggling to process some of the
queries we're firing through to it. The biggest culprit being the News
Listing Rules that are filtered by Category. Currently this type of query
(see below) is taking around a 1mins to 2mins to process.
This generated through the FAPI getContentObject() method....
select objectid, 'dmNews' as typename
from dmNews
where1=1
AND status in ('approved')
AND
(
publishDate is null
OR publishDate = '2050-01-01 00:00:00'
OR publishDate > '2111-12-08 15:51:06'
OR publishDate <= '2011-12-08 15:51:06'
)
AND
(
expiryDate is null
OR expiryDate = '2050-01-01 00:00:00'
OR expiryDate > '2111-12-08 15:51:06'
OR expiryDate >= '2011-12-08 15:51:06'
)
AND
objectid in (
selectobjectid
fromrefCategories
wherecategoryid in ('xxxxxxxx,xxxxxxx')
group byobjectid
havingcount(objectid)=2
)
ORDER BY publishdate DESC;
----
We've tried switching on some indexing but that doesn't seem to have made
any difference so a bit more delving into this we've found out the use of
IN within mySQL is a real performance hit. A good article on it here:-
http://www.artfulsoftware.com/infotree/queries.php#568
The recommendation seems to be that the queries within getContentObjects,
for mySQL anyway, should be formatted like so:-
objectid in (
*select objectid from (*
select objectid
from #application.dbowner#refCategories
where categoryid in (<cfqueryparam cfsqltype="#f.sqltype#"
list="true" value="#f.value#" />)
group by objectid
having count(objectid)=<cfqueryparam
cfsqltype="cf_sql_integer" value="#listlen(f.value)#" />
*) as tmp*
)
I'm still to test this in the project but we're certaintly seeing an
improvement when we run this SQL directly through the database.
Anyway update over, hope it's of some help
Cheers,
James
--
You received this message cos you are subscribed to "farcry-dev" Google group.
To post, email: [email protected]
To unsubscribe, email: [email protected]
For more options: http://groups.google.com/group/farcry-dev
--------------------------------
Follow us on Twitter: http://twitter.com/farcry
