Sql-Server
添加一個額外的“order by”列給了我一個更糟糕的計劃
換句話說,我怎樣才能擺脫下
sort
圖中的運算符?SELECT TOP 1 so.OrgType, ch.Status, rcs.DBSstatusDescription, cid.ApplicationId FROM tbl_application_crb_initialData cid INNER JOIN tbl_season_organisationId so ON cid.OrganisationId = so.OrgId LEFT JOIN tbl_crbHistory ch ON cid.ClientReference = ch.ClientReference LEFT JOIN ref_crbStatus rcs ON ch.Status = rcs.statusId ORDER BY cid.DateAdded DESC, ch.DateAdded DESC SELECT TOP 1 so.OrgType, ch.Status, rcs.DBSstatusDescription, cid.ApplicationId FROM tbl_application_crb_initialData cid INNER JOIN tbl_season_organisationId so ON cid.OrganisationId = so.OrgId LEFT JOIN tbl_crbHistory ch ON cid.ClientReference = ch.ClientReference LEFT JOIN ref_crbStatus rcs ON ch.Status = rcs.statusId ORDER BY cid.DateAdded DESC--, ch.DateAdded DESC
唯一的區別是在第二個查詢中,order by 只有一列。
正如我正在使用的那樣,它會有所不同
top 1
嗎?我相信所有需要的資訊都在查詢計劃中可以看到的索引和表定義上。
如果還有什麼可以幫助擺脫它,請
sort
告訴我,明天我將發布所有可能的資訊。
您的問題缺少很多細節,但我可以重現類似的內容。
設置
CREATE TABLE T1(X INT PRIMARY KEY, Y INT INDEX IX) CREATE TABLE T2(X INT, Y INT , PRIMARY KEY(X, Y)) INSERT INTO T2 OUTPUT INSERTED.* INTO T1 SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), ROW_NUMBER() OVER (ORDER BY @@SPID) FROM sys.all_objects o1, sys.all_objects o2;
查詢 1
SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y FROM T1 JOIN T2 ON T1.X = T2.X ORDER BY T1.Y;
查詢 2
SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y FROM T1 JOIN T2 ON T1.X = T2.X ORDER BY T1.Y, T2.Y
查詢 3
WITH T AS ( SELECT TOP 1 WITH TIES T1.Y AS T1Y, T2.Y AS T2Y FROM T1 JOIN T2 ON T1.X = T2.X ORDER BY T1.Y ) SELECT TOP 1 * FROM T ORDER BY T2Y
查詢 1只是以所需的排序順序從索引中挑選出來
TOP 1
,並在該行的另一個表上進行所需的連接。如果連接成功,它會停在那裡,否則它會按索引順序嘗試下一個,直到找到匹配的行或用完行。查詢 2添加新的排序列時,此計劃不再有效,因為可能有多個匹配與該
TOP 1
值相關聯,並且 SQL Server 決定加入整個批次,然後從中獲取TOP 1
。查詢 3這鼓勵 SQL Server 堅持第一個策略,然後只
TOP 1 Sort
對與第一個排序鍵值相同的任何行執行 a。對於我的範例數據,查詢 3 比查詢 2 效果更好,但如果您有許多重複項與第一個排序鍵的值相關聯,那麼您的里程數可能會有所不同。
你可以試試這個重寫,看看效果如何
WITH T AS (SELECT TOP 1 WITH TIES so.OrgType, ch.Status, rcs.DBSstatusDescription, cid.ApplicationId, ch.DateAdded AS chDateAdded FROM tbl_application_crb_initialData cid INNER JOIN tbl_season_organisationId so ON cid.OrganisationId = so.OrgId LEFT JOIN tbl_crbHistory ch ON cid.ClientReference = ch.ClientReference LEFT JOIN ref_crbStatus rcs ON ch.Status = rcs.statusId ORDER BY cid.DateAdded DESC) SELECT TOP 1 OrgType, Status, DBSstatusDescription, ApplicationId FROM T ORDER BY chDateAdded DESC