February 9, 2010 at 6:43 pm
.."And maintain a clustered index seek? "..
What does this mean??? Doesnt help me !:-)
February 9, 2010 at 6:56 pm
Just trying to get clarification on your question. Your initial post was on how to force a clustered index seek. With that achieved, you then asked how to do a select top 120 records. I am trying to gauge that question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 9, 2010 at 7:11 pm
I am after a execution performance that uses all SEEK scans for this stored procedure ( with test code post on this thread)..
ALTER PROCEDURE [dbo].[procTEST]
AS
BEGIN
SELECT TOP 120 A.[Page]
,C.[Category] AS 'PostCategory'
,A.[Title]
,A.[Notes]
,A.[Color]
,A.[CreatedOn]
,A.[LastUpdate]
,B.Name AS 'Author'
,(YEAR(A.[CreatedOn])*100)+MONTH(A.[CreatedOn]) AS 'YYYYMM'
FROM [dbo].[DIM_Blog] A
INNER JOIN dbo.[FACT_Blog_Users] B
ON A.BlogUserID = B.BlogUserID AND A.BlogID = B.BlogID
INNER JOIN dbo.FACT_Blog_PostCategory C
ON A.PostCategory = C.BlogCatID AND A.BlogID = C.BlogID
WHERE A.[BlogID] = 100
AND A.Deleted = 0
AND A.Page = 0
END
February 9, 2010 at 7:16 pm
Alter your query to the following:
SELECT TOP 120 A.[Page]
,C.[Category] AS 'PostCategory'
,A.[Title]
,A.[Notes]
,A.[Color]
,A.[CreatedOn]
,A.[LastUpdate]
,B.Name AS 'Author'
,(YEAR(A.[CreatedOn])*100)+MONTH(A.[CreatedOn]) AS 'YYYYMM'
FROM [dbo].[DIM_Blog] A
INNER JOIN dbo.[FACT_Blog_Users] B
ON A.BlogUserID = B.BlogUserID AND A.BlogID = B.BlogID
INNER JOIN dbo.FACT_Blog_PostCategory C
ON A.PostCategory = C.BlogCatID AND A.BlogID = C.BlogID
WHERE A.[BlogID] = 100
AND A.Deleted = 0
AND A.Page = 0
And A.BlogTransID = 1 --second part of the clustered primary key
This query created a seek on the clustered index for me. Otherwise, change your primary key to only be on one column instead of two. Then you can eliminate that line of code.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 9, 2010 at 9:34 pm
Are you pulling my legg...???
Sure I have one record of TEST data in the table.
But imaging 1000 records in the table and I want to get the top 120 records by the WHERE clause, and making 'BlogTransID = 1' does NOT get me the SELECT TOP 120 records does it ?
February 9, 2010 at 9:41 pm
The only way that you can get a clustered index seek is to filter on the clustered index. Since you're not filtering on the clustered index columns, you're not going to get a clustered index seek. I might be able to get you a nonclustered index seek, if that's acceptable.
I think you're focusing too much on getting a specific query operator without looking at the big picture. Does it perform well? There's no requirement that a query must do a clustered index seek to perform well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2010 at 10:07 pm
Gail put it very well.
If a clustered index seek is imperative, then a change to the clustered index is needed - or - you need to add the additional column.
However, the actual execution plan shows same cost for the clustered index scan that you are using, as it did for the index seek when I got it to perform a seek. That was with just 1 row and it would likely change with 1000s of rows. It may be acceptable to do a scan of the CI, or as Gail said, it may be acceptable to do a NC Seek.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 9, 2010 at 10:45 pm
Ok I just wanted to see how to change it to SEEK but true, it may not be required for practical use.
February 10, 2010 at 5:01 am
This series may help you understand. http://www.sqlservercentral.com/articles/Indexing/68439/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply