Performance issue while using Crash Magic.

Explanation

Microsoft SQL Server and Oracle use different methods of locking rows when a transaction is occurring. The current technique that Crash Magic uses to find unique IDs causes a performance hit when a user is writing to a table in SQL Server, and an exception error in when writing to a table in Oracle. 

SQL Server’s default behavior allows all rows to change as a result of the transaction. Crash Magic uses the SQL command Max to find the next ID. This value could possible change due to the currently running transaction SQL Server locks out Crash Magic from making this request, waiting until the transaction has completed.

Potential Problems:

If  User A starts a MagicAuto process (using Map Magic to generate a report), while User B simultaneously starts a MagicAuto process, User B has to wait for User A to finish his request. The more people simultaneously trying to use MagicAuto the longer the wait, each process is waiting in line for the last one to complete.

If a user starts a MagicAuto process all other users need to wait for Crash Magic to finish processing the MagicAuto request before they can use Crash Magic. Most MagicAuto requests are fairly fast so the wait should be minimal.

If the paste function is used, all other users must wait for the paste to complete before they can continue to use the program. The paste process is fairly quick, so the wait should be minimal.

If the import function is being used all other users must wait for the import to complete before they can continue working.

If any of these processes take longer than 30 seconds to complete a timeout will occur which result in an exception. No corruption will occur, but the new Project, Study or Report will be lost.

OracleÕs default behavior allows users to view all committed transactions and exclude data from transactions not committed. Crash Magic uses the SQL function Max to find the next ID. Crash Magic could attempt to assign two records in the database with the same ID number. The resulting attempt to insert a record into the database would cause a primary key violation resulting in an exception error for one of the users.

Potential Problems:

If User A starts a MagicAuto process to create a report, while user B simultaneously clicks on a report button, the last report to insert to the database will receive an unhandled exception from the program.

Inserts from Crash Magic happen very quickly, so any errors should be very rare.

No corruption of data will occur, but the new Study or Report will be lost for the user who’s insert completed last.