performance of single database vs multiple databases

[Guest]

Hi

I am currently working on a portal for an online university. The portal
allows students, teachers, and administrators to access information, and
perform certain tasks based on roles. We are currently developping tools for
managing quizzes, assignments, news. What I realize if we have many courses,
and if each course has it's own database, there are tables which are being
repeated.

Here is an example of two courses databases each with the necessary tables
to run a quiz. All tables except the Courses table use foreign keys to relate
to the parent table.

Course A url -> "http://myservername/courseA"
dbo.Courses (Id, Name, Description, Language, Semester)
dbo.Quizzes(Id, Name, Description, CourseId)
dbo.Questions(Id, Name, Question, Choices, QuizId)

Course B url -> "http://myservername/courseB"
dbo.Courses (Id, Name, Description, Language, Semester)
dbo.Quizzes(Id, Name, Description, CourseId)
dbo.Questions(Id, Name, Question, Choices, QuizId)

So as you can see, Course A and Course B have both the same three tables
(Courses, Quizzes, Questions). Of course some tools will need to talk to both
course databases to generate reports, calculates grades, etc. Now is it
better to use a single huge database with only the three tables (Courses,
Quizzes, Questions), or is it better to keep them seperate? If so then why?
Is performance better with multiple databases, or with a single database?
Also I'm programming in asp.net 2.0 and would like to use typed datasets, and
from what I found typed dataset can only use a single connection string. Is
there a way around that? Also what about caching?

If would appreciate any feedback or advice.
Francis

 

[Jerry H.]

"from what I found typed dataset can only use a single connection
string..." that doesn't make much sense, but then again I work main
with Windows .NET apps - however, I can't image ASP.NET would be so
different Datasets are simply XML structures that can hold data - how
it gets there, or how many different connections are used to load it
make no difference.

I'd guess that you are using Adapters to load the various tables in
your typed dataset. Within an adapter are (up to) four Command objects
(SELECT, INSERT, DELETE, UPDATE). Each command object uses a
connection object, and if you built your adapter using the Adapter
configuration wizard, then it's very likely that each Command object
uses the same connection object.

So, if you want to change which database you use, simply update the
Connection object's Connectionstring property. To make life easier,
..NET2.0 now has a SqlConnectionStringBuilder (something like that),
which breaks down the various parameters of a SQL connection string
into properties. I use this in a new app that connects to multiple
databases in a MS Great Plains system - a real time saver.

I can't honestly answer your other question: single huge DB, or
smaller, multiple DBs. If you are using MSDE or SQL EXpress, then
undoubtedly multiple DBs would benefit you, because those versions of
SQL have limits on the filesize of a single DB. Beyond that, this woud
be a better question on the SQL newsgroups.

 

[Guest]

Interesting. Thanks for the info Jerry. I'll definetly post on the sql
newsgroup. By any chance would you have the Url for that newsgroups? Also, I
tried in the past to experiment with updating the connection string property
of a Typed DataSet using the following sample code.

namespace mcQuizDSTableAdapters
{
public partial class ec_upload_submissionsTableAdapter :
upload_submissionsTableAdapter
{
public ec_upload_submissionsTableAdapter()
{
try
{
string courseName =
HttpContext.Current.Session["courseName"].ToString();
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString =
ConfigurationManager.ConnectionStrings[courseName].ConnectionString;
this.Connection = myConnection;
}

catch (Exception e)
{
throw e;
}
}
}
}

Essentially I feed ec_upload_submissionsTableAdapter to my ObjectDataSource.
When EnableCaching is false on my ObjectDataSource, no problem, everything
works fine, but when it's true, if I feed the course name dynamically, it
always returns the first course entered. Which makes sense because it's taken
from the cache instead. Furthermore, I really do need caching or else our
site is going to feel a serious performance hit. Would you have any
suggestions?

Francis

 

[Jerry H.]

Within Usenet, head to "microsoft.public.sqlserver", and there you'll
find about 25 different groups that deal with SQL stuff. Myself I
access groups via groups.google.com, which has a simple search function
available.

I'm sorry, I can't help much more beyond that...I don't do much ASP, as
I've never had the chance. However, I'll refer you to another group:
"microsoft.public.dotnet.framework.aspnet " which I'm sure could help
you out.

Good luck!

 

[Marina Levit [MVP]]

Databases are design to be able to handle hundreds of thousands and even
millions of rows of data. If you have the right indexes on your tables you
should be fine - I doubt for a university you would even come close to have
amounts of data that even come close to this. If you are using SQL Server,
Oracle, etc, I wouldn't even give it a second thought. And if you are not
using something of that caliber, you should be able to talk the university
into buying it - this isn't an unreasonable expense. This kind of situation
is exactly what databases were designed for.

If you have separate databases you are asking for a maintenance nightmare.
Every time you have a schema change you have to apply it to hundreds of
databases? Every time you need to run a report, you need to join data from
hundreds of databases?

Also, I would ditch the typed data sets. You will learn much more writing
code yourself, and end up with much better code without having the black box
magic of having a ton of code generated for you.

 

[Guest]

Francis,

I highly encourage you to take a database design class and practice writing
code that interacts with databases that have one-to-many and many-to-many
relationships before undertaking this project.

Kerry Moorman

 

[Guest]

Marina,

What else would you use instead of ADO.net and datatables, datasets etc...
to access databases in .net? I agree there are performance issues but I
didn't know any other way of doing it, so i'd be very interested in finding
alternatives to ado.net

thanks in advance

 

[Marina Levit [MVP]]

Alternatives? Sorry, I don't think I follow. ADO.NET is great, and so are
datatables, datasets, etc.

I am personally against using *typed datasets*, which are the things you can
visual studio generate for you, along with a ton of ado.net code. Not so
much for performance issues, but for maintainence and having the code
magically written somewhere behind the scenes. I think using the wizards is
fine for starting out and learning, but I wouldn't use them in production
software.

 

[Guest]

Hi Marina

Thank you for the advice Marina. It's not a question of learning database
design here, because I have taken many classes on that subject in the past. I
wanted to ask if there is a way to create a Typed DataSet myself in C#
without using a wizard. Because it's very handy to have dot syntax when you
write your datasets, also the strick clr typing is great. For example, the
Name field in my Quizzes table returns a string because it is part of a typed
dataset.

string question = Courses.Quizzes.Name;

As opposed to writing and casting the object to a string

string question = Courses.Tables["Quizzes"].Rows["Name"].ToString();

So, is there a way to achieve this kind of result without using a wizard?

Francis

 

[Marina Levit [MVP]]

I understand the benefits of using typed datasets, but I think the
disadvantages outweigh the advantages by far. It's a personal choice though,
I was just giving my opinion.

I suppose you could do all the work the wizard does manually, but I imagine
that would be quite an effort. If you are attached to the idea of using
typed datasets, you may as well take advantage of what visual studio has.

Hi Marina

Thank you for the advice Marina. It's not a question of learning database
design here, because I have taken many classes on that subject in the
past. I
wanted to ask if there is a way to create a Typed DataSet myself in C#
without using a wizard. Because it's very handy to have dot syntax when
you
write your datasets, also the strick clr typing is great. For example, the
Name field in my Quizzes table returns a string because it is part of a
typed
dataset.

string question = Courses.Quizzes.Name;

As opposed to writing and casting the object to a string

string question = Courses.Tables["Quizzes"].Rows["Name"].ToString();

So, is there a way to achieve this kind of result without using a wizard?

Francis

Also, I would ditch the typed data sets. You will learn much more
writing
code yourself, and end up with much better code without having the black
box
magic of having a ton of code generated for you.

 

[Guest]

Databases are design to be able to handle hundreds of thousands and even
millions of rows of data.

What about concurrent user connections? is there any benefit to be had from
splitting the data amongst many dbs to reduce stress from connections? Or are
100 connections to an SQL server instance with 1 db the same as 10
connections to an SQL server instance with 10 dbs? I am curious about this as
user load is perhaps a greater concern then the number of rows for our
application.

Thanks!

ned

 

[William \(Bill\) Vaughn]

We've seen SQL Server handle thousands of users. Of course any configuration depends a lot on what SS (or any DBMS) is being asked to do. Other factors include (but are not limited to):
a.. Development complexity
b.. Administration expense
c.. Complexity of query
d.. Volume of rows moved to client
e.. Network performance and loading
and a dozen dozen other factors. Creating multiple databases to share the load tends to overlook economies of scale you get when common procedures or data pages are cached.
Sure, there are configurations where the same database is replicated across a dozen servers. Clustering and server farms are a good way to permit lots of users at the data.
How many users are you expecting? Is this an outward-facing web site or a client/server rig on a corporate LAN?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Guest]

Hi, thanks for the answer - definitely gives me something to chew on.

We are an "outward facing" web site with about 4000 daily users currently
and a reasonable projection of about 2000 new users a year. We have an
existing system that has built up awkwardly in an ad hoc fashion over the
last few years and are now in the early planning stages of a re-design to
move to a more robust, scalable and modular design.

Honestly, we are still modeling the data and so are only starting to think
about how and if the data should be structured in different databases.

Thanks again for the input!

ned