I've got an Umbraco 6.1.6 site that is running with a large number of members loaded into it. We've been seeing poor database performance for a while, but it seems now I'm doing some tracing that at times when the database is under most load (with little traffic on the site) that something is constantly querying the database with user login queries.
So for example I ran a trace earlier for about 5 seconds. During that time the following statements were executed against the database 549 times:
exec sp_executesql N'select count(nodeID) as tmp from cmsMember where LoginName = @loginName',N'@loginName nvarchar(30)',@loginName=N'r*********x@*********.ac.uk'
exec sp_executesql N'SELECT Email, LoginName, Password FROM cmsMember WHERE nodeId=@nodeId',N'@nodeId int',@nodeId=14545
exec sp_executesql N'SELECT id, createDate, trashed, parentId, nodeObjectType, nodeUser, level, path, sortOrder, uniqueID, text FROM umbracoNode WHERE id = @id',N'@id int',@id=14545
exec sp_executesql N'select nodeID from cmsMember where LoginName = @loginName',N'@loginName nvarchar(24)',@loginName=N'r*********x@*********.ac.uk'
Obviously at different times differnt logins will appear, sometimes this same group of statements execute for multiple different logins over, and over again.
Is this on an extranet? We had site which was doing crazy stuff, the dev was newing up a member eachtime on the page he wanted to write anything about the member after they had logged in, also it was being hit by alot of members in fact the whole site bar the home page was an extranet.
Take a look at the code and see where you are getting Member via the api and writing out member data. Also take a look at access to a member edit page so are people saving their data regularly? I would look at replacing the get member code via api to get member via examine it will be alot quicker.
Site is constantly generating login queries to db
I've got an Umbraco 6.1.6 site that is running with a large number of members loaded into it. We've been seeing poor database performance for a while, but it seems now I'm doing some tracing that at times when the database is under most load (with little traffic on the site) that something is constantly querying the database with user login queries.
So for example I ran a trace earlier for about 5 seconds. During that time the following statements were executed against the database 549 times:
Paul,
Is this on an extranet? We had site which was doing crazy stuff, the dev was newing up a member eachtime on the page he wanted to write anything about the member after they had logged in, also it was being hit by alot of members in fact the whole site bar the home page was an extranet.
Regards
Ismail
That makes a lot of sense Ismail -- it's used by all staff as an intranet, and also by most customers as an extranet.
That doesn't explain why it's doing it 500 for the same user in 5 seconds, though does it?
Paul,
Take a look at the code and see where you are getting Member via the api and writing out member data. Also take a look at access to a member edit page so are people saving their data regularly? I would look at replacing the get member code via api to get member via examine it will be alot quicker.
Regards
Ismail
is working on a reply...