0

I have this query:

    SELECT TOP (20) table1.Vit_Codigo
    ,table1.Vit_Nome
    ,table1.Vit_Apelido
    ,table1.Vit_codigoStatus
    ,table1.Vit_Numero
    ,table1.Vit_Ano
    ,table1.Vit_CodigoTiposExames
    ,table3.Esp_Setor
    ,table2.TEs_Descricao
FROM table1
LEFT JOIN table2 ON tb_Vitima.Vit_CodigoTiposExames = table2.TEs_Codigo
LEFT JOIN table3 ON table3 .Esp_Codigo = table1.Vit_CodigoEspecialidade
WHERE (rtrim(ltrim(lower(tb_Vitima.Vit_Rg))) = 'n/a')
    AND (
        table1.Vit_codigoStatus IN (
            3
            ,4
            )
        )
ORDER BY table1.Vit_DataAberturaReal DESC

And these plans:

enter image description here

enter image description here

I made this index:

create index IX_SELECTTOP20_INCLUDE 
    on table1
    ( Vit_Rg ,Vit_codigoStatus )
include (
Vit_Codigo , Vit_Nome , Vit_Apelido , Vit_Numero ,
               Vit_Ano , Vit_CodigoTiposExames )

...but it seems the code is using the old index.

So, Why is my index still using key lookup?

1

Neither index appears to be "covering". To avoid a key lookup, you must at least INCLUDE every column involved in your SELECT, WHERE, and ORDER BY.

The reason the older index is being used is that it's better for the query. That's partially because the index on Vit_DataAberturaReal prevents a sort operator in the plan - because the data is already sorted properly. It's also because the first column in the new index is useless in your query non-sargable. If you can remove the RTRIM, LTRIM, and LOWER, then the performance may be better.

If the index is only to support this query, you may consider adding WHERE Vit_codigoStatus IN (3,4) to your index definition.

Ideally, the WHERE columns would be part of the index itself - not in the INCLUDE. If I were to make an index specifically to support this query, it would probably be along the lines of:

CREATE INDEX [IX_SELECTTOP20_INCLUDE]
    ([Vit_DataAberturaReal])
INCLUDE(
    [Vit_Codigo], [Vit_Nome], [Vit_Apelido], [Vit_codigoStatus],
    [Vit_Numero], [Vit_Ano], [Vit_CodigoTiposExames],
    [Vit_CodigoEspecialidade], [Vit_Rg]) 
WHERE [Vit_Rg] = 'n\a' AND [Vit_codigoStatus] IN ( 3, 4 )

Note: this will not support the LTRIM(RTRIM(LOWER(...))).

0

because your new index does not have the sort
try without the sort on Vit_DataAberturaReal

the index on the sort does not include all the fields in the select

create index IX_SELECTTOP20_INCLUDE 
    on tb_vitima 
        ( Vit_Rg ,Vit_codigoStatus )
include ( Vit_Codigo,  Vit_Nome 
        , Vit_Apelido
        , Vit_Numero, Vit_Ano 
        , Vit_CodigoTiposExames )

SELECT TOP(20) tb_Vitima.Vit_Codigo,  tb_Vitima.Vit_Nome   
             , tb_Vitima.Vit_Apelido, tb_Vitima.Vit_codigoStatus 
             , tb_Vitima.Vit_Numero,  tb_Vitima.Vit_Ano 
             , tb_Vitima.Vit_CodigoTiposExames
             , lst_Especialidades.Esp_Setor, lst_TiposExames.TEs_Descricao 
FROM tb_Vitima 
LEFT OUTER JOIN lst_TiposExames 
     ON tb_Vitima.Vit_CodigoTiposExames = lst_TiposExames.TEs_Codigo 
    AND tb_Vitima.Vit_codigoStatus in(3, 4) 
LEFT OUTER JOIN lst_Especialidades 
     ON lst_Especialidades.Esp_Codigo = tb_Vitima.Vit_CodigoEspecialidade 
    AND rtrim(ltrim(lower(tb_Vitima.Vit_Rg))) = 'n/a'

order by tb_Vitima.Vit_DataAberturaReal desc
-2

Wow, I just took off the ltrim from the query, and now is at full speed. Just because of this, the query is now using paralelism:

enter image description here

But, Why is this happening? I'm really curious.

if I take RTRIM off, the query is still running slowly. it only works with LTRIM.

  • 6
    The LTRIM eliminates leading spaces (if any), when this happens, SQL can't use the index because it no longer knows what the data is going to look like that it needs to grab because you've trimmed it. Think of doing wildcard searches. Having LIKE '%SomeString' won't be able to get you an index seek, but having LIKE 'SomeString%' increases that likelihood. Think of a phonebook, If I give you my first 4 'Grut', you can still navigate to that part of the phonebook and get close to my name. However, if you say anything that contains 'Grut' you're going to have a much harder time. – Kris Gruttemeyer Aug 30 '16 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.