December 2, 2008 at 9:49 am
Here is how to drop and rebuild the key/indexes making the secondary key the clustered index:
/* To prevent any potential data loss issues, you should review this script in detail before running it.*/
BEGIN TRANSACTION
GO
DROP INDEX IX_TransHist_AccountID_Date ON dbo.nisse
GO
ALTER TABLE dbo.nisse
DROP CONSTRAINT PK__nisse__32CB82C6
GO
ALTER TABLE dbo.nisse ADD CONSTRAINT
PK__nisse__32CB82C6 PRIMARY KEY NONCLUSTERED
(
RowNum
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX IX_TransHist_AccountID_Date ON dbo.nisse
(
Account,
Acct_unit,
sub_account,
Fiscal_year,
TMONTH,
postingdate
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
(of course I got this from SSMS).
And you wouldn't want to do this while uses could be accessing it.
[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]
December 2, 2008 at 10:16 am
This is a change to a report that I created that's in testing--hasn't been released. I really appreciate the info but it's almost "TOO MUCH INFO". 🙂
I'm working with a CREATE TABLE #nisse because multiple users will be running this at the same time for different companies. It's not really a table in the database--it's temporary but can be used concurrently.
So I create the table, populate it then this:
ALTER TABLE #nisse ADD PRIMARY KEY CLUSTERED (Account, sub_account,Acct_unit, postingdate,RowNum)
CREATE NONCLUSTERED INDEX IX_TransHist_AccountID_Date
on #nisse (Account, Acct_unit, sub_account,Fiscal_year, TMONTH, postingdate)
Do my old UPDATES for the balances I need with the nonclustered index order. If I do WITH INDEX statement and execute I get an error that the index doesn't exist. But I haven't dropped it.
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))
OPTION (MAXDOP 1);
Then I do this:
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
OPTION (MAXDOP 1);
and it appears to work. I've checked the math and it works out but I don't know why.
December 2, 2008 at 10:21 am
Don't create the non-clustered index at all. Shouldn't be necessary.
WITH (INDEX(0))
Since the clustered index is the only one, that will grab it correctly. While this may work as it is(it can work without specifying an index at all if the optimizer chooses to use it), you should always specify the clustered index as the hint, or possibly do what Barry is doing(not familiar with his method so can't say for sure) to try to force the clustered index to be used.
December 2, 2008 at 10:34 am
Garadin (12/2/2008)
Don't create the non-clustered index at all. Shouldn't be necessary.WITH (INDEX(0))
Since the clustered index is the only one, that will grab it correctly. While this may work as it is(it can work without specifying an index at all if the optimizer chooses to use it), you should always specify the clustered index as the hint, or possibly do what Barry is doing(not familiar with his method so can't say for sure) to try to force the clustered index to be used.
Yes, I agree with Garadin: add the WITH (INDEX(0)) to the UPDATE.
The MAXDOP 1 is just a safety to prevent parallelism from breaking the trick.
[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]
December 2, 2008 at 10:53 am
A picture's worth a thousand words--I've attached one. This is using a primary key only (account/acct_unit/sub_account...).
The BegBal and RunBal were the first ones I did and seem to be okay--nothing I've tried in the past few days has broken them. They break down by account/acct_unit/sub-account.
The new balances need to break by account and sub-account: AcctSubBegBal and AcctSubEndBal. If you look at account 76299, everything is okay until acct_unit 1480500 ends. The following and subsequent lines do not have correct AcctSubBegBal and AcctSubEndBal. This is why I feel I need a new index that goes account/sub_account...
December 2, 2008 at 11:07 am
To make sure we're all on the same page, please post the exact code that generated that screenshot. (The table/index definitions as they currently stand, and the updates targetting acctsubbegbal and acctsubendbal.)
December 2, 2008 at 11:19 am
I've obviously been trying things since I created that data, but I think this is how the code was at that time.
ALTER TABLE #nisse ADD PRIMARY KEY CLUSTERED (Account, sub_account,Acct_unit, postingdate,RowNum)
some processing to get rid of zero activity/balance accounts if they choose
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
DECLARE @PrevRunBal2 MONEY
DECLARE @RowNum int
SET @PrevRunBal2 = 0
SET @RowNum = 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(0)) it errors out with index clause if not a zero
SET @RowNum = 0
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(0))
OPTION (MAXDOP 1);
--CREATE NONCLUSTERED INDEX IX_TransHist_AccountID_Date2
-- on #nisse (Account, sub_account,acct_unit,postingdate)
DECLARE @AcctSubBegBal MONEY
SET @RowNum = 0
SET @AcctSubBegBal = 0
SET @PrevAcct2 = 0
SET @PrevSubAcct2 = 9999
--setting balance, beginning, for account/sub-account changes
--it doesn't change for accounting unit and in fact should add up
UPDATE #nisse
SET @AcctSubBegBal = AcctSubBegBal = Case
WHEN Account = @PrevAcct2
and Sub_Account = @PrevSubAcct2
THEN 0
ELSE BegBal
END,
@PrevAcct2 = account
,@PrevSubAcct2 = sub_account
,@RowNum = RowNum
FROM #nisse --WITH(0))
OPTION (MAXDOP 1);
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(0))
OPTION (MAXDOP 1);
December 2, 2008 at 11:58 am
Ok. As we've said, you cannot be sure that your index will be used unless you give it an index hint (Some people will argue you can't even be sure then, but that's besides the point). You've got it commented out everywhere. One of them is also commented out and wrong / could have been causing some errors, but we'll ignore that for now.
Ignoring the fact that you probably don't really need to use running totals for acctsubbegbal at all, try to rewrite this section:
--setting balance, beginning, for account/sub-account changes
--it doesn't change for accounting unit and in fact should add up
UPDATE #nisse
SET @AcctSubBegBal = AcctSubBegBal = Case
WHEN Account = @PrevAcct2
and Sub_Account = @PrevSubAcct2
THEN 0
ELSE BegBal
END,
@PrevAcct2 = account
,@PrevSubAcct2 = sub_account
,@RowNum = RowNum
FROM #nisse --WITH(0))
OPTION (MAXDOP 1);
Like this:
--setting balance, beginning, for account/sub-account changes
--it doesn't change for accounting unit and in fact should add up
UPDATE #nisse
SET AcctSubBegBal = CASE WHEN Account = @PrevAcct2 AND Sub_Account = @PrevSubAcct2
THEN 0
ELSE BegBal
END,
@PrevAcct2 = account,
@PrevSubAcct2 = sub_account,
@RowNum = RowNum
FROM #nisse WITH (INDEX(0))
OPTION (MAXDOP 1);
And let us know how that piece goes.
December 2, 2008 at 12:34 pm
I'm sorry. I tried using the primary index only and WITH (INDEX(0)) (see numbers in the screen shot).
The reason it looks commented out is that that didn't work, I tried going back to a clustered index WITH(INDEX(IX_TransHist_AccountID_Date2)) and that doesn't work. When I exec the proc I get an error. When I sent you my code I forgot to remove the "--". But it doesn't work with just a primary index and I don't know how to get it to refer to a secondary, clustered index.
December 2, 2008 at 12:38 pm
If I try to put a name in WITH (INDEX(IX_TransHist_AccountID_Date))
Msg 308, Level 16, State 1, Procedure Trans_History3_Crystal_Account_pr, Line 1289
Index 'IX_TransHist_AccountID_Date' on table '#nisse' (specified in the FROM clause) does not exist.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply