December 26, 2008 at 5:26 am
Hi all'
We have a stored proc which internally calls nearly 50 sp's which internally creating lot of temp tables for our business requirements is taking more time to complete the process.
we are planning to tune the sp's and changing temp tables to permanent tables in the db. will it help?
or else can we run the sp in parallel mode of processing.
December 26, 2008 at 8:26 am
It may or may not help. You have to examine execution plans and look to tune each part of it. There's no way to answer a general question like this. It may or may not help.
December 26, 2008 at 9:19 am
Ummmm... if the real tables are going to be in a database that doesn't have SIMPLE recovery, then probably not. If the temp tables are being used as replacements for cursors and, thus, have While loops in them, then I would first fix that to make them good, set based routines. Note that not everything that appears to be set based, actually is. Things with any form of recurssion (something that calls itself), some things that have inequalities in correlated subqueries, and joins with inequalites, are frequently miswritten to actually be worse than a cursor. Please see the following for some examples...
[font="Arial Black"]Hidden RBAR: Triangular Joins[/font][/url]
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2008 at 12:18 pm
you can also think of indexing the temp tables created. As soon as the data is pumped data into the temp tables you can create index on these tables. This will greatly improves the performance if your pumping lots of data into the temp tables. Ensure that tempdb is placed on the different drive other than the normal user databases. Also enusre that SET NOCOUNT ON is used in all the stored procedures. Check for indexes and fragmentation of all the tables used in the stored procedures.
As above said by Steve these are some general check list we can do
December 28, 2008 at 2:09 pm
fakru.y (12/26/2008)
we are planning to tune the sp's and changing temp tables to permanent tables in the db. will it help?
Maybe. But if all you're doing is changing the temp tables to permanent tables, probably not. If it does it means that tempDB was bottlenecked, and there are ways to fix that.
If you can post one of the procs (maybe the slowest) along with the table structures, maybe we can help more.
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
December 29, 2008 at 3:05 am
Here it looks in my proc
*********************************
IF @src_id = 3 AND @calling_nbr = 1
BEGIN
CREATE TABLE #PNLPPK_TABLE_LIM_CRT_MSTR(
CTRY_CD nvarchar(3),
PANEL_CD nvarchar(3),
PROD_ID integer,
PPK_ID integer,
ROOT_PROD_REDEFINE_NM nvarchar(90),
PROD_REDEFINE_ID integer,
LOGICAL_DEL_IND CHAR(1) DEFAULT 'I')
CREATE INDEX LIM_MSTR_GBL_IDX1 ON #PNLPPK_TABLE_LIM_CRT_MSTR (CTRY_CD, PANEL_CD, PROD_ID, PPK_ID)
SET @temp_tbl_lim_crt_mstr = '#PNLPPK_TABLE_LIM_CRT_MSTR'
CREATE TABLE #PNLPPK_TABLE_LIM_CRT(
CTRY_CD nvarchar(3),
PANEL_CD nvarchar(3),
PROD_ID integer,
PPK_ID integer,
ROOT_PROD_REDEFINE_NM nvarchar(90),
PROD_REDEFINE_ID integer,
LOGICAL_DEL_IND CHAR(1) DEFAULT 'I')
CREATE INDEX LIM_GBL_IDX1 ON #PNLPPK_TABLE_LIM_CRT (CTRY_CD, PANEL_CD, PROD_ID, PPK_ID)
SET @temp_tbl_lim_crt = '#PNLPPK_TABLE_LIM_CRT'
END
IF @src_id = 2 OR @src_id = 1 OR (@src_id = 3 AND @calling_nbr = 2)
BEGIN
CREATE TABLE #PPK_TABLE_LIM_CRT_MSTR(
CTRY_CD nvarchar(3),
PROD_ID integer,
PPK_ID integer,
ROOT_PROD_REDEFINE_NM nvarchar(90),
PROD_REDEFINE_ID integer,
LOGICAL_DEL_IND CHAR(1) DEFAULT 'I')
CREATE INDEX LIM_UNF_IDX1 ON #PPK_TABLE_LIM_CRT_MSTR (CTRY_CD, PROD_ID, PPK_ID)
SET @temp_tbl_lim_crt_mstr = '#PPK_TABLE_LIM_CRT_MSTR'CREATE TABLE #PPK_TABLE_LIM_CRT(
CTRY_CD nvarchar(3),
PROD_ID integer,
PPK_ID integer,
ROOT_PROD_REDEFINE_NM nvarchar(90),
PROD_REDEFINE_ID integer,
LOGICAL_DEL_IND CHAR(1) DEFAULT 'I')
CREATE INDEX LIM_UNF_IDX1 ON #PPK_TABLE_LIM_CRT (CTRY_CD, PROD_ID, PPK_ID)
SET @temp_tbl_lim_crt = '#PPK_TABLE_LIM_CRT'
END
IF @src_id = 5
BEGIN
CREATE TABLE #PLPNL_TABLE_LIM_CRT_MSTR(
CTRY_CD nvarchar(3),
PANEL_CD nvarchar(3),
PROD_ID integer,
PPK_ID integer,
ROOT_PROD_REDEFINE_NM nvarchar(90),
PROD_REDEFINE_ID integer,
LOGICAL_DEL_IND CHAR(1) DEFAULT 'I')
CREATE INDEX LIM_MSTR_GBL_IDX1 ON #PLPNL_TABLE_LIM_CRT_MSTR (CTRY_CD, PANEL_CD, PROD_ID, PPK_ID)
SET @temp_tbl_lim_crt_mstr = '#PLPNL_TABLE_LIM_CRT_MSTR'
END
WHILE @@fetch_status = 0
BEGIN --2
SET @sql_st1 = 'TRUNCATE TABLE ' + @temp_tbl_lim_crt
EXEC sp_executesql @sql_st1
print @sql_st1
SET @return = @@error
IF @return <> 0
BEGIN --3
RAISERROR('Error truncating temporary table', 16, 1)
CLOSE Limit_Crit
DEALLOCATE Limit_Crit
RETURN(@return)
END --3
SET @sql_st1 = 'INSERT INTO ' + @temp_tbl_lim_crt + ' SELECT * FROM ' + @temp_tbl
EXEC sp_executesql @sql_st1
print @sql_st1
SET @return = @@error
IF @return <> 0
BEGIN --3
RAISERROR('Error Inserting into temporary table', 16, 1)
CLOSE Limit_Crit
DEALLOCATE Limit_Crit
RETURN(@return)
END --3
SELECT @srchLen=key_srch_len, @fillChar=key_clmn_fill_char
FROM MKT_PROD_SELECTION
WHERE PROD_SELECTION_CLAS = @sel_clas_parent
AND PROD_SELECTION_NM = @sel_nm_parent
AND src_id = @src_id
IF @srchLen > 0
BEGIN --3
SET @operator = ' LIKE '
SET @col_val = SUBSTRING(@col_val_parent, 1, @srchLen)
END --3
ELSE
BEGIN --3
SET @operator = ' = '
SET @col_val = @col_val_parent
SET @fillChar = ''
END --3
IF @src_id = 3 AND @calling_nbr = 1
BEGIN
SELECT @refc_tbl = REFC_TBL_NM FROM MKT_CRITERIA_REFC_LKUP
WHERE CLMN_NM = @col_nm_parent
AND DATA_SRC_NM = '3'
SET @return = @@error
IF @return <> 0
BEGIN
RAISERROR('Error retrieving the reference table name', 16, 1)
RETURN(16)
END
--12/2/2004SET @sql_st = 'UPDATE ' + @temp_tbl_lim_crt + ' SET LOGICAL_DEL_IND = ' + char(39) + 'D' + char(39) + ' FROM ' + @temp_tbl_lim_crt + ' t ' + ' WHERE Not Exists( ' + char(13)
SET @sql_st = 'UPDATE ' + @temp_tbl_lim_crt + ' SET LOGICAL_DEL_IND = ' + char(39) + 'L' + char(39) + ' FROM ' + @temp_tbl_lim_crt + ' t ' + ' WHERE Exists( ' + char(13)
SET @sql_st = @sql_st + ' SELECT 1 FROM ' + @refc_tbl + ' A' + char(13)
SET @sql_st = @sql_st + ' Where t.CTRY_CD = A.CTRY_CD ' + char(13)
SET @sql_st = @sql_st + ' AND t.PANEL_CD = A.PANEL_CD ' + char(13)
SET @sql_st = @sql_st + ' AND t.PROD_ID = A.PROD_ID ' + char(13)
SET @sql_st = @sql_st + ' AND t.PPK_ID = A.PPK_ID ' + char(13)
END
IF @src_id = 2 OR @src_id = 1 OR (@src_id = 3 AND @calling_nbr = 2)
BEGIN
IF @src_id = 2 OR @src_id = 1
Begin
SELECT @refc_tbl = REFC_TBL_NM FROM MKT_CRITERIA_REFC_LKUP
WHERE CLMN_NM = @col_nm_parent
AND DATA_SRC_NM = '1-2'
SET @return = @@error
End
IF @src_id = 3 AND @calling_nbr = 2
Begin
SELECT @refc_tbl = REFC_TBL_NM FROM MKT_CRITERIA_REFC_LKUP
WHERE CLMN_NM = @col_nm_parent
AND DATA_SRC_NM = '3-4'
SET @return = @@error
End
IF @return <> 0
BEGIN
RAISERROR('Error retrieving the reference table name', 16, 1)
RETURN(16)
END
--12/2/2004SET @sql_st = 'UPDATE ' + @temp_tbl_lim_crt + ' SET LOGICAL_DEL_IND = ' + char(39) + 'D' + char(39) + ' FROM ' + @temp_tbl_lim_crt + ' t ' + ' WHERE Not Exists( ' + char(13)
SET @sql_st = 'UPDATE ' + @temp_tbl_lim_crt + ' SET LOGICAL_DEL_IND = ' + char(39) + 'L' + char(39) + ' FROM ' + @temp_tbl_lim_crt + ' t ' + ' WHERE Exists( ' + char(13)
SET @sql_st = @sql_st + ' SELECT 1 FROM ' + @refc_tbl + ' A' + char(13)
SET @sql_st = @sql_st + ' Where t.CTRY_CD = A.CTRY_CD ' + char(13)
SET @sql_st = @sql_st + ' AND t.PROD_ID = A.PROD_ID ' + char(13)
SET @sql_st = @sql_st + ' AND t.PPK_ID = A.PPK_ID ' + char(13)
END
IF @src_id = 5
BEGIN
/*SET @sql_st = 'DELETE FROM ' + @temp_tbl_frst_chd + ' FROM ' + @temp_tbl_frst_chd + ' t ' + ' WHERE Not Exists( ' + char(13)*/
SET @sql_st = 'UPDATE ' + @temp_tbl_lim_crt + ' SET LOGICAL_DEL_IND = ' + char(39) + 'D' + char(39) + ' FROM ' + @temp_tbl_lim_crt + ' t ' + ' WHERE Not Exists( ' + char(13)
SET @sql_st = @sql_st + ' SELECT 1 from ' + @refc_tbl + ' A' + char(13)
SET @sql_st = @sql_st + ' WHERE t.CTRY_CD = A.CTRY_CD ' + char(13)
SET @sql_st = @sql_st + ' AND t.PANEL_CD = A.PANEL_CD ' + char(13)
SET @sql_st = @sql_st + ' AND t.PROD_ID = A.PROD_ID ' + char(13)
SET @sql_st = @sql_st + ' AND t.PPK_ID = A.PPK_ID ' + char(13)
END
SET @sql_st = @sql_st + ' And ( A.' + @col_nm_parent + @operator + char(39) + @col_val + @fillChar + char(39) + ')' + char(13)
IF @ctry_parent IS NOT NULL
BEGIN --3
SET @sql_st = @sql_st + ' AND A.CTRY_CD = ' + char(39) + @ctry_parent + char(39)
END--3
SET @sql_st = @sql_st + ')'
print @sql_st
EXEC sp_executesql @sql_st
SET @return = @@error
IF @return <> 0
BEGIN --3
RAISERROR('Error deleting limit criteria FROM the Temporary Table', 16, 1)
CLOSE Limit_Crit
DEALLOCATE Limit_Crit
RETURN(@return)
END --3
SET @col_val_parent_proc = @operator + char(39) + @col_val + @fillChar + char(39)
SELECT @num_child1=count(*)
FROM mkt_mktdef_selection_criteria
WHERE mktdef_version_id = @mktdef_vrsn_id
AND par_mktdef_criteria_selection_id = @criteria_id_parent
IF @num_child1 > 0
BEGIN --3
EXEC @return = xMKT_CMD_User.PR_MKT_BUILD_FIRST_CHILD_CRIT @mktdef_vrsn_id,
@calling_nbr,
@criteria_id_parent,
@lvl_type,
@temp_tbl_lim_crt,
@refc_tbl,
@col_nm_parent,
@col_val_parent_proc,
@criteria_typ_parent,
@ctry_parent,
@src_id
IF @return <> 0
BEGIN --4
RAISERROR('Error updating first level of child criteria', 16, 1)
CLOSE Limit_Crit
DEALLOCATE Limit_Crit
RETURN(@return)
**********************************************
December 29, 2008 at 4:54 am
Don't waste your time changing that code from temp tables to permanent tables. The real problem is that it has a cursor in it that processes a wad of stuff one bloody row at a time. Until you change the overall method to be set based, nothing in the world is gonna increase the performance of that sproc... and least not much.
Also, the following statement in your code says you didn't post all of the code because there is no DECLARE CURSOR in the code you posted.
WHILE @@fetch_status = 0
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2008 at 1:08 am
Here i'm giving a proc for youtr reference.
like this we have near 50 procs in our process.
******************************************************
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE xMKT_CMD_User.PR_MKT_BUILD_REDEF_LIM_CRIT_FOR_TEMP_TBL
@mktdef_vrsn_id INTEGER, @calling_nbr INTEGER, @redef_id INTEGER, @mast_tbl_nm VARCHAR(128), @bld_lvl CHAR(3),
@temp_tbl VARCHAR(128),
@seq_id_sel_parent INTEGER, @criteria_typ_sel_parent CHAR(1), @sel_clas_sel_parent NVARCHAR(30), @sel_nm_sel_parent NVARCHAR(30), @col_nm_sel_parent NVARCHAR(30),
@sel_src_sel_parent INTEGER, @col_val_sel_parent_proc NVARCHAR(256), @src_id INTEGER
AS
DECLARE @limit_cnt INTEGER
DECLARE @num_child1 INTEGER
DECLARE @num_child2 INTEGER
DECLARE @child_level CHAR(1)
DECLARE @sql_st as NVARCHAR(3000)
DECLARE @sql_st1 as NVARCHAR(3000)
DECLARE @where_cls as NVARCHAR(3000)
DECLARE @return INTEGER
DECLARE @seq_id_parent INTEGER
DECLARE @criteria_typ_parent as CHAR(1)
DECLARE @sel_clas_parent as NVARCHAR(30)
DECLARE @sel_nm_parent as NVARCHAR(30)
DECLARE @col_nm_parent as NVARCHAR(30)
DECLARE @sel_src_parent INTEGER
DECLARE @col_val_parent as NVARCHAR(128)
DECLARE @col_val_parent_proc as NVARCHAR(256)
DECLARE @ctry_parent VARCHAR(3)
DECLARE @srchLen INTEGER
DECLARE @operator VARCHAR(6)
DECLARE @col_val VARCHAR(128)
DECLARE @fillChar VARCHAR(1)
DECLARE @temp_tbl_rlim_crt_mstr VARCHAR(128)
DECLARE @temp_tbl_rlim_crt VARCHAR(128)
DECLARE @refc_tmp VARCHAR(128)
/*********************************************************************************************************************************************************
PURPOSE: This procedure is created as part of the Build logic. This sub procedure handles updating the temporary table
Global LIMIT REDEFINITION CRITERIA.
CHANGE HISTORY
DATE CREATED BY REMARKS
JULY 2003 Meera Copied from the US Marketdef Application and made changes
for Global build logic
SEP 2003 Meera Added logic for Local Product/Local Pack level builds
FEB 2003 Meera Changes for Integration of Weekly Build logic
***********************************************************************************************************************************************************/
--Build necessary temp tables
IF @src_id = 3 AND @calling_nbr = 1
BEGIN
CREATE TABLE #PNLPPK_REDEF_TABLE_REDEF_LIM_CRT_MSTR(
CTRY_CD nvarchar(3),
PANEL_CD nvarchar(3),
PROD_REDEFINE_ID integer,
PROD_ID integer,
PPK_ID integer,
ROOT_PROD_REDEFINE_NM nvarchar(90),
LOGICAL_DEL_IND CHAR(1) DEFAULT 'I')
CREATE INDEX REDEFLIM_MSTR_GBL_IDX1 ON #PNLPPK_REDEF_TABLE_REDEF_LIM_CRT_MSTR (CTRY_CD, PANEL_CD, PROD_ID, PPK_ID)
SET @temp_tbl_rlim_crt_mstr = '#PNLPPK_REDEF_TABLE_REDEF_LIM_CRT_MSTR'
CREATE TABLE #PNLPPK_REDEF_TABLE_REDEF_LIM_CRT(
CTRY_CD nvarchar(3),
PANEL_CD nvarchar(3),
PROD_REDEFINE_ID integer,
PROD_ID integer,
PPK_ID integer,
ROOT_PROD_REDEFINE_NM nvarchar(90),
LOGICAL_DEL_IND CHAR(1) DEFAULT 'I')
CREATE INDEX REDEFLIM_GBL_IDX1 ON #PNLPPK_REDEF_TABLE_REDEF_LIM_CRT(CTRY_CD, PANEL_CD, PROD_ID, PPK_ID)
SET @temp_tbl_rlim_crt = '#PNLPPK_REDEF_TABLE_REDEF_LIM_CRT'
END
IF @src_id = 2 OR @src_id = 1 OR (@src_id = 3 AND @calling_nbr = 2)
BEGIN
CREATE TABLE #PPK_REDEF_TABLE_REDEF_LIM_CRT_MSTR(
CTRY_CD nvarchar(3),
PROD_REDEFINE_ID integer,
PROD_ID integer,
PPK_ID integer,
ROOT_PROD_REDEFINE_NM nvarchar(90),
LOGICAL_DEL_IND CHAR(1) DEFAULT 'I')
CREATE INDEX REDEFLIM_MSTR_UNF_IDX1 ON #PPK_REDEF_TABLE_REDEF_LIM_CRT_MSTR (CTRY_CD, PROD_ID, PPK_ID)
SET @temp_tbl_rlim_crt_mstr = '#PPK_REDEF_TABLE_REDEF_LIM_CRT_MSTR'
CREATE TABLE #PPK_REDEF_TABLE_REDEF_LIM_CRT(
CTRY_CD nvarchar(3),
PROD_REDEFINE_ID integer,
PROD_ID integer,
PPK_ID integer,
ROOT_PROD_REDEFINE_NM nvarchar(90),
LOGICAL_DEL_IND CHAR(1) DEFAULT 'I')
CREATE INDEX REDEFLIM_UNF_IDX1 ON #PPK_REDEF_TABLE_REDEF_LIM_CRT(CTRY_CD, PROD_ID, PPK_ID)
SET @temp_tbl_rlim_crt = '#PPK_REDEF_TABLE_REDEF_LIM_CRT'
END
SELECT @limit_cnt = count(*) FROM MKT_MKTDEF_PROD_REDEFINE_CRITERIA
WHERE MKTDEF_VERSION_ID = @mktdef_vrsn_id
AND PROD_REDEFINE_ID = @redef_id
AND MKTDEF_CRITERIA_TYP_CD = 'L'
AND (PAR_MKTDEF_PROD_REDEFINE_SEQ_ID = 0 or PAR_MKTDEF_PROD_REDEFINE_SEQ_ID IS NULL)
DECLARE Redefine_Limit_Criteria CURSOR FOR
SELECT MKTDEF_PROD_REDEFINE_SEQ_ID, MKTDEF_CRITERIA_TYP_CD, PROD_SELECTION_CLAS, PROD_SELECTION_NM, CLMN_NM, SRC_ID, CLMN_VAL,CTRY_CD
FROM MKT_MKTDEF_PROD_REDEFINE_CRITERIA
WHERE MKTDEF_VERSION_ID = @mktdef_vrsn_id
AND PROD_REDEFINE_ID = @redef_id
AND MKTDEF_CRITERIA_TYP_CD = 'L'
AND (PAR_MKTDEF_PROD_REDEFINE_SEQ_ID = 0 or PAR_MKTDEF_PROD_REDEFINE_SEQ_ID IS NULL)
-- Open the Cursor
OPEN Redefine_Limit_Criteria
-- Fetch the first row FROM the Cursor
FETCH NEXT FROM Redefine_Limit_Criteria
INTO @seq_id_parent, @criteria_typ_parent, @sel_clas_parent, @sel_nm_parent, @col_nm_parent, @sel_src_parent, @col_val_parent, @ctry_parent
WHILE @@fetch_status = 0
BEGIN --2
SET @sql_st1 = 'TRUNCATE TABLE ' + @temp_tbl_rlim_crt
EXEC sp_executesql @sql_st1
print @sql_st1
SET @return = @@error
IF @return <> 0
BEGIN --3
RAISERROR('Error truncating temporary table', 16, 1)
CLOSE Limit_Crit
DEALLOCATE Limit_Crit
RETURN(@return)
END --3
SET @sql_st1 = 'INSERT INTO ' + @temp_tbl_rlim_crt + ' SELECT * FROM ' + @temp_tbl
EXEC sp_executesql @sql_st1
print @sql_st1
SET @return = @@error
IF @return <> 0
BEGIN --3
RAISERROR('Error Inserting into temporary table', 16, 1)
CLOSE Limit_Crit
DEALLOCATE Limit_Crit
RETURN(@return)
END --3
SELECT @srchLen=key_srch_len, @fillChar=key_clmn_fill_char
FROM MKT_PROD_SELECTION
WHERE PROD_SELECTION_CLAS = @sel_clas_parent
AND PROD_SELECTION_NM = @sel_nm_parent
AND src_id = @src_id
IF @srchLen > 0
BEGIN --3
SET @operator = ' LIKE '
SET @col_val = SUBSTRING(@col_val_parent, 1, @srchLen)
END --3
ELSE
BEGIN --3
SET @operator = ' = '
SET @col_val = @col_val_parent
SET @fillChar = ''
END --3
IF @src_id = 3 AND @calling_nbr = 1
BEGIN
SELECT @mast_tbl_nm = REFC_TBL_NM FROM MKT_CRITERIA_REFC_LKUP
WHERE CLMN_NM = @col_nm_parent
AND DATA_SRC_NM = '3'
SET @return = @@error
IF @return <> 0
BEGIN
RAISERROR('Error retrieving the reference table name', 16, 1)
RETURN(16)
END
-- SET @sql_st = 'UPDATE ' + @temp_tbl_rlim_crt + ' SET LOGICAL_DEL_IND = ' + char(39) + 'D' + char(39) + ' FROM ' + @temp_tbl_rlim_crt + ' t ' + ' WHERE Not Exists( ' + char(13)
SET @sql_st = 'UPDATE ' + @temp_tbl_rlim_crt + ' SET LOGICAL_DEL_IND = ' + char(39) + 'L' + char(39) + ' FROM ' + @temp_tbl_rlim_crt + ' t ' + ' WHERE Exists( ' + char(13)
SET @sql_st = @sql_st + ' SELECT 1 FROM ' + @mast_tbl_nm + ' A' + char(13)
SET @sql_st = @sql_st + ' WHERE t.CTRY_CD = A.CTRY_CD ' + char(13)
SET @sql_st = @sql_st + ' AND t.PANEL_CD = A.PANEL_CD ' + char(13)
SET @sql_st = @sql_st + ' AND t.PROD_ID = A.PROD_ID ' + char(13)
SET @sql_st = @sql_st + ' AND t.PPK_ID = A.PPK_ID ' + char(13)
END
IF @src_id = 2 OR @src_id = 1 OR (@src_id = 3 AND @calling_nbr = 2)
BEGIN
IF @src_id = 2 OR @src_id = 1
Begin
SELECT @mast_tbl_nm = REFC_TBL_NM FROM MKT_CRITERIA_REFC_LKUP
WHERE CLMN_NM = @col_nm_parent
AND DATA_SRC_NM = '1-2'
SET @return = @@error
End
IF @src_id = 3 AND @calling_nbr = 2
Begin
SELECT @mast_tbl_nm = REFC_TBL_NM FROM MKT_CRITERIA_REFC_LKUP
WHERE CLMN_NM = @col_nm_parent
AND DATA_SRC_NM = '3-4'
SET @return = @@error
End
IF @return <> 0
BEGIN
RAISERROR('Error retrieving the reference table name', 16, 1)
RETURN(16)
END
-- SET @sql_st = 'UPDATE ' + @temp_tbl_rlim_crt + ' SET LOGICAL_DEL_IND = ' + char(39) + 'D' + char(39) + ' FROM ' + @temp_tbl_rlim_crt + ' t ' + ' WHERE Not Exists( ' + char(13)
SET @sql_st = 'UPDATE ' + @temp_tbl_rlim_crt + ' SET LOGICAL_DEL_IND = ' + char(39) + 'L' + char(39) + ' FROM ' + @temp_tbl_rlim_crt + ' t ' + ' WHERE Exists( ' + char(13)
SET @sql_st = @sql_st + ' SELECT 1 FROM ' + @mast_tbl_nm + ' A' + char(13)
SET @sql_st = @sql_st + ' WHERE t.CTRY_CD = A.CTRY_CD ' + char(13)
SET @sql_st = @sql_st + ' AND t.PROD_ID = A.PROD_ID ' + char(13)
SET @sql_st = @sql_st + ' AND t.PPK_ID = A.PPK_ID ' + char(13)
END
SET @sql_st = @sql_st + ' And ( A.' + @col_nm_parent + @operator + char(39) + @col_val + @fillChar + char(39) + ')' +char(13)
IF @ctry_parent IS NOT NULL
BEGIN
SET @sql_st = @sql_st + ' AND A.CTRY_CD = ' + char(39) + @ctry_parent + char(39)
END
SET @sql_st = @sql_st + ')'
print @sql_st
EXEC sp_executesql @sql_st
SET @return = @@error
IF @return <> 0
BEGIN --3
RAISERROR('Error deleting limit criteria FROM the #PFS_Redef_Table', 16, 1)
CLOSE Redefine_Limit_Criteria
DEALLOCATE Redefine_Limit_Criteria
RETURN(@return)
END --3
SET @col_val_parent_proc = @operator + char(39) + @col_val + @fillChar + char(39)
SELECT @num_child1=count(*)
FROM MKT_MKTDEF_PROD_REDEFINE_CRITERIA
WHERE MKTDEF_VERSION_ID = @mktdef_vrsn_id
AND PROD_REDEFINE_ID = @redef_id
AND PAR_MKTDEF_PROD_REDEFINE_SEQ_ID = @seq_id_parent
IF @num_child1 > 0
BEGIN
EXEC @return = xMKT_CMD_User.PR_MKT_BUILD_FIRST_REDEF_CHILD_CRIT @mktdef_vrsn_id,
@calling_nbr, @redef_id,
@seq_id_parent,
@bld_lvl,
@temp_tbl,
@mast_tbl_nm,
@col_nm_parent,
@col_val_parent_proc,
@ctry_parent,
@criteria_typ_parent,
@src_id
IF @return <> 0
BEGIN
RAISERROR('Error updating first level of child criteria', 16, 1)
CLOSE Redefine_Limit_Criteria
DEALLOCATE Redefine_Limit_Criteria
RETURN(@return)
END
END
SET @sql_st1 = 'INSERT INTO ' + @temp_tbl_rlim_crt_mstr + ' SELECT * FROM ' + @temp_tbl_rlim_crt
EXEC sp_executesql @sql_st1
IF @return <> 0
BEGIN
RAISERROR('Error Inserting into temporary table', 16, 1)
CLOSE Redefine_Limit_Criteria
DEALLOCATE Redefine_Limit_Criteria
RETURN(@return)
End
FETCH NEXT FROM Redefine_Limit_Criteria
INTO @seq_id_parent, @criteria_typ_parent, @sel_clas_parent, @sel_nm_parent, @col_nm_parent, @sel_src_parent, @col_val_parent, @ctry_parent
END
IF @num_child1 = 0
BEGIN --3
SET @sql_st = 'UPDATE ' + @temp_tbl_rlim_crt_mstr + ' SET LOGICAL_DEL_IND = ' + char(39) + 'D' + char(39) + ' WHERE LOGICAL_DEL_IND = ' + char(39) + 'I' + char(39)
print @sql_st
EXEC sp_executesql @sql_st
SET @return = @@error
IF @return <> 0
BEGIN --3
RAISERROR('Error deleting limit criteria FROM the Temporary Table', 16, 1)
CLOSE Limit_Crit
DEALLOCATE Limit_Crit
RETURN(@return)
END --3
SET @sql_st = 'UPDATE ' + @temp_tbl_rlim_crt_mstr + ' SET LOGICAL_DEL_IND = ' + char(39) + 'I' + char(39) + ' WHERE LOGICAL_DEL_IND = ' + char(39) + 'L' + char(39)
print @sql_st
EXEC sp_executesql @sql_st
SET @return = @@error
IF @return <> 0
BEGIN --3
RAISERROR('Error deleting limit criteria FROM the Temporary Table', 16, 1)
CLOSE Limit_Crit
DEALLOCATE Limit_Crit
RETURN(@return)
END --3
END
IF @limit_cnt > 0
BEGIN
SET @sql_st1 = 'TRUNCATE TABLE ' + @temp_tbl
EXEC sp_executesql @sql_st1
IF @return <> 0
BEGIN
RAISERROR('Error truncating temporary table', 16, 1)
CLOSE Limit_Crit
DEALLOCATE Limit_Crit
RETURN(@return)
End
SET @sql_st1 = 'INSERT INTO ' + @temp_tbl + ' SELECT * FROM ' + @temp_tbl_rlim_crt_mstr
EXEC sp_executesql @sql_st1
IF @return <> 0
BEGIN
RAISERROR('Error Inserting into temporary table', 16, 1)
CLOSE Limit_Crit
DEALLOCATE Limit_Crit
RETURN(@return)
END
END
CLOSE Redefine_Limit_Criteria
DEALLOCATE Redefine_Limit_Criteria
RETURN(0)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
****************************************************
December 30, 2008 at 1:19 am
A cursory overview (no pun intended) says that this is a hierarchical sproc and it uses a cursor and a whole lot of RBAR... lord only knows what the sprocs it calls do. The only way it's going to get any faster is if someone sits down with the business rules, and rewrites it using some decent set based logic. If you have 50 such sprocs, I'd say you have your work pretty much cut out for you for about six months of some dedicated programming.
Sorry, there's not much more that I could recommend for something with this much RBAR in it.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2008 at 1:38 am
Jeff Moden (12/30/2008)
The only way it's going to get any faster is if someone sits down with the business rules, and rewrites it using some decent set based logic.
Agreed. There's no quick fix for this.
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
December 30, 2008 at 1:42 am
Posting the full code doesn't make any difference here. You have a cursor, inside a cursor looping through who knows how many rows - creating multiple temp tables for the sole purpose of calling the following procedure (and passing the temp tables to this procedure), which is definitely using dynamic SQL and probably also has cursors that loop through the data in the temp tables that are passed in.
EXEC @return = xMKT_CMD_User.PR_MKT_BUILD_FIRST_REDEF_CHILD_CRIT @mktdef_vrsn_id,
@calling_nbr, @redef_id,
@seq_id_parent,
@bld_lvl,
@temp_tbl,
@mast_tbl_nm,
@col_nm_parent,
@col_val_parent_proc,
@ctry_parent,
@criteria_typ_parent,
@src_id
I have to agree with Jeff M - the only way this is going to get better (and not just faster, better in this case means better performing, better maintainability, better readability, etc...) is to sit down with the analysts and/or users and rewrite this.
Unlike Jeff though - I don't think this will be completed in six months. You probably have at least a years worth of work, and I say that because I bet the procedures being called will all have to be rewritten also.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 30, 2008 at 2:46 am
Thanks all guys for your valuable suggestions.........especially gail & jeff. thanks again
December 30, 2008 at 2:51 am
Jeff Moden (12/30/2008)
The only way it's going to get any faster is if someone sits down with the business rules, and rewrites it using some decent set based logic. If you have 50 such sprocs
Yes I am also agree with Jeff. But I am some suggestions for you which you can use as quick fix.
-There is duplicate query, one for getting COUNT and one for CURSOR declaration. This can be removed.
-There is only one column (PANEL_CD nvarchar(3)) is different in the temporary table so dont use the different table. Use null value when this column is not being used. So IF conditions can be removed in table creation. It also helps to remove the all dynamic quries.
-You are truncating the one table (@temp_tbl_rlim_crt) for each record, so I think dont create index on this table.
Hope these will help you to get some performance improvement. (There is no guarantee!).
Regards,
Nitin
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply