Table locking and Dynamic SP's

I wrote a SP to update a table that is 98 column across.  This is a dynamic 
SP.

I have a few questions:
1.  Is there a better way to deal with table locking
2.  Is there anyway to get around it
3.  Can anyone make suggestions to the code below (is it optimized)

We have added index and keys to all the table involved, tried different
renditions of the code below.  This handles batch processing and ad-hoc.
Basically I am at my wits end and in alot of trouble if this doesn't work.  
I
will admit that I completely forgot about table locking when I designed this
process......  Any and all ideas are appreciated

CREATE PROCEDURE dbo. usp_Upd_Cr_Lg_Sku_Ch
annel_Dollarization @dept as
varchar(10)

AS
SET NoCount On

--Declare variables and counter
Declare @dept1 as varchar(10), @Item_id1 as varchar (50), @cnt varchar(2)

--intializing the variables
If @dept = ''
BEGIN
Select @dept1 = null, @dept = 'null'
END
Else
Begin
Select @dept = '''' + rtrim(@dept) + '''', @dept1 = rtrim(@dept)
End

--Deletes the table when dept and item_id are null
--Should be used during batch processing
Delete from dbo. Cr_Lg_Sku_Channel_Do
llarization
Where (@dept1 is null)
--and @Item_id1 is null

--Inserts all records into the table when dept and item_id are null
--Should be used during batch processing

Insert Into  Cr_Lg_Sku_Channel_Do
llarization
Select Distinct Pyramid, 'Y' as Logility_Flag, Dept,
Item_Id, Item_Desc, Channel_Code,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
getdate()
From cr_LG_RSLT_FCST, cr_CHANNEL
Where (@dept1 is null)
Order by Dept, Item_Id, Channel_Code

set @cnt = '1'

WHILE (@cnt <> 19)
BEGIN

--This will only initialize the dept or sku to zero
EXEC('Update  Cr_Lg_Sku_Channel_Do
llarization
Set Splt_QTY_' + @cnt + ' = 0,
Extd_Retail_' + @cnt + ' = 0,
Extd_Cost_' + @cnt + ' = 0,
Extd_Yen_Retail_' + @cnt + ' = 0,
Extd_Yen_Cost_' + @cnt + ' = 0,
DT_LOAD = getdate()
from   Cr_Lg_Sku_Channel_Do
llarization
WHERE Logility_flag  IN (''Y'', ''H'', ''P'') AND (' + @dept + ' is null or
(' + @dept + ' is not null and DEPT = ' + @dept + '))')

select @cnt = @cnt + 1

End

set @cnt = '1'

WHILE (@cnt <> 19)
BEGIN

--Updating the Extended US / Yen Cost at the SKU level
EXEC('Update  Cr_LG_Sku_Channel_Do
llarization
Set
Extd_Cost_'+@cnt+' = X.Extd_Cost_'+@cnt + ',
Extd_Yen_Cost_' + @cnt + ' = X.Extd_Yen_Cost_' + @cnt + ',
Splt_Qty_' + @cnt + ' = X.Splt_Qty_' + @cnt + ',
Extd_Retail_' + @cnt + ' = X.Extd_Retail_' + @cnt + ',
Extd_Yen_Retail_' + @cnt + ' = X.Extd_Yen_Retail_' + @cnt + '
FROM  Cr_LG_Sku_Channel_Do
llarization J INNER JOIN (
SELECT F.DEPT, F.ITEM_ID, S.Channel_Code,
SUM((F.FCST_QTY_' + @cnt + ' * (Cast(F.US_AVG_COST as decimal (18,
2)))
* S.SPLT_FCTR_' + @cnt + ')) AS Extd_Cost_' + @cnt + ',
Case When S.Channel_Code = ''57001''
Then SUM((F.FCST_QTY_' + @cnt + ' * (Cast(F.YEN_AVG_COST as decimal (18,
2)))
* S.SPLT_FCTR_' + @cnt + '))
Else ''0''
End as Extd_Yen_Cost_' + @cnt + ',
ROUND(SUM(F.FCST_QTY_' + @cnt + ' * S.SPLT_FCTR_' + @cnt + '), 2) AS
Splt_Qty_' + @cnt + ',
Case When S.Channel_Code = ''57001''
Then SUM(((F.FCST_QTY_' + @cnt + ' * (Cast(F.Yen_RETAIL_PRC as decimal
(18, 2)))
* S.SPLT_FCTR_' + @cnt + ') * DIS_FCTR_' + @cnt + ') *
Exchange_Rate)
Else SUM((F.FCST_QTY_' + @cnt + ' * (Cast(F.US_RETAIL_PRC as decimal
(18, 2)))
* S.SPLT_FCTR_' + @cnt + ') * DIS_FCTR_' + @cnt + ')
End as Extd_Retail_' + @cnt + ',
Case When S.Channel_Code = ''57001''
Then SUM((F.FCST_QTY_' + @cnt + ' * (Cast(F.Yen_RETAIL_PRC as decimal
(18, 2)))
* S.SPLT_FCTR_' + @cnt + ') * DIS_FCTR_' + @cnt + ')
Else 0
End as Extd_Yen_Retail_' + @cnt + '
FROM cr_YEN_CONVERSION, cr_LG_RSLT_FCST F INNER JOIN cr_CHANNEL C
ON C.Logility_Channel = F.CHANNEL INNER JOIN cr_CHANNEL_SPLITS S ON
C.Channel_Code = S.Channel_Code AND F.DEPT = S.DEPT Inner Join
 cr_CHANNEL_DISCOUNTS
 d On C.Channel_Code = d.Channel_Code AND f.DEPT =
d.DEPT
WHERE (' + @dept + ' is null or
(' + @dept + ' is not null and F.DEPT = ' + @dept + ')) ' + '
GROUP BY F.DEPT, F.ITEM_ID, S.Channel_Code ) X
ON J.Item_Id = X.Item_Id and J.Channel_Code = X.Channel_Code')

Select @cnt = @cnt + 1

END

On Mon, 29 Aug 2005 14:37:35 -0700, kuhrty
<kuhrty@discussions.microsoft.com>  wrote:
>I wrote a SP to update a table that is 98 column across.  This is a dynamic SP.[/co
lor]

You have 19 repeating groups in the table.

You are thus violating first normal form 19 times.

And your routine is going to run at least 19 times slower than it
should, more like 190 times because of all the dynamic SQL.

J.



>
>I have a few questions:
>1.  Is there a better way to deal with table locking
>2.  Is there anyway to get around it
>3.  Can anyone make suggestions to the code below (is it optimized)
>
>We have added index and keys to all the table involved, tried different
>renditions of the code below.  This handles batch processing and ad-hoc.
>Basically I am at my wits end and in alot of trouble if this doesn't work. 
 I
>will admit that I completely forgot about table locking when I designed thi
s
>process......  Any and all ideas are appreciated
>
>CREATE PROCEDURE dbo. usp_Upd_Cr_Lg_Sku_Ch
annel_Dollarization @dept as
>varchar(10)
>
>AS
>SET NoCount On
>
>--Declare variables and counter
>Declare @dept1 as varchar(10), @Item_id1 as varchar (50), @cnt varchar(2)
>
>--intializing the variables
>If @dept = ''
>	BEGIN
>	Select @dept1 = null, @dept = 'null'
>	END
>Else
>	Begin
>	Select @dept = '''' + rtrim(@dept) + '''', @dept1 = rtrim(@dept)
>	End
>
>--Deletes the table when dept and item_id are null
>--Should be used during batch processing
>Delete from dbo. Cr_Lg_Sku_Channel_Do
llarization
>Where (@dept1 is null)
>--and @Item_id1 is null
>
>--Inserts all records into the table when dept and item_id are null
>--Should be used during batch processing
>
>Insert Into  Cr_Lg_Sku_Channel_Do
llarization
>Select Distinct Pyramid, 'Y' as Logility_Flag, Dept,
>Item_Id, Item_Desc, Channel_Code,
>0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
>0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
>0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
>0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
>0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
>getdate()
>From cr_LG_RSLT_FCST, cr_CHANNEL
>Where (@dept1 is null)
>Order by Dept, Item_Id, Channel_Code
>
>set @cnt = '1'
>
>WHILE (@cnt <> 19)
>BEGIN
>
>--This will only initialize the dept or sku to zero
>EXEC('Update  Cr_Lg_Sku_Channel_Do
llarization
>Set Splt_QTY_' + @cnt + ' = 0,
>    Extd_Retail_' + @cnt + ' = 0,
>    Extd_Cost_' + @cnt + ' = 0,
>    Extd_Yen_Retail_' + @cnt + ' = 0,
>    Extd_Yen_Cost_' + @cnt + ' = 0,
>    DT_LOAD = getdate()
>from   Cr_Lg_Sku_Channel_Do
llarization
>WHERE Logility_flag  IN (''Y'', ''H'', ''P'') AND (' + @dept + ' is null or
>      (' + @dept + ' is not null and DEPT = ' + @dept + '))')
>
>select @cnt = @cnt + 1
>
>End
>
>set @cnt = '1'
>
>WHILE (@cnt <> 19)
>BEGIN
>
>--Updating the Extended US / Yen Cost at the SKU level
>EXEC('Update  Cr_LG_Sku_Channel_Do
llarization
>Set
>Extd_Cost_'+@cnt+' = X.Extd_Cost_'+@cnt + ',
>Extd_Yen_Cost_' + @cnt + ' = X.Extd_Yen_Cost_' + @cnt + ',
>Splt_Qty_' + @cnt + ' = X.Splt_Qty_' + @cnt + ',
>Extd_Retail_' + @cnt + ' = X.Extd_Retail_' + @cnt + ',
>Extd_Yen_Retail_' + @cnt + ' = X.Extd_Yen_Retail_' + @cnt + '
>FROM  Cr_LG_Sku_Channel_Do
llarization J INNER JOIN (
>        SELECT F.DEPT, F.ITEM_ID, S.Channel_Code,
>        SUM((F.FCST_QTY_' + @cnt + ' * (Cast(F.US_AVG_COST as decimal (18,
>2)))
>           * S.SPLT_FCTR_' + @cnt + ')) AS Extd_Cost_' + @cnt + ',
>	Case When S.Channel_Code = ''57001''
> 	Then SUM((F.FCST_QTY_' + @cnt + ' * (Cast(F.YEN_AVG_COST as decimal (18,
>2)))
>               * S.SPLT_FCTR_' + @cnt + '))
>	Else ''0''
>	End as Extd_Yen_Cost_' + @cnt + ',
>	ROUND(SUM(F.FCST_QTY_' + @cnt + ' * S.SPLT_FCTR_' + @cnt + '), 2) AS
>Splt_Qty_' + @cnt + ',
>	Case When S.Channel_Code = ''57001''
>	     Then SUM(((F.FCST_QTY_' + @cnt + ' * (Cast(F.Yen_RETAIL_PRC as decima
l
>(18, 2)))
>        	      * S.SPLT_FCTR_' + @cnt + ') * DIS_FCTR_' + @cnt + ') *
>Exchange_Rate)
>	     Else SUM((F.FCST_QTY_' + @cnt + ' * (Cast(F.US_RETAIL_PRC as decimal
>(18, 2)))
>        	     * S.SPLT_FCTR_' + @cnt + ') * DIS_FCTR_' + @cnt + ')
>     		End as Extd_Retail_' + @cnt + ',
>	Case When S.Channel_Code = ''57001''
>     		Then SUM((F.FCST_QTY_' + @cnt + ' * (Cast(F.Yen_RETAIL_PRC as decima
l
>(18, 2)))
>        	      * S.SPLT_FCTR_' + @cnt + ') * DIS_FCTR_' + @cnt + ')
>		Else 0
>		End as Extd_Yen_Retail_' + @cnt + '
>	FROM cr_YEN_CONVERSION, cr_LG_RSLT_FCST F INNER JOIN cr_CHANNEL C
>	ON C.Logility_Channel = F.CHANNEL INNER JOIN cr_CHANNEL_SPLITS S ON
>	C.Channel_Code = S.Channel_Code AND F.DEPT = S.DEPT Inner Join
> 	cr_CHANNEL_DISCOUNT
S d On C.Channel_Code = d.Channel_Code AND f.DEPT =
>d.DEPT
>	WHERE (' + @dept + ' is null or
>              (' + @dept + ' is not null and F.DEPT = ' + @dept + ')) ' + '
>	GROUP BY F.DEPT, F.ITEM_ID, S.Channel_Code ) X
>ON J.Item_Id = X.Item_Id and J.Channel_Code = X.Channel_Code')
>
>Select @cnt = @cnt + 1
>
>END

kuhrty (kuhrty@discussions.microsoft.com)  writes:
> I wrote a SP to update a table that is 98 column across.  This is a
> dynamic SP.
>...
> We have added index and keys to all the table involved,

Indexes is not going to help you much with WHERE clauses like:

> WHERE Logility_flag  IN (''Y'', ''H'', ''P'') AND (' + @dept + ' is null
> or
>       (' + @dept + ' is not null and DEPT = ' + @dept + '))')

Since you are into dynamic SQL anyway, generate the WHERE clause
that reflects the actual condition. Then at least updates on a single
department, should not lock the table, provided that there is an index
on DEPT.

But a redesign where you move the 19 repeating columns to a subtable
is probably a good idea...

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp