July 20, 2010 at 2:03 am
Hi Lutz....think you may have cracked it....many thanks.
Will need to run against production and see how well it performs on 100K products and 20M transactions....will post back.
Graham
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 20, 2010 at 9:15 am
Extraordinary..........:w00t:
Running on SQL 2000...7 year old box with 4gb RAM
100K products
20M transaction lines
sub 1 minute !!!!
beats the hell out of the vendor's app (2½hrs)...but that sits on a different type of RDBMS
Many thanks to Lutz and Jeff Moden (for his "quirky update")...I sincerely appreciate the help and advice guys.
Graham
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 20, 2010 at 11:53 am
Hi Graham,
😛 It's not my fault!! Jeff started it!! (or at least he's the one wroting the related article I usually refer to...) 😛
But before giong all crazy make sure this code will produce exactly the same result like the vendors code. First of all, the results have to be correct. Performance goes 2nd.
Likke I said before: If you have the chance to mark the rows in your source table as "expired" based on negative Stockdays in your intermediate table the code will just fly since it won't have to touch all the rows no longer needed... Just a thought...
July 20, 2010 at 12:06 pm
LutzM (7/20/2010)
But before giong all crazy make sure this code will produce exactly the same result like the vendors code. First of all, the results have to be correct. Performance goes 2nd.
Dont worry Lutz...I wont be going crazy ......whilst the SQL code is vastly superior in perfomance over vendors code...having been bitten too many times before, I check and check again that results are correct.
However, in this instance, not only is performance impproved, I have a sneeking suspicion that the SQL code returns the correct answers...unlike the other offering.
just kicked off the vendors app to xref...hey ho, only 2 hours to go...:-D
Graham
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 20, 2010 at 8:49 pm
I'm a bit late to the party because I was busy yesterday but I wanted to try that "quirky update" thingo, so I had a crack this morning... just don't tell my boss! 😛
Here is what I ended up with:
----------------------------
IF OBJECT_ID('dbo.Stock_TransactionsEx') IS NOT NULL
DROP TABLE [Stock_TransactionsEx]
GO
CREATE TABLE [Stock_TransactionsEx](
[ProductID] [int] NOT NULL,
[QuantityPurchased] [int] NOT NULL,
[PurchaseDays] [int] NOT NULL,
[CurrentStock] [int] NOT NULL,
[TotalStockSold] [int] NOT NULL,
[StockDays] [int] NOT NULL,
)
GO
INSERT INTO [Stock_TransactionsEx]
SELECT
T.ProductID,
T.Quantity As QuantityPurchased,
DATEDIFF(d, T.Purchase_date, GETDATE()) AS PurchaseDays,
D.Current_Stock AS CurrentStock,
(SUM(T.Quantity) OVER(PARTITION BY T.ProductID)) - D.Current_Stock AS TotalStockSold,
0 As StockDays
FROM dbo.Stock_Transactions T INNER JOIN
dbo.Stock_Details D ON T.ProductID = D.ProductID
order by productId, 3 Desc
GO
DECLARE @ProductID INT
DECLARE @RunningBalance INT
DECLARE @OutRunningBalance INT
UPDATE [dbo].[Stock_TransactionsEx]
SET
@RunningBalance = CASE WHEN ProductId = @ProductID
THEN
CASE WHEN @OutRunningBalance > 0
THEN @OutRunningBalance
ELSE 0
END
ELSE TotalStockSold
END,
StockDays = CASE WHEN QuantityPurchased > @RunningBalance
THEN (QuantityPurchased - @RunningBalance)*PurchaseDays
ELSE 0
END,
@OutRunningBalance = @RunningBalance - QuantityPurchased,
@ProductId = ProductID
FROM [dbo].[Stock_TransactionsEx] WITH (TABLOCKX)
OPTION (MAXDOP 1)
GO
SELECT ProductID, SUM(StockDays/CurrentStock) AS AvStockdays
FROM Stock_TransactionsEx
WHERE CurrentStock >0
GROUP BY ProductID
GO
Apologies for the tabulation... I didn't have time to make it pretty!:-)
July 20, 2010 at 8:57 pm
steve.vidal (7/20/2010)
I'm a bit late to the party because I was busy yesterday but I wanted to try that "quirky update" thingo, so I had a crack this morning... just don't tell my boss! 😛Here is what I ended up with:
----------------------------
IF OBJECT_ID('dbo.Stock_TransactionsEx') IS NOT NULL
DROP TABLE [Stock_TransactionsEx]
GO
CREATE TABLE [Stock_TransactionsEx](
[ProductID] [int] NOT NULL,
[QuantityPurchased] [int] NOT NULL,
[PurchaseDays] [int] NOT NULL,
[CurrentStock] [int] NOT NULL,
[TotalStockSold] [int] NOT NULL,
[StockDays] [int] NOT NULL,
)
GO
INSERT INTO [Stock_TransactionsEx]
SELECT
T.ProductID,
T.Quantity As QuantityPurchased,
DATEDIFF(d, T.Purchase_date, GETDATE()) AS PurchaseDays,
D.Current_Stock AS CurrentStock,
(SUM(T.Quantity) OVER(PARTITION BY T.ProductID)) - D.Current_Stock AS TotalStockSold,
0 As StockDays
FROM dbo.Stock_Transactions T INNER JOIN
dbo.Stock_Details D ON T.ProductID = D.ProductID
order by productId, 3 Desc
GO
DECLARE @ProductID INT
DECLARE @RunningBalance INT
DECLARE @OutRunningBalance INT
UPDATE [dbo].[Stock_TransactionsEx]
SET
@RunningBalance = CASE WHEN ProductId = @ProductID
THEN
CASE WHEN @OutRunningBalance > 0
THEN @OutRunningBalance
ELSE 0
END
ELSE TotalStockSold
END,
StockDays = CASE WHEN QuantityPurchased > @RunningBalance
THEN (QuantityPurchased - @RunningBalance)*PurchaseDays
ELSE 0
END,
@OutRunningBalance = @RunningBalance - QuantityPurchased,
@ProductId = ProductID
FROM [dbo].[Stock_TransactionsEx] WITH (TABLOCKX)
OPTION (MAXDOP 1)
GO
SELECT ProductID, SUM(StockDays/CurrentStock) AS AvStockdays
FROM Stock_TransactionsEx
WHERE CurrentStock >0
GROUP BY ProductID
GO
Apologies for the tabulation... I didn't have time to make it pretty!:-)
And, it looks like you followed the rules, as well! 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 20, 2010 at 9:01 pm
gah (7/20/2010)
Extraordinary..........:w00t:Running on SQL 2000...7 year old box with 4gb RAM
100K products
20M transaction lines
sub 1 minute !!!!
beats the hell out of the vendor's app (2½hrs)...but that sits on a different type of RDBMS
Many thanks to Lutz and Jeff Moden (for his "quirky update")...I sincerely appreciate the help and advice guys.
Graham
My pleasure, Graham. Just remember... always follow the rules to a "T" on this one or it WILL bite you. Of course, you can see what happens when you do follow the rules... accuracy plus absolute blinding speed.
@Lutz... well done, as usual. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2010 at 8:32 am
IF OBJECT_ID('dbo.Stock_TransactionsEx') IS NOT NULL
DROP TABLE [Stock_TransactionsEx]
GO
CREATE TABLE [Stock_TransactionsEx](
[ProductID] [int] NOT NULL,
[QuantityPurchased] [int] NOT NULL,
[PurchaseDays] [int] NOT NULL,
[CurrentStock] [int] NOT NULL,
[TotalStockSold] [int] NOT NULL,
[StockDays] [int] NOT NULL,
)
GO
INSERT INTO [Stock_TransactionsEx]
SELECT
T.ProductID,
T.Quantity As QuantityPurchased,
DATEDIFF(d, T.Purchase_date, GETDATE()) AS PurchaseDays,
D.Current_Stock AS CurrentStock,
(SUM(T.Quantity) OVER(PARTITION BY T.ProductID)) - D.Current_Stock AS TotalStockSold,
0 As StockDays
FROM dbo.Stock_Transactions T INNER JOIN
dbo.Stock_Details D ON T.ProductID = D.ProductID
order by productId, 3 Desc
GO
Hi Steve
maybe this is irrelevant, but would appreciate guidance from Jeff if he is around.
Jeff's article states...
"The bottom line is, if you want to do running totals using a "Quirky Update", a Clustered Index with the correct sort order must be present. If you don't have one of the one that exists on a table cannot be changed, then you need to use SELECT/INTO to copy the correct data to a Temp Table and add the correct Clustered Index to that."
Do you require a clustered index on [Stock_TransactionsEx] ?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 21, 2010 at 5:21 pm
gah (7/21/2010)
Hi Stevemaybe this is irrelevant, but would appreciate guidance from Jeff if he is around.
Jeff's article states...
"The bottom line is, if you want to do running totals using a "Quirky Update", a Clustered Index with the correct sort order must be present. If you don't have one of the one that exists on a table cannot be changed, then you need to use SELECT/INTO to copy the correct data to a Temp Table and add the correct Clustered Index to that."
Do you require a clustered index on [Stock_TransactionsEx] ?
You are absolutely right! I was lucky that my test dataset played ball but that's not good enough for real life applications.
July 22, 2010 at 10:10 am
Steve
No probs...as Jeff says you must "obey all the rules"...good luck with your Boss on this one.
fyi...I am still xref'ng against the vendors results to "prove" my results...cant go live until I am sure I am right 🙂
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply