Help Optimizing the Following SP

[vuyiswamb]

Good Afternoon All I have a SP that i need your help to Optimize it.

ALTER   PROCEDURE [sde].[PROPERTY_LIST]

	@ACTIVE_USER_ID 		INTEGER	=	0,
	@PARCEL_NO 			VARCHAR(50)	=	'', 
	@BOUNDARY_AREA 		VARCHAR(40)	=	'',
	@NUM_KEY 			VARCHAR(10)	=	'',
	@LIS_KEY 			VARCHAR(50)	=	'',
	@OWNER_NAME		VARCHAR(500)	=	'',
	@PARCEL_PORTION_NO 	VARCHAR(10)	=	'',
	@CELL_NO 			VARCHAR(50)	=	'', 
	@ATTRIB_CODE 		VARCHAR(12)	=	'',
	@EXTENSION 			INTEGER	=	0, 
	@FUNC_KEY 			VARCHAR(30)	=	'',
	@PROVINCE_ID 		INTEGER	=	0, 
	@STREET_NO 			VARCHAR(500)	=	'', 
	@STREET_NAME 		VARCHAR(500)	=	'',
	@ID 				INTEGER 	=	0
AS

	SET @PARCEL_NO 		= ISNULL(@PARCEL_NO,'')
	SET @BOUNDARY_AREA 	= ISNULL(@BOUNDARY_AREA,'')
	SET @LIS_KEY 		= ISNULL(@LIS_KEY,'')
	SET @OWNER_NAME 		= ISNULL(@OWNER_NAME,'')
	SET @PARCEL_PORTION_NO 	= ISNULL(@PARCEL_PORTION_NO,'')
	SET @CELL_NO 		= ISNULL(@CELL_NO,'')
	SET @ATTRIB_CODE 		= ISNULL(@ATTRIB_CODE,'')
	SET @FUNC_KEY 		= ISNULL(@FUNC_KEY,'')
	SET @STREET_NO 		= ISNULL(@STREET_NO,'')
	SET @STREET_NAME 		= ISNULL(@STREET_NAME,'')
	SET @EXTENSION		= ISNULL(@EXTENSION, 0)

	IF @ACTIVE_USER_ID <> 0
	BEGIN
		IF @ID = 0
		BEGIN
			DECLARE @strSQL NVARCHAR(4000)
			
			SET @strSQL = 
				'SELECT DISTINCT TOP 200 PARCEL_PORTION_NO,
				PROPERTY_ID, PARCEL_NO AS PARCEL_NO, ROLL_REMARK, RATEABLE, EXTENSION, FUNC_KEY, ISNULL(P.NOTIFICATION_EXIST, ''FALSE'') AS NOTIFICATION_EXIST, ISNULL(P.SUPPL_EXIST, ''FALSE'') AS SUPPL_EXIST, NON_DISCREET_ADD, NON_DISCREET_VALID, P.PROBLEM, 
				COMPL_BOUNDARY_AREA,
				CASE (CASE CHARINDEX(''/'', PARCEL_PORTION_NO)  
					WHEN 0 THEN LEN(LTRIM(RTRIM(ISNULL(PARCEL_PORTION_NO,''''))))
					ELSE LEN(LTRIM(RTRIM(ISNULL( SUBSTRING(PARCEL_PORTION_NO,1,CHARINDEX(''/'', PARCEL_PORTION_NO)-1), ''''))))
					END) 
					WHEN 1 THEN CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_NO,'''')))) + ''  '' +
						    CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_PORTION_NO, '''')))) 
					WHEN 2 THEN CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_NO,'''')))) + '' '' +
						    CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_PORTION_NO, '''')))) 
					ELSE CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_NO,'''')))) + 
					     CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_PORTION_NO, '''')))) 
				END AS COMPL_PARCEL_PORTION, PROP_DESCRIPTION AS [DESCRIPTION], 
				CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_NO,'''')))) +
				CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_PORTION_NO, '''')))) AS COMPL_PARCEL_PORTION_OLD,
				NUM_KEY, BOUNDARY_AREA, P.ATTRIB_CODE, LIS_KEY, PROVINCE_ID, AUTHORITY_ID, CELL_NO, OWN_NAME,
				TITLE_DEED_NO, PROPERTY_KEY, CURRENT_VALUES, SITE_VALUE, IMPROVED_VALUE, ACTUAL_EXTENT, PROVINCE, AUTHORITY, PROP_ADDRESS, STREET_NO, STREET_NAME,
				EAST_OF, WEST_OF, NORTH_OF, SOUTH_OF, DENSITY, PROP_SOURCE_ID, PROP_CATEGORY_ID, LEGAL_AREA, ACTIVE, PARCEL_CLASS,
				PARCEL_PORTION_NO, PARCEL_STATUS, PARCEL_REMARK, PARCEL_CAPTURED, PARCEL_MODIFIED, PARCEL_NAME, BUILDING_FARM_NAME, 
				SG_DIAGRAM_NO, GP_SUBDIVISION_NO, GENERAL_PLAN_NO, MUNIC_STATUS, PARCEL_TYPE, HISTORY, SUBDIVISION_COMPONENT, CONSOLIDATION_COMPONENT, REG_DATE, 
				(
					SELECT TOP 1 C.PROP_CATEGORY_DESCR
					FROM SDE.PROP_ATTRIB_PATTERN_CATEGORY A
					INNER JOIN SDE.LU_PROP_CATEGORY_NEW C
						  ON C.PROP_CATEGORY_ID = A.CATEGORY_ID
					WHERE SUBSTRING(A.ATTRIB_CODE_PATTERN, 1, 8) = SUBSTRING(P.ATTRIB_CODE, 1, 8)
				) AS PROP_CATEGORY_DESCR
				FROM sde.PROPERTY_SUMMARY P
				WHERE 1=1 --P.ARCHIVE_DATE IS NULL
				'
-- PROP_CATEGORY_DESCR added above by Jacob Jackson 200708 to include category as defined by new legislation

			IF @EXTENSION <> 0
			BEGIN				SET @strSQL = @strSQL  + ' AND P.EXTENSION = ' +  CONVERT(VARCHAR, @EXTENSION)
			END

			IF @BOUNDARY_AREA  <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.BOUNDARY_AREA LIKE ''' + @BOUNDARY_AREA + ''''
			END

			IF @OWNER_NAME  <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.OWN_NAME LIKE ''' + @OWNER_NAME + ''''
			END

			IF @PARCEL_NO <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.PARCEL_NO LIKE ''%' + @PARCEL_NO + '%'''
			END

			IF @LIS_KEY  <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.LIS_KEY LIKE ''' + @LIS_KEY + '%'''
			END

			IF @PARCEL_PORTION_NO  <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.PARCEL_PORTION_NO LIKE ''' + @PARCEL_PORTION_NO + ''''
			END

			IF @CELL_NO <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.CELL_NO = ' +  @CELL_NO
			END

			IF @ATTRIB_CODE = '' OR @ATTRIB_CODE = '__________'
			BEGIN
				SET @strSQL = @strSQL + ' AND (P.ATTRIB_CODE IS NULL OR P.ATTRIB_CODE LIKE ''%'') '
			END
			ELSE
			BEGIN
				SET @strSQL = @strSQL +  ' AND ISNULL(P.ATTRIB_CODE, '''') LIKE '''+  @ATTRIB_CODE + '%'' '
			END

			IF @FUNC_KEY <> '' 
			BEGIN
				SET @strSQL = @strSQL  + ' AND P.FUNC_KEY LIKE ''' + CONVERT(VARCHAR, @FUNC_KEY) + ''''
			END

/*			IF @STREET_NO  <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.PROP_ADDRESS LIKE ''' + CONVERT(VARCHAR, @STREET_NO) + '%'''
			END

			IF @STREET_NAME  <> ''
			BEGIN				SET @strSQL = @strSQL  + ' AND P.PROP_ADDRESS LIKE ''' + CONVERT(VARCHAR, @STREET_NAME) + '%'''
			END
*/
			IF @STREET_NO  <> ''
			BEGIN
				SET @strSQL = @strSQL  + '   AND (P.PROP_ADDRESS LIKE ''' + CONVERT(VARCHAR, @STREET_NO) + '%'' 
								OR P.PROP_ADDRESS LIKE ''%' + CONVERT(VARCHAR, @STREET_NO) + '%'' ) '
			END

			IF @STREET_NAME  <> ''
			BEGIN
				SET @strSQL = @strSQL  + ' AND P.PROP_ADDRESS LIKE ''%' + CONVERT(VARCHAR, @STREET_NAME) + '%'''
			END

			IF @PROVINCE_ID <> 0 
			BEGIN
				SET @strSQL = @strSQL  + ' AND P.PROVINCE_ID = ' + CONVERT(VARCHAR, @PROVINCE_ID)
			END
	
	
			/* CURRENT QUICK FIX FOR GUEST & FINANCE USERS TO PREVENT THEM FROM SEEING PROPERTIES WITHOUT CURRENT_VALUES. TO BE REMOVED AT A LATER DATE*/
			DECLARE @IS_GUEST_FINANCE INTEGER
			SET @IS_GUEST_FINANCE =	(
							SELECT	CASE WHEN LOGIN_NAME IN ('GUEST', 'FINANCE')
									THEN 1
									ELSE 0
									END 
							FROM		SDE.USER_INFO
							WHERE	[USER_ID] = @ACTIVE_USER_ID
							)

			IF ISNULL(@IS_GUEST_FINANCE, 0) <> 0 
			BEGIN
				SET @strSQL = @strSQL  + ' AND P.CURRENT_VALUES IS NOT NULL '
			END
	

			SET @strSQL = @strSQL + ' AND P.ARCHIVE_DATE IS NULL ' + ' ORDER BY	P.COMPL_BOUNDARY_AREA, P.COMPL_PARCEL_PORTION, P.PROPERTY_KEY'

			EXECUTE SP_EXECUTESQL @strSQL

		END
		ELSE -- SPECIFIC RECORD
		BEGIN	
					SELECT 	PP.*, 
					ISNULL(P.NOTIFICATION_EXIST, 'FALSE') AS NOTIFICATION_EXIST, ISNULL(P.SUPPL_EXIST, 'FALSE') AS SUPPL_EXIST, NON_DISCREET_ADD, NON_DISCREET_VALID, P.PROBLEM, 
					PARCEL_NO AS PARCEL_NO, ROLL_REMARK, RATEABLE, FUNC_KEY, PROP_DESCRIPTION AS [DESCRIPTION], 
					EXTENSION, NUM_KEY, BOUNDARY_AREA, P.ATTRIB_CODE, LIS_KEY, PROVINCE_ID, AUTHORITY_ID, CELL_NO, OWN_NAME,
					TITLE_DEED_NO, PROPERTY_KEY, CURRENT_VALUES, SITE_VALUE, IMPROVED_VALUE, IMPROVEMENTS_VALUE,  ACTUAL_EXTENT, PROVINCE, AUTHORITY, PROP_ADDRESS, STREET_NO, STREET_NAME,
					EAST_OF, WEST_OF, NORTH_OF, SOUTH_OF, DENSITY, PROP_SOURCE_ID, PROP_CATEGORY_ID, LEGAL_AREA, ACTIVE, PARCEL_CLASS,
					PARCEL_PORTION_NO, PARCEL_STATUS, PARCEL_REMARK, PARCEL_CAPTURED, PARCEL_MODIFIED, PARCEL_NAME, BUILDING_FARM_NAME, 
					SG_DIAGRAM_NO, GP_SUBDIVISION_NO, GENERAL_PLAN_NO, MUNIC_STATUS, PARCEL_TYPE, HISTORY, SUBDIVISION_COMPONENT, CONSOLIDATION_COMPONENT, REG_DATE, 
					PURCHASE_DATE, PURCHASE_PRICE, LAST_SUPPL_NO, OLD_SUPPL_NO, VALUES_EFFECTIVE, ACTIVE_VAL_PROCESSED_DATE, 

					(
						SELECT TOP 1 C.PROP_CATEGORY_DESCR
						FROM SDE.PROP_ATTRIB_PATTERN_CATEGORY A
					
						INNER JOIN SDE.LU_PROP_CATEGORY_NEW C
							  ON C.PROP_CATEGORY_ID = A.CATEGORY_ID
						WHERE SUBSTRING(A.ATTRIB_CODE_PATTERN, 1, 8) = SUBSTRING(P.ATTRIB_CODE, 1, 8)
					) AS PROP_CATEGORY_DESCR


			FROM    	sde.PROPERTY_SUMMARY P
			INNER JOIN 	SDE.VW_PROPERTY_PROCESSING_PENDING PP ON P.PROPERTY_ID = PP.PROPERTY_ID
			WHERE 	P.PROPERTY_ID = @ID

		END
	END
	ELSE
	BEGIN
		PRINT 'PLEASE SPECIFY AN ACTIVE_USER_ID'
	END

Thank you

[techgnome]

Oh.... my.... GAWD! tisk, tisk, tisk.... you've got dynamic sql running lose in there..... that's gonna be a performance killer..... no promises... might take some time.... but I'll see what I can do. I've done this before.

-tg

[si_the_geek]

Are you just using strSQL (rather than a hard-coded Select statement) so that you can have the Where conditions only when apt?

If so it can be easily done without resorting to strSQL, by adding very simple extra conditions (which are just the opposite of your If statements), eg:

  WHERE 1=1 --P.ARCHIVE_DATE IS NULL
  AND (@EXTENSION = 0  OR P.EXTENSION = @EXTENSION)
  AND (@BOUNDARY_AREA = '' OR P.BOUNDARY_AREA LIKE BOUNDARY_AREA)
...

The amount of checking done is the same as you had (eg: if @EXTENSION is 0, the P.EXTENSION = @EXTENSION part will not be checked as True Or Anything is True), but the speed will increase due using a hard-coded SQL statement.

[techgnome]

Dang it.... simplicity wins out.... I had gone another route.... but what si posted will work much better - and be quicker to implement.

-tg
*stops rewriting sp*