希望提高查詢性能

這是我的查詢：

select S-Num.Title, 
      jobs.JobNum, 
      jobs.JobPart, 
      Part.PartNum, 
      jobs.RevisionNum,
      jobs.Seq,
      jobs.Parent, 
      Part.PartDescription, 
      jobs.part_type 
from S-Num
inner join 
(
   select Asem.jobNum as JobNum, 
          Asem.job_part as JobPart, 
          Asem.Asem as Part, 
          Asem.RevisionNum, 
          Asem.AssemblySeq as Seq, 
          Asem.Parent, 
          Asem.part_type
   from Asem 

   union   

   select Mats.jobNum as JobNum, 
          Mats.job_part as JobPart, 
          Mats.material as Part, 
          Mats.RevisionNum, 
          Mats.MtlSeq as Seq, 
          Mats.Parent, 
          Mats.part_type
   from Mats
) as jobs on S-Num.JobNum = jobs.JobNum
inner join Part on Part.PartNum = jobs.Part
where S-Num.Title = 'user selected data'
order by jobs.part_type, jobs.Seq, jobs.Parent ;

如您所見，這裡有 4 個表：

1. S-Num230k 條記錄 - 此表包含最終使用者知道的唯一內容，恰好是標題。在查找標題時，我們得到了 JobNumber將表格與Asem和 聯繫起來的內容Mats。
2. Asem2m 記錄 - 每個 JobNumber 包含許多記錄。裝配的每個相關元件都有一個記錄。所以它有關鍵的加入Part表。
3. Mats20m 記錄 - 每個都包含許多記錄JobNumber。材料的每個相關部分都有一個記錄。因此，它具有加入Part表的關鍵。
4. Part25k 條記錄 - 僅包含生成表所需的元件號和說明。為每個組件和材料收集。

沒有主鍵，因此無法建構外鍵。我在Asem(2m Records) 和Mats(20m Records) 表上為JobNum列建立了一個聚集索引。但是，結果仍需要 3 到 5 秒。這主要是因為Mats它有 2000 萬條記錄，好像我從查詢中取出它大約需要一秒鐘。該數據來自客戶端，因此我們無法將其更改為包含可以管理一對多關係並更好地處理關係的查找表。這將成為維護的噩夢，因為這些數據也不會停滯不前。

結果只是一個表格，其中包含我為最終使用者列出的列，以查看由什麼組成的。

我不是 DBA，我們內部也沒有 DBA。我只是一個試圖從這種情況中獲得最佳性能的程序員。

謝謝您的幫助！

–edit– 所有這些都在 Ms SQL Server 2008R2 上

–edit2– 在我提出的原始查詢之外，我對 Asem 表的聚集索引掃描獲得了 10% 的成本，而對 Mats 表的聚集索引掃描，我獲得了 84% 的成本。

您可以嘗試拆分聯合中的兩個查詢以連接所有其他表，然後合併這些結果。

我創建了帶有聚集索引的表並更新了統計資訊以模擬指定的行數。

   CREATE TABLE [Part25kRecords]([PartNum] int,
                             [PartDescription] varchar(10));
CREATE TABLE [Mats20mRecords]([jobNum] int,
                             [job_part] int ,
                             [material] int,
                             [RevisionNum] int,
                             [mtlSeq] varchar(10),
                             [Parent] int,
                             [part_type] int);
CREATE TABLE [S-Num230kRecords]([title] varchar(10),
                               [JobNum] int);
CREATE TABLE [Asem2mRecords]([jobNum] int,
                            [job_part] int ,
                            [assembly] int,
                            [RevisionNum] int,
                            [AssemblySeq] varchar(10),
                            [Parent] int,
                            [part_type] int);

CREATE CLUSTERED INDEX [CL_Asem2mRecords] ON [Asem2mRecords]([JobNum]);
CREATE CLUSTERED INDEX [CL_Mats20mRecords] ON [Mats20mRecords]([JobNum]);

UPDATE STATISTICS [Asem2mRecords] WITH ROWCOUNT = 2000000;
UPDATE STATISTICS [Mats20mRecords] WITH ROWCOUNT = 20000000;
UPDATE STATISTICS [S-Num230kRecords] WITH ROWCOUNT = 230000;
UPDATE STATISTICS [Part25kRecords] WITH ROWCOUNT = 25000;

然後當執行上面的查詢和一個重寫的移動聯合的查詢時，我得到了一個更好的新查詢的執行計劃。

SELECT [S-Num230kRecords].[Title],
      [jobs].[JobNum],
      [jobs].[JobPart],
      [Part25kRecords].[PartNum],
      [jobs].[RevisionNum],
      [jobs].[Seq],
      [jobs].[Parent],
      [Part25kRecords].[PartDescription],
      [jobs].[part_type]
FROM [S-Num230kRecords]
    INNER JOIN(
               SELECT [Asem2mRecords].[jobNum] AS      [JobNum],
                      [Asem2mRecords].[job_part] AS    [JobPart],
                      [Asem2mRecords].[assembly] AS    [Part],
                      [Asem2mRecords].[RevisionNum],
                      [Asem2mRecords].[AssemblySeq] AS [Seq],
                      [Asem2mRecords].[Parent],
                      [Asem2mRecords].[part_type]
               FROM [Asem2mRecords]
               UNION ALL
               SELECT [Mats20mRecords].[jobNum] AS   [JobNum],
                      [Mats20mRecords].[job_part] AS [JobPart],
                      [Mats20mRecords].[material] AS [Part],
                      [Mats20mRecords].[RevisionNum],
                      [Mats20mRecords].[MtlSeq] AS   [Seq],
                      [Mats20mRecords].[Parent],
                      [Mats20mRecords].[part_type]
               FROM [Mats20mRecords])AS [jobs]
    ON [S-Num230kRecords].[JobNum] = [jobs].[JobNum]
    INNER JOIN [Part25kRecords]
    ON [Part25kRecords].[PartNum] = [jobs].[Part]
WHERE [S-Num230kRecords].[Title] = 'user selected data'
ORDER BY [jobs].[part_type], [jobs].[Seq], [jobs].[Parent];

SELECT [S-Num230kRecords].[Title],
      [jobs].[JobNum],
      [jobs].[Job_Part],
      [Part25kRecords].[PartNum],
      [jobs].[RevisionNum],
      [jobs].[AssemblySeq],
      [jobs].[Parent],
      [Part25kRecords].[PartDescription],
      [jobs].[part_type]
FROM [S-Num230kRecords]
    INNER JOIN [Asem2mRecords] AS [jobs]
    ON [S-Num230kRecords].[JobNum] = [jobs].[JobNum]
    INNER JOIN [Part25kRecords]
    ON [Part25kRecords].[PartNum] = [jobs].[assembly]
WHERE [S-Num230kRecords].[Title] = 'user selected data'
UNION ALL
SELECT [S-Num230kRecords].[Title],
      [jobs].[JobNum],
      [jobs].[Job_Part],
      [Part25kRecords].[PartNum],
      [jobs].[RevisionNum],
      [jobs].[mtlSeq],
      [jobs].[Parent],
      [Part25kRecords].[PartDescription],
      [jobs].[part_type]
FROM [S-Num230kRecords]
    INNER JOIN [Mats20mRecords] AS [jobs]
    ON [S-Num230kRecords].[JobNum] = [jobs].[JobNum]
    INNER JOIN [Part25kRecords]
    ON [Part25kRecords].[PartNum] = [jobs].[material]
WHERE [S-Num230kRecords].[Title] = 'user selected data';

這些結果沒有考慮數據分佈，使用 part25krecords 和 S-num230krecords 上的索引可能會更好

通過執行兩個單獨的查詢，我能夠在一秒鐘內執行查詢，只需將 mat 或 assem 表與元件表連接起來，並通過標題查找中可用的 jobNumber 進行查找。

我還嘗試製作視圖和儲存過程，以便使用原始查詢測試性能，但無濟於事。查詢花費了同樣長的時間。

引用自：https://dba.stackexchange.com/questions/117032