I'm not an excellent VB programmer, however i am assigned with maintaining/improving a VB6 desktop application that utilizes Sybase ASE like a back-finish. This application has about 500 customers.
Lately, I added functionality for this application which works one more place/update to some single row within the database, key area being transaction number and also the area is indexed. The table being up-to-date generally has about 6000 records inside it, as records are removed when transactions are completed. After deployment, the application labored acceptable for each day . 5 before customers were confirming slow performance.
Eventually, we tracked the performance problem to some table secure the database and needed to roll to the prior version from the application. The very first day useful was on Monday, which generally is a very heavy day for system use, so I am confused why the problem did not appear tomorrow.
Within the code which was in position, there's a phone call to begin a Sybase transaction. Inside the block between your BeginTrans and CommitTrans, there's a phone call to some DLL file that updates the database. I placed my new code inside a class module within the DLL.
I am confused why just one place/update to some single row would cause this type of problem, especially because the system have been working okay prior to the change. Is it feasible I have uncovered a bigger problem here? Or which i simply need to reconsider my approach?
Thanks ahead for anybody who has been around an identical situation and may offer advice.
It works out the reason would be a message box that seems inside the scope from the BeginTrans and CommitTrans calls. The consumer using the message box would conserve a obstructing lock around the database until they acknowledged the content. The answer ended up being to slowly move the message box outdoors from the aforementioned scope.
I'm not in a position to comprehend the truth with no SQL code, that you're using.
Also, if it's just one place OR update, the reason for utilizing a transaction? Is it feasible that lots of customers will attempt to update exactly the same row?
It might be useful should you published both VB code as well as your SQL (using the query plan if at all possible). Though the data we've I'd run
update statistics table_name from the table to make certain the query plan's current.
If you are certain your code needs to run inside a transaction perhaps you have attempted adding your personal transaction block that contains your SQL instead of while using one already there?