Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
maximus_vj
Yak Posting Veteran
88 Posts |
 Posted - 2006-05-17 : 11:29:41
|
| Hello,I have an ASP.Net Web Application using SQL Server 2000 database. It is having some performance problems. One of the problems is that there are a few tables that are huge. One of them has 25 million rows and it is a heavily used table. We are getting timeouts in the application because of locking issues on these tables.To solve this issue we are exploring two solutions. Can you guys tell us which approach is better in our case?Approach 1: Use Horizontal Partitioning on the large tablesUse Partitioned views to split the large tables horizontally. I came to know that there are a few limitations with horizontal portioned views in SQL Server 2000. (Refer to: http://sqlteam.com/forums/topic.asp?TOPIC_ID=49629 )Approach 2: Create different databases for a group of clients.We will split the clients into a few groups and for each group we will create the same exact database. So all the info related to these clients will be in one database. We will have another database which will have information as to which database the application has to go to depending on the client.Lets say, if we have 100 Clients. Now for Clients 1 to 50 we will create a database called dbApp1 and for clients 51 to 100 we will create another database called dbApp2. And we will create a database called dbRoute (which will route to the appropriate database). This dbRoute will have the client’s authentication info and also information as to which database this client has to go. So when a client tries to log into the system the ASP.Net application will authenticate the user against the dbRoute database and it determines which database to use and at this point we will create the connection string and we will store this in the application’s session so the next time the application has to connect to the database it goes to the correct database.Which approach is better?Thanksmaximus_vj |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-17 : 11:46:42
|
You might like to check the requirements for Horizontal Partitions:http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Requirements%20for%20Horizontal%20Partitionsand just get comfortable that there are no Gotchas from your perspective.Personally I would be looking for a scalable low maintenance solution. Something that avoided me having to make a new table every month, and changing the VIEW, to encompass next months data - although in fairness I presume I could create 12 tables at a time to accommodate the next year's month-by-month data.Kristen |
 |
|
|
maximus_vj
Yak Posting Veteran
88 Posts |
Posted - 2006-05-17 : 17:11:26
|
| Hi Kristen,Thanks for your reply. What do you think about the second approach? Is it a good scalable solution?maximus_vj |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-05-17 : 21:22:31
|
| Approach #2 will probably scale pretty well, but maintenence will be a bear. Also, you may develop a "hot spot" of sorts. Are all 50 customers equally as "large"? I doubt it.What you'd need to do is somehow assign a "weight" to a customer. When you are deciding where to put a customer, do a query to find the server with the least "customer weight" and put the new customer there. So, you may wind up with 40 "heavier" customers on one system and 60 "lighter" customer's on the other. Keeping everything in-sync between the main "login" database and the N number of child databases could prove to be challenging and a total mess to support though.At this point, it may be wise to look at the design of this 25 million row table. Do you really need all of those rows in the table? How are you querying that table? Are there better indexes? Instead of going with some exotic solutions (horizontal partitioning the data or databases) maybe some simple database design and index optimazation would solve your problems. Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2006-05-17 : 22:21:46
|
| what kind of activity are you performing? If it is select then you might want to use the NOLOCK hint or set to a lower isolation level.May the Almighty God bless us all! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-18 : 01:10:46
|
| "maintenence will be a bear"That was what I though when I saw Option #2.(I meant "maintenance" rather than "scalable" in my original post)Kristen |
|
|
|
|
|
|
|
|
Performance Problems - Huge Tables