sql-server - 在这种情况下如何将索引扫描更改为索引查找？

Question

我在 sql server 2008 r2 上有以下查询:

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
  /*Then it exists*/
  DROP TABLE #temp
Select *
FROM
      openquery(mysqlserver, 'select
id,street,name,customer,customerId
from table1                     as t1
    left join table2    as t2 
        on t2.id=t1.id
    left join table3    as t3
        on t3.id=t1.id
    left join table4    as t4
        on t4.id=t1.producto_id
    left join table5            as t5
        on t1.id = t5.id
where t1.type=0 
    and t3.service=''X''
    and t1.check is null 
    and t1.date > date_sub(CURDATE(),INTERVAL 5 DAY)
    ') 

    SELECT * FROM #temp as t
    left join View as v on v.customerId=t.customerId collate
    SQL_Latin1_General_CP1_CI_AI

上面的语句显示了一个执行计划，其中有一个索引扫描成本为 27%，另一个成本为 26%。这与 View 内其中一个表中的左连接操作有关。

我想用这个在临时表中添加索引:

    CREATE NONCLUSTERED INDEX ix_temp_customerId on #temp(customerId)

还是一样，所以我尝试应用之前的整理来创建索引:

ALTER TABLE #temp alter COLUMN customerId varchar(30) collate SQL_Latin1_General_CP1_CI_AI

那么第一个语句的最后一个查询是:

SELECT * FROM #temp as t
        left join View as v on v.customerId=t.customerId

这显示了排序规则错误，尽管 View 使用了此排序规则，并且 View 查询中的表也使用了此排序规则。然后我再次将整理语句添加到查询中。

当前语句是:

Select *
    FROM
          openquery(mysqlserver, 'select
    id,street,name,customer,customerId
    from table1                     as t1
        left join table2    as t2 
            on t2.id=t1.id
        left join table3    as t3
            on t3.id=t1.id
        left join table4    as t4
            on t4.id=t1.producto_id
        left join table5            as t5
            on t1.id = t5.id
    where t1.type=0 
        and t3.service=''X''
        and t1.check is null 
        and t1.date > date_sub(CURDATE(),INTERVAL 5 DAY)
        ') 
ALTER TABLE #temp alter COLUMN customerId varchar(30) collate SQL_Latin1_General_CP1_CI_AI

CREATE NONCLUSTERED INDEX ix_temp_customerId on #temp(customerId)

        SELECT * FROM #temp as t
        left join View as v on v.customerId=t.customerId collate
        SQL_Latin1_General_CP1_CI_AI

这个执行计划仍然显示有一个索引扫描。我想将其更改为索引搜索，但我没有实现。

有什么提高性能的建议吗？

谢谢

Answer 1

使用 Paste The Plan @ brentozar.com 分享您的执行计划以下是说明:How to Use Paste the Plan .

我会尝试使用正确的数据类型、大小和排序规则显式创建您的 #temp 表(将 customerId 的大小与 varchar() View.customerId 的大小。

我还会考虑在索引中包含列，因为您正在使用 select *。

create table #temp (
    id int 
  , street varchar(128) collate SQL_Latin1_General_CP1_CI_AI
  , name varchar(128) collate SQL_Latin1_General_CP1_CI_AI
  , customer varchar(128) collate SQL_Latin1_General_CP1_CI_AI
  , customerId varchar(30) collate SQL_Latin1_General_CP1_CI_AI
  );

insert into #temp
select *
from openquery(mysqlserver, '
  select
    id,street,name,customer,customerId
  from table1           as t1
    left join table2    as t2 
      on t2.id=t1.id
    left join table3    as t3
      on t3.id=t1.id
    left join table4    as t4
      on t4.id=t1.producto_id
    left join table5    as t5
      on t1.id = t5.id
  where t1.type=0 
    and t3.service=''X''
    and t1.check is null 
    and t1.date > date_sub(CURDATE(),INTERVAL 5 DAY)
        ') 

create nonclustered index ix_temp_customerId 
  on #temp(customerId)
    include (id, street, name, customer);

select * 
from #temp as t
  left join View as v 
    on v.customerId=t.customerId