SQL/ c#中的理论搜索功能

本文关键字：理论 搜索 功能 SQL | 更新日期: 2023-09-27 18:07:30

我在SQL/c#中实现一个用户搜索功能。我在功能本身的后勤方面遇到了一点麻烦，我正在寻找一些指导。

我一直在使用三重嵌套子查询，我觉得运行有点慢(平均280-300ms从一个Users表中的1000条记录中提取结果)。

我首先想按属性搜索用户(一个单独的表)，然后根据位置搜索。然后我想要计算和排序距离，然后一次只带回最近的10条记录(分页结果)。

我是否使用了使用三重嵌套子查询的正确方法?或者是否有一个标准或指导原则来做这类事情?

代码示例:

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

SQL/ c#中的理论搜索功能

因为您想要对数据进行分页，这意味着您需要对需要由Distance计算排序的ROW_NUMBER()值进行过滤，因此您将需要3个嵌套子查询。然而，我确实认为它可以做得更干净一点。

我不知道为什么你要从你的内部查询之一的TOP 100 PERCENT，因为这实际上不会做任何事情。我只能假设您在某个时刻对内部查询进行排序，并使用TOP子句使其不会出错。这不起作用，并且不能保证结果的顺序。所以这很好，看起来你从那时起就走了不同的路。

你在查询中使用别名的方式也有点令人困惑。在最内层的查询中，您将实际的User表别名为UsersSubquery，然后将后面的子查询别名为相同的名称。最后，您将最外层的子查询别名为Users，它与基表同名。

没有理由不在执行其他连接的同时执行对Pictures表的连接。在该连接和最内层查询(具有过滤用户属性的WHERE子句)之间没有任何额外的过滤，因此在那里执行连接将是相同的事情。

我是这样写查询的:

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

最后，我实际上会在查询的任何地方使用SELECT *(特别是最内层的子查询)。相反，只选择您需要的特定列。这将有助于减少SQL服务器需要做的工作量，并减少返回结果时需要通过网络移动的数据量。我不知道你需要哪些列，所以我重新使用了你的SELECT *。

相关文章：