Clustered Index Scan 3

[gmmastros]

I have a stored procedure that I am trying to improve performance on.

ALTER PROCEDURE dbo.MapGetStreetsForGrid  
		@GridNumber Integer  
As  
SET NOCOUNT ON  
Select	MapStreetsGridFeatures.FeatureId,  
	MapStreets.Label, 
	MapStreetsPoints.Longitude,
	MapStreetsPoints.Latitude
From	MapStreetsGridFeatures  
	Inner Join MapStreets On MapStreetsGridFeatures.FeatureId = MapStreets.FeatureId  
	Inner Join MapStreetsPoints On MapStreetsGridFeatures.FeatureId = MapStreetsPoints.FeatureId  
Where 	MapStreetsGridFeatures.GridNumber = @GridNumber  
Order By MapStreetsGridFeatures.GridNumber, MapStreetsGridFeatures.FeatureId, PointNumber

62% of the time is MapStreets.PK_MapStreets. FeatureId is the primary key for the MapStreets table. I've made this a clustered index. For some reason, it is doing a scan instead of a seek.

I have the same problem with the MapStreetsPoints table. FeatureId and Point Number are the primary key for this table. This step takes 21% of the execution time.

The execution plan looks like.

A <- B <- C <- D <- E
            ^     ^
            |     |- F
            |
            |- G

A = Select 0%
B = Sort 2%
C = Merge Join/Inner Join 11%
D = Merg Join/Inner Join 4%
E = MapStreetsGridFeatures - Index Seek 0%
F = MapStreets.PK_MapStreets - Clustered Index Scan 62%
G = MapStreetsPoints.MapStreetsPoints_FeatureId - Clustered Index Scan 21%

What can I do to make this any faster?
Am I correct in assuming that seeks are faster than scans.





-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

Excellent. Glad it's working now. Hopefully the new execution plan generator in SQL 2005 won't have these little issues.

(BTW when I meant and didn't make clear was that I wanted you to remove the clustered index from the primary key and create a new seperate clustered index seperate from the primary key index.)

To create a primary key that isn't clustered, open the table in design mode in Enterprise Manager, and click the Indexes button at the top. It's the grid with the little lightning bolt. Then uncheck the Create as Clustered check box.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Catadmin]

So you can create a non-clustered PK? I've never been able to do that. Been a while since I tried though.

Thanks for the info. I'll see if I can't play around with this, try to find out the diff between a non-clustered PK and a unique index.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[gmmastros]

mrdenny,

I never saw that screen before. I didn't misunderstand you, and I did exactly what you suggested. I just did it the hard.

I have a habit of doing things the hard way. I consider it a character flaw that I am working on.

Thank you for all your help.

Catadmin and vongrunt, I really appreciated your help as well. I can't thank you guys enough. Will little purple stars do?


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

Little purple stars are always good.

Glad it's working correctly. That's the most important part here.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Catadmin]

I will never refuse a little purple stars! @=)

You've helped me out plenty of times, so I consider it fantastic that I can actually help you out once or twice. After all, that's why we're all here, right? @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???