having issues with timing out.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

having issues with timing out.

Author

Topic

morphviper
Yak Posting Veteran

60 Posts

Posted - 2006-12-29 : 10:25:49

this stored procedure basically restocks closets automatically and tells me what to put in those closets.

The following stored procedure calls a udf.
I have tried it with the udf and with out and it still takes the same amount of time. the problem is that this is on a website so it takes a few minutes to run causeing it to time out. If any one can look at this and ask questions and make suggestions it would be greatly appreciated! Thank you in advance! This is all in SQL 2000. I did not write this and I am not real good at SQL yet, I was asked to fix this and everything I try does not change things.
-----------------Stored Procedure------------------------------
CREATE Procedure Restock
@CS_Recnum as Integer,
@LOC_Recnum as Integer
AS

CREATE TABLE #Restock
(
ItRecnum Int,
ParLevel Int,
CSOnHand Int,
LocOnHand Int,
Need Int,
CanTransfer Int
)

DECLARE @ItRecnum as Int
DECLARE @ParLevel as Int
DECLARE @CSOnHand as Int
DECLARE @LocOnHand as Int
DECLARE @Need as int
DECLARE @CanTransfer as int
DECLARE @CURRENTDATE as DateTime

SET @CurrentDate=(Select DateAdd(n,1,GetDate()))

DECLARE CURSOR1 CURSOR FAST_FORWARD FOR
Select It_Recnum, Itml_ParLevel from Item
LEFT JOIN Itml On Itml_It_Recnum=It_Recnum
Where Itml_ParLevel IS NOT NULL AND Itml_ItLoc_Recnum=@LOC_Recnum
OPEN CURSOR1

FETCH NEXT FROM CURSOR1 INTO @ItRecnum, @ParLevel

WHILE @@FETCH_STATUS=0
BEGIN
SET @CSOnHand=(Select Oh_CalculatedOnHand from GetOnHand(@ItRecnum,@CS_Recnum,@CurrentDate))
SET @LocOnHand=(Select Oh_CalculatedOnHand from GetOnHand(@ItRecnum,@LOC_Recnum,@CurrentDate))
SET @Need=@ParLevel-@LocOnHand
IF @Need>@CSOnHand
IF @CSOnHand<=0
SET @CanTransfer=0
ELSE
SET @CanTransfer=@CSOnHand
ELSE
SET @CanTransfer=@Need
IF @Need<=0 SET @CanTransfer=0
IF @Need<=0 SET @Need=0
INSERT #Restock Values (@ItRecnum,@ParLevel,@CSOnHand,@LocOnHand,@Need,@CanTransfer)
FETCH NEXT FROM CURSOR1 INTO @ItRecnum, @ParLevel
END

CLOSE CURSOR1
DEALLOCATE CURSOR1

SELECT It_Descr, Uom_ID, Uom_Text, #Restock.* FROM #Restock
Left Join Item On ItRecnum=It_recnum
Left Join ItemUOM on It_Uom_Recnum=Uom_Recnum
WHERE CSOnHand IS NOT NULL
Order By Need DESC

DROP TABLE #Restock

GO

-------------------FUNCTION-----------------------------------
CREATE FUNCTION dbo.GetOnHand(@It_Recnum as Int, @ItLoc_Recnum as Int, @OnHandDate as DateTime)
RETURNS @PICount TABLE
(Oh_It_Recnum int,
Oh_ItLoc_Recnum int,
Oh_LastCountDate DateTime,
Oh_LastCount int,
Oh_CalculatedOnHand Int
)
AS

BEGIN

DECLARE @Act TABLE
(Act_DateTime DateTime,Act_Quantity Int)

DECLARE @LastCountDate as DateTime --Last Date that a PI Count was taken
DECLARE @LastCount as Integer --Last Count Quantity
--DECLARE @ItLoc_Recnum as int --LocationRecnum
DECLARE @CalculatedCount as int --Count Calculated From All Activity from Prevous Count to @OnHandDate
DECLARE @CountTaken as int --Indicated if a count was taken at all in the give date range

IF @ItLoc_Recnum=0
BEGIN
DECLARE LocList CURSOR FAST_FORWARD FOR
--SELECT DISTINCT Act_ItLoc_Recnum FROM Activity Where Act_It_Recnum=@It_Recnum and Act_DateTime<@OnHandDate
Select ItLoc_Recnum from ItemLocation
OPEN LocList
END
ELSE
BEGIN
DECLARE LocList CURSOR FAST_FORWARD FOR
Select ItLoc_Recnum from ItemLocation where ItLoc_Recnum=@ItLoc_Recnum
OPEN LocList
END

FETCH NEXT FROM LocList INTO @ItLoc_Recnum
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @ACT Select Top 1 Act_DateTime,Act_Quantity from ActivityA Where Act_Type=3 and Act_ItLoc_recnum=@ItLoc_Recnum And Act_It_Recnum=@It_Recnum and Act_DateTime<@OnHandDate Order By Act_DateTime Desc
SET @CountTaken=(Select Count(Act_DateTime) from @ACT)
IF @CountTaken>0
BEGIN
SET @LastCountDate=(Select Act_DateTime from @ACT)
SET @LastCount= (Select Act_Quantity from @ACT)
END
ELSE
BEGIN
SET @LastCountDate='1/1/2002'
SET @LastCount=0
END
SET @CalculatedCount=(select Sum(act_quantity) from ActivityA where Act_DateTime>=@LastCountDate and Act_DateTime<@OnHandDate And Act_It_Recnum=@It_Recnum and Act_ItLoc_recnum=@ItLoc_Recnum Group By Act_ItLoc_Recnum)
Insert @PiCount Values (@It_Recnum,@ItLoc_Recnum,@LastCountDate,@LastCount,@CalculatedCount)
DELETE FROM @ACT
FETCH NEXT FROM LocList INTO @ItLoc_Recnum
END
CLOSE LocList
DEALLOCATE LocList
RETURN
END

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-29 : 12:46:27

Apparently, the person that wrote this wasn't "very good at SQL", either

Basically, the code isn't worth saving. I'd figure out what the code is doing and rewrite it without any cursors, loops, or correlated subqueries that contain inequalities. I'd also lose the hardcoded dates.

Before you write a lick of code, write out a simple ERD for the table joins and some pseudo code for what you want the code to do. Avoid the use of UDF's, if possible (should be possible considering that this is just an inventory check/restock proc). Planning is absolutely essential to rewriting this code with some level of performance in mind. It's pretty obvious that all the original developer (actually hate to call him/her that) did was hack a solution with no plan.

Be sure to include the pseudo code as comments so the next guy can maintain this.

It's not right to give someone who is relatively new such a mess to cleanup especially if they need it in a hurry... however (this is the bright side and well worth pursuing), you will quickly become an expert if they keep giving you garbage code like this to cleanup. Like I said, if you really want to make a good impression, figure out what the code is supposed to do and scrap the original code...

--Jeff Moden

morphviper
Yak Posting Veteran

60 Posts

Posted - 2006-12-29 : 15:23:59

Ok, lets scrap it.... You are assumming I know what I am doing! :)
any way...

here is what it is doing. it recieves the supply closset id and destination id.

It gets the Item description from the ITEM table, it gets the Par level from the ITML table. That is easy thus far...
where it gets tricky is it needs to get the supply qty on hand from a table called Activity. it also needs to get the supply qty for the destination id from the Activity table for each item that is in both places.

What I need to do is figure out what is my available stock in the supply room and what is the available stock in the destination. If the destination is less than par amount then I need to figure out how many is needed to make it at the par level and I also need to put in how many are available for transfer.

so the output looks like this.

Item,ItemID,ParLevel,Supply_Qty_on_Hand,Destination_Qty_On_Hand,How_many_needed,how_many_can_transfer

this is for every item that is located in both locations.

do you think you can point in the best approach to do this?

I have been doing a ton of research and it appears there is about 6 different ways to get this done...They all seem very taxing on the system?
thank you for any assistance!!!!

morphviper
Yak Posting Veteran

60 Posts

Posted - 2006-12-29 : 15:46:46

I also forgot to mention that the Supply qty and Destination qty has to be a sum from activity because this table keeps track of every transaction of stock. No running totals.

Thanks in advance for any guidance!

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-29 : 16:28:25

Couple o' questions...

1. Is @CS_RecNum the "supply closet ID" you spoke of?
2. Is @Loc_RecNum the "desigination ID" you spoke of?
3. Just confirming... are you saying that the "supply qty on hand" AND the "supply qty for the destination ID" (assuming this is the qty the destination has on hand) are both in the same table and that table is called the "Activity" table?
4. If item 3 is true, do both the "supply qty on hand" and "supply qty for the destination ID" come from the "Act_Quantity" column?
5. What is the difference between the "Activity" table and the "ActivityA" table? Or, is the "ActivityA" table the "Activity" table I mentioned in #3?
6. What says that the quantity on hand in a closet has been consumed or not?
7. What says that the quantity on hand at a destination has been consumed or not?
8. And what the heck does it mean when Act_Type=3?

The reason I'm concentrating so much on the proverbial activity table, is that is where it appears that most of the info we need to deal with is stored. The best way to attack these large problems is "one potato at a time". In other words...

Step 1. Determine how to figure out what the QTY on hand is for a given closet (all items, not just one...). This would be in the form of a "derived table" so we can easily join to it instead of using a cursor.
Step 2. Determine how to figure out what the QTY on hand is for a given destination (all items, not just one...). This would be in the form of a "derived table" so we can easily join to it instead of using a cursor.
Step 3. Create a "test join (select)" between the derived tables in steps 1 and 2... check results and performance.
Step 4. Tackle the next part of the problem depending on the outcome of the above.

Just a bit of "advise".... start thinking "How do I produce an entire column of information" instead of "How do I do everything for a single row?"

Post back...

--Jeff Moden

morphviper
Yak Posting Veteran

60 Posts

Posted - 2006-12-29 : 17:03:10

Answers
1=yes
2=yes
3=yes
4=yes
5=Same table...ActivityA is a view with less columns.
6=people carry hand held scanners that when an item is taken out of any supply or put in it is scanned by the person doing it. That scanned transaction goes to the Activity table and will put a -1 for withdrawl and 1 for an addition.
7=same as above, but the person is registered to that closet.
8=Well I am not a 100% sure but it seems to be a transaction type for physical transactions. adding and taking of stock.

Like I said, I am new to this....But I will give the stuff you suggested an honest effort!!

Thank you so far.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-29 : 17:13:41

On #6 and #7... What is the name of the column that the +/-1 go into? Or is there some bloody magic going on where the Act_Quantity is immediately incremented/decremented so that Act_Quantity ALWAYS has the correct count for ANY point in time?

--Jeff Moden

morphviper
Yak Posting Veteran

60 Posts

Posted - 2007-01-03 : 08:29:00

Sorry for the delay in responding...

The name of the column is act_quantity. You have to do a sum of the item to find out what the quantity is of a any given item and location.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources