对具有 30mill 记录的表进行 SQL 查询

Question

我在本地 SQL Server 中构建表时遇到问题。最初它导致 tempdb 表变满并引发异常。这有很多连接和外部应用，所以为了具体找出问题所在，我在 sql 查询的第一个表上做了一个选择，以确定花了多长时间，这很快所以我添加了下一个表第一次加入查询并重新运行，我继续这样做，直到找到停滞的表。

我发现问题(或者至少是第一个问题)与 shipper_container 表有关。这张表很大，单独拉它会得到一个 System.OutOfMemoryException，只显示对该表单独结果的选择(它只有 5 列)。它删除了 1600 万条记录，但有 3000 万行。它的大小为 1.2GB。这对我来说似乎不是很大，SQL Management Studio 无法处理它。

在 2015 年 1 月 1 日至 10 日之间使用 WHERE 语句收集值仍然会导致搜索耗时超过 5 分钟，并且在我取消时仍在执行。我还在每个选择参数上添加了索引，这也没有提高性能。

这是 SQL 查询。您可以看到我已经注释掉了其他尚未在其他连接和外部应用中添加的参数。

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
DECLARE @Shipper_Key INT = NULL
DECLARE @Part_Key INT = NULL

SET @startDate = '2015-01-01'
SET @endDate = '2015-01-10'

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

INSERT Shipped_Container
(
    Ship_Date,  
    Invoice_Quantity,
    Shipper_No,
    Serial_No,
    Truck_Key,
    Shipper_Key
)
SELECT
    S.Ship_Date,
    SC.Quantity,
    S.Shipper_No,
    SC.Serial_No, 
    S.Truck_Key,
    S.Shipper_Key
    FROM Shipper AS S
JOIN Shipper_Line AS SL
    --ON SL.PCN = S.PCN
    ON SL.Shipper_Key = S.Shipper_Key
JOIN Shipper_Container AS SC
    --ON SC.PCN = SL.PCN
    ON SC.Shipper_Line_Key = SL.Shipper_Line_Key
WHERE S.Ship_Date >= @startDate AND S.Ship_Date <= @endDate
    AND S.Shipper_Key = ISNULL(@Shipper_Key, S.Shipper_Key)
    AND SL.Part_Key = ISNULL(@Part_Key, SL.Part_Key)

服务器实例在本地网络上运行 - 这可能是个问题吗？我在这方面的经验真的很少，非常感谢帮助，并尽可能详细和清楚。通常在 SQL 论坛中，人们会直接跳入技术细节，我不太了解。

Answer 1

不要在 SS Management Studio 返回时执行 Select ... From yourtable数十万或数百万行。当系统必须在 Management Studio 数据表的屏幕上绘制和显示数据时，1GB 的数据会变得更大

The server instance is run on the local network

当您在 SSMS 中执行 Select ... From yourtable 时，服务器必须将所有数据发送到您的笔记本电脑/台式机。这对网络造成了很多不必要的压力。插入时这应该不是问题，因为所有内容都保留在服务器上。但是，如果您的数据模型不够好，留在服务器上并不意味着它会很快。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

如果你使用它，你可能会得到脏数据......最好删除它，除非你知道它为什么在那里以及为什么你需要它。

I have also added indexes on each of the select parameters and this did not increase performance either

如果你的意思是索引:

• S.Ship_Date,
• SC.数量，
• S.Shipper_No,
• SC.Serial_No,
• S.Truck_Key，
• S.Shipper_Key

它们的定义是什么？如果它们是 1 列上的单独索引，您可以删除 SC.Quantity、S.Shipper_No、SC.Serial_No 和 S.Truck_Key 上的索引。他们没有被使用。Ship_Date 和 Shipper_key 可能会有用。这完全取决于您的模型和现有的主键。 (你需要描述，见下文)

It will help to give a more accurate answer if you could tell us:

• 你的 3 个表之间的关系(哪个字段将 A 链接到 B 以及在哪个方向)
• 你的 3 个表的主键
• 3 个表中所有索引(和列)的完整列表

如果您的索引都没有用或丢失，它很可能会读取整个 3 个表并尝试匹配它们。因为它很大，所以没有足够的内存来处理它，它使用 tempdb 来存储中间数据。

For now I will suppose that shipper_key + PCN is the primary key on each tables. I think you can try that:

• 您可以在 S.Ship_Date 上创建索引

  Create Index Shipper_Line_Ship_Date(Ship_Date) -- subject to updates according to your Primary Key
  

• 查询优化器可能不会使用带有这样一个 where 子句的索引(如果它们存在的话):

  AND S.Shipper_Key = ISNULL(@Shipper_Key, S.Shipper_Key)
  AND SL.Part_Key = ISNULL(@Part_Key, SL.Part_Key)
  

  你可以使用:

  AND (S.Shipper_Key = @Shipper_Key or @Shipper_Key is null)
  AND (SL.Part_Key = @Part_Key or @Part_Keyis null)
  

• 在 Shipper_Key 和 PCN 上建立索引会很有帮助

Finally

正如我上面已经说过的，我们需要更多地了解您的数据模型(创建表...)、主键和索引(创建索引)。您可以在这里创建模型 http://sqlfiddle.com/所有 3 个创建表及其索引。然后转到链接并在此处添加链接。

在 SSMS 中，您可以右键单击一个表并转到 Script Table as/Create To/New Query Window 并将其添加到此处或 http://sqlfiddle.com/ 中.只保留 CREATE TABLE ... 部分到第一个 GO。然后，您可以对所有索引执行相同的操作。

您还应该添加查询计划的副本。在 SSMS 中，转到 Query menu/Display Estimated Execution Plan 并右键单击将其另存为 xml(xml 更好)。这只是一个估计，不会执行整个查询。应该很快。