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 |
|
cric2k
Starting Member
1 Post |
 Posted - 2007-10-12 : 08:10:54
|
| We are currently developing a website which is an extension to an old flat file based telephone system.To make the website scalable we are duplicating the data stored in the flat files and importing it into SQL tables.Because of various issues finding out when data has been modified we are required to update a lot of rows every time there has been a modification to a specific flat file.What this means is that we can sometimes be doing upto 1000 update and insert queries every 10 seconds or so, all done in a single transaction. What we have now noticed is that when we have our SQL updating program running (with the 1000 queries every 10 seconds) our website runs very very slow. As soon as we stop the queries the website speed is very fast.Now I would imagine that there are bigger operations than us who would be doing much more than 1000 update/insert queries without their corresponding software developing big lags when using select statements.I was wondering could anyone give me advice on how to improve performance when using select statements or an alternative configuration to get around this issue (for instance clustering servers and have one set for select statements with the other for updates/inserts)?Many thanks in advance. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 09:26:44
|
| Presumably not all the 1000 rows will have changed?In which case put a full WHERE clause to restrict the update to ONLY the rows that have changed.Your UPDATE may be updating 1,000 rows randomly all over the table, which is probably escalating the localised Page Locks to Full Table Locks, and that would block all access to the table!I presume you have a Clustered Index on both the Table and the thing that the 1,000 rows are coming from, to make the update, so that the two tables can be efficiently joined ... but if not SQL Server is probably scanning the whole table, every time ;:(]Kristen |
 |
|
|
|
|
|
Scalability issues with insert queries