这个SQL视图可以阻止其他活动吗？

Question

我们的客户抱怨以下SQL视图导致他们的主要信息系统阻塞。我需要找出这是否是真的(因为他们在时间上也做了其他事情，但他们不确定)，我想修复这种情况。

背景:以下是用于从安装在Microsoft SQL Server 2008上的Navision (现在是Microsoft Dynamics-Nav或其名称)提取数据的简化遗留SQL代码。

该视图来自SSIS包，用于将数据提取到具有固定列宽的txt文件中。生成的文件被导入到其他与Navision没有直接关系的软件中。该包作为作业通过SQL Server代理启动。运行大约需要10分钟。

如何确定数据提取过程是否会阻塞其他活动？SELECT总是自动阻塞吗？如果是，是否可以以某种方式改进视图以避免阻塞？

CREATE VIEW [A_Company$my_view] AS
SELECT ItemLedgerEntry.[Document No_] AS DocumentNo,
    ...
    CAST(CONVERT(DECIMAL(14,2),
                 ROUND((SELECT SUM([Sales Amount (Actual)])
                        FROM [A_Company$Value Entry] AS ValueEntry
                        WHERE ValueEntry.[Item Ledger Entry No_] = ItemLedgerEntry.[Entry No_]),
                       2)) AS VARCHAR(14)) AS SalesAmount,
    ...
    Dim1.[Dimension Value Code] AS Dim1,
    ...
    COALESCE((select top 1 [Group Code]
              from [A_Company$Statistic Group Accom]
              where [Type] = 1
                  and [Sales Code] = ItemLedgerEntry.[Source No_]
                  and [Ship-to Code] = ItemLedgerEntry.[Source No_ 3]
                  and [Starting Date] <= ItemLedgerEntry.[Posting Date]
              order by [Starting Date] desc),
             (select top 1 [Group Code]
              from [A_Company$Statistic Group Accom]
              where [Type] = 1
                  and [Sales Code] = ItemLedgerEntry.[Source No_]
                  and [Ship-to Code] = ''
                  and [Starting Date] <= ItemLedgerEntry.[Posting Date]
              order by [Starting Date] desc), ''
              ) as StatisticGroup

FROM [A_Company$Item Ledger Entry] AS ItemLedgerEntry
    LEFT OUTER JOIN [A_Company$Item] AS Item ON (Item.[No_] = ItemLedgerEntry.[Item No_])
    ...
    LEFT OUTER JOIN [A_Company$Salesperson_Purchaser] AS Salesperson_Purchaser
                 ON (Salesperson_Purchaser.[Code] = Customer.[Salesperson Code])

    LEFT OUTER JOIN [A_Company$Ledger Entry Dimension] AS Dim1
      ON (ItemLedgerEntry.[Entry No_] = Dim1.[Entry No_]) AND (Dim1.[Table ID] = 32) AND
         (Dim1.[Dimension Code] = (SELECT [Shortcut Dimension 1 Code] FROM [A_Company$General Ledger Setup]))
    ...
    LEFT OUTER JOIN [A_Company$Ledger Entry Dimension] AS Dim8
      ON (ItemLedgerEntry.[Entry No_] = Dim8.[Entry No_]) AND (Dim8.[Table ID] = 32) AND
         (Dim8.[Dimension Code] = (SELECT [Shortcut Dimension 8 Code] FROM [A_Company$General Ledger Setup]))

WHERE ((ItemLedgerEntry.[Invoiced Quantity] <> 0)
      AND (ItemLedgerEntry.[Entry Type] = 1))

这是真正的代码，我试图保留我认为可能导致问题的所有功能(删除了重复的部分)。有没有StackExchange论坛，我可以在那里发布完整的代码以供审阅？

避免阻塞是第一步。你能想到性能方面有什么改进吗？(我不擅长SQL编程。)

非常感谢你抽出时间，皮特

Answer 1

在某种程度上，这取决于其他进程正在做什么。他们请求的是哪种锁。

您可以尝试在上述查询中的每个表定义之后添加WITH (Nolock)。

即

FROM [A_Company$Item Ledger Entry] AS ItemLedgerEntry WITH (NOLOCK)