Hello.
By default the aspnet_Membership_GetAllUsers stored procedure contains the following code:
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId uniqueidentifier
)
-- Insert into our temp table
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u
WHERE u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
ORDER BY u.UserName
SELECT @TotalRecords = @@ROWCOUNT
SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate,
m.LastLoginDate,
u.LastActivityDate,
m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,
m.LastLockoutDate
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND
p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
ORDER BY u.UserName
My question is, why the temp table is used here? It becomes much faster if I change it to:
SELECT @TotalRecords = COUNT(u.UserId)
FROM dbo.aspnet_Membership m (NOLOCK), dbo.aspnet_Users u (NOLOCK)
WHERE u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
SELECT
u.UserName,
m.Email,
m.PasswordQuestion,
m.Comment,
m.IsApproved,
m.CreateDate,
m.LastLoginDate,
u.LastActivityDate,
m.LastPasswordChangedDate,
u.UserId,
m.IsLockedOut,
m.LastLockoutDate
FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY u.UserName) AS IndexId,
u.UserName
FROM dbo.aspnet_Membership m (NOLOCK), dbo.aspnet_Users u (NOLOCK)
WHERE u.UserId = m.UserId AND u.ApplicationId = @ApplicationId AND m.ApplicationId = @ApplicationId)
AS p INNER JOIN dbo.aspnet_Users u ON u.UserName = p.UserName INNER JOIN dbo.aspnet_Membership m ON m.UserId = u.UserId
WHERE
IndexId >= @PageLowerBound AND IndexId <= @PageUpperBound
What drawbacks can you see in such change?
