[SOLVED] How to change index scan to index seek in this case?

Issue

I have the following query on a 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

The above statement shows an execution plan where there is an index scan which costs 27% and another one which cost 26%. This is in related of the left join operation in one of the tables inside the view.

I thought to add index in the temp table with this:

    CREATE NONCLUSTERED INDEX ix_temp_customerId on #temp(customerId)

It still the same, so I tried to apply the collate before to create the index:

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

Then the last query of the first statement was:

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

This shows the collation error despite the view uses this collation, and the tables inside the view query uses this collation. Then I add again the collate statement to the query.

The current statement is:

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

This execution plan still showing that there is an index scan. I would like to change this into index seek but I do not achieve that.

Any suggestion to achieve better performance?

Thank you

Solution

Share your execution plans using Paste The Plan @ brentozar.com here are the instructions: How to Use Paste the Plan.

I would try explicitly creating your #temp table with the correct datatype, size, and collation (match the size of customerId to the varchar() size of View.customerId).

I would also consider including the columns in the index, since you are using 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

Answered By – SqlZim

Answer Checked By – Dawn Plyler (BugsFixing Volunteer)