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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-18 : 10:08:14
|
| Chris writes "I am involved in building an application using SQL server 2000 that has rather large data storage requirements. The application needs to store data on around 500 different sports events which will change from day to day. There are two main bits of information that must be stored for each event. One is a list of all of the players in the sports event (probably averaging around ten per event) and the more difficult is users ratings for any particular player. This could result in several hundred ratings for any one player. Each rating that is given to a player also needs to have a unique id for that particular event so that it can be referenced back to. All records relating to a particular event will be deleted when the event is removed. Doing the maths on this would suggest that there could be about 5000 unique players at any one time. I doubt that this would cause a problem but auto-generated unique id's could quickly become very large. The real problem that I imagine is that the ratings table could hold up to 500,000 records at any time. I imagine that doing a query against a such a large table for all ratings on a particular event could become quite slow and auto-generated id's will become large very quickly. Queries will only ever relate to one event at a time. My first idea was to dynamically create two new tables for each sports event when the event is created. For example, if my event receives id 20453 then I would create a P_20453 table to store the player entries and R_20453 to store the ratings for that event. I could then use dynamic sql to access the data in these tables. I have since become aware of several hassles involved in using dynamic sql and also performance problems resulting from stored procedures not storing execution plans for dynamic sql. I have read about using Partitioned Views for reading and writing data in partitioned tables but I don't feel that this technique is suitable as the events change very frequently and recreating an entire view would be impractical every time an event is added or removed. Each entry into the database is fairly small but the shear numbers is the cause of my concern. Would you suggest using large tables to store the data and storing a reference to the sports event with each entry or using partitioned tables and dynamic sql or perhaps and idea that I haven't thought of?" |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-03-18 : 10:08:14
|
| We have an article on using numeric primary keys with large numbers: http://www.sqlteam.com/item.asp?ItemID=4123 |
 |
|
|
|
|
|
Storing large amounts of data in Sql Server 2000