CODEWITHSUNDEEP
asp.net-mvcasp.net-web-apiazure-sql-databasesimplemembership
Raph
Raph

Reputation: 384

User SELECT extremely slow in .NET MVC with SimpleMembershipProvider

I built a .NET MVC4 / Webapi2 app which uses SimpleMembershipProvider for authentification. Since yersterday, all the app is extremely slow, so I monitored it with NewRelic. The database uses up 95% of the web request, and the following SQL request accounts for 99% of the database time consumption : 

SELECT [Id] FROM [User] WHERE (UPPER([UserGuid]) = UPPER(@?))

I suppose this is called by :

WebSecurity.CurrentUserId

There is 550 000 rows in the User Table.

Do you know what makes this request so slow ?

Upvotes: 1

Views: 195

Answers (1)

Stan
Stan

Reputation: 1999

The problem is that for every query it is scanning 550K of records. Of course this will be extremely slow. The first step is to define index for the UserGuid field if you haven't. 

CREATE INDEX
    UX_User_UserGuid
ON [User] (
    [UserGuid]
);

When defined, check if it helps. 99% that it doesn't because have to calculate UPPER call for every record. So the second step is to ensure, that UserGuid has case-insensitive collation and therefor doesn't need UPPER calls.

SELECT
    collation_name
FROM
    sys.columns
WHERE
    object_id = OBJECT_ID('User')
    AND name = 'UserGuid'

Your result has to contain "CI", that means "Case-Insensitive". If no, you need to change collation.

The third problem is inside SimpleMembershipProvider. UPPER call is hardcoded, so you have to replace it with your own implementation. Smth liked this:

public class MyCustomProvider : WebMatrix.WebData.SimpleMembershipProvider
{
    public override MembershipUser GetUser(string username, bool userIsOnline)
    {
        int userId = /*
            get from your database without any UPPER calls
            just like "SELECT [Id] FROM [User] WHERE [UserGuid] = @?"
        */

        return this.GetUser(userId, userIsOnline);
    }
}

Upvotes: 4

Related Questions