Performance Question

[Tim Morrison]

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

[Erland Sommarskog]

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

[Tim Morrison]

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