1

Closed

UPPER() in GetUserId causes scan instead of seek in SimpleMembershipProvider

description

GetUserId in WebMatrix.WebData.SimpleMembershipProvider is defined like this:
internal static int GetUserId(IDatabase db, string userTableName, string userNameColumn, string userIdColumn, string userName)
        {
            // Casing is normalized in Sql to allow the database to normalize username according to its collation. The common issue 
            // that can occur here is the 'Turkish i problem', where the uppercase of 'i' is not 'I' in Turkish.
            var result = db.QueryValue(@"SELECT " + userIdColumn + " FROM " + userTableName + " WHERE (UPPER(" + userNameColumn + ") = UPPER(@0))", userName);
            if (result != null)
            {
                return (int)result;
            }
            return -1;
        }
Consider a large system with a bigger user database. The UserId column will be the clustered index, and an additional index for the UserName column will be added.

Now using UPPER() forces a scan touching every row in the table completely disregarding the index. It might not be an issue for smaller systems, but with a sample database with 8 millon users the query takes 3.5 seconds on my machine vs 11 milliseconds without UPPER(). That will effectively bring a bigger system to a halt.

I think there is probably a carefully chosen collation already since we are able to use our own tables. If we have chosen a CI or a CS collation i think it should be respected.

If you think UPPER is neccesary, then I argue the value should be stored in uppercase from the beginning. Since it's a table that simpleMembership doesn't own, there should be an additional field userIdColumn + 'Upper'.

Even better in my opinion would be support for stored procedures, since it would give the added benefit of being able to run a system without select or update rights, only execute on a chosen schema.
Then it would be very easy to improve the tsql to suit the specific solution.
Closed Aug 19, 2013 at 10:55 PM by eilonlipton
Hi TheCarlR,

Thank you for reporting this issue. The "Simple Membership" library that was introduced with ASP.NET Web Pages was intended for relatively small-scale systems, so ease of use was the primary goal. The performance problem you have identified is certainly a real issue, but for sites using Simple Membership we think it is unlikely to be a common problem. With regard to functional issues in Simple Membership, we are focusing our efforts on the highest rated and most severe issues.

We are now working on an updated system called ASP.NET Identity that is meant to scale to any size site, and with additional advanced scenarios such as customizing the storage, schema, and other aspects of the system.

You can read a bit more on the new ASP.NET Identity system on these two sites:
http://blogs.msdn.com/b/webdev/archive/2013/06/27/introducing-asp-net-identity-membership-system-for-asp-net-applications.aspx
http://www.asp.net/vnext/overview/latest/release-notes#TOC8

Please check out the new systems and let us know if you have any feedback on them.

Thanks,
Eilon

comments