While I consider myself fairly experienced with CF and SQL syntax, I am not terribly well-versed in SQL Server. Therefore, I do not (have not) used stored procedures or triggers, etc. I would prefer not to, at this point, until I have time to fully learn SQL Server.
My current project involves hospital patients (stored in one table) and their treatment events (stored in another table). The treatment table contains 56 columns of varying type and, since it contains every unique patient treatment event, contains hundreds of thousands of records. The table can be accessed from dozens of pages and reports and can be updated in a half-dozen other places.
Long story short, it's a big table that has multiple avenues of entry and, as a result, is constantly being viewed and altered by users.
Slowly, over time (years), I've been noticing more and more of the "Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim." errors and they all seem to involve using a SELECT on the treatment table.
I've tried to consider and repair most "best practice" SQL issues (there are no "SELECT * FROM", for example) but it has not helped.
I've read in many places that I should consider using "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" before my SELECTS (and, presumably, my INSERTS and UPDATES?). I'm not sure if this is the best solution to the issue or if it simply glosses over a bigger issue.
So, considering the following:
1) Pure SQL only (no triggers or stored procedures)
2) Large (and growing) table (indexed by SQL Query Analyzer)
3) Can be accessed from many places through the system
4) We have several installations -- all meet a minimum system requirement of CF7 and MS SQL 2005.
What would be the best solution or way to attack this?