asked on Stored Procedures - Microsoft Access
SQL Stored Procedure Question - From within an Access application -- sending changes to the SQL Server via a stored procedure call ----
Is it better to update one field at a time -- or to send all changes made on an unbound form in one function to the server?
Attached is an example of the Stored Procedure.
Client is experiencing awful bottlenecks and locking - ODBC timeouts, etc. I am in the process of rewriting the application in the trouble-spots but want to get some advise on how best to do this. I have used almost the exact stored procedure as the attached for other client apps with no issues -- but in this client's environment it locks everyone up - everything locks their system up.
Some forms have dozens of hidden subforms with links back to the customer table -- being eliminated -- and all their combo boxes (to select a customer) is another link back to the customer table -- I am replacing those with passthrough queries, etc.
Is it better to update one field at a time -- or to send all changes made on an unbound form in one function to the server?
Attached is an example of the Stored Procedure.
Client is experiencing awful bottlenecks and locking - ODBC timeouts, etc. I am in the process of rewriting the application in the trouble-spots but want to get some advise on how best to do this. I have used almost the exact stored procedure as the attached for other client apps with no issues -- but in this client's environment it locks everyone up - everything locks their system up.
Some forms have dozens of hidden subforms with links back to the customer table -- being eliminated -- and all their combo boxes (to select a customer) is another link back to the customer table -- I am replacing those with passthrough queries, etc.
Microsoft SQL ServerSQL
Last Comment
Eileen Murphy
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hi John. I am moving them to temp tables where I can -- their database is pretty dynamic... 3 different apps use the customer information -- and are also updating it throughout the day -- the accounting dept is updating customer information, sales is updating customer information, marketing is reading (mostly) customer information, etc. That is the primary table causing the locking when I ran the SQL scans -- and I also isolated the biggest culprits being the sales departments (the most users pounding away at customer information all day) -- it's just awful for them. So - I am de-constructing each form and rebuilding them as I go -- just wasn't sure about the stored procedure versus say a single call to the server with an execute, db SeeChanges, or an update query --- am in a time crunch trying to perform the work while they're all out of the system. Appreciate your input :)
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
"Well, as a user I expect that the entire data is saved. Sending it per field means, that an error can lead to partial saved data, which is imho a no go." << Good Point!