March 4, 2009 at 11:58 pm
Hi All,
We have thousands of SPs in our db. Here am providing the SP, i am unalbr to catch the logic of improving to fine tune. I couldnt get how can i fine tune, and whats wrong in the SP as i cross checked num of times but couldnt find any logic to improve and fine TUNE.
Please HELP me...
StoredProcedure.
----------------------------------------------
/****** Object: StoredProcedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Ind_Newton_HomePage]
(@userID varchar(8000))
as
-- DECLARARIONS
declare @Roles varchar(8000)
declare @RoleID varchar(8000)
declare @tbl varchar(8000)
declare @profileName varchar(8000)
declare @sql varchar(8000)
declare @query varchar(8000)
declare @profileId varchar(100)
declare @childProcess varchar(100)
declare @parentProcess varchar(100)
declare @ServiceActionID varchar(100)
declare @PageID varchar(100)
declare @cnt int
Create table #tmpDataSource (DataSource varchar(8000),ProfileName varchar(1000), childProcess varchar(8000), ParentID int, ListActionID varchar(50), PageID varchar(50))
create table #tmpHome(
MenuId int, Name varchar(8000), Description varchar(8000),
ParentId int, Type varchar(8000), SortOrder int,
IsFunctional nchar(1), ServiceActionId varchar(8000), StateId varchar(8000),
CaptionId varchar(8000), urlabspath varchar(8000), imageurl varchar(8000),
width char(3), height char(3), onclick varchar(8000),
AuthRequired char(3), DateCreated datetime, CreatedBy varchar(8000),
DateModified datetime, ModifiedBy varchar(8000), OwnerOrgId varchar(8000),
OwnerLocId varchar(8000), PAGEID varchar(8000), OldOnClick varchar(8000),
UrlRelPath varchar(8000)
)
select @Roles = Roles from Users where UserID = @userid
set @Roles = replace(@Roles,'SysRecipient' + ',','')
while(len(@Roles)>1)
begin
set @RoleID = substring(@Roles,0,charindex(',',@Roles,0))
set @Roles = replace(@Roles,@RoleID + ',','')
insert into #tmpHome (MenuId,Name,Description,ParentId,Type,SortOrder,IsFunctional,ServiceActionId,StateId,CaptionId,urlabspath,imageurl,
width,height,onclick,AuthRequired,DateCreated,CreatedBy,DateModified,OwnerOrgId,OwnerLocId,PAGEID,OldOnClick,UrlRelPath)(
select MenuId,Name,Description,ParentId,Type,SortOrder,IsFunctional,ServiceActionId,StateId,CaptionId,urlabspath,imageurl,
width,height,onclick,AuthRequired,DateCreated,CreatedBy,DateModified,OwnerOrgId,OwnerLocId,PAGEID,OldOnClick,UrlRelPath from menus
where serviceactionid in (
select distinct serviceactionid from workflowTransitions where serviceactiontype='select'
and permissiontoken like '%' + @RoleID +'%' AND 1 in (SELECT 1 from Roles where RoleId like '%' + @RoleId + '%' ) ))
-- AND IsGroupInboxEnabled = 'Y'
end
--select @cnt = count(*) from #tmpHome
--select * from #tmpHome
insert into #tmpDataSource (DataSource, ProfileName, childProcess, ParentID,ListActionID,PageID)
(select distinct DPC.DataSource, DPC.Name, T.Name, T.ParentID, T.ServiceActionID, T.PageID from DataProfileClasses DPC
inner join ServiceActions SA on SA.DataProfileID = DPC.DataProfileClassID
inner join #tmpHome T on T.ServiceActionID = SA.ServiceActionID)
select @cnt = count(*) from #tmpDataSource
--select * from #tmpDataSource
create table #tmpFinalTable (TableName varchar(50),childProcess varchar(50),parentProcess varchar(50),Inbox int, GroupInbox int, FollowUp int, ListActionID varchar(50), PageID varchar(50))
while(@cnt>0)
begin
select top 1 @tbl = T.DataSource,@profileName = T.ProfileName, @childProcess = T.childProcess, @parentProcess = (select Name from Menus where MenuID = T.ParentID),@ServiceActionID = ListActionID ,@PageID = PageID from #tmpDataSource T
delete #tmpDataSource where DataSource = @tbl
if exists(select DataProfileID from GlobalInboxType where DataProfileID = @profileName)
begin
select @profileId = DataProfileID from GlobalInboxType where DataProfileID = @profileName
print @profileId + ' @@'
set @sql = 'insert into #tmpFinalTable (TableName, childProcess, parentProcess, Inbox, GroupInbox, FollowUp,ListActionID,PageID) (select ''' + @tbl + ''' as TableName, '''
+ @childProcess + ''' as childProcess, ''' + @parentProcess + ''' as parentProcess, (select count(*) FROM ' + @tbl
+ ' INNER JOIN DataProfileClassWorkflowStates DP ON DP.StateId = ' + @tbl + '.StateId AND ' + @tbl + '.RecipientId = ''' + @userid
+ ''' where StateName <> ''Request Completed'' And DP.StateId<>''IndCAEJobTemplateCreated'' And DP.StateId<>''IndCFAOAPSInvoiceAccountApproved'' AND DP.StateId<>''IndCoeGlobalAmendmentCanceledState'' and FollowUp <> ''Y'''
if(@tbl in ('IndCoeImports', 'IndCAEJob'))
begin
set @sql = @sql + ' and InboxEnabled = 1 '
end
set @sql = @sql + ') as Inbox,'
+ '(select count(*) from ' + @tbl +' where RecipientId in (
Select userid from AgIndCoeDeskUsers where StateId IN(
select FromStateId from (
SELECT top 100 percent dbo.WorkflowTransitions.FromStateId, dbo.WorkflowTransitions.DataProfileClassId, dbo.RolePrincipals.PrincipalId
FROM dbo.WorkflowTransitions
JOIN dbo.Roles ON dbo.WorkflowTransitions.PermissionToken LIKE ''%'' + dbo.Roles.RoleId + ''%''
JOIN dbo.RolePrincipals ON dbo.RolePrincipals.RoleId = dbo.Roles.RoleId
WHERE (dbo.WorkflowTransitions.FromStateId <> dbo.WorkflowTransitions.ToStateId)
AND (dbo.RolePrincipals.RoleId <> ''SysRecipient'')
AND (dbo.WorkflowTransitions.FromStateId IN (SELECT stateid FROM AgIndCoeDeskUsers))
And DataProfileClassId='''+ @profileId + '''
group by dbo.WorkflowTransitions.FromStateId, dbo.WorkflowTransitions.DataProfileClassId, dbo.RolePrincipals.PrincipalId
ORDER BY dbo.RolePrincipals.PrincipalId ) A
where PrincipalId = ''' + @userid + ''')
AND (dbo.' + @tbl +'.Stateid IN (SELECT stateid FROM DataProfileClassWorkflowStates where StateName <> ''Request Completed'' AND StateId<>''AgIndCoeGlobalAmendmentCanceledState'' AND StateId<>''AgIndCFAOAPSInvoiceAccountApproved'' AND StateId <>''AgIndCAEJobTemplateCreated''))
)'
if(@tbl in ('AgIndCoeImports', 'AgIndCAEJob'))
begin
set @sql = @sql + ' and InboxEnabled = 1 '
end
set @sql = @sql + ') as GroupInbox,'
+ '(select count(*) FROM ' + @tbl +
+ ' INNER JOIN DataProfileClassWorkflowStates DP ON DP.StateId = ' + @tbl + '.StateId AND ' + @tbl + '.RecipientId = ''' + @userid
+ ''' where DP.StateId <>''AgIndCAEJobTemplateCreated'' AND StateName <> ''Request Completed'' and ' + @tbl + '.FollowUp = ''Y'' AND ' + @tbl + '.RecipientId = ''' + @userid + ''''
if(@tbl in ('AgIndCoeImports', 'AgIndCAEJob'))
begin
set @sql = @sql + ' and InboxEnabled = 1 '
end
set @sql = @sql + ') as FollowUp, ''' + @ServiceActionID + ''' as ListActionID, ''' + @PageID + ''' as PageID)'
print @sql
exec(@sql)
end
set @cnt = @cnt-1
end
select distinct parentProcess from #tmpFinalTable
select * from #tmpFinalTable
select * from NewtonDocuments
--drop table #tmpDataSource
--drop table #tmpHome
--drop table #tmpFinalTable
/* Added By Prakash*/
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'T' AND name = '#tmpDataSource')
BEGIN
DROP Table #tmpDataSource
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'T' AND name = '#tmpHome')
BEGIN
DROP Table #tmpHome
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'T' AND name = '#tmpFinalTable')
BEGIN
DROP Table #tmpFinalTable
END
---------------------------------------------------------------------------
Thanks,
CH&HU.
March 5, 2009 at 1:34 am
I'd suggest, to start, run the proc with Statistics Time and Statistics IO on and find out where the slow pieces are and then focus on the pieces that are slow.
What does that while loop in the middle do? What is it that you need to do repeatedly?
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
March 5, 2009 at 1:49 am
Many things I can suggest...
1) Identify the Need of While Loop as before Post
2) Find Order by Distinct all required
3) Please check why 100 percent is required in the query
4) if exist is not at all required u are confident that tables are
created then what is there to drop that table.
5) Please confirm all the varialbels that are declared are used...
6) Finally Check the endexes are properly created....
check all these you will surely get improvements.....
March 5, 2009 at 2:03 am
CH&HU (3/4/2009)
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'T' AND name = '#tmpDataSource')BEGIN
DROP Table #tmpDataSource
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'T' AND name = '#tmpHome')
BEGIN
DROP Table #tmpHome
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'T' AND name = '#tmpFinalTable')
BEGIN
DROP Table #tmpFinalTable
END
One thing I just noticed. Not one of those EXISTS will ever return true. Temp tables aren't created in the current database and as such, checking sysobjects will find no match.
Also, the type for user tables isn't T. There is no object that has a type of 'T'. The type for user tables is 'U'
Did anyone ever test those EXISTS statements before adding them to the proc?
If you created a temp table in your proc, you can drop it without worry, it will exist as no one else can drop a temp table that you created. If you don't drop them then, being temp tables, they will be dropped automatically when the proc ends
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
March 5, 2009 at 2:16 am
Iam thakful for your efforts on my Procedure...
As i have a bit knowledge on SPs but now i came to learn into dept.....
Kindly let me know if anything else can be done .......
Thanks,
CH&HU.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply