March 5, 2008 at 2:26 am
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 /
March 5, 2008 at 5:12 am
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
March 5, 2008 at 5:30 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2008 at 5:32 am
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
March 6, 2008 at 8:00 pm
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
March 7, 2008 at 11:34 am
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