MS SQL Server - Table locking and Dynamic SP's

Author

Table locking and Dynamic SP's

kuhrty

2005-08-29, 8:23 pm

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

JXStern

2005-08-30, 11:23 am

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.

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 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_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

Erland Sommarskog

2005-08-30, 8:23 pm

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

Sponsored Links