asked
storing parent child table on different databases but same server
i have a server with two hard disks i have two enormous tables with millions of transactions hourly customers table and transactions table was thinking what if i stored customer on database customers and transaction on trans database and placed each db on a drive but on same instance of SQL server will that improve the performance what i'm worried about is the reporting i need to join the two tables when generating reports joining two tables from different databases but on same server same instance i'm using sql server 2008 R2 Enterprise edition i need your opinions and feedback please
Q1) What is your overall objective? Q2) Is the server running anything else as well? (only running one instance of sql / only used for this application / running other applications too) Q3) Do you only have two hard disk, if so how are they used at present ie. where is tempdb, the log files, the os and the data files? Q4) Is this physical or virtual? Q5) What have you done to ascertain that the two tables in question are your primary performance issue on the instance? Q6) Is this an in-house application that's using the db or a third party one?
This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
No need for separate databases! That will just complicate things! Put the tables in different file groups store the file groups on different disks. If reporting is a concern then maybe also look at partitioning your data. [
http://msdn.microsoft.com/en-gb/library/ms189563.aspx][1] [1]:
http://msdn.microsoft.com/en-gb/library/ms189563.aspx
but in my case the business requires me for some branches to install one database only the customers database and for another branch both databases customers and transactions of course i can merge them into one db and remove only non required tables but do you think joins between two databases will affect the reporting ?
EDIT I totally agree with Thomas Rushton, about recovery being a good reason for keeping one database. I also want to mention that if you have a logical relation between customers and transactions, it could (would) be wise to also put constraints on that logical relation, so you don't mess up the consistency between customers and transactions. END EDIT Instead of putting them on two databases, I suggest you setup a new filegroup on a different disk than your current filegroup (if disk I/O is your bottleneck), and move one of the tables to the new filegroup. Adding row level compression will also speed up things a bit (and save you some disk space). You could also consider partitioning your transactions table to speed up some queries. The later is in no way a golden bullet to solve performance problems, but if your queries against the transactions table are normally within a given date-range, you will benefit from having the table partitioned on date. Remember that partitioning is an eneterprise feature. If you are using standard edition, that's not an option. Neither is row level compression an option on standard edition.
Another reason for keeping it in one database is recovery. In the event of a failure, and you do a restore of the system, at least you'll only have one database which will be in a transactionally-consistent state. If your data is stored in two databases, you can be pretty sure that you won't have that consistency.
19 People are following this question.
