July 4, 2011 at 11:32 am
Hi, I have following storage procedure:
ALTER PROCEDURE [dbo].[cms_Forum_CalcLastPost]
(
@pUserId uniqueidentifier =null,
@pWebProjectId uniqueidentifier =null,
@pComponentId uniqueidentifier
)
as
begin try
begin transaction
declare
@pSubForumLastPostDate datetime=null,
@pForumTopicLastPostDate datetime=null,
@pForumTopicLastPostId uniqueidentifier=null,
@pSubForumLastPostId uniqueidentifier=null
/**/
select
top 1
@pForumTopicLastPostDate = fp.POST_DATE,
@pForumTopicLastPostId=ft.LAST_POST_ID
from
cms_tblComponent c
inner join
cms_tblForumTopic ft
on c.COMPONENT_ID=ft.COMPONENT_ID
inner join cms_tblForumPost fp
on ft.LAST_POST_ID=fp.FORUM_POST_ID
where
c.OWNER_ID=@pComponentId
and
c.INSTANCE_STATE_ID='F7875EBF-B289-4B03-B947-DCA6AD82B0E8'
and
fp.INSTANCE_STATE_ID in ('F7875EBF-B289-4B03-B947-DCA6AD82B0E8','C5335F7B-B1B6-4A02-B0DC-C0AECEE1C2A8')
order by fp.POST_DATE desc
select
top 1
@pSubForumLastPostDate = fp.POST_DATE,
@pSubForumLastPostId=fp.FORUM_POST_ID
from
cms_tblComponent c
inner join cms_tblForum f on
f.COMPONENT_ID=c.COMPONENT_ID
inner join
cms_tblComponentChild cc
on cc.CHILD_ID=c.COMPONENT_ID
and cc.COMPONENT_ID=@pComponentId
inner join cms_tblForumPost fp
on f.LAST_POST_ID=fp.FORUM_POST_ID
where
c.INSTANCE_STATE_ID='F7875EBF-B289-4B03-B947-DCA6AD82B0E8'
and
fp.INSTANCE_STATE_ID in ('F7875EBF-B289-4B03-B947-DCA6AD82B0E8','C5335F7B-B1B6-4A02-B0DC-C0AECEE1C2A8')
order by fp.POST_DATE desc
declare
@pLastPostId uniqueidentifier=null
if isnull(@pSubForumLastPostDate,'1900-01-01') >isnull(@pForumTopicLastPostDate,'1900-01-01')
begin
set @pLastPostId=@pSubForumLastPostId
end
else
begin
set @pLastPostId=@pForumTopicLastPostId
end
update cms_tblForum
set
LAST_POST_ID =@pLastPostId
where COMPONENT_ID=@pComponentId
declare @pParentForumId uniqueidentifier=null
select @pParentForumId = COMPONENT_ID
from
cms_tblComponentChild
where CHILD_ID =@pComponentId
if @pParentForumId is not null
begin
exec cms_Forum_CalcLastPost
@pUserId=@pUserId,
@pWebProjectId=@pWebProjectId,
@pComponentId=@pParentForumId
end
exec cms_Component_DoOnChange_
@pUserId=@pUserId,
@pWebProjectId=@pWebProjectId,
@pComponentId=@pComponentId
if @@TRANCOUNT>0
commit transaction
end try
begin catch
if @@TRANCOUNT>0
rollback transaction
exec dbo.yom_raiserror
end catch
and table cms_tblForumPost has following indexes:
/****** Object: Index [IX_cms_tblForumPost_1] Script Date: 07/01/2011 14:34:24 ******/
CREATE NONCLUSTERED INDEX [IX_cms_tblForumPost_1] ON [dbo].[cms_tblForumPost]
(
[POST_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_cms_tblForumPost] Script Date: 07/01/2011 14:34:54 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_cms_tblForumPost] ON [dbo].[cms_tblForumPost]
(
[FORUM_TOPIC_ID] ASC,
[USER_NAME] ASC,
[POST_HASH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
GO
execution freezes when it called second time (calling same sp for parent) on first select:
declare @pParentForumId uniqueidentifier=null
select @pParentForumId = COMPONENT_ID
from
cms_tblComponentChild
where CHILD_ID =@pComponentId
if @pParentForumId is not null
begin
exec cms_Forum_CalcLastPost
@pUserId=@pUserId,
@pWebProjectId=@pWebProjectId,
@pComponentId=@pParentForumId
end
.....
select
top 1
@pForumTopicLastPostDate = fp.POST_DATE,
@pForumTopicLastPostId=ft.LAST_POST_ID
from
cms_tblComponent c
inner join
cms_tblForumTopic ft
on c.COMPONENT_ID=ft.COMPONENT_ID
inner join cms_tblForumPost fp
on ft.LAST_POST_ID=fp.FORUM_POST_ID
where
c.OWNER_ID=@pComponentId
and
c.INSTANCE_STATE_ID='F7875EBF-B289-4B03-B947-DCA6AD82B0E8'
and
fp.INSTANCE_STATE_ID in ('F7875EBF-B289-4B03-B947-DCA6AD82B0E8','C5335F7B-B1B6-4A02-B0DC-C0AECEE1C2A8')
order by fp.POST_DATE desc
Waiting time for execution is about 1min 30 sec
If I drop Index on POST_DATE, it executes fine in 0 sec.
if I exclude [order by fp.POST_DATE] from select statement -same effect
What is wrong with my code. Any Ideas?
July 4, 2011 at 1:54 pm
what's the version of sql server?
July 4, 2011 at 2:19 pm
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2) . It happend after system update
July 5, 2011 at 5:01 am
can you post execution plan? (actual + estimed plans)
July 5, 2011 at 5:07 am
see attachment
July 5, 2011 at 6:59 am
if you remove index ix_cms_tblforumpost_1 and keep order by clause? do you have same effects?
do you really need this index? some consolidation or just to order data?
July 5, 2011 at 7:58 am
yep, If I remove index and keep order by I have no problems with my sp (it executes in 0sec).
If I dont remove index and remove order by - sp executes in 0 sec.
I created this index in order to speed up my transaction. As I have few data at the moment removing this index is not a problem for performance. But I suppose it helps when data grows to millions records (now I have 20 000 rows).
July 5, 2011 at 8:27 am
khourshed (7/5/2011)
yep, If I remove index and keep order by I have no problems with my sp (it executes in 0sec).If I dont remove index and remove order by - sp executes in 0 sec.
good!!
I created this index in order to speed up my transaction. As I have few data at the moment removing this index is not a problem for performance. But I suppose it helps when data grows to millions records (now I have 20 000 rows).
sort data is not good (should be studied carefully!)!!! this can increase the workload in your server, because is necessary allocate space in memory (if the memory availbled isn't sufficent then can cause swap memory or use virtual memory!) and with increase of your database this problem will constant!!
you can remove index or order by clause from procedure!! because both are doing same things!! one after another!!!! if you'll to use a consolidation by post_id, for example: "how many post have in table when post_id='2011-07-03'", then keep the index!!! otherwise keep order by clause!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply