If it's the front-end, then it depends on what is happening in the templates/macros, e.g. using the Document object API would/could do this, so not advised.
For the back-office, I'd say yes, it is usual for a lot of SQL queries - especially when loading/editing/saving content/media. Are you noticing major performance impacts?
There is caching - in the Developer section, there is a Cache Browser which can show you what types of objects are being cached. If this isn't caching anything, then that *might* be a reason for the poor performance?
Think you've nailed it with the uncached 'GetMember' call. I don't know much about how the Razor macro engine does it's "magic", best person to speak with about that is Gareth Evans, (not sure how much he's on the forum, but he's on Twitter).
Sorry I can't help more... you wouldn't want me suggesting to use XSLT? ;-)
Hi. Yes, as it seems it's because the "HasAccess" property requests the current member object every time being called. I suppose you can workaround that by getting at first the list of visible pages with Children.Where("Visible") and than filtering it with some lower-level method that wouldn't require calling GetMember inside its implementation (i.e. one of Access class' methods). Also, actually, navigation rendering macros are usually good candidates for caching themselves (since usually site structure changes much more seldom than content), so, perhaps simple macro caching could help.
Sorry for the delayed reply. Nice catch, no that's not normal. I am not sure how I can optimise it though, I guess the umbraco membership provider could have some short term caching added?
The .Where magic is actually an string=>expressiontree generator, at the end of the day it generates an expression tree which evaluates to a true/false result. Within that, it makes multiple calls to DynamicNode TryGetMember (as in field/property, not login member) to get the data for the fields used in the predicate.
In this case, because the HasAccess evaluates to DynamicNode.HasAccess this is the property getter that should be optimised to speed this particular where clause up.
One of the other guys had issues with his menu being slow on twitter, i'll hit him up and see if he was using HasAccess too.
The actual problem is not the HasAccess itself but the membership framework. By design the forms authentication stores in the cookie only the user name and each time the application wants any additional user data (like an e-mail or the ID) it has to go to the database for it. As I mentioned in some other thread here recently I'm expecting to get some problem with it in my current project, but I suppose a probable solution can be to build quite a simple custom membership provider on the top of umbraco's just to add some caching functionality to its calls.
Please skype me on agrath and I'll send you a nightly dll to test - I just added a bit of caching and dropped sql queries on one of my in-development sites from 60 to 12 (per page load)
I haven't committed the changes to trunk yet as I was hoping to have the changes (which I've only just coded in the last hour) tested on a few sites first. Could I send you some nightly dlls instead or do you have other changes that are in a custom build?
Has this been fixed in 4.7.2? Currently I've got a 4.7.1.1 website and I'm using the Cultiv Search Engine Sitemap which uses .Where("HasAccess"). This makes generating the xml sitemap very slow.
Lot's of SQL queries
Hi,
Is it normal for Umbraco 4.7.1 to fire more than 150 queries for each page-load?
A lot of queries are the same too:
SELECT id, createDate, trashed, parentId, nodeObjectType, nodeUser, level, path, sortOrder, uniqueID, text FROM umbracoNode WHERE id = @id
SELECT Email, LoginName, Password FROM cmsMember WHERE nodeId=@nodeId
SELECT nodeid FROM cmsContentType WHERE alias = @alias
It looks like it does'nt cache at all.
Anyone has any clue on how to fix this?
Thnx!
Hi Floris,
Is this on the front-end or back-office?
If it's the front-end, then it depends on what is happening in the templates/macros, e.g. using the Document object API would/could do this, so not advised.
For the back-office, I'd say yes, it is usual for a lot of SQL queries - especially when loading/editing/saving content/media. Are you noticing major performance impacts?
There is caching - in the Developer section, there is a Cache Browser which can show you what types of objects are being cached. If this isn't caching anything, then that *might* be a reason for the poor performance?
Cheers, Lee.
Hi Lee,
Thnx for your reply!
This is a front-end issue. I can see a lot of caching in the backoffice cache browser, so stuff is being cached.
I'm using a lot of Dynamic razor stuff. Stuff like:
I'm not sure if this is actually the Document object API.
Is there a better way of doing this?
Also, I saw that in version 4.7.1 members aren't cached. When using the following statement in Razor
It creates a members and runs the GetMember query for every page.
Hi Floris,
Think you've nailed it with the uncached 'GetMember' call. I don't know much about how the Razor macro engine does it's "magic", best person to speak with about that is Gareth Evans, (not sure how much he's on the forum, but he's on Twitter).
Sorry I can't help more... you wouldn't want me suggesting to use XSLT? ;-)
Cheers, Lee.
Hi. Yes, as it seems it's because the "HasAccess" property requests the current member object every time being called. I suppose you can workaround that by getting at first the list of visible pages with Children.Where("Visible") and than filtering it with some lower-level method that wouldn't require calling GetMember inside its implementation (i.e. one of Access class' methods). Also, actually, navigation rendering macros are usually good candidates for caching themselves (since usually site structure changes much more seldom than content), so, perhaps simple macro caching could help.
Sorry for the delayed reply.
Nice catch, no that's not normal.
I am not sure how I can optimise it though, I guess the umbraco membership provider could have some short term caching added?
The .Where magic is actually an string=>expressiontree generator, at the end of the day it generates an expression tree which evaluates to a true/false result.
Within that, it makes multiple calls to DynamicNode TryGetMember (as in field/property, not login member) to get the data for the fields used in the predicate.
In this case, because the HasAccess evaluates to DynamicNode.HasAccess this is the property getter that should be optimised to speed this particular where clause up.
One of the other guys had issues with his menu being slow on twitter, i'll hit him up and see if he was using HasAccess too.
Gareth
The actual problem is not the HasAccess itself but the membership framework. By design the forms authentication stores in the cookie only the user name and each time the application wants any additional user data (like an e-mail or the ID) it has to go to the database for it. As I mentioned in some other thread here recently I'm expecting to get some problem with it in my current project, but I suppose a probable solution can be to build quite a simple custom membership provider on the top of umbraco's just to add some caching functionality to its calls.
Hi Rodion
Please skype me on agrath and I'll send you a nightly dll to test - I just added a bit of caching and dropped sql queries on one of my in-development sites from 60 to 12 (per page load)
Gareth
Hi Gareth,
Could you send me some Mercurial Patch files for those changes? I'm working on a heavy website and this is becoming an issue :(
Floris
Floris,
I haven't committed the changes to trunk yet as I was hoping to have the changes (which I've only just coded in the last hour) tested on a few sites first.
Could I send you some nightly dlls instead or do you have other changes that are in a custom build?
Gareth
I do have some other things in my custom build (like this one: http://umbraco.codeplex.com/SourceControl/network/forks/florisrobbemont/MacroCachingHeavyLoad) and some other stuff. I tested my code and it's stable, but didn't had the time to create a fork and a pull request.
Could you send me your modified cs files in a rar? I could implement the changes by merging the files with my custom build :)
Please DM me on twitter your email address, I'll send you a diff (I think I generated that correctly)
--Edit,
Think I just found your email in my address book and sent you the patch
Has this been fixed in 4.7.2? Currently I've got a 4.7.1.1 website and I'm using the Cultiv Search Engine Sitemap which uses .Where("HasAccess"). This makes generating the xml sitemap very slow.
Jeroen
@Jeroen:
The problem lies with the Membership provider. There is a fix however, but it's quite intrusive.
Gareth wrote a ShorTermCacheProvider which caches all content and member access for about 5 seconds (enough for 1 request).
I have a custom build 4.7.2 here which has that extra option. If you want I can send it to you :)
Hi Floris,
I hoped that there would have been a proper fix. It's only for the xml sitemap so speed isn't really important. I'll leave it for now, but thanks :).
Jeroen
is working on a reply...