January 31, 2009 at 10:07 am
We have a call center web application (ColdFusion) connected to a SQL 2005 server. As our call center gradually grows we are getting more and more deadlocks.
We currently have one big table which have about 6000 insert transaction a day of raw data and that same table is being used to search a specific row of data about 12,000 times a day (select col1, col2 .. where phone = @phone), there are also UPDATE transactions on that table but not as many.
Would you recommend, breaking up the table and separating incoming data (inserts) from the call center logic ?
Also, do you think creating a VIEW on that table would be effective ?
Thanks
January 31, 2009 at 10:34 am
If the indexes on that table are appropriate and the queries well written, there shouldn't be a problem. If you want some advice on that, post the table structure, existing indexes and the most frequent queries.
A view won't help. A view is just a saves SQL statement and the data retrieved from the base table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2009 at 6:23 am
Nir Netivi (1/31/2009)
We have a call center web application (ColdFusion) connected to a SQL 2005 server. As our call center gradually grows we are getting more and more deadlocks.We currently have one big table which have about 6000 insert transaction a day of raw data and that same table is being used to search a specific row of data about 12,000 times a day (select col1, col2 .. where phone = @phone), there are also UPDATE transactions on that table but not as many.
Would you recommend, breaking up the table and separating incoming data (inserts) from the call center logic ?
Also, do you think creating a VIEW on that table would be effective ?
Thanks
That's only about 250 inserts an hour or 4 a minute. Unless you're inserting gigabytes of data on each insert, I just don't think you have much to worry about. Follow Gail's advice. A clean design is the best thing you can do. You're not to the scale where you need to worry about partitioning the data.
At the end of a year, you'll have 2.1 million rows, which is not that much. If it all has to remain online forever, in 4-6 years, you might want to consider some partitioning, maybe, depending.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply