January 7, 2009 at 6:43 am
Hi All,
I am new to performance tuning. I wanted to do tune below stored procedure. I can not avoid sorting in this SP, because functionality requires it. Please help me that anything needs to be change.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Asco_CheckAssoc]
(
@p_sLoggedUserType char(1),
@p_iLoggedUserId int,
@p_sRoom char(1),
@p_sActionCode varchar(50),
@p_sView varchar(20),
@p_sFilterBy varchar(20),
@p_sSortBy varchar(50),
@p_sStartWith varchar(100), -- CR 26038 Project name length is increased to 100 characters
@p_sContaining varchar(100), -- CR 26038 Project name length is increased to 100 characters
@p_sOrderBy varchar(50),
@p_iPageSize int,
@p_iStartRecord int OUTPUT,
@p_iCurrentPage int OUTPUT,
@p_iNoOfPages int OUTPUT,
@p_bookedProjects char(1)= NULL
)
AS
DECLARE
@sSQL nvarchar(4000),
@iTotalRecords int,
@iUpperLimit int,
@iLowerLimit int,
@iProjectScopeCode int,
@iProjectScopeObjId int,
@iUserKey int,
@iProjAcctGrpId int,
@iProjOfficeLocId int,
@iProjSiteLocId int,
@iProjCustId int,
@sRoom char(1),
@sFamily varchar(8),
@sName varchar(24),
@sDisplayCodeValueInd char(1),
@iRowCount int
SET NOCOUNT ON
CREATE TABLE #OrderedResultSet
(
counter int identity(1,1),
user_type char(1),
user_id int,
project_id int,
identifier varchar(100), -- CR 26038 Project name length is increased to 100 characters
xref varchar(32),
contact_name varchar(50),
description varchar(160),
customer_id int,
customer_name varchar(100), -- CR 25891- Client name length is increased to 100 characters
status varchar(16),
name varchar(24),
funding_type_code varchar(12),
user_field_index int
)
CREATE TABLE #LocationsList
(
location_keyint,
location_roomchar(1),
location_familyvarchar(8),
location_idint
)
IF ( @p_sActionCodeis null or @p_sActionCode= '' )SET @p_sActionCode= 'REFRESH'
IF ( @p_sViewis null or @p_sView= '' )SET @p_sView= 'ALL'
IF ( @p_sFilterByis null or @p_sFilterBy= '' )SET @p_sFilterBy= 'project_id'
IF ( @p_sSortByis null or @p_sSortBy= '' )SET @p_sSortBy= 'project_id'
IF ( @p_sOrderByis null or @p_sOrderBy= '' )SET @p_sOrderBy= 'ASC'
IF ( @p_iPageSizeis null or @p_iPageSize= '' )SET @p_iPageSize= 10
IF ( @p_iStartRecordis null or @p_iStartRecord= '' )SET @p_iStartRecord = 1
SELECT
@sRoom = room,
@sFamily = family,
@sName = name,
@iUserKey = user_key
FROM
CXmain.dbo.Users WITH (READUNCOMMITTED)
WHERE
user_id =@p_iLoggedUserId
ANDuser_type = @p_sLoggedUserType
SELECT
@iProjectScopeCode = project_scope_code,
@iProjectScopeObjId = project_scope_object_key
FROM
CXmain.dbo.User_Dashboard_Scope WITH (READUNCOMMITTED)
WHERE
user_key = @iUserKey
IF @iProjectScopeCode IS NULL
SELECT @iProjectScopeCode = 1
IF @iProjectScopeCode = 2
SELECT @iProjAcctGrpId = @iProjectScopeObjId
IF @iProjectScopeCode = 4
BEGIN
SELECT
@iProjOfficeLocId = location_id
FROM
CXmain.dbo.Office_Locations WITH (READUNCOMMITTED)
WHERE
office_location_key = @iProjectScopeObjId
INSERT INTO #LocationsList(
location_key,
location_room,
location_family,
location_id
)
SELECT
office_location_key,
room,
family,
location_id
FROM
office_Locations
WHERE
office_location_key = @iProjectScopeObjId
SELECT @iRowCount = @@ROWCOUNT
WHILE ( @iRowCount > 0 )
BEGIN
INSERT INTO #LocationsList(
location_key,
location_room,
location_family,
location_id
)
SELECT
office_location_key,
room,
family,
location_id
FROM
office_Locations
WHERE
NOT EXISTS (
SELECT 1
FROM
#LocationsList
WHERE
office_location_key = location_key
)
AND EXISTS (
SELECT 1
FROM
#LocationsList
WHERE
location_room = parent_room
AND location_family = parent_family
AND location_id = parent_location_id
)
SELECT @iRowCount = @@ROWCOUNT
END
END --End IF (@iProjectScopeCode = 4)
IF @iProjectScopeCode = 5
BEGIN
SELECT
@iProjSiteLocId = location_id
FROM
CXmain.dbo.Site_Locations WITH (READUNCOMMITTED)
WHERE
site_location_key = @iProjectScopeObjId
INSERT INTO #LocationsList (
location_key,
location_room,
location_family,
location_id
)
SELECT
site_location_key,
room,
family,
location_id
FROM
site_Locations
WHERE
site_location_key = @iProjectScopeObjId
SELECT @iRowCount = @@ROWCOUNT
WHILE ( @iRowCount > 0 )
BEGIN
INSERT INTO #LocationsList (
location_key,
location_room,
location_family,
location_id
)
SELECT
site_location_key,
room,
family,
location_id
FROM
site_Locations
WHERE
NOT EXISTS (
SELECT 1
FROM
#LocationsList
WHERE
site_location_key = location_key
)
AND EXISTS (
SELECT 1
FROM
#LocationsList
WHERE
location_room = parent_room
AND location_family = parent_family
AND location_id = parent_location_id
)
SELECT @iRowCount = @@ROWCOUNT
END
END --End IF @iProjectScopeCode = 5
IF @iProjectScopeCode = 6
SELECT @iProjCustId = @iProjectScopeObjId
SELECT
@sDisplayCodeValueInd = display_code_value_ind
FROM
Cxmain.dbo.Code_Table_Label WITH (READUNCOMMITTED)
WHERE
room = @sRoom
AND family = @sFamily
AND code_table_id = 1020
SET @sSQL = N'
INSERT INTO #OrderedResultSet
(
user_type,
user_id,
project_id,
identifier,
xref,
contact_name,
description,
customer_id,
customer_name,
status,
name,
funding_type_code,
user_field_index
)
'
IF (@p_sView = 'ALL')
BEGIN
SET @sSQL = @sSQL + N'
SELECT
p.project_user_type,
p.project_user_id,
p.project_id,
p.project_identifier,
p.project_xref,
p.project_contact_name,
p.project_description,
p.project_customer_id,
p.project_customer_name,
p.project_status_name,
p.project_owner_name,
p.project_funding_type_code,
p.project_user_field_index
FROM
CXmain.dbo.Viewable_Projects_All_Security_In_One_VW p WITH (READUNCOMMITTED)
WHERE
p.user_type = @p_sLoggedUserType
AND p.user_id = @p_iLoggedUserId
AND p.bus_rule_mask LIKE ''1%''
'
END -- end all
ELSE IF (@p_sView = 'MY')
BEGIN
SET @sSQL = @sSQL + N'
SELECT
p.user_type project_user_type,
p.user_id project_user_id,
p.project_id project_id,
p.identifier project_identifier,
p.xref project_xref,
p.contact_name project_contact_name,
p.description project_description,
p.customer_id project_customer_id,
ISNULL(c.customer_name,''<Secured>'') project_customer_name,
s.status_name project_status_name,
@sName project_owner_name,
p.funding_type_code project_funding_type_code,
p.user_field_index project_user_field_index
FROM
CXmain.dbo.Projects p WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Status s WITH (READUNCOMMITTED)
ON p.project_status_id = s.status_id
LEFT OUTER JOIN CXmain.dbo.Secured_Client_Display_Base_vw c WITH (READUNCOMMITTED)
ON p.customer_id = c.customer_id
AND c.active_user_id = @p_iLoggedUserId
WHERE
p.user_type = @p_sLoggedUserType
AND p.user_id = @p_iLoggedUserId
AND s.room = @sRoom
AND s.family = @sFamily
AND s.application_object_id = 3
'
IF (@p_bookedProjects = 'Y')
BEGIN
SET @sSQL = @sSQL + '
AND s.bus_rule_mask like ''1%''
'
END
END-- my
ELSE IF (@p_sView = 'EDIT')
BEGIN
SET @sSQL = @sSQL + N'
SELECT
p.project_user_type,
p.project_user_id,
p.project_id,
p.project_identifier,
p.project_xref,
p.project_contact_name,
p.project_description,
p.project_customer_id,
p.project_customer_name,
p.project_status_name,
p.project_owner_name,
p.project_funding_type_code,
p.project_user_field_index
FROM
CXmain.dbo.Viewable_Projects_All_Security_In_One_VW p WITH (READUNCOMMITTED)
WHERE
p.user_type = @p_sLoggedUserType
AND p.user_id = @p_iLoggedUserId
AND SUBSTRING(p.permission_mask, 2, 1) = ''1''
AND p.bus_rule_mask like ''1%''
'
END -- end edit
ELSE IF (@p_sView = 'TOTAL_PROJECTS' OR @p_sView = 'PROJ_OPEN_POSITIONS' OR @p_sView = 'PROJ_OPEN_POSTINGS')
BEGIN
IF (@iProjAcctGrpID IS NOT NULL AND @iProjAcctGrpID <>'' AND @iProjAcctGrpID<>'0')
BEGIN
SET @sSQL = @sSQL + N'
SELECT
p.project_user_type,
p.project_user_id,
p.project_id,
p.project_identifier,
p.project_xref,
p.project_contact_name,
p.project_description,
p.project_customer_id,
p.project_customer_name,
p.project_status_name,
p.project_owner_name,
p.project_funding_type_code,
p.project_user_field_index
FROM
CXmain.dbo.Viewable_Projects_All_Security_In_One_VW p WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Preferred_Vendors_Assign pva WITH (READUNCOMMITTED)
ON pva.member_id = p.project_user_id
AND pva.member_type = p.project_user_type
WHERE
p.user_type = @p_sLoggedUserType
AND p.user_id = @p_iLoggedUserId
AND pva.pref_vend_group = @iProjAcctGrpID
AND p.bus_rule_mask like ''1%''
'
IF (@p_sView = 'PROJ_OPEN_POSITIONS')
BEGIN
SET @sSQL = @sSQL + N'
AND EXISTS (
SELECT 1
FROM
CXmain.dbo.Project_Detail pd WITH (READUNCOMMITTED)
WHERE
p.project_user_type = pd.user_type
AND p.project_user_id = pd.user_id
AND p.project_id = pd.project_id
AND pd.project_object_id = 1
)
'
END -- end proj_open_positions
ELSE IF (@p_sView = 'PROJ_OPEN_POSTINGS')
BEGIN
SET @sSQL = @sSQL + N'
AND EXISTS (
SELECT 1
FROM
CXmain.dbo.Posting po WITH (READUNCOMMITTED)
WHERE
p.project_user_type = po.project_user_type
AND p.project_user_id = po.project_user_id
AND p.project_id = po.project_id
AND po.status = ''A''
AND po.date_timeout > getdate()
)
'
END
END --if account group
ELSE -- not account group
BEGIN
SET @sSQL = @sSQL + N'
SELECT
pr.user_type project_user_type,
pr.user_id project_user_id,
pr.project_id project_id,
pr.identifier project_identifier,
pr.xref project_xref,
pr.contact_name project_contact_name,
pr.description project_description,
pr.customer_id project_customer_id,
ISNULL(c.customer_name,''<Secured>'') project_customer_name,
s.status_name project_status_name,
@sName project_owner_name,
pr.funding_type_code project_funding_type_code,
pr.user_field_index project_user_field_index
FROM
CXmain.dbo.Viewable_Projects_All_Security_In_One_VW p WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Projects pr WITH (READUNCOMMITTED)
ON pr.project_key = p.project_key
INNER JOIN CXmain.dbo.Status s WITH (READUNCOMMITTED)
ON pr.project_status_id = s.status_id
LEFT OUTER JOIN CXmain.dbo.Secured_Client_Display_Base_vw c WITH (READUNCOMMITTED)
ON pr.customer_id = c.customer_id
AND c.active_user_id = @p_iLoggedUserId '
IF (@iProjOfficeLocId IS NOT NULL AND @iProjOfficeLocId <>'' AND @iProjOfficeLocId<>'0')
BEGIN
SET @sSQL = @sSQL + N'
INNER JOIN #LocationsList oll WITH (READUNCOMMITTED)
ON pr.office_location_room = oll.location_room
AND pr.office_location_family = oll.location_family
AND pr.office_location_id = oll.location_id'
END
IF (@iProjSiteLocId IS NOT NULL AND @iProjSiteLocId <>'' AND @iProjSiteLocId<>'0')
BEGIN
-- CR: 25655 : In Decision dashboard set scope to site location and view the Projects COW, no projects are displayed.
-- so following condition is modified -- pr.office_location_family into pr.site_locaiton_family
SET @sSQL = @sSQL + N'
INNER JOIN #LocationsList sll WITH (READUNCOMMITTED)
ON pr.site_location_room = sll.location_room
AND pr.site_location_family = sll.location_family
AND pr.site_location_id = sll.location_id'
END
SET @sSQL = @sSQL + N'
WHERE
p.user_type = @p_sLoggedUserType
AND p.user_id = @p_iLoggedUserId
AND s.room = @sRoom
AND s.family = @sFamily
AND s.application_object_id = 3
AND s.bus_rule_mask like ''1%''
'
-- If the project scope is account, limit the results to the account's projects
IF (@iProjectScopeCode = 1)
BEGIN
SET @sSQL = @sSQL + N'
AND pr.user_id = @p_iLoggedUserId
'
END
IF (@iProjCustId IS NOT NULL AND @iProjCustId <>'' AND @iProjCustId<>'0')
SET @sSQL = @sSQL + N'
AND pr.customer_id = ' + convert(varchar, @iProjCustId)
IF (@p_sView = 'PROJ_OPEN_POSITIONS')
BEGIN
SET @sSQL = @sSQL + N'
AND EXISTS (
SELECT 1
FROM
CXmain.dbo.Project_Detail pd WITH (READUNCOMMITTED)
WHERE
pr.user_type = pd.user_type
AND pr.user_id = pd.user_id
AND pr.project_id = pd.project_id
AND pd.project_object_id = 1
)
'
END
ELSE IF (@p_sView = 'PROJ_OPEN_POSTINGS')
BEGIN
SET @sSQL = @sSQL + N'
AND EXISTS (
SELECT 1
FROM
CXmain.dbo.Posting po WITH (READUNCOMMITTED)
WHERE
pr.user_type = po.project_user_type
AND pr.user_id = po.project_user_id
AND pr.project_id = po.project_id
AND po.status = ''A''
AND po.date_timeout > getdate()
)
'
END
END -- end not account group
END -- end total projects or open positions or open postings
IF (@p_sContaining is not null) AND (@p_sContaining <> '')
BEGIN
/* CR 25985 - Condition to be checked for opens project with search parameter in project maintenance */
IF( @p_sView = 'MY' )
BEGIN
SELECT @sSQL = @sSQL +
CASE
WHEN ( @p_sFilterBy = 'project_id') THEN N' AND p.user_type + CONVERT(varchar,p.user_id) + ''-'' + CONVERT(varchar,p.project_id) like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'identifier ') THEN N' AND p.identifier like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'xref') THEN N' AND p.xref like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'contact_name') THEN N' AND p.contact_name like"%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'description') THEN N' AND p.description like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'customer_name') THEN N' AND c.customer_name like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'status') THEN N' AND s.status_name like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'name') THEN N' AND "'+@sName+ '" like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'account') THEN N' AND "'+@sName+ '" like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'funding_type_code') THEN N' AND p.funding_type_code like "%' + @p_sContaining + N'%" '
END
END
/* CR:26173 : Checking @iProjAcctGrpId is not necessary for filter : ref: commented in Version 30 */
-- IF( @p_sView = 'TOTAL_PROJECTS' AND ( @iProjAcctGrpId IS NULL OR @iProjAcctGrpId = '' OR @iProjAcctGrpId = '0') )
--OR( @p_sView = 'PROJ_OPEN_POSITIONS' AND ( @iProjAcctGrpId IS NULL OR @iProjAcctGrpId = '' OR @iProjAcctGrpId = '0') )
--OR( @p_sView = 'PROJ_OPEN_POSTINGS'AND ( @iProjAcctGrpId IS NULL OR @iProjAcctGrpId = '' OR @iProjAcctGrpId = '0') )
IF(@p_sView = 'TOTAL_PROJECTS' OR @p_sView = 'PROJ_OPEN_POSITIONS' OR @p_sView = 'PROJ_OPEN_POSTINGS')
BEGIN
/* CR 20355 lgf 07-15-03 - changed code to handle apostrophe's */
SELECT @sSQL= @sSQL +
CASE
WHEN ( @p_sFilterBy = 'project_id') THEN N' AND p.project_user_type + CONVERT(varchar,p.project_user_id) + ''-'' + CONVERT(varchar,p.project_id) like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'identifier ') THEN N' AND p.project_identifier like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'xref') THEN N' AND p.project_xref like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'contact_name') THEN N' AND p.project_contact_name like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'description') THEN N' AND p.project_description like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'customer_name') THEN N' AND p.project_customer_name like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'status') THEN N' AND p.project_status_name like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'name') THEN N' AND p.project_owner_name like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'account') THEN N' AND p.project_owner_name like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'funding_type_code') THEN N' AND p.project_funding_type_code like "%' + @p_sContaining + N'%" '
END
END
/* CR 25985 end */
/* CR 25994 - condition to be checked for ALL Projects */
--IF ( @p_sView = 'ALL' )
IF( ( @p_sView = 'ALL') OR ( @p_sView = 'EDIT'))
BEGIN
SELECT @sSQL= @sSQL +
CASE
WHEN ( @p_sFilterBy = 'project_id') THEN N' AND p.project_user_type + CONVERT(varchar,p.project_user_id) + ''-'' + CONVERT(varchar,p.project_id) like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'identifier ') THEN N' AND p.project_identifier like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'xref') THEN N' AND p.project_xref like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'contact_name') THEN N' AND p.project_contact_name like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'status') THEN N' AND p.project_status_name like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'name') THEN N' AND p.project_owner_name like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'account') THEN N' AND p.project_owner_name like "%' + @p_sContaining + N'%" '
WHEN ( @p_sFilterBy = 'funding_type_code') THEN N' AND p.project_funding_type_code like "%' + @p_sContaining + N'%" '
END
END
/* 25994 end */
END
IF ( @p_sStartWith is not null) AND ( @p_sStartWith<> '')
/* CR -25985 Condition to be checked for opens project with search parameter in project maintenance.*/
/* CR:26173 : Checking @iProjAcctGrpId is not necessary for filter : ref: commented in Version 30 */
BEGIN
-- IF( @p_sView = 'TOTAL_PROJECTS' AND ( @iProjAcctGrpId IS NULL OR @iProjAcctGrpId = '' OR @iProjAcctGrpId = '0') )
--OR( @p_sView = 'PROJ_OPEN_POSITIONS' AND ( @iProjAcctGrpId IS NULL OR @iProjAcctGrpId = '' OR @iProjAcctGrpId = '0') )
--OR( @p_sView = 'PROJ_OPEN_POSTINGS'AND ( @iProjAcctGrpId IS NULL OR @iProjAcctGrpId = '' OR @iProjAcctGrpId = '0'))
IF( @p_sView = 'TOTAL_PROJECTS' OR @p_sView = 'PROJ_OPEN_POSITIONS' OR @p_sView = 'PROJ_OPEN_POSTINGS')
BEGIN
SELECT @sSQL= @sSQL +
CASE
WHEN ( @p_sFilterBy = 'project_id') THEN N' AND p.project_user_type + CONVERT(varchar,p.project_user_id) + ''-'' + CONVERT(varchar,p.project_id) like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'identifier ') THEN N' AND p.project_identifier like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'xref') THEN N' AND p.project_xref like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'contact_name') THEN N' AND p.project_contact_name like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'description') THEN N' AND p.project_description like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'customer_name') THEN N' AND p.project_customer_name like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'status') THEN N' AND p.project_status_name like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'name') THEN N' AND p.project_owner_name like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'account') THEN N' AND p.project_owner_name like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'funding_type_code') THEN N' AND p.project_funding_type_code like "' + @p_sStartWith + N'%" '
END
END
IF( @p_sView = 'MY' )
BEGIN
SELECT @sSQL= @sSQL +
CASE
WHEN ( @p_sFilterBy = 'project_id') THEN N' AND p.user_type + CONVERT(varchar,p.user_id) + ''-'' + CONVERT(varchar,p.project_id) like "' + @p_sStartWith + N'%" ' --KJC
WHEN ( @p_sFilterBy = 'identifier ') THEN N' AND p.identifier like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'xref') THEN N' AND p.xref like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'contact_name') THEN N' AND p.contact_name like"' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'description') THEN N' AND p.description like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'customer_name') THEN N' AND c.customer_name like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'status') THEN N' AND s.status_name like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'name') THEN N' AND "'+@sName+ '" like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'account') THEN N' AND "'+@sName+ '" like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'funding_type_code') THEN N' AND p.funding_type_code like "' + @p_sStartWith + N'%" '
END
END
/* CR 25994 - condition to be checked for ALL Projects */
-- IF( @p_sView = 'ALL' )
IF( ( @p_sView = 'ALL') OR ( @p_sView = 'EDIT'))
BEGIN
SELECT @sSQL= @sSQL +
CASE
WHEN ( @p_sFilterBy = 'project_id') THEN N' AND p.project_user_type + CONVERT(varchar,p.project_user_id) + ''-'' + CONVERT(varchar,p.project_id) like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'identifier ') THEN N' AND p.project_identifier like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'xref') THEN N' AND p.project_xref like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'contact_name') THEN N' AND p.project_contact_name like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'description') THEN N' AND p.project_description like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'customer_name') THEN N' AND p.project_customer_name like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'status') THEN N' AND p.project_status_name like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'name') THEN N' AND p.project_owner_name like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'account') THEN N' AND p.project_owner_name like "' + @p_sStartWith + N'%" '
WHEN ( @p_sFilterBy = 'funding_type_code') THEN N' AND p.project_funding_type_code like "' + @p_sStartWith + N'%" '
END
END
/* CR 25994 - end */
END
/* CR -25985 */
IF ( @p_sSortBy is not null ) AND ( @p_sSortBy <> '')
BEGIN
SELECT @sSQL= @sSQL +
CASE
WHEN ( @p_sSortBy = 'project_id') THEN N' ORDER BY project_user_type, project_user_id, project_id ' + @p_sOrderBy
WHEN ( @p_sSortBy = 'identifier ') THEN N' ORDER BY project_identifier ' + @p_sOrderBy
WHEN ( @p_sSortBy = 'xref') THEN N' ORDER BY project_xref '+ @p_sOrderBy
WHEN ( @p_sSortBy = 'contact_name') THEN N' ORDER BY project_contact_name '+ @p_sOrderBy
WHEN ( @p_sSortBy = 'description') THEN N' ORDER BY project_description '+ @p_sOrderBy
WHEN ( @p_sSortBy = 'customer_name') THEN N' ORDER BY project_customer_name '+ @p_sOrderBy
WHEN ( @p_sSortBy = 'status') THEN N' ORDER BY project_status_name '+ @p_sOrderBy
WHEN ( @p_sSortBy = 'name') THEN N' ORDER BY project_owner_name '+ @p_sOrderBy
WHEN ( @p_sSortBy = 'account') THEN N' ORDER BY project_owner_name ' +@p_sOrderBy --
WHEN ( @p_sSortBy = 'funding_type_code') THEN N' ORDER BY project_funding_type_code '+ @p_sOrderBy
END
END
SET @sSQL = @sSQL + ' OPTION(MAXDOP 1)'
--print @ssql
EXEC sp_executesql @sSQL, N'@p_sLoggedUserType char(1), @p_iLoggedUserId int, @sName varchar(255), @sRoom char(1), @sFamily varchar(8), @iProjAcctGrpId int',
@p_sLoggedUserType, @p_iLoggedUserId, @sName, @sRoom, @sFamily, @iProjAcctGrpId
SELECT
@iTotalRecords = count(*)
FROM
#OrderedResultSet OPTION (KEEPFIXED PLAN)
IF (@p_sActionCode = 'REFRESH') OR (@p_sActionCode = 'OPEN_WINDOW_HEADER') OR (@p_sActionCode = 'OPEN_WINDOW_LIST') OR (@p_sActionCode = 'FIRST')
SET @p_iStartRecord = 1
ELSE IF (@p_sActionCode = 'NEXT')
SET @p_iStartRecord = @p_iStartRecord + @p_iPageSize
ELSE IF (@p_sActionCode = 'PREV')
IF @p_iStartRecord <= @p_iPageSize
SET @p_iStartRecord = 1
ELSE
SET @p_iStartRecord = @p_iStartRecord - @p_iPageSize
ELSE IF (@p_sActionCode = 'LAST')
BEGIN
SET @p_iStartRecord = (@iTotalRecords/@p_iPageSize) * @p_iPageSize + 1
IF @p_iStartRecord > @iTotalRecords SET @p_iStartRecord = @p_iStartRecord - @p_iPageSize
END
EXEC COMM_GetPagingData@iTotalRecords, @p_iStartRecord, @p_iPageSize, @p_iCurrentPage OUTPUT, @p_iNoOfPages OUTPUT, @iLowerLimit OUTPUT, @iUpperLimit OUTPUT
IF ( @p_iStartRecord is null)or( @p_iStartRecord = 0)
SET @p_iStartRecord = 1
IF ( @p_iCurrentPage is null)or( @p_iCurrentPage = 0)
SET @p_iCurrentPage = 1
IF ( @p_iNoOfPages is null)or( @p_iNoOfPages = 0)
SET @p_iNoOfPages = 1
SELECT
o.user_type + CONVERT(varchar,o.user_id) + '-' +
CONVERT(varchar,o.project_id) + '|~|' +
o.identifier + '|~|' + o.customer_name + '|~|' +
isnull(CONVERT(varchar,o.user_field_index),'') + '|~|' +
isnull(CONVERT(varchar,o.customer_id),'') object_key,
o.user_type + CONVERT(varchar,o.user_id)+ '-' +
CONVERT(varchar, o.project_id) project_id,
o.identifier,
ISNULL(o.customer_name, '<Secured>') customer_name,
o.xref,
o.contact_name,
o.description,
o.status,
o.name,
o.name account, --
CASE
WHEN ( @sDisplayCodeValueInd = 'Y' )
THEN isnull(o.funding_type_code,'')+'-'+isnull(cte.display_value,'')
ELSE
isnull(cte.display_value,'')
END AS funding_type_code
FROM
#OrderedResultSet o
LEFT OUTER JOIN CXmain.dbo.Code_Table_Entry cte WITH (READUNCOMMITTED)
ON o.funding_type_code = cte.code_value
AND cte.code_table_id = 1020
ANDcte.room = @sRoom
ANDcte.family = @sFamily
WHERE
o.counter >= @iLowerLimit
AND o.counter <= @iUpperLimit
ORDER BY counter
OPTION (MAXDOP 1)
SET NOCOUNT OFF
Regards,
KB.
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
January 7, 2009 at 9:26 am
The first thing I'd do is break the proc into smaller procs and have it all called from a master proc. Then you can tune each one.
As written, the thing has a few too many conditional checks, which usually make for a worse execution plan than otherwise. So, break each of those into a separate sub-proc, called by the main proc based on conditional checks, and you'll likely end up with better performance just because of that.
Then, you can check each sub-proc for performance issues (execution plans and IO data are a great place to start on that), and find and fix those.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2009 at 11:30 am
GSquared (1/7/2009)
The first thing I'd do is break the proc into smaller procs and have it all called from a master proc. Then you can tune each one.As written, the thing has a few too many conditional checks, which usually make for a worse execution plan than otherwise. So, break each of those into a separate sub-proc, called by the main proc based on conditional checks, and you'll likely end up with better performance just because of that.
Then, you can check each sub-proc for performance issues (execution plans and IO data are a great place to start on that), and find and fix those.
I'll second all GSquared said. If you break it down it will be easier to debug and tune.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 7, 2009 at 11:25 pm
Thanks guys.
I will have a play around with that today.
KB
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
January 8, 2009 at 2:23 am
I agree to previous posts that you should break down the code to smaller procedures.
Another thing to think of is that you should avoid changing parameters inside your procedure, like :
IF ( @p_sActionCode is null or @p_sActionCode = '' ) SET @p_sActionCode = 'REFRESH'
IF ( @p_sView is null or @p_sView = '' ) SET @p_sView = 'ALL'
IF ( @p_sFilterBy is null or @p_sFilterBy = '' ) SET @p_sFilterBy = 'project_id'
IF ( @p_sSortBy is null or @p_sSortBy = '' ) SET @p_sSortBy = 'project_id'
IF ( @p_sOrderBy is null or @p_sOrderBy = '' ) SET @p_sOrderBy = 'ASC'
IF ( @p_iPageSize is null or @p_iPageSize = '' ) SET @p_iPageSize = 10
IF ( @p_iStartRecord is null or @p_iStartRecord = '' ) SET @p_iStartRecord = 1
This will confuse the SQL optimizer and the wrong execution plan can be used.
"When a stored procedure is first executed (and it does not have the WITH RECOMPILE option), it is optimized and a query plan is compiled and cached in SQL Server's buffer. If the same stored procedure is called again from the same connection, it will used the cached query plan instead of creating a new one, often saving time and boosting performance. This may or may not be what you want.
If the query in the stored procedure is exactly the same each time, and the query plan is the same each time, then this is a good thing. But if the query within the stored procedure is dynamic (for example, the WHERE clauses changes from one execution of the stored procedure to the next), then this may not be a good thing, as the query may not be optimized when it is run, and the performance of the query can suffer greatly. This can happen because changes in the query plan may occur, and if you run a cached query plan for what is essentially a new query, it may not be appropriate and it may cause performance to suffer greatly." (http://www.sql-server-performance.com/tips/stored_procedures_p2.aspx)
/Håkan Winther
Senior Development DBA
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
February 5, 2009 at 8:48 am
I don't think the procedure is exceptionally long, or that 1 long procedure is any harder to optimize than the same code broken down into N smaller procedures to be optimized. But, you should remove all of the conditional logic possible and where its not possible then you should break the procedure down into additional procedures. Any time you have conditional code there is the risk that the queries within it will not be optimized.
You should also see if you can rework the procedure to remove the While loops. Most people use while loops because they are thinking one row at a time, if you want better performance, try to figure out how to work with sets of data at a time.
That group of IF statments at the top can probably be eliminated by adding default values in your parameter definitions.
Read this article http://www.sql-server-performance.com/tips/t_sql_where_p1.aspx
which will probably help you on your where clauses.
You gave us the query, but one of the most important parts of optimization is knowing what your indexes are and either adding indexes to improve your queries or writing your queries to make the best use of the indexes you have. This article is a good one on Index Optimization
http://www.sql-server-performance.com/tips/covering_indexes_p1.aspx
Also I'd be wary of when you are using ReadUncommited or NoLock. Read this one:
http://milambda.blogspot.com/2006/11/nolock-no-good.html
On the bright side you do have your DDL seperated up at the top, which helps reduce recompiles. (although with all that conditional logic the recompiles might be helpful)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply