Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've recently taken over software development for a small company. My mandate includes managing our SQL stuff, which is something I don't really know much about, and I don't think the guy before me knew much about it either - the whole setup seems to be a bit of a mess.

I'm trying to improve the speed of a complex search of user accounts, which is taking 5-10 seconds, which seems a lot for 750,000 records (apparently, this not considered a big table.)

For some reason, there are two tables which contain user details:

Aspnet_Users
    UserId uniqueidentifier (Primary Key)
    Username nvarchar(256)
    .... (other stuff)

Users
    UserId uniqueidentifier (Primary Key)
    FirstName nvarchar(50)
    Surname nvarchar(50)
    Email nvarchar(256)
    ... (other columns related to our business)

Note that Users.UserId has no relationship to Aspnet_Users.UserId, even though users.userid should probably be a foreign key of aspnet_users.userid. I don't know if this matters.

There are more tables referenced in the search query, but the bulk of the query time seems to be spent on merging the two tables above: 90%.

The generated execution plan looks like this:

Clustered Index Scan [aspnet_Users].[aspnet_users_index] Cost: 13%
|
|
|
V
Sort Cost: 50%
|
|                Clustered Index Scan (Clustered) [Users].[PK_user_details] Cost:23%
|                |
V                V
Merge Join (Inner Join) 4%
|
|
|
V
(Lots more stuff, none of which is very expensive)

As you can see, most of the time is taken up by that sort.

I don't know that much about SQL, so I don't really know how to make go faster. I tried making a View that included most of the tables used in this query, but it didn't seem to help.

For information, here is the full search query. I think that the problems I am having aren't really related to the complexity of this particular query, though.

ALTER PROCEDURE [dbo].[sp_FTSearchLocation] 
     @SearchFor nvarchar(200)
     ,@SearchInLat Decimal(18,15)
     ,@SearchInLng Decimal(18,15)
     ,@SearchActivity int
     ,@StartRow int
     ,@EndRow int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * FROM (
    SELECT TOP (@EndRow)
        FT_TBL.CompanyName, FT_TBL.Email, FT_TBL.UserId, aspnet_Users.UserName as Handle,
        FT_TBL.ServicePoint1, FT_TBL.ServicePoint2, FT_TBL.ServicePoint3,
        FT_TBL.ServicePoint4, FT_TBL.ServicePoint5, FT_TBL.ServicePoint6,
        Addresses.Building, Addresses.Street, Addresses.City, Addresses.Region,
        Addresses.Postcode, Addresses.Latitude, Addresses.Longitude, Activities.Activity,
        vw_Ratings.Rating, FT_TBL.IsFromPaidBusinessDB,FT_TBL.ActivityID,
        ROW_NUMBER() OVER (ORDER BY vw_Ratings.Rating DESC,
        FT_TBL.CompanyName  ) AS num

    FROM
        Users as FT_TBL INNER JOIN
        aspnet_Users ON FT_TBL.UserId = aspnet_Users.UserId INNER JOIN
        Addresses ON FT_TBL.UserId = Addresses.UserId INNER JOIN
        Activities ON FT_TBL.ActivityID = Activities.ActivityID INNER JOIN
        Country ON Addresses.CountryISO = Country.Iso LEFT OUTER JOIN
        vw_Ratings ON FT_TBL.UserId = vw_Ratings.UserId

    WHERE
        ( -- filter for all queries --
                (FT_TBL.IsActive = 1)
            AND (FT_TBL.IsPro = 1)
        )       
        AND ( -- filter for 'searchfor' --
               (@SearchFor IS NULL)
            OR (FT_TBL.CompanyName like '%' + @SearchFor + '%') 
            OR (aspnet_Users.UserName like '%' + @SearchFor + '%')
            OR (Activities.Activity like '%' + @SearchFor + '%')
        )
        AND ( -- filter for 'searchIn'
                (
                    (@SearchInLat is null)
                    OR (@SearchInLng is null)
                )
                OR (    
                    (Addresses.Latitude IS NOT NULL)
                    AND (Addresses.Longitude IS NOT NULL)
                    AND ( (Addresses.Latitude - @SearchInLat)  BETWEEN -0.5 AND 0.5)
                    AND ( (Addresses.Longitude - @SearchInLng) BETWEEN -0.5 AND 0.5)
                )
            )                       
        AND ( -- filter by activity --
               (@SearchActivity IS NULL)
            OR (@SearchActivity = Activities.ActivityID)
        )

    ORDER BY 
        vw_Ratings.Rating DESC,
        FT_TBL.CompanyName  
    ) As a
    WHERE num > @StartRow

    OPTION (RECOMPILE)

END

Can anyone suggest a way to make this faster?

EDIT: Pastebin of xml wexecution plan

Plan

share|improve this question
    
The Aspnet_Users table is a standard table if you use the SQL Server membership provider in ASP.NET BTW so they probably just left that one alone and put all your custom additional information in another table. –  Martin Smith Jan 23 '12 at 12:29
    
@MartinSmith I figured that out. To me it seems like it shouldn't just be left alone and only tangentally linked to the other user tables though, but I don't know if this actually makes a performance difference. –  Oliver Jan 23 '12 at 12:31
    
The search conditions with leading wildcards and on location aren't sargable (can use an index). Which is probably why performance is so bad. What version of SQL Server are you on? –  Martin Smith Jan 23 '12 at 12:32
    
@MartinSmith I kind of thought it might be that, but then I looked at the execution plan and it said that 90% of time is spent merging these two tables. The scan on Users.PK_User_Details has a predicate related to the search, but the big hitter, the sort, doesn't. We're writing for SQL Server 2005, because some of our clients still use this. –  Oliver Jan 23 '12 at 12:35
1  
You could CREATE NONCLUSTERED INDEX ix ON [testdb].[dbo].[aspnet_Users](UserId) INCLUDE (UserName) to avoid the sort and a scan of this would entail less IO as well. You might also consider denormalizing and storing the username in your other table to avoid the need to join at all. Edit: Actually I assume aspnet_Users already has a unique index on UserId so maybe just drop that and replace it with a new unique index with that included column. –  Martin Smith Jan 23 '12 at 13:18

1 Answer 1

up vote 3 down vote accepted

Looking at the definition of aspnet_Users from here it appears to be a heap with a non clustered primary key on UserId.

You might consider altering this to be a clustered primary key. You will need to then consider issues such as an appropriate FILL_FACTOR to use and frequency with which to defragment but it seems more useful for the joins against your other user table.

Alternatively if the only column you ever care about is username then you could drop the primary key and replace it with a unique index instead.

CREATE UNIQUE NONCLUSTERED INDEX ix ON [dbo].[aspnet_Users](UserId) INCLUDE (UserName) 

This would require dropping and recreating any Foreign Constraints referencing aspnet_Users

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.