k_task.ProjGUID in ('20ee7748-63ca-4289-bc9c-de828b9cbd21','21983bc9-118c-46fe-a487-36222fc3dd81','73338871-91c5-4bdc-86ea-835a669526d2','9d56e8f3-5c6f-44a9-9c23-0f5a41cab158','6bf0dad2-a2f3-4ec4-8f0d-cf928c587c3b','667d9a97-d140-429e-b5ff-408121258e19','c6d17027-37d7-4e4d-8c85-6a4f43c3639c','a3c80652-13e8-4c9a-8c0b-6eb0a6b961e6','47ec8614-5028-4179-8b82-879fda147263','e3ad8a04-522e-4fd1-baf3-e7e683b002cb','ba54b3a7-d25a-41cf-b9cb-7e4dcf0627d9','9848d95c-3ad0-48f4-9a44-c75288b0a3e7','3879220a-b45d-4da9-ad4d-97e6632a2fed'))这句可不可以改成一个表?
SELECT TOP 100 k_task.taskguid, k_task.taskcode, k_task.topic, k_task.taskType, k_task.sldate, k_Receive.tsFcInfo, k_Receive.requestMan, k_task.Status ,k_Receive.CstName,k_Receive.BUGUID FROM k_task INNER JOIN k_Receive ON k_task.receiveguid = k_Receive.ReceiveGUID WHERE (k_task.BUGUID = '……') AND k_task.TaskRange = '日常服务' AND k_task.IsZbcl = 0 ORDER BY k_task.SlDate desc, k_task.TaskCode DESC 如果是这样也会存在同样的问题,问题在聚集索引查找上!!能否通过改造语句,或者上面方法降低开销呢? 我现在已经没办法解决了,请大家帮帮忙!!
SELECT k_task.taskguid, k_task.taskcode, k_task.topic, k_task.taskType, k_task.sldate, --k_Receive.tsFcInfo, k_Receive.requestMan, k_task.Status --,k_Receive.CstName,k_Receive.BUGUID ,k_task.receiveguid into #t FROM k_task WHERE (k_task.ProjGUID in ('20ee7748-63ca-4289-bc9c-de828b9cbd21','21983bc9-118c-46fe-a487-36222fc3dd81','73338871-91c5-4bdc-86ea-835a669526d2','9d56e8f3-5c6f-44a9-9c23-0f5a41cab158','6bf0dad2-a2f3-4ec4-8f0d-cf928c587c3b','667d9a97-d140-429e-b5ff-408121258e19','c6d17027-37d7-4e4d-8c85-6a4f43c3639c','a3c80652-13e8-4c9a-8c0b-6eb0a6b961e6','47ec8614-5028-4179-8b82-879fda147263','e3ad8a04-522e-4fd1-baf3-e7e683b002cb','ba54b3a7-d25a-41cf-b9cb-7e4dcf0627d9','9848d95c-3ad0-48f4-9a44-c75288b0a3e7','3879220a-b45d-4da9-ad4d-97e6632a2fed')) AND k_task.TaskRange = '日常服务' AND k_task.IsZbcl = 0 SELECT TOP 100 k_task.taskguid, k_task.taskcode, k_task.topic, k_task.taskType, k_task.sldate, k_Receive.tsFcInfo, k_Receive.requestMan, k_task.Status ,k_Receive.CstName,k_Receive.BUGUID from #t k_task INNER JOIN k_Receive ON k_task.receiveguid = k_Receive.ReceiveGUID ORDER BY k_task.SlDate desc, k_task.TaskCode DESC drop table #t
SELECT TOP 100 k_task.taskguid, k_task.taskcode, k_task.topic, k_task.taskType, k_task.sldate, k_Receive.tsFcInfo, k_Receive.requestMan, k_task.Status ,k_Receive.CstName,k_Receive.BUGUID FROM k_task INNER JOIN k_Receive ON k_task.receiveguid = k_Receive.ReceiveGUID WHERE (k_task.ProjGUID ='20ee7748-63ca-4289-bc9c-de828b9cbd21') AND k_task.TaskRange = '日常服务' AND k_task.IsZbcl = 0 ORDER BY k_task.SlDate desc, k_task.TaskCode DESCk_task.SlDate desc, k_task.TaskCode 这两个建立的非聚集索引是组合的吗?改成组合索引,而且都是DESC的把IN那里改成等于一个值的时候试试
('20ee7748-63ca-4289-bc9c-de828b9cbd21','21983bc9-118c-46fe-a487-36222fc3dd81','73338871-91c5-4bdc-86ea-835a669526d2','9d56e8f3-5c6f-44a9-9c23-0f5a41cab158','6bf0dad2-a2f3-4ec4-8f0d-cf928c587c3b','667d9a97-d140-429e-b5ff-408121258e19','c6d17027-37d7-4e4d-8c85-6a4f43c3639c','a3c80652-13e8-4c9a-8c0b-6eb0a6b961e6','47ec8614-5028-4179-8b82-879fda147263','e3ad8a04-522e-4fd1-baf3-e7e683b002cb','ba54b3a7-d25a-41cf-b9cb-7e4dcf0627d9','9848d95c-3ad0-48f4-9a44-c75288b0a3e7','3879220a-b45d-4da9-ad4d-97e6632a2fed'))这句可不可以改成一个表?
k_task聚集索引是在TaskGUID上,ReceiveGUID上是非聚集索引
SELECT TOP 100 k_task.taskguid, k_task.taskcode,
k_task.topic, k_task.taskType,
k_task.sldate, k_Receive.tsFcInfo,
k_Receive.requestMan, k_task.Status
,k_Receive.CstName,k_Receive.BUGUID
FROM k_task INNER JOIN k_Receive ON k_task.receiveguid = k_Receive.ReceiveGUID
WHERE (k_task.BUGUID = '……')
AND k_task.TaskRange = '日常服务'
AND k_task.IsZbcl = 0
ORDER BY k_task.SlDate desc, k_task.TaskCode DESC 如果是这样也会存在同样的问题,问题在聚集索引查找上!!能否通过改造语句,或者上面方法降低开销呢?
我现在已经没办法解决了,请大家帮帮忙!!
k_task.topic, k_task.taskType,
k_task.sldate, --k_Receive.tsFcInfo, k_Receive.requestMan,
k_task.Status
--,k_Receive.CstName,k_Receive.BUGUID
,k_task.receiveguid
into #t
FROM k_task WHERE (k_task.ProjGUID in
('20ee7748-63ca-4289-bc9c-de828b9cbd21','21983bc9-118c-46fe-a487-36222fc3dd81','73338871-91c5-4bdc-86ea-835a669526d2','9d56e8f3-5c6f-44a9-9c23-0f5a41cab158','6bf0dad2-a2f3-4ec4-8f0d-cf928c587c3b','667d9a97-d140-429e-b5ff-408121258e19','c6d17027-37d7-4e4d-8c85-6a4f43c3639c','a3c80652-13e8-4c9a-8c0b-6eb0a6b961e6','47ec8614-5028-4179-8b82-879fda147263','e3ad8a04-522e-4fd1-baf3-e7e683b002cb','ba54b3a7-d25a-41cf-b9cb-7e4dcf0627d9','9848d95c-3ad0-48f4-9a44-c75288b0a3e7','3879220a-b45d-4da9-ad4d-97e6632a2fed'))
AND k_task.TaskRange = '日常服务'
AND k_task.IsZbcl = 0 SELECT TOP 100 k_task.taskguid, k_task.taskcode,
k_task.topic, k_task.taskType,
k_task.sldate, k_Receive.tsFcInfo, k_Receive.requestMan,
k_task.Status
,k_Receive.CstName,k_Receive.BUGUID
from #t k_task
INNER JOIN k_Receive ON k_task.receiveguid = k_Receive.ReceiveGUID
ORDER BY k_task.SlDate desc, k_task.TaskCode DESC drop table #t
k_task.topic, k_task.taskType,
k_task.sldate, k_Receive.tsFcInfo,
k_Receive.requestMan, k_task.Status
,k_Receive.CstName,k_Receive.BUGUID
FROM k_task INNER JOIN k_Receive ON k_task.receiveguid = k_Receive.ReceiveGUID
WHERE (k_task.ProjGUID ='20ee7748-63ca-4289-bc9c-de828b9cbd21')
AND k_task.TaskRange = '日常服务'
AND k_task.IsZbcl = 0
ORDER BY k_task.SlDate desc, k_task.TaskCode DESCk_task.SlDate desc, k_task.TaskCode 这两个建立的非聚集索引是组合的吗?改成组合索引,而且都是DESC的把IN那里改成等于一个值的时候试试
不排序3秒,Reads 2600
加排序6秒,Reads 21W
在这三个字段上建立一个索引试试,现在是不是分别建立的
非聚集索引 建在 ProjGUID、TaskRange、IsZbcl、SlDate、TaskCode 上
是组合的
首先建立表连接,声称一张临时表。
然后再查中两个条件,不是IN那个,生成临时表
最后查找in