Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

If I have two sets of database tables used by a system

  • Tables that collect results of some data processing. Most of the time get a lot of INSERT operations.
  • Tables that are used by the web application to generate pages and reports

For this question, lets assume the sets of tables are mutually exclusive (maybe there is some process that migrates data from the first to the second set periodically).

Will I see a performance difference between the two following setups

  • The two sets of tables are in one DB
  • The two sets of tables are in two separate DBs on the same SQL server
share|improve this question
add comment

3 Answers

up vote 5 down vote accepted

I'd say one single DB. You want the sets of table to be kept in sync in regard to backup/restore state, user access security, availability and so on and so forth. Separate tables also add problems with mirorring and log shipping solutions.

Because you expect such a different I/O access pattern, you should place the tables on different filegroups. Then you can deploy each filegroup on the proper disk for your I/O pattern.

With both sets of tables in a single DB the some contention points are shared, like log writes. But that is not a concern for the case you describe because only the OLTP part of the application cares about the log.

share|improve this answer
    
+1 for the filegroups and single source of security –  squillman Aug 28 '09 at 23:56
add comment

Yes. You want option B, so that the OS is referencing 2 different disk files. One DB can be optimized for writing, the other for reading.

Or you could accomplish the same in a single db, if you split your tables into separate filegroups/separate files.

share|improve this answer
add comment

Will I see a performance difference between the two following setups

* The two sets of tables are in one DB
* The two sets of tables are in two separate DBs on the same SQL

server

No - not unless you separate the databases onto different hard drives (and even that is debatable as to whether it would impact performance given that we have no other info).

So remove "performance" from your mental checklist of what you should consider in this design. That means the question is one of domain - "Should I place them in the same database or different databases" should depend on how related they are.

If I have two sets of database tables used by a system

* Tables that collect results of some data processing. Most of the time

get a lot of INSERT operations. * Tables that are used by the web application to generate pages and reports

For this question, lets assume the sets of tables are mutually exclusive (maybe there is some process that migrates data from the first to the second set periodically).

I don't understand what you mean by "the sets of tables are mutually exclusive". From the description, the tables do seem to be related (and by "related", I mean that they should be grouped together in the same database) since may periodically migrate the data between them. As such, I think that you likely need one database.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.