UNION 或 UNION 是否都构建了一个锁定所有选定表的大规模查询

Question

我的首席DBA告诉我，我写的代码格式很差，因为我使用UNION ALL来累积不同表上连续查询的结果。 我想当具有多个选择语句的查询具有结果 UNIONed 单独执行时，因此当每个选择语句执行时，它会在表上放置一个共享锁，该锁在完成后释放并且下一个选择开始。我认为结果是累积在某个缓冲区或 tmp 表中的。

有人能告诉我幕后发生了什么，以及当一百个选择语句的结果被联合时消耗了哪些资源。 每个选择对一个表进行操作，并收集架构、表和列名称。

抱歉，我没有查询计划。 DBA抱怨查询太大，无法显示大部分计划。 他的评论在查询下方。

SELECT 'R_Stage' as TheSchema, 'DateFrozenSectionModF63x086' as TheTable, 'PersonModTextStaffSID' as TheColumn, COUNT(*) as NullCount 
FROM [R_Stage].[DateFrozenSectionModF63x086] WHERE [PersonModTextStaffSID] = -1  
UNION ALL 
SELECT 'R_Stage' as TheSchema, 'DateFrozenSectionModF63x086' as TheTable, 'LabDataLabSubjectSID' as TheColumn, COUNT(*) as NullCount 
FROM [R_Stage].[DateFrozenSectionModF63x086] WHERE [LabDataLabSubjectSID] = -1  
UNION ALL 
SELECT 'R_Stage' as TheSchema, 'DateFrozenSectionModF63x086' as TheTable, 'LabDataPatientSID' as TheColumn, COUNT(*) as NullCount 
FROM [R_Stage].[DateFrozenSectionModF63x086] WHERE [LabDataPatientSID] = -1  
UNION ALL 
SELECT 'R_Stage' as TheSchema, 'DateGrossDescChangedF63x087' as TheTable, 'PersonModTextStaffSID' as TheColumn, COUNT(*) as NullCount 
FROM [R_Stage].[DateGrossDescChangedF63x087] WHERE [PersonModTextStaffSID] = -1  
UNION 
ALL SELECT 'R_Stage' as TheSchema, 'DateGrossDescChangedF63x087' as TheTable, 'LabDataLabSubjectSID' as TheColumn, COUNT(*) as NullCount 
FROM [R_Stage].[DateGrossDescChangedF63x087] WHERE [LabDataLabSubjectSID] = -1  
UNION ALL 
SELECT 'R_Stage' as TheSchema, 'DateGrossDescChangedF63x087' as TheTable, 'LabDataPatientSID' as TheColumn, COUNT(*) as NullCount 
FROM [R_Stage].[DateGrossDescChangedF63x087] WHERE [LabDataPatientSID] = -1  
UNION ALL 

无论如何，上面的查询肯定可以用更有效的方式编写。正如为查询中的每个表所写的那样，它将扫描整个表以查找每个 UNION，即 791 次。只需查看查询的前几行，我们可以看到这些只是来自同一表的计数，这可以通过使用计数的 CASE 表达式对该表进行单次扫描来完成，并且您将在每个表的一次传递中获得所有计数。最重要的是，现在我们在FRE上只有少数用户，这样的流程已经影响了许多用户/工作。想象一下，当我们有成百上千的用户时。我们根本负担不起像这两个例子那样运行未经审查或正确测试的流程。这不是个人的，不应该这样认为，这完全是关于服务器和所有用户的整体福祉。指出这些问题是我工作的一部分，这样当我看到它们时就可以解决这些问题，这无疑是其中之一。在重写它们以确保它们执行预期操作并且它们足够高效不会导致其他进程出现问题之前，它们无法再次运行。

Answer 1

DBA的建议似乎很合理。他/她没有提到锁定，也不清楚你为什么提到这个问题。

正如 DBA 所述，您正在执行 791 个查询，然后数据库引擎将这些查询联合在一起。这将给数据库带来负载。假设您的 DBA 对这些查询是全表扫描是正确的，这意味着整个表将被读取 791 次。

无论任何锁定，这都会破坏磁盘，溢出文件系统和数据库缓存，并加载运行这些查询的 CPU。

假设您的数据库足够大，无法容纳 RAM 文件系统或数据库缓存，这意味着每次都必须从磁盘中完全读取它。如果按照 DBA 的建议重写查询，使其仅通过数据库进行 1 次全表扫描，则对文件系统的影响将是当前写入查询的 1/791。

如果数据库确实同时采用读锁定，则查询将影响该表的更新程序 791 次。

DBA 的建议可以使建议的查询效率大约提高 791 倍。

如果我们假设您的表是 100 meg 的工作示例，则在 100 mb/s 的磁盘读取速度下，处理 791 个查询中的每一个大约需要 1 秒，因此完整查询大约需要 14 分钟。按照您的 DBA 建议重写大约需要 1 秒。

这不是锁定问题，而是经典的 I/O 性能问题。如果您也有锁定问题，那只会使情况变得更糟。

查询的

确切性能特征取决于许多因素，包括表的大小、定义的索引(请注意，在某些情况下，索引会使查询变慢(、表的"宽度"程度、表中的列类型、查询运行的硬件、您使用的数据库系统、 磁盘的速度有多快，数据库有多少 RAM，系统上还发生了什么，等等。因此，如果没有更多信息，就不可能给出明确的答案。

但是，避免 791 次全表扫描是提高性能的良好开端。

Answer 2

很抱歉，那篇帖子让我的眼睛受伤了。听起来您需要编写脚本来清理或识别问题。为了简化这一点，您可以自动化为脚本，该脚本将在发布这 300 个表之前吐出小的可测试的 sql 语句代码。但是，如果您的 dba 允许您使用游标和临时表(应尽可能避免这两种情况(，这似乎更像是识别问题和/或清理问题，而不是关注效率。话虽如此，我不想将这些表锁定在生产系统上一段时间......所以做很多小任务来减少锁并达到相同的目标。您可以在 sql Server 管理员中运行此脚本并将输出作为输入提供给您的 dba，也许它会有所帮助。

SET NOCOUNT ON
DECLARE  @OUTPUT TABLE
(  
    TheSchema NVARCHAR(45),
    TheTable NVARCHAR(45),
    Field1 NVARCHAR(45),
    Field2 NVARCHAR(45),
    Field3 NVARCHAR(45)
)
INSERT @OUTPUT SELECT 'R_Stage','DateFrozenSectionModF63x086','PersonModTextStaffSID','LabDataLabSubjectSID','LabDataPatientSID'
INSERT @OUTPUT SELECT 'R_Stage','DateFrozenSectionModF63x087','PersonModTextStaffSID','LabDataLabSubjectSID','LabDataPatientSID'
INSERT @OUTPUT SELECT 'R_Stage','DateFrozenSectionModF63x088','PersonModTextStaffSID','LabDataLabSubjectSID','LabDataPatientSID'
INSERT @OUTPUT SELECT 'R_Stage','DateFrozenSectionModF63x089','PersonModTextStaffSID','LabDataLabSubjectSID','LabDataPatientSID'
INSERT @OUTPUT SELECT 'R_Stage','DateFrozenSectionModF63x090','PersonModTextStaffSID','LabDataLabSubjectSID','LabDataPatientSID'
INSERT @OUTPUT SELECT 'R_Stage','DateFrozenSectionModF63x091','PersonModTextStaffSID','LabDataLabSubjectSID','LabDataPatientSID'
INSERT @OUTPUT SELECT 'R_Stage','DateFrozenSectionModF63x092','PersonModTextStaffSID','LabDataLabSubjectSID','LabDataPatientSID'
INSERT @OUTPUT SELECT 'R_Stage','DateFrozenSectionModF63x093','PersonModTextStaffSID','LabDataLabSubjectSID','LabDataPatientSID'
INSERT @OUTPUT SELECT 'R_Stage','DateFrozenSectionModF63x094','PersonModTextStaffSID','LabDataLabSubjectSID','LabDataPatientSID'
INSERT @OUTPUT SELECT 'R_Stage','DateFrozenSectionModF63x095','PersonModTextStaffSID','LabDataLabSubjectSID','LabDataPatientSID'

DECLARE @TheSchema NVARCHAR(45),@TheTable NVARCHAR(45),@Field1 NVARCHAR(45),@Field2 NVARCHAR(45),@Field3 NVARCHAR(45)
DECLARE LOOP CURSOR FOR
SELECT TheSchema,TheTable,Field1,Field2,Field3 FROM @OUTPUT

PRINT '
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE  TABLE_NAME = ''__MY_SCAN''))
DROP TABLE __MY_SCAN

CREATE TABLE  __MY_SCAN(
    TheShema NVARCHAR(45),
    TheTable NVARCHAR(45),
    Field1NullCount INT,
    Field2NullCount INT,
    Field3NullCount INT
)'
OPEN LOOP 
FETCH NEXT FROM LOOP INTO @TheSchema,@TheTable,@Field1,@Field2,@Field3
WHILE(@@FETCH_STATUS=0) BEGIN
    PRINT 
        'INSERT __MY_SCAN
            SELECT 
                '''+@TheSchema+''' AS '+@TheSchema+',
                '''+@TheTable+''' AS '+@TheTable+',
                COUNT(Field1),
                COUNT(Field2),
                COUNT(Field3)
            FROM
            (   
                SELECT       
                    Field1=CASE WHEN '+@Field1+'=-1 THEN 1 ELSE 0 END,
                    Field2=CASE WHEN '+@Field2+'=-1 THEN 1 ELSE 0 END,
                    Field3=CASE WHEN '+@Field3+'=-1 THEN 1 ELSE 0 END
                FROM
                    '+@TheTable+'
                WHERE
                    '+@Field1+'=-1 OR '+@Field2+'=-1 OR '+@Field3+'=-1
            )AS X
            GO'
    FETCH NEXT FROM LOOP INTO @TheSchema,@TheTable,@Field1,@Field2,@Field3
END
CLOSE LOOP
DEALLOCATE LOOP
PRINT '
SELECT * FROM __MY_SCAN 
GO
DROP TABLE __MY_SCAN
GO
'