Query Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query Performance

Author

Topic

Ramnadh
Starting Member

23 Posts

Posted - 2004-11-16 : 00:32:46

Message
----------
Id int 4 (Primary Key)
SenderId int 4
SentOn datetime 8
Subject Title 500
PriorityTypeId 4
TypeId int 4
Message text 16
SentState varchar 2
SentFlag bit 1
FolderId int 4

MessageRecipient
--------------------
MessageId int 4 (Foreign Key refers Message (Id))
RecipientId int 4
MessageState varchar 2
ExtRecipient Email 100
BelongsTo bit 1
IsNew bit 1

Indexes on MessageRecipient table :
MessageId : Non-Clustered Index
RecipientId : Clustered Index

Message Table is having 218,493 rows and
MessageRecipient Table is having 171,915 rows

The rows in the Message and MessageRecipient Table will increase by 100 to 200 every day.

When i am executing the below query it will give 2000 rows as output in which it is taking 19 sec. of time. When i see the execution Plan 75% of time was taken for Clustered Index Scan On the Message (Id) table where it is scanning all 218,493 rows which is taking lot of time to scan through.Can anyone please try to help me out.

Any help is highly appreciated. Thanx for help in advance.

SELECT MSGS.Id
, MSGS.SentOn
, MSGS.Subject
, MSGR.MessageState
, MSGS.Message
, MSGS.FolderId
, dbo.GetMessageRecipientsFn(MSGS.Id, 0) AS 'To'
, dbo.GetMessageRecipientsFn(MSGS.Id, 1) AS 'Cc'
, MSGR.IsNew
FROM dbo.Message MSGS (NOLOCK)
INNER JOIN dbo.MessageRecipient MSGR (NOLOCK)
ON MSGS.Id = MSGR.MessageId
AND ISNULL(MSGR.MessageState,'') NOT IN ('TD','PD')
AND MSGR.RecipientId = 32
ORDER BY MSGS.SentOn DESC

Ramnadh

Kristen
Test

22859 Posts

Posted - 2004-11-16 : 00:59:39

ISNULL(MSGR.MessageState,'') is probably not helping - that can't be resolved from the index, can it?

You could try creating an index for columns MSGR.MessageId , MSGR.MessageState, MSGR.RecipientId (actually you may not need MSGR.MessageId if that is the key in the Clustered Index)

Kristen

Ramnadh
Starting Member

23 Posts

Posted - 2004-11-16 : 01:39:12

Yes, There was already Non-Clustered Index on MessageId and Clustered Index on RecipientId in the MessageRecipient table.

I had created Non-Clustered Index on SentState in the Message table but there is no use it is taking more time and doing Clustered Index Scan on the Message(Id) on all the rows of the table.

This is the execution plan that came while executing the Stored Procedure.

|--Compute Scalar(DEFINE:([MSGS].[Message]=[MSGS].[Message]))
|--Compute Scalar(DEFINE:([Expr1002]=[dbo].[GetMessageRecipientsFn]([MSGS].[Id], 0), [Expr1003]=[dbo].[GetMessageRecipientsFn]([MSGS].[Id], 1)))
|--Sort(ORDER BY:([MSGS].[SentOn] DESC))
|--Merge Join(Inner Join, MERGE:([MSGS].[Id])=([MSGR].[MessageId]), RESIDUAL:([MSGR].[MessageId]=[MSGS].[Id]))
|--Clustered Index Scan(OBJECT:([Viper_Onsite].[dbo].[Message].[PK_Message] AS [MSGS]), ORDERED FORWARD)
|--Sort(ORDER BY:([MSGR].[MessageId] ASC))
|--Clustered Index Seek(OBJECT:([Viper_Onsite].[dbo].[MessageRecipient].[IX_MessageRecipient_RecipientId] AS [MSGR]), SEEK:([MSGR].[RecipientId]=[@recipientId]), WHERE:(([MSGR].[MessageState]<'TD' OR [MSGR].[MessageState]>'

These are statistics while executing the query

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Table 'MessageRecipient'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0.
Table 'Message'. Scan count 1, logical reads 3368, physical reads 0, read-ahead reads 3335.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

Thanx for the help

Ramnadh

Kristen
Test

22859 Posts

Posted - 2004-11-16 : 02:55:50

Table 'MessageRecipient'. Scan count 1, logical reads 10
Table 'Message'. Scan count 1, logical reads 3368

This is taking 20 seconds?? Doesn't seem like hardly any work for SQL Server ...

Kristen

Ramnadh
Starting Member

23 Posts

Posted - 2004-11-16 : 04:06:05

Sorry for giving wrong Statistics. These are statistics after the execution plan is in the Cache.

These are statistics if execution plan is not in the cahce.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Table 'MessageRecipient'. Scan count 2, logical reads 22, physical reads 0, read-ahead reads 18.
Table 'Message'. Scan count 2, logical reads 7100, physical reads 0, read-ahead reads 6896.

SQL Server Execution Times:
CPU time = 411 ms, elapsed time = 12009 ms.

SQL Server Execution Times:
CPU time = 411 ms, elapsed time = 12009 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

Thanx for you help.

Ramnadh

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-11-16 : 06:38:42

For the following....
"You could try creating an index for columns MSGR.MessageId , MSGR.MessageState, MSGR.RecipientId"....you might do better if the 'combined index' had some of these columns reversed..."MSGR.MessageId , MSGR.RecipientId, MSGR.MessageState"....there's no (minimal) benefit in having an unknown value in the 2nd column....best to have it in the 3rd column.

Post the DDL for the 2 tables....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources