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

---

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

---