|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
performance of single database vs multiple databasesI 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 "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. 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 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! 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. Show quote "Francis Reed" <FrancisR***@discussions.microsoft.com> wrote in message news:EB6ECB67-325F-4BDF-BE2E-79822AEA4E3F@microsoft.com... > 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 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 Show quote "Marina Levit [MVP]" wrote: > 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. > > "Francis Reed" <FrancisR***@discussions.microsoft.com> wrote in message > news:EB6ECB67-325F-4BDF-BE2E-79822AEA4E3F@microsoft.com... > > 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 > > > 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. Show quote "Ben Lam" <Ben***@discussions.microsoft.com> wrote in message news:F1EC5C81-AC2C-40AB-AF4A-9E793F03962F@microsoft.com... > 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]" wrote: > >> 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. >> >> "Francis Reed" <FrancisR***@discussions.microsoft.com> wrote in message >> news:EB6ECB67-325F-4BDF-BE2E-79822AEA4E3F@microsoft.com... >> > 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 >> >> >> 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[i].Name; As opposed to writing and casting the object to a string string question = Courses.Tables["Quizzes"].Rows[i]["Name"].ToString(); So, is there a way to achieve this kind of result without using a wizard? Francis Show quote "Marina Levit [MVP]" wrote: > 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. 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. Show quote "Francis Reed" <FrancisR***@discussions.microsoft.com> wrote in message news:3DE63730-8DC2-4B68-9472-F65A046EEFCB@microsoft.com... > 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[i].Name; > > As opposed to writing and casting the object to a string > > string question = Courses.Tables["Quizzes"].Rows[i]["Name"].ToString(); > > So, is there a way to achieve this kind of result without using a wizard? > > Francis > > "Marina Levit [MVP]" wrote: > >> 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. "Marina Levit [MVP]" wrote: What about concurrent user connections? is there any benefit to be had from >Databases are design to be able to handle hundreds of thousands and even > millions of rows of data. 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 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? -- Show quote____________________________________ 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. __________________________________ "Ned Schwartz" <Ned Schwa***@discussions.microsoft.com> wrote in message news:B30E57B6-950B-4DB1-AB31-7371D4346472@microsoft.com... > "Marina Levit [MVP]" wrote: > >>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 > 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 Show quote "William (Bill) Vaughn" wrote: > 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. > __________________________________ > > "Ned Schwartz" <Ned Schwa***@discussions.microsoft.com> wrote in message news:B30E57B6-950B-4DB1-AB31-7371D4346472@microsoft.com... > > "Marina Levit [MVP]" wrote: > > > >>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 > > 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 Show quote "Francis Reed" wrote: > 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  |
|||||||||||||||||||||||
Other interesting topics