|
|
You're in the
sql server programming
group:Performance Issue
sql server programming:
---------- 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
Yes, even if i removed the functions it is taking so much time and it is
doing clustered index scan on Message (Id). I also checked by making the MessageId,RecipientId as the clustered Index on the MessageRecipient Table. It is still doing the Clustered Index Scan on Message (Id) on all the rows in the table. This is the query plan that i got while executing this query. |--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) |--Clustered Index Scan(OBJECT:([Viper_Onsite].[dbo].[MessageRecipient].[IX_MessageRecipient_MsgRecipient] AS [MSGR]), WHERE:(([MSGR].[RecipientId]=[@recipientId] AND [MSGR].[MessageState]<>'PD') AND [MSGR].[MessageState]<>'TD') These are the statistics i got while executing Stored Procedure. Table 'MessageRecipient'. Scan count 1, logical reads 545, physical reads 0, read-ahead reads 0. Table 'Message'. Scan count 1, logical reads 3368, physical reads 0, read-ahead reads 3335. Thanx for reply. [quoted text, click to view] "avnrao" wrote:
> looking at the indexes you created, it should ideally do clustered index > seek on Message table. > what do you have in the GetMessageRecipientFn? what does it do? comment it > out and see if does Clustered index scan? > can you include Recipient ID and Message ID in Clustered index on > MessageRecipient table? > > this query explains better what you are thinking to do..but the order of the > statements will not have any effect on optimization.. > > 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.MessageRecipient MSGR (NOLOCK) > INNER JOIN dbo.Message MSGS (NOLOCK) > ON MSGS.Id = MSGR.MessageId > AND MSGR.RecipientId = 32 > AND ISNULL(MSGR.MessageState,'') NOT IN ('TD','PD') > ORDER BY MSGS.SentOn DESC > > try this and let us know.. > > Av. > http://dotnetjunkies.com/WebLog/avnrao > http://www28.brinkster.com/avdotnet > > > "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message > news:7F2056F3-5E45-408E-B1E5-867C822016B1@microsoft.com... > > 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 > > > > > >
I had already kept the index in SentState and check it. But no luck it is
also taking more time and doing clustered index scan on Message (Id) table for all the rows. I am providing the script for creating two tables inserting the 100 rows for each table. drop table MessageRecipient go drop table Message go CREATE TABLE [dbo].[Message] ( [Id] [int] IDENTITY (1, 1) NOT NULL , [SenderId] [int] NULL , [SentOn] [datetime] NOT NULL , [Subject] [varchar] (500) NOT NULL , [PriorityTypeId] [int] NULL , [TypeId] [int] NOT NULL , [Message] [text] COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NULL , [SentState] [varchar] (2) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NULL , [SentFlag] [bit] NULL , [FolderId] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[Message] WITH NOCHECK ADD CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED ( [Id] ) WITH FILLFACTOR = 95 ON [PRIMARY] GO CREATE INDEX [IX_Message_SentState] ON [dbo].[Message]([SentState]) ON [PRIMARY] GO CREATE TABLE [dbo].[MessageRecipient] ( [MessageId] [int] NOT NULL , [RecipientId] [int] NULL , [MessageState] [varchar] (2) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NOT NULL , [ExtRecipient] [varchar] (100) NULL , [BelongsTo] [bit] NULL , [IsNew] [bit] NOT NULL ) ON [PRIMARY] GO CREATE CLUSTERED INDEX [IX_MessageRecipient_RecipientId] ON [dbo].[MessageRecipient]([RecipientId]) ON [PRIMARY] GO ALTER TABLE [dbo].[MessageRecipient] ADD CONSTRAINT [DF_MessageState] DEFAULT ('U') FOR [MessageState], CONSTRAINT [DF_MessageRecipient_IsNew] DEFAULT (1) FOR [IsNew] GO CREATE INDEX [IX_MessageRecipient_MessageId] ON [dbo].[MessageRecipient]([MessageId]) ON [PRIMARY] GO ALTER TABLE [dbo].[MessageRecipient] ADD CONSTRAINT [FK_MessageRecipient_MessageId] FOREIGN KEY ( [MessageId] ) REFERENCES [dbo].[Message] ( [Id] ) GO DECLARE @i INT SET @i = 0 WHILE (@i<100) BEGIN INSERT INTO Message (SenderId,SentOn,Subject,PriorityTypeId,TypeId,Message,SentState,SentFlag) VALUES ( 1,GetDate(),'Test',66,158,'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=Content-Type content="text/html; charset=unicode"> <META content="MSHTML 6.00.2800.1106" name=GENERATOR></HEAD> <BODY></BODY></HTML>' ,'R',1) SET @i = @i + 1 END SET @i = 0 WHILE (@i<100) BEGIN INSERT INTO MessageRecipient (MessageId,RecipientId,MessageState,ExtRecipient,BelongsTo,IsNew) VALUES ( @i,32,'DU',NULL,1,0) SET @i = @i + 1 END Thanx for the help. [quoted text, click to view] "avnrao" wrote:
> looks like it is doing clustered index scan on MessageRecipient table also. > that shud not be the case here. > is it because MessageState? do you have index on MessageState? > > it would be good if you generate scripts for creating tables, indexes and > some data about 100 rows or so in both these tables. > i can try and let you know.. > > Av. > http://dotnetjunkies.com/WebLog/avnrao > http://www28.brinkster.com/avdotnet > > "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message > news:B71EFB42-202A-4CC0-8F51-30EB01D21A15@microsoft.com... > > Yes, even if i removed the functions it is taking so much time and it is > > doing clustered index scan on Message (Id). > > > > I also checked by making the MessageId,RecipientId as the clustered Index > on > > the MessageRecipient Table. > > > > It is still doing the Clustered Index Scan on Message (Id) on all the rows > > in the table. > > > > This is the query plan that i got while executing this query. > > > > |--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) > > |--Clustered Index > > > Scan(OBJECT:([Viper_Onsite].[dbo].[MessageRecipient].[IX_MessageRecipient_Ms > gRecipient] > > AS [MSGR]), WHERE:(([MSGR].[RecipientId]=[@recipientId] AND > > [MSGR].[MessageState]<>'PD') AND [MSGR].[MessageState]<>'TD') > > > > These are the statistics i got while executing Stored Procedure. > > > > Table 'MessageRecipient'. Scan count 1, logical reads 545, physical reads > 0, > > read-ahead reads 0. > > Table 'Message'. Scan count 1, logical reads 3368, physical reads 0, > > read-ahead reads 3335. > > > > Thanx for reply. > > > > "avnrao" wrote: > > > > > looking at the indexes you created, it should ideally do clustered index > > > seek on Message table. > > > what do you have in the GetMessageRecipientFn? what does it do? comment > it > > > out and see if does Clustered index scan? > > > can you include Recipient ID and Message ID in Clustered index on > > > MessageRecipient table? > > > > > > this query explains better what you are thinking to do..but the order of > the > > > statements will not have any effect on optimization.. > > > > > > 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.MessageRecipient MSGR (NOLOCK) > > > INNER JOIN dbo.Message MSGS (NOLOCK) > > > ON MSGS.Id = MSGR.MessageId > > > AND MSGR.RecipientId = 32 > > > AND ISNULL(MSGR.MessageState,'') NOT IN ('TD','PD') > > > ORDER BY MSGS.SentOn DESC > > > > > > try this and let us know.. > > > > > > Av. > > > http://dotnetjunkies.com/WebLog/avnrao > > > http://www28.brinkster.com/avdotnet > > > > > > > > > "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message > > > news:7F2056F3-5E45-408E-B1E5-867C822016B1@microsoft.com... > > > > 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 > > > >
ok, I had removed the Order By , ISNULL and User defined function and run the
Query. This is the query plan it is giving |--Compute Scalar(DEFINE:([MSGS].[Message]=[MSGS].[Message])) |--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].[CI_MessageRecipient_RecipientId] AS [MSGR]), SEEK:([MSGR].[RecipientId]=[@recipientId]) ORDERED FORWARD) [quoted text, click to view] "avnrao" wrote:
> unfortunately its doing clustered index seek on these 100 rows. > let me create 218,493 and 171,915 rows and run the query. > > before that..can you post query plan for these scenarios > 1. comment Order by clause and run > 2. comment IsNull clause and run > 3. comment GetMessageRecipientsFn and run. > > this would pin point which statement is causing the scan.. > > Av. > http://dotnetjunkies.com/WebLog/avnrao > http://www28.brinkster.com/avdotnet > > > > "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message > news:8F4C85F6-6805-49CF-B465-3282950A6FAE@microsoft.com... > > I had already kept the index in SentState and check it. But no luck it is > > also taking more time and doing clustered index scan on Message (Id) table > > for all the rows. > > > > I am providing the script for creating two tables inserting the 100 rows > for > > each table. > > > > drop table MessageRecipient > > go > > drop table Message > > go > > > > CREATE TABLE [dbo].[Message] ( > > [Id] [int] IDENTITY (1, 1) NOT NULL , > > [SenderId] [int] NULL , > > [SentOn] [datetime] NOT NULL , > > [Subject] [varchar] (500) NOT NULL , > > [PriorityTypeId] [int] NULL , > > [TypeId] [int] NOT NULL , > > [Message] [text] COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NULL , > > [SentState] [varchar] (2) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NULL > , > > [SentFlag] [bit] NULL , > > [FolderId] [int] NULL > > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > > GO > > > > ALTER TABLE [dbo].[Message] WITH NOCHECK ADD > > CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED > > ( > > [Id] > > ) WITH FILLFACTOR = 95 ON [PRIMARY] > > GO > > > > CREATE INDEX [IX_Message_SentState] ON [dbo].[Message]([SentState]) ON > > [PRIMARY] > > GO > > > > > > CREATE TABLE [dbo].[MessageRecipient] ( > > [MessageId] [int] NOT NULL , > > [RecipientId] [int] NULL , > > [MessageState] [varchar] (2) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS > > NOT NULL , > > [ExtRecipient] [varchar] (100) NULL , > > [BelongsTo] [bit] NULL , > > [IsNew] [bit] NOT NULL > > ) ON [PRIMARY] > > GO > > > > CREATE CLUSTERED INDEX [IX_MessageRecipient_RecipientId] ON > > [dbo].[MessageRecipient]([RecipientId]) ON [PRIMARY] > > GO > > > > ALTER TABLE [dbo].[MessageRecipient] ADD > > CONSTRAINT [DF_MessageState] DEFAULT ('U') FOR [MessageState], > > CONSTRAINT [DF_MessageRecipient_IsNew] DEFAULT (1) FOR [IsNew] > > GO > > > > CREATE INDEX [IX_MessageRecipient_MessageId] ON > > [dbo].[MessageRecipient]([MessageId]) ON [PRIMARY] > > GO > > > > ALTER TABLE [dbo].[MessageRecipient] ADD > > CONSTRAINT [FK_MessageRecipient_MessageId] FOREIGN KEY > > ( > > [MessageId] > > ) REFERENCES [dbo].[Message] ( > > [Id] > > ) > > GO > > > > > > > > DECLARE @i INT > > SET @i = 0 > > > > WHILE (@i<100) > > BEGIN > > INSERT INTO Message > > (SenderId,SentOn,Subject,PriorityTypeId,TypeId,Message,SentState,SentFlag) > > VALUES > > ( 1,GetDate(),'Test',66,158,'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 > > Transitional//EN"> <HTML><HEAD> <META http-equiv=Content-Type > > content="text/html; charset=unicode"> <META content="MSHTML > 6.00.2800.1106" > > name=GENERATOR></HEAD> <BODY></BODY></HTML>' > > ,'R',1) > > SET @i = @i + 1 > > END > > > > SET @i = 0 > > > > WHILE (@i<100) > > BEGIN > > INSERT INTO MessageRecipient > > (MessageId,RecipientId,MessageState,ExtRecipient,BelongsTo,IsNew) VALUES > > ( @i,32,'DU',NULL,1,0) > > SET @i = @i + 1 > > > > END > > > > Thanx for the help. > > > > "avnrao" wrote: > > > > > looks like it is doing clustered index scan on MessageRecipient table > also. > > > that shud not be the case here. > > > is it because MessageState? do you have index on MessageState? > > > > > > it would be good if you generate scripts for creating tables, indexes > and > > > some data about 100 rows or so in both these tables. > > > i can try and let you know.. > > > > > > Av. > > > http://dotnetjunkies.com/WebLog/avnrao > > > http://www28.brinkster.com/avdotnet > > > > > > "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message > > > news:B71EFB42-202A-4CC0-8F51-30EB01D21A15@microsoft.com... > > > > Yes, even if i removed the functions it is taking so much time and it > is > > > > doing clustered index scan on Message (Id). > > > > > > > > I also checked by making the MessageId,RecipientId as the clustered > Index > > > on > > > > the MessageRecipient Table. > > > > > > > > It is still doing the Clustered Index Scan on Message (Id) on all the > rows > > > > in the table. > > > > > > > > This is the query plan that i got while executing this query. > > > > > > > > |--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) > > > > |--Clustered Index > > > > > > > > Scan(OBJECT:([Viper_Onsite].[dbo].[MessageRecipient].[IX_MessageRecipient_Ms > > > gRecipient] > > > > AS [MSGR]), WHERE:(([MSGR].[RecipientId]=[@recipientId] AND > > > > [MSGR].[MessageState]<>'PD') AND [MSGR].[MessageState]<>'TD') > > > > > > > > These are the statistics i got while executing Stored Procedure. > > > > > > > > Table 'MessageRecipient'. Scan count 1, logical reads 545, physical > reads > > > 0, > > > > read-ahead reads 0. > > > > Table 'Message'. Scan count 1, logical reads 3368, physical reads 0, > > > > read-ahead reads 3335. > > > > > > > > Thanx for reply. > > > > > > > > "avnrao" wrote: > > > > > > > > > looking at the indexes you created, it should ideally do clustered > index > > > > > seek on Message table.
HI rao,
any luck with the query why is doing clustered index scan on all the rows of the table. I doesn't get it till now. thanx for you help. [quoted text, click to view] "Ramnadh" wrote:
> ok, I had removed the Order By , ISNULL and User defined function and run the > Query. This is the query plan it is giving > > |--Compute Scalar(DEFINE:([MSGS].[Message]=[MSGS].[Message])) > |--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].[CI_MessageRecipient_RecipientId] > AS [MSGR]), SEEK:([MSGR].[RecipientId]=[@recipientId]) ORDERED FORWARD) > > > > "avnrao" wrote: > > > unfortunately its doing clustered index seek on these 100 rows. > > let me create 218,493 and 171,915 rows and run the query. > > > > before that..can you post query plan for these scenarios > > 1. comment Order by clause and run > > 2. comment IsNull clause and run > > 3. comment GetMessageRecipientsFn and run. > > > > this would pin point which statement is causing the scan.. > > > > Av. > > http://dotnetjunkies.com/WebLog/avnrao > > http://www28.brinkster.com/avdotnet > > > > > > > > "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message > > news:8F4C85F6-6805-49CF-B465-3282950A6FAE@microsoft.com... > > > I had already kept the index in SentState and check it. But no luck it is > > > also taking more time and doing clustered index scan on Message (Id) table > > > for all the rows. > > > > > > I am providing the script for creating two tables inserting the 100 rows > > for > > > each table. > > > > > > drop table MessageRecipient > > > go > > > drop table Message > > > go > > > > > > CREATE TABLE [dbo].[Message] ( > > > [Id] [int] IDENTITY (1, 1) NOT NULL , > > > [SenderId] [int] NULL , > > > [SentOn] [datetime] NOT NULL , > > > [Subject] [varchar] (500) NOT NULL , > > > [PriorityTypeId] [int] NULL , > > > [TypeId] [int] NOT NULL , > > > [Message] [text] COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NULL , > > > [SentState] [varchar] (2) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NULL > > , > > > [SentFlag] [bit] NULL , > > > [FolderId] [int] NULL > > > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > > > GO > > > > > > ALTER TABLE [dbo].[Message] WITH NOCHECK ADD > > > CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED > > > ( > > > [Id] > > > ) WITH FILLFACTOR = 95 ON [PRIMARY] > > > GO > > > > > > CREATE INDEX [IX_Message_SentState] ON [dbo].[Message]([SentState]) ON > > > [PRIMARY] > > > GO > > > > > > > > > CREATE TABLE [dbo].[MessageRecipient] ( > > > [MessageId] [int] NOT NULL , > > > [RecipientId] [int] NULL , > > > [MessageState] [varchar] (2) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS > > > NOT NULL , > > > [ExtRecipient] [varchar] (100) NULL , > > > [BelongsTo] [bit] NULL , > > > [IsNew] [bit] NOT NULL > > > ) ON [PRIMARY] > > > GO > > > > > > CREATE CLUSTERED INDEX [IX_MessageRecipient_RecipientId] ON > > > [dbo].[MessageRecipient]([RecipientId]) ON [PRIMARY] > > > GO > > > > > > ALTER TABLE [dbo].[MessageRecipient] ADD > > > CONSTRAINT [DF_MessageState] DEFAULT ('U') FOR [MessageState], > > > CONSTRAINT [DF_MessageRecipient_IsNew] DEFAULT (1) FOR [IsNew] > > > GO > > > > > > CREATE INDEX [IX_MessageRecipient_MessageId] ON > > > [dbo].[MessageRecipient]([MessageId]) ON [PRIMARY] > > > GO > > > > > > ALTER TABLE [dbo].[MessageRecipient] ADD > > > CONSTRAINT [FK_MessageRecipient_MessageId] FOREIGN KEY > > > ( > > > [MessageId] > > > ) REFERENCES [dbo].[Message] ( > > > [Id] > > > ) > > > GO > > > > > > > > > > > > DECLARE @i INT > > > SET @i = 0 > > > > > > WHILE (@i<100) > > > BEGIN > > > INSERT INTO Message > > > (SenderId,SentOn,Subject,PriorityTypeId,TypeId,Message,SentState,SentFlag) > > > VALUES > > > ( 1,GetDate(),'Test',66,158,'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 > > > Transitional//EN"> <HTML><HEAD> <META http-equiv=Content-Type > > > content="text/html; charset=unicode"> <META content="MSHTML > > 6.00.2800.1106" > > > name=GENERATOR></HEAD> <BODY></BODY></HTML>' > > > ,'R',1) > > > SET @i = @i + 1 > > > END > > > > > > SET @i = 0 > > > > > > WHILE (@i<100) > > > BEGIN > > > INSERT INTO MessageRecipient > > > (MessageId,RecipientId,MessageState,ExtRecipient,BelongsTo,IsNew) VALUES > > > ( @i,32,'DU',NULL,1,0) > > > SET @i = @i + 1 > > > > > > END > > > > > > Thanx for the help. > > > > > > "avnrao" wrote: > > > > > > > looks like it is doing clustered index scan on MessageRecipient table > > also. > > > > that shud not be the case here. > > > > is it because MessageState? do you have index on MessageState? > > > > > > > > it would be good if you generate scripts for creating tables, indexes > > and > > > > some data about 100 rows or so in both these tables. > > > > i can try and let you know.. > > > > > > > > Av. > > > > http://dotnetjunkies.com/WebLog/avnrao > > > > http://www28.brinkster.com/avdotnet > > > > > > > > "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message > > > > news:B71EFB42-202A-4CC0-8F51-30EB01D21A15@microsoft.com... > > > > > Yes, even if i removed the functions it is taking so much time and it > > is > > > > > doing clustered index scan on Message (Id). > > > > > > > > > > I also checked by making the MessageId,RecipientId as the clustered > > Index > > > > on > > > > > the MessageRecipient Table. > > > > > > > > > > It is still doing the Clustered Index Scan on Message (Id) on all the > > rows > > > > > in the table. > > > > > > > > > > This is the query plan that i got while executing this query. > > > > > > > > > > |--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) > > > > > |--Clustered Index > > > > > > > > > > > Scan(OBJECT:([Viper_Onsite].[dbo].[MessageRecipient].[IX_MessageRecipient_Ms > > > > gRecipient] > > > > > AS [MSGR]), WHERE:(([MSGR].[RecipientId]=[@recipientId] AND > > > > > [MSGR].[MessageState]<>'PD') AND [MSGR].[MessageState]<>'TD')
try this
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 WHERE (MSGR.MessageState = 'TD' or MSGR.MessageState = 'PD') AND MSGR.RecipientId = 32 ORDER BY MSGS.SentOn DESC
It is still showing Clustered Index Scan on the Message (Id) table.
I again tried by creating new table and inserting rows. But still it is doing clustered index scan on all the rows of the table and it is taking about 81% of the time. Can u please execute the below script again and try once. I am unable to get why was it showing 13% for clustered index scan in ur query. Please check it once. Thanx for your help. drop table MessageRecipient go drop table Message go CREATE TABLE [dbo].[Message] ( [Id] [int] IDENTITY (1, 1) NOT NULL , [SenderId] [int] NULL , [SentOn] [datetime] NOT NULL , [Subject] [varchar] (500) NOT NULL , [PriorityTypeId] [int] NULL , [TypeId] [int] NOT NULL , [Message] [text] COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NULL , [SentState] [varchar] (2) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NULL , [SentFlag] [bit] NULL , [FolderId] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[Message] WITH NOCHECK ADD CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED ( [Id] ) WITH FILLFACTOR = 95 ON [PRIMARY] GO CREATE INDEX [IX_Message_SentState] ON [dbo].[Message]([SentState]) ON [PRIMARY] GO CREATE TABLE [dbo].[MessageRecipient] ( [MessageId] [int] NOT NULL , [RecipientId] [int] NULL , [MessageState] [varchar] (2) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NOT NULL , [ExtRecipient] [varchar] (100) NULL , [BelongsTo] [bit] NULL , [IsNew] [bit] NOT NULL ) ON [PRIMARY] GO CREATE CLUSTERED INDEX [IX_MessageRecipient_RecipientId] ON [dbo].[MessageRecipient]([RecipientId]) ON [PRIMARY] GO ALTER TABLE [dbo].[MessageRecipient] ADD CONSTRAINT [DF_MessageState] DEFAULT ('U') FOR [MessageState], CONSTRAINT [DF_MessageRecipient_IsNew] DEFAULT (1) FOR [IsNew] GO CREATE INDEX [IX_MessageRecipient_MessageId] ON [dbo].[MessageRecipient]([MessageId]) ON [PRIMARY] GO ALTER TABLE [dbo].[MessageRecipient] ADD CONSTRAINT [FK_MessageRecipient_MessageId] FOREIGN KEY ( [MessageId] ) REFERENCES [dbo].[Message] ( [Id] ) GO DECLARE @i INT SET @i = 0 WHILE (@i<200000) BEGIN INSERT INTO Message (SenderId,SentOn,Subject,PriorityTypeId,TypeId,Message,SentState,SentFlag) VALUES ( 1,GetDate(),'Test',66,158,'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=Content-Type content="text/html; charset=unicode"> <META content="MSHTML 6.00.2800.1106" name=GENERATOR></HEAD> <BODY></BODY></HTML>' ,'R',1) SET @i = @i + 1 END SET @i = 1 WHILE (@i<100000) BEGIN INSERT INTO MessageRecipient (MessageId,RecipientId,MessageState,ExtRecipient,BelongsTo,IsNew) VALUES ( @i,32,'DU',NULL,1,0) SET @i = @i + 1 END [quoted text, click to view] "avnrao" wrote:
> hey Ramnadh, > I could create 2 tables with 218,493 and 171,915 rows. > when i ran the query, i saw this execution plan : > > > > as you said, clustered index scan on Message table was taking lot of time (75%)..but it takes only 13% of it. > i guess the clustered index scan on Message table is unavoidable..and scan on MessageRecipient table is because of NOT IN ('TD','PD') clause. > how did it show 75% for you? > > Av. > http://dotnetjunkies.com/WebLog/avnrao > http://www28.brinkster.com/avdotnet > > > > > "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message news:C8153E12-2AA6-4942-AD24-A9EA1D2BB332@microsoft.com... > > HI rao, > > any luck with the query why is doing clustered index scan on all the rows of > > the table. > > I doesn't get it till now. > > > > thanx for you help. > > > > "Ramnadh" wrote: > > > > > ok, I had removed the Order By , ISNULL and User defined function and run the > > > Query. This is the query plan it is giving > > > > > > |--Compute Scalar(DEFINE:([MSGS].[Message]=[MSGS].[Message])) > > > |--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].[CI_MessageRecipient_RecipientId] > > > AS [MSGR]), SEEK:([MSGR].[RecipientId]=[@recipientId]) ORDERED FORWARD) > > > > > > > > > > > > "avnrao" wrote: > > > > > > > unfortunately its doing clustered index seek on these 100 rows. > > > > let me create 218,493 and 171,915 rows and run the query. > > > > > > > > before that..can you post query plan for these scenarios > > > > 1. comment Order by clause and run > > > > 2. comment IsNull clause and run > > > > 3. comment GetMessageRecipientsFn and run. > > > > > > > > this would pin point which statement is causing the scan.. > > > > > > > > Av. > > > > http://dotnetjunkies.com/WebLog/avnrao > > > > http://www28.brinkster.com/avdotnet > > > > > > > > > > > > > > > > "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message > > > > news:8F4C85F6-6805-49CF-B465-3282950A6FAE@microsoft.com... > > > > > I had already kept the index in SentState and check it. But no luck it is > > > > > also taking more time and doing clustered index scan on Message (Id) table > > > > > for all the rows. > > > > > > > > > > I am providing the script for creating two tables inserting the 100 rows > > > > for > > > > > each table. > > > > > > > > > > drop table MessageRecipient > > > > > go > > > > > drop table Message > > > > > go > > > > > > > > > > CREATE TABLE [dbo].[Message] ( > > > > > [Id] [int] IDENTITY (1, 1) NOT NULL , > > > > > [SenderId] [int] NULL , > > > > > [SentOn] [datetime] NOT NULL , > > > > > [Subject] [varchar] (500) NOT NULL , > > > > > [PriorityTypeId] [int] NULL , > > > > > [TypeId] [int] NOT NULL , > > > > > [Message] [text] COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NULL , > > > > > [SentState] [varchar] (2) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NULL > > > > , > > > > > [SentFlag] [bit] NULL , > > > > > [FolderId] [int] NULL > > > > > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > > > > > GO > > > > > > > > > > ALTER TABLE [dbo].[Message] WITH NOCHECK ADD > > > > > CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED > > > > > ( > > > > > [Id] > > > > > ) WITH FILLFACTOR = 95 ON [PRIMARY] > > > > > GO > > > > > > > > > > CREATE INDEX [IX_Message_SentState] ON [dbo].[Message]([SentState]) ON > > > > > [PRIMARY] > > > > > GO > > > > > > > > > > > > > > > CREATE TABLE [dbo].[MessageRecipient] ( > > > > > [MessageId] [int] NOT NULL , > > > > > [RecipientId] [int] NULL , > > > > > [MessageState] [varchar] (2) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS > > > > > NOT NULL , > > > > > [ExtRecipient] [varchar] (100) NULL , > > > > > [BelongsTo] [bit] NULL ,
scott this statement looks wrong for me.
(MSGR.MessageState = 'TD' or MSGR.MessageState = 'PD') as we are checking for the rows satisfy this condition ISNULL(MSGR.MessageState,'') NOT IN ('TD','PD') which is not equivalent to above condition. any help is appreciated. [quoted text, click to view] "Scott Simons" wrote:
> try this > > 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 > WHERE > (MSGR.MessageState = 'TD' or MSGR.MessageState = 'PD') > AND MSGR.RecipientId = 32 > ORDER BY MSGS.SentOn DESC >
looking at the indexes you created, it should ideally do clustered index
seek on Message table. what do you have in the GetMessageRecipientFn? what does it do? comment it out and see if does Clustered index scan? can you include Recipient ID and Message ID in Clustered index on MessageRecipient table? this query explains better what you are thinking to do..but the order of the statements will not have any effect on optimization.. 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.MessageRecipient MSGR (NOLOCK) INNER JOIN dbo.Message MSGS (NOLOCK) ON MSGS.Id = MSGR.MessageId AND MSGR.RecipientId = 32 AND ISNULL(MSGR.MessageState,'') NOT IN ('TD','PD') ORDER BY MSGS.SentOn DESC try this and let us know.. Av. http://dotnetjunkies.com/WebLog/avnrao http://www28.brinkster.com/avdotnet [quoted text, click to view] "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message news:7F2056F3-5E45-408E-B1E5-867C822016B1@microsoft.com... > 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 > >
looks like it is doing clustered index scan on MessageRecipient table also.
that shud not be the case here. is it because MessageState? do you have index on MessageState? it would be good if you generate scripts for creating tables, indexes and some data about 100 rows or so in both these tables. i can try and let you know.. Av. http://dotnetjunkies.com/WebLog/avnrao http://www28.brinkster.com/avdotnet [quoted text, click to view] "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message Scan(OBJECT:([Viper_Onsite].[dbo].[MessageRecipient].[IX_MessageRecipient_Msnews:B71EFB42-202A-4CC0-8F51-30EB01D21A15@microsoft.com... > Yes, even if i removed the functions it is taking so much time and it is > doing clustered index scan on Message (Id). > > I also checked by making the MessageId,RecipientId as the clustered Index on > the MessageRecipient Table. > > It is still doing the Clustered Index Scan on Message (Id) on all the rows > in the table. > > This is the query plan that i got while executing this query. > > |--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) > |--Clustered Index > gRecipient] [quoted text, click to view] > AS [MSGR]), WHERE:(([MSGR].[RecipientId]=[@recipientId] AND > [MSGR].[MessageState]<>'PD') AND [MSGR].[MessageState]<>'TD') > > These are the statistics i got while executing Stored Procedure. > > Table 'MessageRecipient'. Scan count 1, logical reads 545, physical reads 0, > read-ahead reads 0. > Table 'Message'. Scan count 1, logical reads 3368, physical reads 0, > read-ahead reads 3335. > > Thanx for reply. > > "avnrao" wrote: > > > looking at the indexes you created, it should ideally do clustered index > > seek on Message table. > > what do you have in the GetMessageRecipientFn? what does it do? comment it > > out and see if does Clustered index scan? > > can you include Recipient ID and Message ID in Clustered index on > > MessageRecipient table? > > > > this query explains better what you are thinking to do..but the order of the > > statements will not have any effect on optimization.. > > > > 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.MessageRecipient MSGR (NOLOCK) > > INNER JOIN dbo.Message MSGS (NOLOCK) > > ON MSGS.Id = MSGR.MessageId > > AND MSGR.RecipientId = 32 > > AND ISNULL(MSGR.MessageState,'') NOT IN ('TD','PD') > > ORDER BY MSGS.SentOn DESC > > > > try this and let us know.. > > > > Av. > > http://dotnetjunkies.com/WebLog/avnrao > > http://www28.brinkster.com/avdotnet > > > > > > "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message > > news:7F2056F3-5E45-408E-B1E5-867C822016B1@microsoft.com... > > > 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 > > > > > > > > > > > >
unfortunately its doing clustered index seek on these 100 rows.
let me create 218,493 and 171,915 rows and run the query. before that..can you post query plan for these scenarios 1. comment Order by clause and run 2. comment IsNull clause and run 3. comment GetMessageRecipientsFn and run. this would pin point which statement is causing the scan.. Av. http://dotnetjunkies.com/WebLog/avnrao http://www28.brinkster.com/avdotnet [quoted text, click to view] "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message
news:8F4C85F6-6805-49CF-B465-3282950A6FAE@microsoft.com... > I had already kept the index in SentState and check it. But no luck it is > also taking more time and doing clustered index scan on Message (Id) table > for all the rows. > > I am providing the script for creating two tables inserting the 100 rows for > each table. > > drop table MessageRecipient > go > drop table Message > go > > CREATE TABLE [dbo].[Message] ( > [Id] [int] IDENTITY (1, 1) NOT NULL , > [SenderId] [int] NULL , > [SentOn] [datetime] NOT NULL , > [Subject] [varchar] (500) NOT NULL , > [PriorityTypeId] [int] NULL , > [TypeId] [int] NOT NULL , > [Message] [text] COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NULL , > [SentState] [varchar] (2) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS NULL , > [SentFlag] [bit] NULL , > [FolderId] [int] NULL > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > GO > > ALTER TABLE [dbo].[Message] WITH NOCHECK ADD > CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED > ( > [Id] > ) WITH FILLFACTOR = 95 ON [PRIMARY] > GO > > CREATE INDEX [IX_Message_SentState] ON [dbo].[Message]([SentState]) ON > [PRIMARY] > GO > > > CREATE TABLE [dbo].[MessageRecipient] ( > [MessageId] [int] NOT NULL , > [RecipientId] [int] NULL , > [MessageState] [varchar] (2) COLLATE SQL_Latin1_General_Pref_CP437_CI_AS > NOT NULL , > [ExtRecipient] [varchar] (100) NULL , > [BelongsTo] [bit] NULL , > [IsNew] [bit] NOT NULL > ) ON [PRIMARY] > GO > > CREATE CLUSTERED INDEX [IX_MessageRecipient_RecipientId] ON > [dbo].[MessageRecipient]([RecipientId]) ON [PRIMARY] > GO > > ALTER TABLE [dbo].[MessageRecipient] ADD > CONSTRAINT [DF_MessageState] DEFAULT ('U') FOR [MessageState], > CONSTRAINT [DF_MessageRecipient_IsNew] DEFAULT (1) FOR [IsNew] > GO > > CREATE INDEX [IX_MessageRecipient_MessageId] ON > [dbo].[MessageRecipient]([MessageId]) ON [PRIMARY] > GO > > ALTER TABLE [dbo].[MessageRecipient] ADD > CONSTRAINT [FK_MessageRecipient_MessageId] FOREIGN KEY > ( > [MessageId] > ) REFERENCES [dbo].[Message] ( > [Id] > ) > GO > > > > DECLARE @i INT > SET @i = 0 > > WHILE (@i<100) > BEGIN > INSERT INTO Message > (SenderId,SentOn,Subject,PriorityTypeId,TypeId,Message,SentState,SentFlag) > VALUES > ( 1,GetDate(),'Test',66,158,'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 > Transitional//EN"> <HTML><HEAD> <META http-equiv=Content-Type > content="text/html; charset=unicode"> <META content="MSHTML 6.00.2800.1106" > name=GENERATOR></HEAD> <BODY></BODY></HTML>' > ,'R',1) > SET @i = @i + 1 > END > > SET @i = 0 > > WHILE (@i<100) > BEGIN > INSERT INTO MessageRecipient > (MessageId,RecipientId,MessageState,ExtRecipient,BelongsTo,IsNew) VALUES > ( @i,32,'DU',NULL,1,0) > SET @i = @i + 1 > > END > > Thanx for the help. > > "avnrao" wrote: > > > looks like it is doing clustered index scan on MessageRecipient table also. > > that shud not be the case here. > > is it because MessageState? do you have index on MessageState? > > > > it would be good if you generate scripts for creating tables, indexes and > > some data about 100 rows or so in both these tables. > > i can try and let you know.. > > > > Av. > > http://dotnetjunkies.com/WebLog/avnrao > > http://www28.brinkster.com/avdotnet > > > > "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message > > news:B71EFB42-202A-4CC0-8F51-30EB01D21A15@microsoft.com... > > > Yes, even if i removed the functions it is taking so much time and it is > > > doing clustered index scan on Message (Id). > > > > > > I also checked by making the MessageId,RecipientId as the clustered Index > > on > > > the MessageRecipient Table. > > > > > > It is still doing the Clustered Index Scan on Message (Id) on all the rows > > > in the table. > > > > > > This is the query plan that i got while executing this query. > > > > > > |--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) > > > |--Clustered Index > > > > > Scan(OBJECT:([Viper_Onsite].[dbo].[MessageRecipient].[IX_MessageRecipient_Ms > > gRecipient] > > > AS [MSGR]), WHERE:(([MSGR].[RecipientId]=[@recipientId] AND > > > [MSGR].[MessageState]<>'PD') AND [MSGR].[MessageState]<>'TD') > > > > > > These are the statistics i got while executing Stored Procedure. > > > > > > Table 'MessageRecipient'. Scan count 1, logical reads 545, physical reads > > 0, > > > read-ahead reads 0. > > > Table 'Message'. Scan count 1, logical reads 3368, physical reads 0, > > > read-ahead reads 3335. > > > > > > Thanx for reply. > > > > > > "avnrao" wrote: > > > > > > > looking at the indexes you created, it should ideally do clustered index > > > > seek on Message table. > > > > what do you have in the GetMessageRecipientFn? what does it do? comment > > it > > > > out and see if does Clustered index scan? > > > > can you include Recipient ID and Message ID in Clustered index on > > > > MessageRecipient table? > > > > > > > > this query explains better what you are thinking to do..but the order of > > the > > > > statements will not have any effect on optimization.. > > > > > > > > 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.MessageRecipient MSGR (NOLOCK) > > > > INNER JOIN dbo.Message MSGS (NOLOCK) > > > > ON MSGS.Id = MSGR.MessageId > > > > AND MSGR.RecipientId = 32 > > > > AND ISNULL(MSGR.MessageState,'') NOT IN ('TD','PD') > > > > ORDER BY MSGS.SentOn DESC > > > > > > > > try this and let us know.. > > > > > > > > Av. > > > > http://dotnetjunkies.com/WebLog/avnrao
hey Ramnadh,
I could create 2 tables with 218,493 and 171,915 rows. when i ran the query, i saw this execution plan : as you said, clustered index scan on Message table was taking lot of = time (75%)..but it takes only 13% of it. i guess the clustered index scan on Message table is unavoidable..and = scan on MessageRecipient table is because of NOT IN ('TD','PD') clause. how did it show 75% for you? Av. http://dotnetjunkies.com/WebLog/avnrao http://www28.brinkster.com/avdotnet [quoted text, click to view] "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message = Seek(OBJECT:([Viper_Onsite].[dbo].[MessageRecipient].[CI_MessageRecipient=news:C8153E12-2AA6-4942-AD24-A9EA1D2BB332@microsoft.com... > HI rao, > any luck with the query why is doing clustered index scan on all the = rows of=20 > the table. > I doesn't get it till now. >=20 > thanx for you help. >=20 > "Ramnadh" wrote: >=20 > > ok, I had removed the Order By , ISNULL and User defined function = and run the=20 > > Query. This is the query plan it is giving > >=20 > > |--Compute = Scalar(DEFINE:([MSGS].[Message]=3D[MSGS].[Message])) > > |--Merge Join(Inner Join,=20 > > MERGE:([MSGS].[Id])=3D([MSGR].[MessageId]),=20 > > RESIDUAL:([MSGR].[MessageId]=3D[MSGS].[Id])) > > |--Clustered Index=20 > > Scan(OBJECT:([Viper_Onsite].[dbo].[Message].[PK_Message] AS [MSGS]), = ORDERED=20 > > FORWARD) > > |--Sort(ORDER BY:([MSGR].[MessageId] ASC)) > > |--Clustered Index=20 > > = _RecipientId]=20 [quoted text, click to view] > > AS [MSGR]), SEEK:([MSGR].[RecipientId]=3D[@recipientId]) ORDERED = (SenderId,SentOn,Subject,PriorityTypeId,TypeId,Message,SentState,SentFlag=FORWARD) > >=20 > >=20 > >=20 > > "avnrao" wrote: > >=20 > > > unfortunately its doing clustered index seek on these 100 rows. > > > let me create 218,493 and 171,915 rows and run the query. > > >=20 > > > before that..can you post query plan for these scenarios > > > 1. comment Order by clause and run > > > 2. comment IsNull clause and run > > > 3. comment GetMessageRecipientsFn and run. > > >=20 > > > this would pin point which statement is causing the scan.. > > >=20 > > > Av. > > > http://dotnetjunkies.com/WebLog/avnrao > > > http://www28.brinkster.com/avdotnet > > >=20 > > >=20 > > >=20 > > > "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message > > > news:8F4C85F6-6805-49CF-B465-3282950A6FAE@microsoft.com... > > > > I had already kept the index in SentState and check it. But no = luck it is > > > > also taking more time and doing clustered index scan on Message = (Id) table > > > > for all the rows. > > > > > > > > I am providing the script for creating two tables inserting the = 100 rows > > > for > > > > each table. > > > > > > > > drop table MessageRecipient > > > > go > > > > drop table Message > > > > go > > > > > > > > CREATE TABLE [dbo].[Message] ( > > > > [Id] [int] IDENTITY (1, 1) NOT NULL , > > > > [SenderId] [int] NULL , > > > > [SentOn] [datetime] NOT NULL , > > > > [Subject] [varchar] (500) NOT NULL , > > > > [PriorityTypeId] [int] NULL , > > > > [TypeId] [int] NOT NULL , > > > > [Message] [text] COLLATE SQL_Latin1_General_Pref_CP437_CI_AS = NULL , > > > > [SentState] [varchar] (2) COLLATE = SQL_Latin1_General_Pref_CP437_CI_AS NULL > > > , > > > > [SentFlag] [bit] NULL , > > > > [FolderId] [int] NULL > > > > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > > > > GO > > > > > > > > ALTER TABLE [dbo].[Message] WITH NOCHECK ADD > > > > CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED > > > > ( > > > > [Id] > > > > ) WITH FILLFACTOR =3D 95 ON [PRIMARY] > > > > GO > > > > > > > > CREATE INDEX [IX_Message_SentState] ON = [dbo].[Message]([SentState]) ON > > > > [PRIMARY] > > > > GO > > > > > > > > > > > > CREATE TABLE [dbo].[MessageRecipient] ( > > > > [MessageId] [int] NOT NULL , > > > > [RecipientId] [int] NULL , > > > > [MessageState] [varchar] (2) COLLATE = SQL_Latin1_General_Pref_CP437_CI_AS > > > > NOT NULL , > > > > [ExtRecipient] [varchar] (100) NULL , > > > > [BelongsTo] [bit] NULL , > > > > [IsNew] [bit] NOT NULL > > > > ) ON [PRIMARY] > > > > GO > > > > > > > > CREATE CLUSTERED INDEX [IX_MessageRecipient_RecipientId] ON > > > > [dbo].[MessageRecipient]([RecipientId]) ON [PRIMARY] > > > > GO > > > > > > > > ALTER TABLE [dbo].[MessageRecipient] ADD > > > > CONSTRAINT [DF_MessageState] DEFAULT ('U') FOR [MessageState], > > > > CONSTRAINT [DF_MessageRecipient_IsNew] DEFAULT (1) FOR [IsNew] > > > > GO > > > > > > > > CREATE INDEX [IX_MessageRecipient_MessageId] ON > > > > [dbo].[MessageRecipient]([MessageId]) ON [PRIMARY] > > > > GO > > > > > > > > ALTER TABLE [dbo].[MessageRecipient] ADD > > > > CONSTRAINT [FK_MessageRecipient_MessageId] FOREIGN KEY > > > > ( > > > > [MessageId] > > > > ) REFERENCES [dbo].[Message] ( > > > > [Id] > > > > ) > > > > GO > > > > > > > > > > > > > > > > DECLARE @i INT > > > > SET @i =3D 0 > > > > > > > > WHILE (@i<100) > > > > BEGIN > > > > INSERT INTO Message > > > > = ) [quoted text, click to view] > > > > VALUES on all the> > > > ( 1,GetDate(),'Test',66,158,'<!DOCTYPE HTML PUBLIC "-//W3C//DTD = HTML 4.0 > > > > Transitional//EN"> <HTML><HEAD> <META = http-equiv=3DContent-Type > > > > content=3D"text/html; charset=3Dunicode"> <META = content=3D"MSHTML > > > 6.00.2800.1106" > > > > name=3DGENERATOR></HEAD> <BODY></BODY></HTML>' > > > > ,'R',1) > > > > SET @i =3D @i + 1 > > > > END > > > > > > > > SET @i =3D 0 > > > > > > > > WHILE (@i<100) > > > > BEGIN > > > > INSERT INTO MessageRecipient > > > > = (MessageId,RecipientId,MessageState,ExtRecipient,BelongsTo,IsNew) VALUES > > > > ( @i,32,'DU',NULL,1,0) > > > > SET @i =3D @i + 1 > > > > > > > > END > > > > > > > > Thanx for the help. > > > > > > > > "avnrao" wrote: > > > > > > > > > looks like it is doing clustered index scan on = MessageRecipient table > > > also. > > > > > that shud not be the case here. > > > > > is it because MessageState? do you have index on = MessageState? > > > > > > > > > > it would be good if you generate scripts for creating tables, = indexes > > > and > > > > > some data about 100 rows or so in both these tables. > > > > > i can try and let you know.. > > > > > > > > > > Av. > > > > > http://dotnetjunkies.com/WebLog/avnrao > > > > > http://www28.brinkster.com/avdotnet > > > > > > > > > > "Ramnadh" <Ramnadh@discussions.microsoft.com> wrote in message > > > > > news:B71EFB42-202A-4CC0-8F51-30EB01D21A15@microsoft.com... > > > > > > Yes, even if i removed the functions it is taking so much = time and it > > > is > > > > > > doing clustered index scan on Message (Id). > > > > > > > > > > > > I also checked by making the MessageId,RecipientId as the = clustered > > > Index > > > > > on > > > > > > the MessageRecipient Table. > > > > > > > > > > > > It is still doing the Clustered Index Scan on Message (Id) =
sorry, I meant
sorry Scott, the performance problem it is still doing the clustered index
scan on the Message (Id) Column. what i am trying to change is if the MessageState Column can be made from varchar to INT. Can it give any change in the choosing the Other options rather than Clustered Index Scan. [quoted text, click to view] "Scott Simons" wrote:
> sorry, I meant >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |