Theoretical search function in SQL/C#

Question

I'm implementing a user search function in SQL/C#. I'm having a bit of trouble with the logistics of the function itself and I'm looking for some guidance.

I've been using a triple-nested subquery, which I feel is running a bit slow (avg 280-300ms to pull results from 1000 records in a Users table).

I first want to search by attributes for users (a separate table), then search based on location. I then want distance calculated and sorted, then only the closest 10 records brought back at a time (paged results).

Am I using the correct approach using a triple nested subquery? Or is there a standard or guiding principle to do this sort (pun not intended) kind of thing?

Code sample:

SELECT * FROM
    (SELECT *, ROW_NUMBER()                 
        OVER (ORDER BY UsersSubquery.Distance ASC) as RowNumber  
        FROM

    (

        SELECT TOP 100 PERCENT UsersSubquery.*, 

         (((geography::Point(UsersLocationsSubquery.Latitude, UsersLocationsSubquery.Longitude, 4326)).STDistance(@a)) / 1000) AS Distance      

        FROM Users UsersSubquery 

            INNER JOIN UsersAndAttributes ON UsersSubquery.UserId = UserAndAttributes.UserId

            INNER JOIN UsersAndLocations AS UsersWithLocationsSubquery  ON UsersSubquery.UserId = UsersWithLocationsSubquery.UserId

        WHERE 
        ( 

            ((@Attribute1 = NULL) OR (UsersSubquery.Attribute1Id = @Attribute1)) 
            AND
            ((@Attribute2 = NULL) OR (UsersSubquery.Attribute2Id = @Attribute2)) 
            AND
            ((@Attribute3 = NULL) OR (UsersSubquery.Attribute3Id = @Attribute3)) 
            AND
            ...etc

        )


    )

    AS UsersSubquery )

Users

    INNER JOIN Pictures ON Users.UserId = Pictures.UserId

WHERE RowNumber >= @StartRow and RowNumber <= @EndRow 

Order by RowNumber

Answer 1

Because you are wanting to page the data which means you need to filter on the ROW_NUMBER() value which needs to be ordered by the Distance calculation you are going to need the 3 nested sub-queries. However, I do think it can be done a bit more cleanly.

I'm not sure why you're taking the TOP 100 PERCENT from one of you your inner queries since that isn't actually going to do anything. I can only assume at one point you were ordering that inner query and using the TOP clause to make it not error out. That doesn't work and the order of the results is not guaranteed. So it is good it looks like you've gone a different way since then.

The way you've used aliases in your query is also a bit confusing. In the innermost query you've aliased the actual User table as UsersSubquery and then you've aliased a later subquery with the same name. Finally you've aliased the outer-most sub-query as Users which is the same name as the base table.

There is no reason not to do your join to the Pictures table at the same time you're doing the other joins. There isn't any additional filtering between that join and the innermost query (which has the WHERE clause that filters the user attributes) so doing the join there would be the same thing.

This is how I would write the query:

DECLARE @RowsToFetch INT = 10;

SELECT TOP (@RowsToFetch) *
FROM (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY Distance ASC) AS RowNumber
    FROM (
        SELECT Users.*,
            (((geography::Point(UsersAndLocations.Latitude, UsersAndLocations.Longitude, 4326)).STDistance(@a)) / 1000) AS Distance
        FROM Users
        INNER JOIN UsersAndAttributes ON Users.UserId=UsersAndAttributes.UserId
        INNER JOIN UsersAndLocations ON Users.UserId=UsersAndLocations.UserId
        INNER JOIN Pictures ON Users.UserId=Pictures.UserId
        WHERE (@Attribute1 = NULL OR UsersAndAttributes.Attribute1Id = @Attribute1)
            AND (@Attribute2 = NULL OR UsersAndAttributes.Attribute1Id = @Attribute2)
            ..etc
    ) AS UsersData
) AS UsersNumbered
WHERE RowNumber >= @StartRow
ORDER BY RowNumber

Finally, I would actually be using SELECT * anywhere in the query (especially the innermost sub-query). Rather, select only the specific columns you need. This will help reduce the amount of work SQL server needs to do and reduce the amount of data that needs to move over the network when returning the results. I didn't know which columns you needed though so I've re-used your SELECT *.