469,320 Members | 2,189 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,320 developers. It's quick & easy.

Performance Question

SQL Server 2000, MSDE

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.com
Jul 20 '05 #1
2 1706
Tim Morrison (sa***@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
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" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1...
Tim Morrison (sa***@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

115 posts views Thread by Mark Shelor | last post: by
4 posts views Thread by Martin | last post: by
13 posts views Thread by bjarne | last post: by
6 posts views Thread by Mike | last post: by
18 posts views Thread by Rune B | last post: by
5 posts views Thread by Varangian | last post: by
30 posts views Thread by galiorenye | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.