Sunday, February 19, 2012

Database Design - 3000+ users

We are in the process to develop a new VB6/SQL 2000 application (do
not ask why not .net, our company policy is to use VB6), which will
have over 3000 users to access the database (50GB data) and perform
heavy add/update on one table. Without the privilege to have a
better/faster server, we have to design database tables in a way to
increase the performance (on updates).
My boss wants to partition the data into different databases based on
defferent users groups (for examples, use users' department). Since
the data now on different databases, the updates for different user
groups would be faster. My concern is that these different databases
will hard to maintain. For example, a same query/stored procedure will
either reside on different
databases or coded with logic to go to different databases before
executing a update. It may better to have one database with multiple
(same structure) tables to store data for different user groups, then
use a (partitioned) view to link all tables (with check constraint
based on user's department number). When update, it would go to
different tables (hence reduce the traffic to tables and increase the
performance).
Thank you in advance for your suggestions.
Yang ZhongWhat makes you think since you have several db's the updates will be faster?
All the databases share the same resources and it doesn't sound like they
will be updating the same row anyway.
--
Andrew J. Kelly SQL MVP
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.com...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong|||(a) Do these users only access the data that they Insert/Update/Delete? Or,
can they access the data created by other user groups ~ different database?
(b) "perform heavy add/update on one table. Without the privilege to have a".
What kind (vertical / industry) of application is it ? (Different user
groups all updating a single table)
(c) Since all your databases (based on the different user groups) will
reside in one server - there will be no performance benefit when you use
partitioned views.
BUT, is it posisble to have each user group's database sitting on a
different physical disk with its own controller? If YES, then you will get
some of the performance benfits of a partitioned view.
Cheers!|||Since SQL can do row level locking, I suspect there will not be a lot of
locking issues for updates, (assuming single row updates), on a properly
indexed table where the Primary Key is used in the where clause.
Therefore, there may be no reason to go to all of the trouble you are
talking about.
It is certainly worth doing some testing to see!
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.com...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong

No comments:

Post a Comment