problem with temp table losing its order

Question

problem with temp table losing its order

gardenlady

SSCommitted

Points: 1974
More actions
December 1, 2008 at 1:37 pm

#126174

Here is part of a stored procedure that is creating a general ledger with Lawson data:
CREATE TABLE #nisse (RowNum INT IDENTITY(1,1) NOT NULL
,Company int NOT NULL
,Name nvarchar(50)
,Fiscal_year int NOT NULL
,Acct_unit nvarchar(15) NOT NULL
,Account int NOT NULL
,sub_account int NOT NULL
,postingdate nvarchar(10) --datetime NOT NULL
,updatedate nvarchar(10) --datetime NOT NULL
,reference nvarchar(150) NOT NULL
,SystemCode nvarchar(2) NOT NULL
,TotBegDebits decimal(30,2) NOT NULL
,TotBegCredits decimal(30,2) NOT NULL
,TMONTH int NOT NULL
,account_desc nvarchar(150) NOT NULL
,acct_unitname nvarchar(50) NOT NULL
,debits decimal (24,2) NOT NULL
,credits decimal (24,2) NOT NULL
,BegBal MONEY NOT NULL
,RunBal MONEY NOT NULL
,AcctSubBegBal MONEY NOT NULL
,AcctSubEndBal MONEY NOT NULL
,TransDescr nvarchar(150) NOT NULL
,StartDate DATETIME NOT NULL
,EndDate DATETIME NOT NULL
,JE int NOT NULL)
insert into #nisse
(company
,Name
,account
,acct_unit
,sub_account
,fiscal_year
,postingdate
,updatedate
,reference
,SystemCode
,TotBegDebits
,TotBegCredits
,debits
,credits
,BegBal
,RunBal
,AcctSubBegBal
,AcctSubEndBal
,TMONTH
,account_desc
,acct_unitname
,TransDescr
,StartDate
,EndDate
,JE
)
Select company
,Name
,account
,acct_unit
,sub_account
,fiscal_year
,convert(varchar,posting_date,101) as postingdate
,convert(varchar,update_date,101) as Updatedate
,reference
,SystemCode
,TotBegDebits
,TotBegCredits
,debits
,credits
,0
,0
,0
,0
,TMONTH
,account_desc
,acct_unitname
,TransDescr
,StartDate
,EndDate
,JE
FROM @tmp_Details
ALTER TABLE #nisse ADD PRIMARY KEY CLUSTERED (RowNum)
CREATE NONCLUSTERED INDEX IX_TransHist_AccountID_Date
on #nisse (Account, Acct_unit, sub_account,Fiscal_year, TMONTH, postingdate)
--beginning balance
UPDATE #nisse
SET BegBal = TotBegDebits + TotBegCredits
FROM #nisse
--running balance
DECLARE @PrevRunBal2 MONEY
SET @PrevRunBal2 = 0
DECLARE @PrevAcct2 int
SET @PrevAcct2 = 0
DECLARE @PrevSubAcct2 smallint
SET @PrevSubAcct2 = 9999
DECLARE @PrevAcctUnit2 nvarchar(15)
SET @PrevAcctUnit2 = ''
--set the beginning balances for Running Balance field ddr 9/11/08
UPDATE #nisse
SET RunBal = BegBal
FROM #nisse --WITH (INDEX(IX_TransHist_AccountID_Date),TABLOCKX)
--EXEC @sql
UPDATE #nisse
SET @PrevRunBal2 = RunBal = Case
WHEN
Account = @PrevAcct2
and acct_unit = @PrevAcctUnit2
and Sub_Account = @PrevSubAcct2
THEN @PrevRunBal2
+ debits + credits
ELSE BegBal + debits + credits
END,
@PrevAcct2 = account,
@PrevSubAcct2 = sub_account,
@PrevAcctUnit2 = acct_unit
FROM #nisse
------------------------------------------------------
Everything works above this line. They also want a beginning and ending balance based on account and sub-account (accounting unit is not a deciding factor). It seems like it gets out of order and so my next Beginning Balance code, based on different factors, doesn't work.
DECLARE @AcctSubBegBal MONEY
SET @AcctSubBegBal = 0
SET @PrevAcct2 = 0
SET @PrevSubAcct2 = 9999
UPDATE #nisse
SET @AcctSubBegBal = AcctSubBegBal = Case
WHEN
Account <> @PrevAcct2
or Sub_Account <> @PrevSubAcct2
THEN BegBal
ELSE 0
END,
@PrevAcct2 = account
,@PrevSubAcct2 = sub_account
FROM #nisse
This isn't working--it appears the temp table has lost the index ordering. Any thoughts or suggestions?
I left out the other ending balance. I think I can get it if I can get this other Beginning Balance to work. It's like this balance would be the first row in a Crystal report, for each account/sub-account combination, then each accounting unit within each of those combinations would have it's own beginning and running balances (done and working).
It ends like this:
SELECT company,name, account,acct_unit, sub_account
,debits, credits, BegBal, RunBal
,AcctSubBegBal, AcctSubEndBal
, postingdate, Updatedate
,startdate, enddate,fiscal_year, tmonth
,SystemCode, JE
,TransDescr, acct_unitname
,reference, convert(nvarchar(8),Account) as AcctSort
FROM #nisse
ORDER BY Company
, account
,acct_unit
,sub_account
,fiscal_year
,tmonth
,postingdate

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

I'm not sure if I really understood the problem since those update statements looked a little strange, but you may be under the impression that just because you have a clustered index on a table, the data will be returned in that order. Not so. The only way to be sure of a particular return order in any DML statement is to add the 'Order By' clause and specify the order. Without that, the server can choose to return the data in whatever way it considers best.

And then again, I might be wrong ...
David Webb

gardenlady SSCommitted Points: 1974 More actions · Answer 2

I got the Running Balance code off the Internet and it worked well. Until I tried to do it again.

Garadin One Orange Chip Points: 29613 More actions · Answer 3

A couple quick thoughts. (I admittedly skimmed this)

1. You're not using an index hint. While it's not always necessary, it's always good practice. Add WITH (INDEX(0)) to your update statements. (You can replace the 0 with the name of your clustered index)

2. You're not using an anchor. Not using an anchor can lead to unforseen results. Add @RowNum = RowNum to both updates.

Make those mods and let us know if the issue still exists.

For a full article on the running totals method, see: http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Daved is right. There are, however, some things that you can do to "encourage" it to run in Clustered index order. This this:

DECLARE @RowNum int

UPDATE #nisse

SET @AcctSubBegBal = AcctSubBegBal = Case

WHEN

Account <> @PrevAcct2

or Sub_Account <> @PrevSubAcct2

THEN BegBal

ELSE 0

END,

@PrevAcct2 = account

,@PrevSubAcct2 = sub_account

,@RowNum = RowNum --** important

FROM #nisse

OPTION (MAXDOP 1); --** important

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Haven't looked at your code, but a good article on computing running balances is referenced below in my signature block. It is worth a read if you haven't yet.

gardenlady SSCommitted Points: 1974 More actions · Answer 6

I really appreciate all the replies. I figured there had to be a way to specify an index on this, but could not find it in any of my books.

So now I have the following (left new balances off for brevity) and it tells me:

Index 'IX_TransHist_AccountID_Date' on table '#nisse' (specified in the FROM clause) does not exist.

And says its line 1287, which does not have anything to do with indexes. Is my syntax incorrect?

ALTER TABLE #nisse ADD PRIMARY KEY CLUSTERED (RowNum)

CREATE NONCLUSTERED INDEX IX_TransHist_AccountID_Date

on #nisse (Account, Acct_unit, sub_account,Fiscal_year, TMONTH, postingdate)

UPDATE #nisse

SET BegBal = TotBegDebits + TotBegCredits

FROM #nisse

UPDATE #nisse

SET RunBal = BegBal

FROM #nisse WITH (INDEX(IX_TransHist_AccountID_Date))

SET @RowNum = 0

--update Running Balance based on whether it needs to include the Beginning Balance

--or not (if account, sub_account or acct_unit changes) ddr 9/10/08

UPDATE #nisse

SET @PrevRunBal2 = RunBal = Case

WHEN

Account = @PrevAcct2

and acct_unit = @PrevAcctUnit2

and Sub_Account = @PrevSubAcct2

THEN @PrevRunBal2

+ debits + credits

ELSE BegBal + debits + credits

END

,@PrevAcct2 = account

,@PrevSubAcct2 = sub_account

,@PrevAcctUnit2 = acct_unit

,@RowNum = RowNum

FROM #nisse WITH (INDEX(IX_TransHist_AccountID_Date))

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

gardenlady (12/1/2008)
FROM #nisse WITH (INDEX(IX_TransHist_AccountID_Date))

Why did you add this? I do not believe that you can use INDEX hints anywhere but in a SELECT. What you need here is the "OPTION (MAXDOP 1);" that I listed.

That along with the ", @RowNum = RowNum" is what you need to get the UPDATE to process in the order of your Clustered Index. The Clustered Index is the ONLY order that you can reliably coax an UPDATE statement to use, and the "reliability" of that is a matter of considerable debate.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

gardenlady SSCommitted Points: 1974 More actions · Answer 8

Thanks for the replies.

To the person with the link re running totals, thanks much, that's where I originally got this code. The "with tablelockx" (not viewing it right now-spelling?) never did work for me.

I took off the WITH INDEX because I was getting errors. I added the MAXDOP clause. However I think the problem is that I need to change the index between the Running Balance by accounting unit and the two balances that I DON'T want to break on accounting unit but continue to do so. Maybe I can drop the first index and create a second...

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

The correct syntax is WITH (TABLOCKX)... parenthesis included.

Also, Seth pointed out some basic faults with you code like missing an anchor column... go back a take a look at his recommendations.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Garadin One Orange Chip Points: 29613 More actions · Answer 10

Barry: You can, I do it all the time when applying this method.

Gardenlady: Your index specification looks correct... unfortunately you're specifying the WRONG index :cool:. You need to specify the clustered index.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

You guys are right, my bad: WITH (INDEX..) is Ok for Updates.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

gardenlady SSCommitted Points: 1974 More actions · Answer 12

Okay, I'm confused. I have the @RowNum = RowNum in the last code snippet I posted. And that would be the clustered index, but I don't want to use it because it's not in the order that I want. It's just doing a primary key on the row number whereas I need the table in account, sub-account, posting date order, which is a non-clustered index. And I can't make that clustered because I can only have one clustered index.

Currently, this is what I have and the balances still aren't breaking on account--still on acct_unit from index above this:

DROP INDEX #nisse.IX_TransHist_AccountID_Date

CREATE NONCLUSTERED INDEX IX_TransHist_AccountID_Date

on #nisse (Account, sub_account,acct_unit,postingdate)

DECLARE @AcctSubEndBal MONEY

SET @RowNum = 0

SET @AcctSubEndBal = 0

SET @PrevAcct2 = 0

SET @PrevSubAcct2 = 9999

-- ending balances by account/sub-account

UPDATE #nisse

SET @AcctSubEndBal = AcctSubEndBal = Case

WHEN Account = @PrevAcct2

and Sub_Account = @PrevSubAcct2

THEN @AcctSubEndBal

+ debits + credits

ELSE BegBal + debits + credits

END,

@PrevAcct2 = account

,@PrevSubAcct2 = sub_account

,@RowNum = RowNum

FROM #nisse WITH (INDEX(IX_TransHist_AccountID_Date))

OPTION (MAXDOP 1);

Garadin One Orange Chip Points: 29613 More actions · Answer 13

You have to make your clustered index the order that you want the table updated in. Make what you currently have your non-clustered index into your primary key / clustered index. Add in row_num to the end if you have to for uniqueness.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

You can leave the Primary Key the same, but you definitely have to change the Clustered index.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]