Can this be done without a cursor?
-
Just for **** and giggles, I created a function zeroIsMinimum to handle the negative numbers and see how this affected performance. I got some pretty unusual results. now this may be just my system and I'm sure more testing is needed, but here goes.
Using my statement with two case's had a 16 Byte cache plan size and .029 estimated cost. Gova's came in with 22 Bytes and .044. The odd part is, when ran with the function (below) it comes in with a lower subtree cost of .019 but a higher plan size of 25 Bytes.
I think it's definately time to get a book on optimization!
CREATE FUNCTION dbo.zeroIsMinimum(@inputValue INT)
RETURNS INT
AS
BEGIN
DECLARE @returnValue INT
IF @inputValue < 0
SET @returnValue = 0
ELSE
SET @returnValue = @inputValue
RETURN @returnValue
END
GO
UPDATE i
SET qty_on_hand = dbo.zeroIsMinimum(qty_on_hand - (SELECT COUNT(*) FROM @workTable w WHERE w.partno = i.inv_ID))
FROM @I_master i
Other than the possible model changes, what's your take on this Sergiy?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
> Other than the possible model changes, what's your take on this Sergiy?
1. Simplicity
2. Logical integrity
3. Less processing (and processing moved to precompiled view code - more effective)
4. Automatic and instant updating, no need to set a job to refresh table
5. Less maintenance
Need more?
_____________
Code for TallyGenerator -
Actually I was refering to the execution plan diffs.
EDIT: Oh, and I agree with you on the previous...
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
It was more not for your eyes, but for OP.
You just asked right question.
_____________
Code for TallyGenerator
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply