Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...DNNWerk.Users giving Sql Time OutDNNWerk.Users giving Sql Time Out
Previous
 
Next
New Post
5/15/2013 2:39 AM
 

Hi,

I have the DNNWerk.Users module installed on one of the pages and the one installed on our live site now gives the error 'DotNetNuke.Services.Exceptions.PageLoadException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.Data.SqlClient.SqlException: Timeout expired

I have added the timeout in the web.config ';Connect Timeout=240' in both of the connection strings but no luck.

I have located the issue to the stored procedure [dbo].[dnn_dnnWerk_GetUserList] which is called on line no 2377 in uc_Manage.ascx.vb which takes about 40 seconds to run when ran direct from sql server.  We only have just over 400 users and I would have thought that dnnwerk.users would be able to work with more users than that.

Anybody any ideas, have more users and all is okay or has experience of the issue ?

Regards

Jacqui

 
New Post
5/15/2013 6:36 AM
 
Jacqui,

what happens if you run the following query from Host::SQL?

DECLARE @RoleId int = 1
DECLARE @PortalId int = 0
DECLARE @SearchText nvarchar(256) = ''
DECLARE @SearchCols nvarchar(256) = 'UserName'
DECLARE @ShowDeleted bit = 0

SELECT DISTINCT
U.UserID,
U.Username,
U.FirstName,
U.LastName,
U.DisplayName,
UP.CreatedDate,
U.Email,
UP.PortalId,
(Select Top 1 PropertyValue From {databaseOwner}{objectQualifier}UserProfile Inner Join {databaseOwner}{objectQualifier}ProfilePropertyDefinition On {databaseOwner}{objectQualifier}ProfilePropertyDefinition.PropertyDefinitionID = {databaseOwner}{objectQualifier}UserProfile.PropertyDefinitionID Where {databaseOwner}{objectQualifier}ProfilePropertyDefinition.PropertyName = 'PostalCode' and {databaseOwner}{objectQualifier}UserProfile.UserID = U.UserID) as 'PostalCode',
(Select Top 1 PropertyValue From {databaseOwner}{objectQualifier}UserProfile Inner Join {databaseOwner}{objectQualifier}ProfilePropertyDefinition On {databaseOwner}{objectQualifier}ProfilePropertyDefinition.PropertyDefinitionID = {databaseOwner}{objectQualifier}UserProfile.PropertyDefinitionID Where {databaseOwner}{objectQualifier}ProfilePropertyDefinition.PropertyName = 'City' and {databaseOwner}{objectQualifier}UserProfile.UserID = U.UserID) as 'City',
(Select Top 1 PropertyValue From {databaseOwner}{objectQualifier}UserProfile Inner Join {databaseOwner}{objectQualifier}ProfilePropertyDefinition On {databaseOwner}{objectQualifier}ProfilePropertyDefinition.PropertyDefinitionID = {databaseOwner}{objectQualifier}UserProfile.PropertyDefinitionID Where {databaseOwner}{objectQualifier}ProfilePropertyDefinition.PropertyName = 'Country' and {databaseOwner}{objectQualifier}UserProfile.UserID = U.UserID) as 'Country',
(Select Top 1 PropertyValue From {databaseOwner}{objectQualifier}UserProfile Inner Join {databaseOwner}{objectQualifier}ProfilePropertyDefinition On {databaseOwner}{objectQualifier}ProfilePropertyDefinition.PropertyDefinitionID = {databaseOwner}{objectQualifier}UserProfile.PropertyDefinitionID Where {databaseOwner}{objectQualifier}ProfilePropertyDefinition.PropertyName = 'Telephone' and {databaseOwner}{objectQualifier}UserProfile.UserID = U.UserID) as 'Telephone',
(Select Top 1 PropertyValue From {databaseOwner}{objectQualifier}UserProfile Inner Join {databaseOwner}{objectQualifier}ProfilePropertyDefinition On {databaseOwner}{objectQualifier}ProfilePropertyDefinition.PropertyDefinitionID = {databaseOwner}{objectQualifier}UserProfile.PropertyDefinitionID Where {databaseOwner}{objectQualifier}ProfilePropertyDefinition.PropertyName = 'Cell' and {databaseOwner}{objectQualifier}UserProfile.UserID = U.UserID) as 'Cell',
(Select Top 1 PropertyValue From {databaseOwner}{objectQualifier}UserProfile Inner Join {databaseOwner}{objectQualifier}ProfilePropertyDefinition On {databaseOwner}{objectQualifier}ProfilePropertyDefinition.PropertyDefinitionID = {databaseOwner}{objectQualifier}UserProfile.PropertyDefinitionID Where {databaseOwner}{objectQualifier}ProfilePropertyDefinition.PropertyName = 'PreferredLocale' and {databaseOwner}{objectQualifier}UserProfile.UserID = U.UserID) as 'PreferredLocale'
FROM
{databaseOwner}{objectQualifier}Users U
INNER JOIN {databaseOwner}{objectQualifier}UserPortals UP on UP.UserId = U.UserID
INNER JOIN {databaseOwner}{objectQualifier}UserProfile P on p.UserID = U.UserID
INNER JOIN {databaseOwner}{objectQualifier}ProfilePropertyDefinition PD on PD.PropertyDefinitionID = p.PropertyDefinitionID
WHERE
UP.PortalId = @PortalId
AND
((UP.IsDeleted = 0 and @ShowDeleted = 0) or (UP.IsDeleted = 1 and @ShowDeleted = 1))
AND
(
(
(P.PropertyValue Like '%' + @SearchText + '%' And (PatIndex('%' + Pd.PropertyName + ',%' , @SearchCols) > 0))
or
(
(U.FirstName Like '%' + @SearchText + '%' and PATINDEX('%FirstName%', @SearchCols) > 0)
OR
(U.LastName Like '%' + @SearchText + '%' and PATINDEX('%LastName%', @SearchCols) > 0)
OR
(U.DisplayName Like '%' + @SearchText + '%' and PATINDEX('%DisplayName%', @SearchCols) > 0)
OR
(U.Email Like '%' + @SearchText + '%' and PATINDEX('%Email%', @SearchCols) > 0)
OR
(U.Username Like '%' + @SearchText + '%' and PATINDEX('%Username%', @SearchCols) > 0)
)
)
or @SearchText is Null
)
AND
(
(U.UserID in(Select UserId from {databaseOwner}{objectQualifier}UserRoles where RoleID = @RoleId))
or
(@ShowDeleted = 1)
)
Order By
UP.CreatedDate desc


(This should return all non-deleted Registered Users from Portal 0 (if you have another PortalID, please change it, and also change the RoleID for the registered users).

It is exaclty the query of the Stored Procedure that is used by dnnWerk.Users, and for 870+ users, it takes ~4 seconds at my place.

Best wishes
Michael

Michael Tobisch
DNN★MVP
dnn-Connect.org - The most vibrant community around the DNN-platform
 
New Post
5/15/2013 8:38 AM
 
I just got page refresh in the host:sql when I tried to run and no error, however when I run the query in SQL server but use the portal and role id that is appropriate it takes just over 40 seconds.
Could it be a data error perhaps, or somebody has suggested I get fulltextsearch turned on the database by our server team.
Jacqui.
 
New Post
5/15/2013 10:36 AM
 
Jacqui,

I think there is a performance problem with your SQL Server. If you have access, please check the EventLog table's size - and try to find out if there are other large tables.

Maybe your recycle bin is very full - in this case, just try to delete it.

Check the scheduler - are there any tasks that need very long time or fail?

Best wishes
Michael



Michael Tobisch
DNN★MVP
dnn-Connect.org - The most vibrant community around the DNN-platform
 
New Post
5/15/2013 10:28 PM
 
Hi,
Thanks for your support here.
I got a copy of the database on my local machine, ran the query and still took 40 seconds.
Installed FullTextSearch and got it down to 19 seconds so a good improvement there.
Will ask the IT Guys if they mind installing it on the live database and see.
Still 19 seconds is a long time so will look for further improvements.
I have checked the recyle bin and that is okay.
Got a list of tables and table sizes, the largest 5 are below.

TableName RowCounts TotalSpaceKB UsedSpaceKB UnusedSpaceKB
dnn_UserProfile 8867 1240 1144 96
dnn_Packages 150 1208 1176 32
act_reference_codes 3314 1040 928 112
act_positions_history 2856 792 784 8
dnn_HtmlText 89 680 632 48

Regards
Jacqui.
 
Previous
 
Next
HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...DNNWerk.Users giving Sql Time OutDNNWerk.Users giving Sql Time Out


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.

Content Layout

Subscribe to DNN Digest

Subscribe to DNN Digest

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  


Copyright 2017 by DNN Corp Terms of Use Privacy
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out