Performance table vs databases

What is the best thing to do to get the best performance ? Multiple tables
in 1 database or multiple databases containing less tables ?typically, database contain numerous tables.
not sure if your question is clear.....
Greg Jackson
PDX, Oregon|||Hi,
Keeping tables in mutiple database or viceversa will not give you
performance improvements.
Tips for Performance.
1. If you have mutiple disk controllers, Create file groups and place tge
tables and indexes in seperate file group. this will reduce I/O
2. Allot more physicval memory for SQL server
3. Allocate dedicated server for SQL server
4. Design the database in proper and structure manner
5. Create the necessory indexes
6. Optimize your SQL's (Select statements)
Make use of Query optimizer, Performance monitor, Profiler and Index tuning
wizard to tune your application and database.
Thanks
Hari
MCDBA
"Cris" <anonymous@.discussions.microsoft.com> wrote in message
news:D3F1AA37-12C0-4F1F-AEA2-9D4FB10AAB90@.microsoft.com...
> What is the best thing to do to get the best performance ? Multiple
tables in 1 database or multiple databases containing less tables ?|||I don't think performance is the question. There are several techniques to
handle large databases ... but you ask your self if you logically need/shoul
d create two+ databases. You wouldn't typically combine your Reporting with
Orders, right ?
What's the motivation behind your question ?

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.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