Can you tell us a little about this table? Provide the DDL if you can and how much data would be typically included in the columns if you have things like varchar(max) or varbinary(max). Is it just a single write operation and then disconnect?
Can you provide transact sql statements for your insert, update, and read requests?
How large is this table and what are the indexes on it (provide indexes in DDL format please)? If you have indexes on data that frequently changes, for example, then a write operation could cause a lot of blocking which could cause the problems you are experiencing. If you have a poorly created index or query it could also cause blocking and increasing the number of clients would only increase overall wait time.
How is the code structured that calls the write/read? Are connections being closed immediately after use? Are transactions committed immediately after use? Are writes forced through a stored procedure or is this a direct INSERT or UPDATE statement generated in the code? Personally I would prefer a Stored Proc for writing to the table because it affords the Sql Admin flexibility to change/tweak it as needed without having to touch the client PCs.
Mark as answer or vote as helpful if you find it useful | Igor
No comments:
Post a Comment