October 20, 2007 at 3:22 pm
First of all... I'm not going to solve all your problems with all your temp tables, etc, etc... You've already done most of that. My purpose here is just to show you how to make a super fast "Grouped Running Total" based on when your Conta and u_SubConta columns change value. I'm only going to take care of that...
Adam's method, which uses <=, is known as a "Triangular Join" and has some pretty serious negative affects on performance when your rowcounts start to creep up... Even a cursor will be faster than that method. Here's a link for more information on "Triangular Joins" and how bad they can be (you'll need to find my message in that link)...
http://www.sqlservercentral.com/Forums/Topic359124-338-1.aspx#bm360151
Ok... I always prove my code with test data... lots of it... here's how to make lots of random test data to look kind of like your "ML" table... like I said, I'm not solving all of your problems so I didn't make all the same columns you have... just enough to demo the code we'll make later... Make sure you read the comments in all code I post!
--=======================================================================================
-- Create a test table with LOTS of data to simulate what you have in the "ML" table
-- Table contains a million transactions (rows), 1 thousand customers (Conta),
-- 10 accounts for each customer (u_subConta), Debit amounts, Credit amounts, and
-- a DateTime for each transaction that falls between 01/01/2000 00:00:00.000 and
-- 12/31/2009 23:59:59.997 (ten years worth of transactions).
-- THIS IS NOT PART OF THE SOLUTION!!! IT'S JUST TO SIMULATE YOUR "ML" TABLE SO
-- I HAVE SOMETHING TO TEST WITH AND DEMO HOW FAST YOU CAN MAKE A "GROUPED RUNNING
-- TOTAL THE WAY YOU WANT!!!
--=======================================================================================
--===== Create and populate the table on the fly
IF OBJECT_ID('TempDB..#ML','U') IS NOT NULL
DROP TABLE #ML
SELECT TOP 1000000
TransNum = IDENTITY(INT,1,1),
Conta = CAST(RAND(CAST(NEWID() AS VARBINARY))*1000+21100 AS INT),
u_SubConta = CAST(RAND(CAST(NEWID() AS VARBINARY))*10+50000 AS INT),
Debit = CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS DECIMAL(9,2)),
Credit = CAST(RAND(CAST(NEWID() AS VARBINARY))*10 AS DECIMAL(9,2)),
TransDate = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME)
INTO #ML
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
ALTER TABLE #ML
ADD PRIMARY KEY CLUSTERED (TransNum)
Now, the next task is to get the data into a temp table so we can work on it... forget the cursor, it will be much too slow... we'll just use a temp table... note that I add a RowNum and RunningBalance column to the temp table. Based on your previous comments, I'm assuming that you want running balances on all 1 million transactions with the balance "breaks" in all the right spots... including the copying of the data from the source table to the #Running Balance table, this whole thing only takes 16 seconds... read the comments and the names of the variables... they explain it all
--===== Create and populate a running total table for all 1 million transactions
-- (Does balance "breaks" when Conta or u_SubConta changes)
-- Only takes about 15 seconds including the PK index
IF OBJECT_ID('TempDB..#RunningTotal','U') IS NOT NULL
DROP TABLE #RunningTotal
SELECT IDENTITY(INT,1,1) AS RowNum,
Conta,
u_SubConta,
TransDate,
Debit,
Credit,
CAST(0 AS DECIMAL(15,2)) AS RunningTotal
INTO #RunningTotal
FROM #ML
-- WHERE Conta = 21100 --Use something like this for just one customer
ORDER BY Conta,u_SubConta,TransDate
--===== Create a clustered index to keep this from being a heap
-- and to make the running balance method possible.
ALTER TABLE #RunningTotal
ADD PRIMARY KEY CLUSTERED (RowNum)
--===== Declare some local variables.
DECLARE @PrevConta INT --Keeps track of Conta from previous row
DECLARE @Prevu_SubConta INT --Keeps track of u_SubContra from previous row
DECLARE @RunBalance DECIMAL(15,2) --Keeps track of RunningTotal from previous row
SET @PrevConta = -1 --Some number that can't be a cust id
SET @Prevu_SubConta = -1 --Some number that can't be an account id
--===== Create the grouped running balances
-- (This is SO fast, you might not think it ran!!)
-- (Takes about 8 seconds)
UPDATE #RunningTotal
SET @RunBalance = RunningTotal = CASE WHEN Conta = @PrevConta
AND u_SubConta = @Prevu_SubConta
THEN @RunBalance + Debit - Credit
ELSE Debit - Credit
END,
@PrevConta = Conta,
@Prevu_SubConta = u_SubConta
FROM #RunningTotal WITH (TABLOCKX)
--===== Show the first 1000 rows in the correct order
-- (Takes about 1 second)
SELECT TOP 1000 * FROM #RunningTotal
ORDER BY Conta, u_SubConta, TransDate
Think about it... Grouped running total (balance resets when Conta or u_SubContra changes) from a 1000 customers, each with 10 accounts, for a million rows... in about 24-25 seconds... Try that with a cursor 😉
Of course, like I said, this is not a complete solution... you need to take the method I used in the code above and transcribe it into your real code.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2007 at 7:24 am
Jeff your method is equisite as usual 😉
Luissantos, if you just want to get it working with the cursor you can make the following modifications.
code below
--------------
DECLARE RunningTotalCursor
CURSOR LOCAL FAST_FORWARD FOR
SELECT conta, u_subconta, data,
dinome, adoc, dilno, edeb, ecre
FROM ml
--ADD WHERE AND ORDER BY CLAUSE HERE IF NEEDED
ORDER BY conta, u_subconta
OPEN RunningTotalCursor
--I am not sure of the datatypes you need, so change accordingly
DECLARE @conta varchar(20)
DECLARE @u_subconta varchar(20)
DECLARE @data varchar(20)
DECLARE @dinome varchar(20)
DECLARE @adoc varchar(20)
DECLARE @dilno varchar(20)
DECLARE @edeb smallmoney
DECLARE @ecre smallmoney
DECLARE @pre_subconta varchar(20)
--set the presubconta to an invalid value so that it can be skipped first go around
SET @pre_subconta = '-1'
--set starting value of the running total
DECLARE @RunningTotal smallmoney
SET @RunningTotal = 0
--hold our results data
DECLARE @Results TABLE
(
conta varchar(20),
u_subconta varchar(20),
data varchar(20),
dinome varchar(20),
adoc varchar(20),
dilno varchar(20),
edeb smallmoney,
ecre smallmoney,
RunningTotal smallmoney
)
FETCH NEXT FROM RunningTotalCursor
INTO @conta, @u_subconta, @data, @dinome, @adoc, @dilno, @edeb, @ecre
WHILE @@FETCH_STATUS = 0
BEGIN
-- if it is the first u_subconta dont worry about checking this
-- otherwise compare the previous subconta with the current to see if
-- we need to start from scratch.
IF @pre_subconta <> @u_subconta and @pre_subconta <> '-1'
BEGIN
SET @RunningTotal = 0
END
--If there is a credit amount use it; otherwise, use debit
IF @ecre > 0
BEGIN
SET @RunningTotal = @RunningTotal + @ecre
--set current subconta to prevsubconta
SET @pre_subconta = @u_subconta
END
ELSE
BEGIN
SET @RunningTotal = @RunningTotal - @edeb
--set current subconta to prevsubconta
SET @pre_subconta = @u_subconta
END
--insert values into the results table
INSERT @Results
VALUES (@conta, @u_subconta, @data, @dinome, @adoc, @dilno, @edeb, @ecre, @RunningTotal)
FETCH NEXT FROM RunningTotalCursor
INTO @conta, @u_subconta, @data, @dinome, @adoc, @dilno, @edeb, @ecre
END
CLOSE RunningTotalCursor
DEALLOCATE RunningTotalCursor
--select all records from the results table variable
SELECT *
FROM @Results
ORDER BY conta,u_subconta
October 24, 2007 at 7:22 pm
Jeff your method is equisite as usual
Thank for the nice compliment, Adam...:blush:
Hopefully, Luissantos doesn't have a million rows...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2007 at 2:29 am
Hello comunity
Thanks for all yours replies and interest. i will try all your suggestions
Many thanks
Luis Santos
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply