时间戳

首页 > 解决方案 > 每月日志表列表中的 MSSQL 高效返回

sql-server - 每月日志表列表中的 MSSQL 高效返回

问题描述

我正在重写一个连接到 MSSQL 并报告用户使用情况的报告（在经典 asp 中）。

自从我们开始记录以来，我每个月都有多个“日志”表，每当用户登录系统时，他们都会添加一个类型为“成功登录”的日志。

在我的报告中，我试图根据用户 ID 返回最新的登录信息。

目前，我正在使用 ASP 创建数据库中所有日志表的循环，以“联合所有”，并在联合表中查询“成功登录”的用户 ID 和操作。这需要大约 30 分钟才能完成，因为日志已经变得如此之大。

如果我将搜索限制在最近 3 个月的表格中，这可以归结为 5 分钟左右（数据库中有 14k 多个用户，并且报告提供的信息比他们上次登录时使用 SQL 执行计划管理器提供的信息多得多，我发现搜索这 40 多个日志表是瓶颈。

我一直在考虑在 SQL 中使用 If-Else 语句可能是一种只搜索多个表的方法，如果找不到一行，但我正在努力识别逻辑，并且一如既往，可能会有找到我什至没有考虑过的结果的更有效的方法。

使用过去 3 个月

SELECT Name, (SELECT Top 1 LastLogin FROM (SELECT Top 1 Date As LastLogin FROM PARProjectLogs.dbo.Y2020M7 WHERE Y2020M7.UserID = Project_Users.UserID AND Type = 'Successful Login' ORDER BY Date Desc UNION ALL SELECT Top 1 Date As LastLogin FROM PARProjectLogs.dbo.Y2020M6 WHERE Y2020M6.UserID = Project_Users.UserID AND Type = 'Successful Login' ORDER BY Date Desc UNION ALL SELECT Top 1 Date As LastLogin FROM PARProjectLogs.dbo.Y2020M5 WHERE Y2020M5.UserID = Project_Users.UserID AND Type = 'Successful Login' ORDER BY Date Desc UNION ALL SELECT Top 1 Date As LastLogin FROM PARProjectLogs.dbo.Y2020M4 WHERE Y2020M4.UserID = Project_Users.UserID AND Type = 'Successful Login' ORDER BY Date Desc) As Tbl Order BY LastLogin Desc) As LastLogin FROM Project_Users ORDER BY Name

有许多数据库的标题格式为Y2020M1、Y2020M2、Y2020M3、Y2020M4等...

如果我在后面的日志中找到结果，如何避免搜索早期的日志？

标签： sql-serverperformancenested-queries

解决方案

为了提高性能，您可以去掉 select 语句中的子查询。使用依赖于外部选择的 where 子句在选择中执行子查询意味着 SQL 服务器将逐行评估该行。

选项 1将 GROUP BY 与 union 和 join 结合使用

SELECT Name
    , logins.LastLogin
FROM Project_Users pu
LEFT JOIN (
    SELECT UserId , MAX(LastLogin) LastLogin
        FROM (
            SELECT Y2020M7.UserID, DATE AS LastLogin
            FROM PARProjectLogs.dbo.Y2020M7
            WHERE Type = 'Successful Login'
            GROUP BY Y2020M7.UserID
            ORDER BY DATE DESC
            
            UNION ALL
            
            SELECT Y2020M6.UserID, DATE AS LastLogin
            FROM PARProjectLogs.dbo.Y2020M6
            WHERE Type = 'Successful Login'
            GROUP BY Y2020M6.UserID
            ORDER BY DATE DESC
            -- TODO other tables
            
            ) AS Tbl
        GROUP BY UserId
) logins on logins.UserId = pu.UserID
ORDER BY Name

选项 2 使用多个连接和最大。


SELECT Name
    , (SELECT Max(v) 
   FROM (VALUES (loginsY2020M7.Date), (loginsY2020M6.loginsY2020M6),...) AS value(v)) as [LastLogin]
FROM Project_Users pu
LEFT JOIN (
    SELECT Y2020M7.UserID , MAX(DATE) Date
    FROM PARProjectLogs.dbo.Y2020M7
    WHERE  Type = 'Successful Login'
    GROUP BY Y2020M7.UserID
) loginsY2020M7 on loginsY2020M7.UserId = pu.UserId
LEFT JOIN (
    SELECT Y2020M6.UserID , MAX(DATE) Date
    FROM PARProjectLogs.dbo.Y2020M6
    WHERE  Type = 'Successful Login'
    GROUP BY Y2020M6.UserID
) loginsY2020M6 on loginsY2020M6.UserId = pu.UserId
-- TODO all other table in same way
ORDER BY Name

通过使用SQL 服务器可以在集合上优化join和group by执行，而不是像您的示例那样在行级别上进行优化和执行。

推荐阅读