我有一個查詢時間太長。
任何列上都沒有索引，我很確定 OR 在這方面的行為方式使服務器上的操作變得過于困難。
這是我的一個視圖，我正在制作一個SELECT *需要 4 分鐘才能完成的視圖。
修改后，我在這個視圖上做的查詢花費了最多的時間。
SELECT * FROM Penny_Assoc_PCB WHERE PRODUCT_ID=68 ORDER BY RECORD_DT, ASSOCIATION_TYPE
/***** 這里是執行計劃 *******/ https://www.brentozar.com/pastetheplan/?id=Bki03eIHK
SELECT dbo.synfact_record.RECORD_ID
,dbo.synfact_record.PART_ID
,dbo.synfact_record.RECORD_DT
,dbo.synfact_association.ASSOCIATION_PART_A
,dbo.synfact_association.ASSOCIATION_PART_B
,dbo.synfact_association.ASSOCIATION_TYPE
,dbo.synfact_association.ASSOCIATION_ID
,dbo.synfact_record.PRODUCT_ID
FROM dbo.synfact_association
INNER JOIN dbo.synfact_record ON dbo.synfact_association.RECORD_ID = dbo.synfact_record.RECORD_ID
WHERE (
dbo.synfact_record.PART_ID IN (
SELECT PART_ID
FROM dbo.synfact_record AS synfact_record_1
WHERE (RECORD_STATUS = 1)
AND (RECORD_TYPE = 0)
)
)
AND dbo.synfact_record.PRODUCT_ID IN(
8,
9,
10,
15,
27,
31,
34,
56,
60,
61,
62,
66,
67,
68)
AND (dbo.synfact_record.RECORD_ID > 499)
AND (dbo.synfact_record.RECORD_STATUS = 1)
GROUP BY dbo.synfact_record.RECORD_ID
,dbo.synfact_record.PART_ID
,dbo.synfact_record.RECORD_DT
,dbo.synfact_association.ASSOCIATION_PART_A
,dbo.synfact_association.ASSOCIATION_PART_B
,dbo.synfact_association.ASSOCIATION_TYPE
,dbo.synfact_association.ASSOCIATION_ID
,dbo.synfact_record.PRODUCT_ID
,dbo.synfact_record.RECORD_STATUS
uj5u.com熱心網友回復：
您可以大大簡化您的查詢。
我已經洗掉了GROUP BY，它就像一個DISTINCT沒有聚合的巨人。如果你得到重復，我建議你多考慮你的加入。也許您需要更好的加入條件，或者top-1-per-group。
SELECT r.RECORD_ID,
r.PART_ID,
r.RECORD_DT,
a.ASSOCIATION_PART_A,
a.ASSOCIATION_PART_B,
a.ASSOCIATION_TYPE,
r.ASSOCIATION_ID,
r.PRODUCT_ID
FROM
dbo.synfact_association AS a
INNER JOIN
dbo.synfact_record AS r ON a.RECORD_ID = r.RECORD_ID
WHERE
(r.PART_ID IN (
SELECT PART_ID
FROM dbo.synfact_record AS r1
WHERE (r1.RECORD_STATUS = 1)
AND (r1.RECORD_TYPE = 0)
)
)
AND r.PRODUCT_ID IN
(8,9,10,15,27,31,34,56,60,61,62,67,68)
AND (r.RECORD_ID > 499)
AND (r.RECORD_STATUS = 1);
在此基礎上查詢孤獨，我會建議以下指標：
CREATE CLUSTERED INDEX IX_synfact_association_RECORD_ID
ON synfact_association (RECORD_ID)
-- for non clustered add: INCLUDE (ASSOCIATION_PART_A, ASSOCIATION_PART_B, ASSOCIATION_TYPE)
CREATE CLUSTERED INDEX IX_synfact_record_RECORD_ID
ON synfact_record (RECORD_STATUS, RECORD_ID)
-- for non clustered add: INCLUDE (PART_ID, RECORD_DT, ASSOCIATION_ID, PRODUCT_ID)
在這第二個索引中，它可能值得交換RECORD_ID和PART_ID
CREATE NONCLUSTERED INDEX IX_synfact_record_RECORD_TYPE
ON synfact_record (RECORD_STATUS, RECORD_TYPE, PART_ID)
最后一個索引是IN子句所必需的
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/316308.html
標籤：sql sql-server 数据库性能 sql-优化
上一篇：查詢以使用進入條件填充排行榜
下一篇：多次將選擇查詢的結果用于另一個