none
[dbo].[aspnet_Membership_GetAllUsers] stored procedure RRS feed

  • Вопрос

  • 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?

     


    • Изменено Ivan_sccs 13 января 2012 г. 14:18
    13 января 2012 г. 14:17

Все ответы