March 21, 2009 at 8:18 am
Background I’m creating an application that can be hosted by a customer on their own site and might be for one user using a SQL 2008 express edition. Through to this system being hosted on the internet and being used as a service for hopefully 10,000’s of users.
1.I’m designing a new database that stores 3 types of vehicles. MPV's, HGV’s and motorbikes. Would you
a.Create 3 separate tables one for MPV's one for HGV's and one for motorbikes to ensure if the system were to grow you could move these tables on to separate discs for scalability and tuning indexes
b.Create a vehicle table that had a mapping to a vehicle type table thus creating a ‘tidier’ database but the table risked becoming quiet large, thus possibly generating performance issues
c.Some other model
2.If you have 100 customers who have signed up to your hosted application that update a database. Would you
a.Have one database, that risked being extremely large, and ensure that each user of the system could only see their data. This filtering of data being achieved through the software / stored procedure.
b.Have multiple databases thus ensuring that users could only see data in their database. This allowing each database to exist on a different server if necessary as such increasing scalability, but also increasing the cost of keeping the databases updated with stored procedure changes etc
c.Some other model
March 21, 2009 at 9:30 am
These look like homework/interview/exam style questions. Are they?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 21, 2009 at 11:01 am
In any event, the answer to both questions is "It Depends"
#1 depends on how often you will use these 3 different vehicle types at the same time, versus how often you will want to use them separately
#2 depends on what you mean by "Customers" and how much independence there is supposed to be between them. If by "Customers" you mean users, then 100 Users is really not a lot and I would tend to keep them together to reduce duplication of overhead.
However, if by "customers" you mean "Client" companies who can each have many users, then I would more seriously consider separating them. There is still a good case for keeping them together with a Shared Schema Multi-Tenant design however.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 21, 2009 at 11:10 am
Real questions for a commercial system I'm developing. Ordinarily I'd create a single table with a type mapping, and go for the multiple database option within a single instance. But to make sure I wasnt missing anthing I thought I open it up to suggestions and hope for comments 🙂
Regards
March 21, 2009 at 11:20 am
RBarryYoung (3/21/2009)
#1 depends on how often you will use these 3 different vehicle types at the same time, versus how often you will want to use them separately
Also how similar they are. If all three have exactly the same attributes, then one table. If 90% of the attributes are different between the different vehicle types, then I'd lean more to different tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 21, 2009 at 12:11 pm
Agreed. If they're 90% the same then one table. If they're 90% different, then separate tables. But in the middle, there's some other choices also, like a generic parent/master table that has the common attributes and then type-specific sub/child tables for the specific attributes.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 21, 2009 at 1:34 pm
Firstly I want to say thank you for all the information you’re giving me, although I’ve read many forums, this is the first time I’ve actually posted and your assistance is most appreciated.
The vehicle attributes are generally going to be the same i.e. registration date, engine capacity mileage etc. This is where the idea of a single table appeals to me. However my concern is that the database could become very focused on this vehicle table, with most queries accessing this table for their primary information. As such it could create a bottle neck in my system. Having separate tables might solve this but it doesn’t feel the right solution.
I envisage the data I’ll need to store is for each company is a maximum of 40,000 vehicle details. I’ll have up to 500 companies with 10 users per company using the system. As such I could have up to 20 million vehicles details being accessed by 5000 users. The predicted load will be that during most of the day demand will be low, however at the start and end of the day there will be a high demand. If most reads are going to be accessing one table on one database then how do I prevent the bottleneck? All ideas gratefully received
Thanks
March 21, 2009 at 8:49 pm
You can partition the table to multiple different physical disks if you need to.
But this is an issue of Database Design, and NOT Data Design. Wherever possible, you should avoid mixing the two.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 21, 2009 at 9:09 pm
I should also point out that strictly speaking, a Table is a logical structure/resource, and not a physical one.
This is important because although a physical resource can bottleneck on read functions, logical resources almost never do. Thus, splitting a table into multiple tables does nothing to address any read bottlenecks. However, splitting it into multiple physical resources (partitions, filegroups, disks, etc.) can help and fortunately SQL Server & Windows have many options to allow managing the use of physical resources separate from the design of the logical resources.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 22, 2009 at 1:41 am
Can you advise how, if I go for the multiple database option I can best keep the structure and sp's in sync? Can this be automated?
Also what do I need to bear in mind if I go for the large single table option, with regards to keeping the indexes and pages in their optimum state?
Thanks
March 22, 2009 at 10:41 am
I was not recommending multiple databases, I was recommending things like Table Partitions, multiple Database Filegroups and SAN/NAS multidisk volumes (Raid 1+0, etc.).
You can use multiple databases, however there are a lot more maintenance and performance issues with that approach. Mutliple databases are usually driven by other issues: the need to separate different types of processing, the need for different apps to have their own databases but still share some of their data. The need for diferent goegraphies to have local databases, etc.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 22, 2009 at 10:55 am
I'm with Barry here. There are other techniques to improve performance.
I see people spin on things like this, and it's rare that it every becomes an issue. I wouldn't look to separate things out into separate databases, unless they are truly separate items. Like separate customers. Separate products, if they are every queried together, wouldn't make sense.
If you have 3 separate types of business, then there might be value here, but not for performance reasons.
March 22, 2009 at 11:02 am
Andy Bod (3/21/2009)
However my concern is that the database could become very focused on this vehicle table, with most queries accessing this table for their primary information. As such it could create a bottle neck in my system.
If the queries are well written and the table has appropriate indexes, it should not be a concern.
Start simple and, if there is an obvious performance problem later, then start considering partition schemes, multiple tables and all those kind of complex tricks. They're really not needed in the vast majority of cases
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2009 at 11:18 am
Andy Bod (3/22/2009)
Can you advise how, if I go for the multiple database option I can best keep the structure and sp's in sync? Can this be automated?
Hmm, I did backup and reread the thread again,... I had forgotten that you have multiple customers. Multiple customers are a legitimate driver for multiple databases, but it does come at a cost, part of which is just what you mentioned: the difficulty of keeping metadata versions in synch.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 22, 2009 at 11:20 am
you should be scripting deployments. No clicking SSMS for changes. If you script them, loop the script through databases.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply