View Performance with a Like operater

  • Hello....

    I use some Nested Set Tables to generate Rights to a Different Tables. The Following View shows all Projects, a user has rights to.

    ALTER VIEW [mangel].[MangelProjekteSelect] AS

    WITH ProjektRolle (linker_rand, rechter_rand) AS (

    SELECT linker_rand, rechter_rand

    FROM dbo.RollenHierarchie

    WHERE rolle_id = 116

    ),

    AlleRollen (rechte_von, rechte_bis) AS (

    SELECT DISTINCT

    BenutzerRechteUsr.rechte_von

    , BenutzerRechteUsr.rechte_bis

    FROM dbo.RollenHierarchie

    INNER JOIN ProjektRolle

    ON ProjektRolle.linker_rand <= RollenHierarchie.linker_rand

    AND ProjektRolle.rechter_rand >= RollenHierarchie.rechter_rand

    INNER JOIN dbo.BenutzerRechteUsr

    ON BenutzerRechteUsr.rolle_id = RollenHierarchie.rolle_id

    )

    SELECT --DISTINCT

    p.projekt_id

    ,p.projekt_name

    ,p.betrieb_id

    ,p.ark_id

    ...

    ,p.subkostenstelle_id

    ,m.mangel_projekt_id

    ,m.wohnungen

    ,m.mangel_bezeichnung

    ....

    FROM dbo.Projekte as p

    INNER JOIN AlleRollen

    ON ((1000 * p.betrieb_id + p.ark_id) BETWEEN rechte_von AND rechte_bis)

    LEFT JOIN mangel.Projekte as m

    ON p.projekt_id = m.mangel_projekt_id

    On the Projekte-Table There is an index on projekt_name (unclustered)

    If I Select * FROM the View where Projekt_name LIKE %xxx% the view needs over a miniute to show the result. (3 Lines)

    If I delete the Index it works very Fast.

    The index was created to speed up some other Views.

    Any Ideas what i could do.

    /edit.

    The Execution Plan shows an index Scan on projekt_name.

    If i use the Like operator on another Column it works fast.

    damn, im searching for hours now

    edit /

  • I did the following To speed up the View:

    RTRIM(LTRIM(projekt_name)

    The execution plan shows, that the index is not used any longer...

    Did it for me

  • When you use the Like operator with a leading wildcard (%) an index scan is the best you will get. Also using Select * also will give you at best an index scam because, unless you have all the columns in the index, you cannot have a covering index.

  • This may come as a bit of a shock, but the usual method of performance tuning is not to try to eliminate the use of an index, but rather to get the use of an index optimized. From the sounds of things, you found that a table scan was faster than an index scan, which could be true, but there are probably other tuning opportunities. For example, are you sure you need the DISTINCT? That's a pretty expensive operator to have in a query. What does the execution plan look like?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • If you really need to use a LIKE '%xxx%' clause and can't change it to LIKE 'xxx%' so it can use an index perhaps something like this will help:

    Assuming that your dbo.Projekte table is fairly wide put your index back on projeckt_name, and do your LIKE against it first and put the resulting 'projekt_id's into a temp table / table variable (I'm assuming that projekt_id is the clustered primary key on dbo.Projekte):

    create table #MatchingProjects

    (projekt_id int primary key)

    insert into #MatchingProjects

    (projekt_id)

    select

    projekt_id

    from dbo.Projekte

    where projekt_name like '%xxx%'

    Then join the #MatchingProjects table against your view on projekt_id. This way you still have to cop an index scan on the mangel.Projekte.ix_projekt_name index (which is very narrow and hopefully not too costly) but the actual join into the view will be done on a clustered key.

    No guarantees here but give it a test - it may make a big difference in your performance.

    Almost forgot - if you end up deciding that an clustered index scan on the Projecke table is your optimal solution and you still want your index on projekt_name for other views to use you can force a clustered index scan with an index hint - something like:

    ...

    from dbo.Projekte with (index=pk_Projekte)

    where pk_Projekte is the name of your clustered index on the Projekte table. I almost regret suggesting this as index hints should only ever be used as a very last resort, but that decision is ultimately yours.

    Regards,

    Jacob

  • Thanks to all of you for helping me out.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply