求个大佬给这条sql优化下

[crawling_wl]

select distinct d.IndentDetailId, i.IndentCode,isnull(i.PayTime,i.CreateTime) as FundToTime,
d.FundMoney,i.IndentType,i.BidderName as BidderName,p.TendProjectNo
,case when isnull(p.IsSection,0)=1 then s.SectionNo when ISNULL(p.IsSection,0)=2 then s.SectionNo else '' end as BidSectionNo
,l.DeptName ,i.BidderId as BidCompanyId,
br.ContactUser as BidContractUser,
br.MobileTelephone as BidContractTel,
br.EMail as BidEmail ,p.TendAgencyId,p.TendProjectName,l.TendFileFeeWay
from
T_BidRegistRecord br right join T_Indent i on (i.IndentCode = br.OrderNo or i.IndentCode=br.PretrialOrderNo)
left join T_IndentDetail d on d.IndentId=i.IndentId
left join T_BidSection s on d.BidSectionId=s.BidSectionId
left join T_TendProject p on s.TendProjectId=p.TendProjectId
left join T_TendProjectPlan l on s.TendProjectId=l.TendProjectId
where 1=1 and i.IsFund is not null and i.AgencyId = '2E858978-A7C0-4D99-A602-967A31FB4828'

[crawling_wl]

主要是or的使用，运行起来cpu使用率太高，用union也不行。求大佬啊。。

[听雨停了]

SELECT d.IndentDetailId,
       i.IndentCode,
       ISNULL(i.PayTime, i.CreateTime)  AS FundToTime,
       d.FundMoney,
       i.IndentType,
       i.BidderName                     AS BidderName,
       p.TendProjectNo,
       CASE 
            WHEN ISNULL(p.IsSection, 0) = 1 THEN s.SectionNo
            WHEN ISNULL(p.IsSection, 0) = 2 THEN s.SectionNo
            ELSE ''
       END                              AS BidSectionNo,
       l.DeptName,
       i.BidderId                       AS BidCompanyId,
       br.ContactUser                   AS BidContractUser,
       br.MobileTelephone               AS BidContractTel,
       br.EMail                         AS BidEmail,
       p.TendAgencyId,
       p.TendProjectName,
       l.TendFileFeeWay
FROM   T_BidRegistRecord br
       RIGHT JOIN T_Indent i ON i.IndentCode = br.OrderNo
       LEFT JOIN T_IndentDetail d
            ON  d.IndentId = i.IndentId
       LEFT JOIN T_BidSection s
            ON  d.BidSectionId = s.BidSectionId
       LEFT JOIN T_TendProject p
            ON  s.TendProjectId = p.TendProjectId
       LEFT JOIN T_TendProjectPlan l
            ON  s.TendProjectId = l.TendProjectId
WHERE isnull(i.IsFund,'')<>''
       AND i.AgencyId = '2E858978-A7C0-4D99-A602-967A31FB4828' 

这样单跑一个条件能很快出来吗？也就是去掉了那个or的条件，如果不能的话，那么就要去优化你这些表的索引了，这样看代码是没有优化的余地了

[crawling_wl]

@qq_37170555 必须2个联合。这是往接口推送的数据，不好改。还是用了union来解决的。虽然还是挺慢。

[听雨停了]

引用 3 楼 crawling_wl 的回复:

@qq_37170555 必须2个联合。这是往接口推送的数据，不好改。还是用了union来解决的。虽然还是挺慢。

我的意思是你去掉一个or条件执行一下看速度快不，没有说叫你直接改成这样去用，改成这样明显不符合需求啊

[顺势而为1]

PretrialOrderNo 与 OrderNo 不存在在同一行的情况吗 ? 要不试试将这PretrialOrderNo,OrderNo 合成一列试试 ?

[crawling_wl]

@qq_37170555 快啊。就是or的问题

[crawling_wl]

@appetizing_fish1 这个行不通。。

[顺势而为1]

引用 7 楼 crawling_wl 的回复:

@appetizing_fish1 这个行不通。。

既然是or 的原因, 那你肯定要想办法去掉or, 如果合并br.OrderNo ,br.PretrialOrderNo 成新的一列 br.NewOrderNo , 那 i.IndentCode = br.OrderNo or i.IndentCode=br.PretrialOrderNo 就可以写成 i.IndentCode = br.NewOrderNo

[crawling_wl]

@appetizing_fish1不符合业务逻辑 ，这两字段可能都有值，这样查询数据不正确

[听雨停了]

引用 8 楼 appetizing_fish1 的回复:

[quote=引用 7 楼 crawling_wl 的回复:] @appetizing_fish1 这个行不通。。

既然是or 的原因, 那你肯定要想办法去掉or, 如果合并br.OrderNo ,br.PretrialOrderNo 成新的一列 br.NewOrderNo , 那 i.IndentCode = br.OrderNo or i.IndentCode=br.PretrialOrderNo 就可以写成 i.IndentCode = br.NewOrderNo[/quote] 逻辑完全不对啊，假如说i.IndentCode=‘123’，br.OrderNo=‘123’ ,br.PretrialOrderNo=‘456’，那合并后这条数据就取不到了，原本是符合条件的

[听雨停了]

引用 9 楼 crawling_wl 的回复:

@appetizing_fish1不符合业务逻辑 ，这两字段可能都有值，这样查询数据不正确

个人建议你用union all把数据合并，不要用union去重合并，用union all就单单的合并，不去重，所以基本上不需要消耗时间（很快）。然后你再用row_number()<>1删除重复的数据，再查询，这样应该很快的（至少比你现在union快哈）。

[顺势而为1]

引用 10 楼 qq_37170555 的回复:

[quote=引用 8 楼 appetizing_fish1 的回复:] [quote=引用 7 楼 crawling_wl 的回复:] @appetizing_fish1 这个行不通。。

既然是or 的原因, 那你肯定要想办法去掉or, 如果合并br.OrderNo ,br.PretrialOrderNo 成新的一列 br.NewOrderNo , 那 i.IndentCode = br.OrderNo or i.IndentCode=br.PretrialOrderNo 就可以写成 i.IndentCode = br.NewOrderNo[/quote] 逻辑完全不对啊，假如说i.IndentCode=‘123’，br.OrderNo=‘123’ ,br.PretrialOrderNo=‘456’，那合并后这条数据就取不到了，原本是符合条件的[/quote] 我用这个数据测试是合理的啊

IF OBJECT_ID('tempdb..#tmp_data') IS NOT NULL 
          DROP TABLE #tmp_data
 
CREATE TABLE #tmp_data(
         orderno varchar(10),
		 PretrialOrderNo varchar(10))

insert into #tmp_data values ('123','456')


IF OBJECT_ID('tempdb..#tmp_data1') IS NOT NULL 
          DROP TABLE #tmp_data1
 
CREATE TABLE #tmp_data1(
         NewOrderno varchar(10))

INSERT INTO #tmp_data1 Values ('123')
INSERT INTO #tmp_data1 Values ('456')


IF OBJECT_ID('tempdb..#tmp_data2') IS NOT NULL 
          DROP TABLE #tmp_data2
 
CREATE TABLE #tmp_data2(
         IndentCode varchar(10))

INSERT INTO #tmp_data2 Values ('123')
INSERT INTO #tmp_data2 Values ('567')
INSERT INTO #tmp_data2 Values ('456')


--合并前
Select *
From #tmp_data br 
     right join #tmp_data2 i on (i.IndentCode=br.orderno or i.IndentCode=br.PretrialOrderNo)

--合并后
Select *
From #tmp_data1 br 
     right join #tmp_data2 i on i.IndentCode=br.NewOrderno


orderno    PretrialOrderNo IndentCode
---------- --------------- ----------
123        456             123
NULL       NULL            567
123        456             456

(3 row(s) affected)

NewOrderno IndentCode
---------- ----------
123        123
NULL       567
456        456

[吉普赛的歌]

;WITH cte as(    
	SELECT i.IndentCode,
		   ISNULL(i.PayTime, i.CreateTime)  AS FundToTime,
		   i.IndentType,
		   i.BidderName                     AS BidderName,
		   i.BidderId                       AS BidCompanyId,
		   br.ContactUser                   AS BidContractUser,
		   br.MobileTelephone               AS BidContractTel,
		   br.EMail                         AS BidEmail
	FROM T_BidRegistRecord br
		   RIGHT JOIN T_Indent i ON i.IndentCode = br.OrderNo 
				AND i.IsFund IS NOT NULL
				AND i.AgencyId = '2E858978-A7C0-4D99-A602-967A31FB4828' 
	UNION
	SELECT i.IndentCode,
		   ISNULL(i.PayTime, i.CreateTime)  AS FundToTime,
		   i.IndentType,
		   i.BidderName                     AS BidderName,
		   i.BidderId                       AS BidCompanyId,
		   br.ContactUser                   AS BidContractUser,
		   br.MobileTelephone               AS BidContractTel,
		   br.EMail                         AS BidEmail 
	FROM T_BidRegistRecord br
		   RIGHT JOIN T_Indent i ON i.IndentCode = br.PretrialOrderNo
				AND i.IsFund IS NOT NULL
				AND i.AgencyId = '2E858978-A7C0-4D99-A602-967A31FB4828'
)
SELECT  DISTINCT d.IndentDetailId,
       i.IndentCode,
       i.FundToTime,
       d.FundMoney,
       i.IndentType,
       i.BidderName,
       p.TendProjectNo,
       CASE 
            WHEN ISNULL(p.IsSection, 0) = 1 THEN s.SectionNo
            WHEN ISNULL(p.IsSection, 0) = 2 THEN s.SectionNo
            ELSE ''
       END                              AS BidSectionNo,
       l.DeptName,
       i.BidCompanyId,
       i.BidContractUser,
       i.BidContractTel,
       i.BidEmail,
       p.TendAgencyId,
       p.TendProjectName,
       l.TendFileFeeWay
FROM cte AS i
   LEFT JOIN T_IndentDetail d
        ON  d.IndentId = i.IndentId
   LEFT JOIN T_BidSection s
        ON  d.BidSectionId = s.BidSectionId
   LEFT JOIN T_TendProject p
        ON  s.TendProjectId = p.TendProjectId
   LEFT JOIN T_TendProjectPlan l
        ON  s.TendProjectId = l.TendProjectId

试下这样快不快？