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 |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2002-07-10 : 02:25:12
|
| I have a table that is relatively large and growing fast, it is only 4-5 columns but I have about 900,000 rows. This table stores members votes and the corresponding info. Each time a member votes (which is a major activity and happens ten of thousands of times daily and growing) the vote is run against this table to check to see if it is a duplicate vote. I have noticed in the past couple weeks the SQL server is using alot more CPU, and we are already way underpowered so this is not a good thing! ;) . I believe it is properly indexed via SQL server wizard. The thing is this table is just gonna keep on growing and eventually hold millions of rows. When each user does such a simple task as cast 1 vote it is going to have to compare against millions.Is there any common strategies to speed something like this up? Or is there no way around it like it seems?Thanks alot for any insight / suggestions.Mike |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-07-10 : 02:51:42
|
| I assume you have something like user_id, vote_id.Use ID's rather than text.Make sure these are indexed in this order then your checkif exists (select * from tbl where user_id = @user_id and vote_id = @vote_id)will be quick and it shouldn't matter too much how big the table gets.>> properly indexed via SQL server wizard.Don't trust it.Also archive off any old voting subjects that are out of date to keep the table maneagable.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-10 : 08:00:28
|
| nr's advice is sound. If you want more indepth commentary, please post the ddl of the table and indexes in question. Then post the dml that is giving poor performance. There is probably more we can do for you with more detail.<O> |
|
|
|
|
|
|
any suggestions on large table