sql - "tempdb.mdf"由于 SQL 查询增加到天文大小(即 800GB)

Question

我一直在尝试在我的 SQL Server 上运行以下 SQL。但是，我不断遇到一个错误，该错误是 tempdb.mdf 文件增大到占用整个硬盘 (800GB) 的天文大小的结果。有办法解决这个问题吗？

SQL查询如下:

SELECT 
    t1.[mmsi], t1.[tagblock_timestamp], t1.[x] , t1.[y], 
    t1.[sog], t1.[Location Code],
    DATEDIFF(second , MAX (t2.[tagblock_timestamp]),   
    t1.[tagblock_timestamp]) AS LengthOfRecord
INTO 
    [dbo]. [VehProcessed]
FROM 
    [dbo]. [VehMovement] t1 
LEFT JOIN 
    [dbo]. [VehMovement] t2 ON t1. [mmsi] = t2 .[mmsi] 
                            AND t2.[tagblock_timestamp] < t1.[tagblock_timestamp]
GROUP BY 
    t1.[mmsi], t1.[tagblock_timestamp], t1.[x], t1.[y], t1.[sog], t1.[Location Code];

Answer 1

在 (mmsi, tagblock_timestamp DESC) 上创建索引。这是必须的。

以下查询不如 LAG 函数(自 SQL Server 2012+ 起可用)那么高效，但它应该比您拥有的更好。

SELECT
    t1.[mmsi]
    ,t1.[tagblock_timestamp]
    ,t1.[x]
    ,t1.[y]
    ,t1.[sog]
    ,t1.[Location Code]
    ,DATEDIFF(second, A.[tagblock_timestamp], t1.[tagblock_timestamp]) AS LengthOfRecord
INTO
    [dbo].[VehProcessed]
FROM
    [dbo].[VehMovement] AS t1
    OUTER APPLY
    (
        SELECT TOP(1) t2.[tagblock_timestamp]
        FROM [dbo].[VehMovement] AS t2
        WHERE 
            t1.[mmsi] = t2.[mmsi]
            AND t2.[tagblock_timestamp] < t1.[tagblock_timestamp]
        ORDER BY t2.[tagblock_timestamp] DESC
    ) AS A
;

您想计算连续行之间的差异。在 SQL Server 2008 上，最好的方法很可能是使用简单的游标。