I have a table containing simple financial transactions. The 3 primary fields are:
TranSysID INT(PK)
TranDate DATETIME
TranAmount DECIMAL
TranAmount contains both positive (Credit) and negative (Debit) values.
I need to perform a query that returns the beginning balance and ending balance for a particular day.
BegBal = SELECT SUM(TranAmount) WHERE MONTH(TranDate) < 2 AND DAY(TranDate) < 1 AND YEAR(TranDate) < 2004
EndBal = SELECT SUM(TranAmount) WHERE MONTH(TranDate) < 2 AND DAY(TranDate) < 2 AND YEAR(TranDate) < 2004
SHOULD give me the beggining and ending balance for Feb 1, 2004.
My question is what indexes should I create to give me the best possible performance? Right now I have an index on the TranDate field. I do not on the TranAmount field.
TIA
--
Tim Morrison
------------------------
Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.
http://www.vehiclewebstudio.comTim Morrison (sales@.kjmsoftware.com) writes:
> I have a table containing simple financial transactions. The 3 primary
> fields are:
> TranSysID INT(PK)
> TranDate DATETIME
> TranAmount DECIMAL
> TranAmount contains both positive (Credit) and negative (Debit) values.
> I need to perform a query that returns the beginning balance and ending
> balance for a particular day.
> BegBal = SELECT SUM(TranAmount) WHERE MONTH(TranDate) < 2 AND
> DAY(TranDate) < 1 AND YEAR(TranDate) < 2004
> EndBal = SELECT SUM(TranAmount) WHERE MONTH(TranDate) < 2 AND
> DAY(TranDate) < 2 AND YEAR(TranDate) < 2004
> SHOULD give me the beggining and ending balance for Feb 1, 2004.
> My question is what indexes should I create to give me the best possible
> performance? Right now I have an index on the TranDate field. I do not
> on the TranAmount field.
And that index on TranDate is not likely to be used, the way you have
written the query. This is because the column figures in expressions,
SQL Server cannot seek the index. I can possibly scan.
So you should write the query as:
SELECT @.date = '20020204'
SELECT BegBal = SUM(CASE WHEN TranDate < dateadd(DAY, -1, @.date)
THEN TranAmount
ELSE 0
END),
EndBal = SUM(Tranamount)
FROM tbl
WHERE TranDate < @.date
And you should have a non-clustered index on (TranDate, TranAmount)
The reason that for the somewhat complicated expression on BegBal, is
that I would expect most queries to be on recent dates, so in fact,
you will have to traverse most rows. Better then to only do it once.
Since you are traversing allmost all rows, you are in fact scanning the
data. But, by having both the date and the amount in the index, SQL Server
does not have to read the data pages, but only the narrower non-
clustered index. If there are more columns than you are showing, for
instance an account number, you need to add that column to the index
as well.
In the end you may find that you get better performance, by having this
data computed in advance. This requires more work to maintain the data.
The system I work is about financial transactions (securities trading),
and we have tables with all balances pre-computed.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you. It may be possible that I am worrying for nothing. I expect about
10-15 rows added per day. It may be 3 years before I start to notice a
slowdown...
Tim
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns947A6DF7DA17Yazorman@.127.0.0.1...
> Tim Morrison (sales@.kjmsoftware.com) writes:
> > I have a table containing simple financial transactions. The 3 primary
> > fields are:
> > TranSysID INT(PK)
> > TranDate DATETIME
> > TranAmount DECIMAL
> > TranAmount contains both positive (Credit) and negative (Debit) values.
> > I need to perform a query that returns the beginning balance and ending
> > balance for a particular day.
> > BegBal = SELECT SUM(TranAmount) WHERE MONTH(TranDate) < 2 AND
> > DAY(TranDate) < 1 AND YEAR(TranDate) < 2004
> > EndBal = SELECT SUM(TranAmount) WHERE MONTH(TranDate) < 2 AND
> > DAY(TranDate) < 2 AND YEAR(TranDate) < 2004
> > SHOULD give me the beggining and ending balance for Feb 1, 2004.
> > My question is what indexes should I create to give me the best possible
> > performance? Right now I have an index on the TranDate field. I do not
> > on the TranAmount field.
> And that index on TranDate is not likely to be used, the way you have
> written the query. This is because the column figures in expressions,
> SQL Server cannot seek the index. I can possibly scan.
> So you should write the query as:
> SELECT @.date = '20020204'
> SELECT BegBal = SUM(CASE WHEN TranDate < dateadd(DAY, -1, @.date)
> THEN TranAmount
> ELSE 0
> END),
> EndBal = SUM(Tranamount)
> FROM tbl
> WHERE TranDate < @.date
> And you should have a non-clustered index on (TranDate, TranAmount)
> The reason that for the somewhat complicated expression on BegBal, is
> that I would expect most queries to be on recent dates, so in fact,
> you will have to traverse most rows. Better then to only do it once.
> Since you are traversing allmost all rows, you are in fact scanning the
> data. But, by having both the date and the amount in the index, SQL Server
> does not have to read the data pages, but only the narrower non-
> clustered index. If there are more columns than you are showing, for
> instance an account number, you need to add that column to the index
> as well.
> In the end you may find that you get better performance, by having this
> data computed in advance. This requires more work to maintain the data.
> The system I work is about financial transactions (securities trading),
> and we have tables with all balances pre-computed.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
