Temperature data is first written to a holding table - LocationID int, Hour int, whichVariable int, value float. Then it is merged into the main table (using a MERGE statement)
All this works fine when the temperature data written to the holding table goes in one variable at a time, executes the MERGE, loads the next variable, etc.
If I have multiple programs adding multiple locations and different variables all at the same time, the system deadlocks. I could force one instance of the outside program inserting the data to use a mutex to guarantee undisturbed calls to MERGE but it is quite possible we will have multiple instances of the outside program running.
What is the best approach to take to allow merging of the data?
The routine uses the MERGE statement to determine if a record for the particular observation exists. If it does not, it inserts a new record. If a record does exist, it updates the appropriate field.
Problem comes in that there are three raw import records for the particular observation. So the first merge may try to insert something for a high temperature while at the same time the system is trying to insert for the low temperature.
I can use a cursor to cycle through the imported data row by row but want to avoid the speed hit.
I also can't change the main table to hold a record for each individual type of temperature because of the huge number of records (~17 million per observation cycle)
Here's an idea:
Set up a table that is a queue table.
Just insert all the data into this table - no merge needed.
Then you have one process that reads from this table performing merges and deleting the row from the queue once the merge has been performed.
As you are only performing inserts then deletes on the queue I can't see a deadlock occurring on the queue - as the process will be issuing one merge at a time you should avoids deadlocks on your merge table too.
“That which can be asserted without evidence, can be dismissed without evidence.”
I used a mutex to control the calls to the MERGE sections and it is running. If performance becomes and issue, I will split the program in two so a multi threaded section pulls data and inserts to a temporary table and a single threaded program calls for merging the data.
How would you think that we would know how you added a calendar to your site? We cannot see your project, your code or read your mind. Besides that, what does your calendar on your site have to do with a database?
Another one with a crappy design, available seats should be an enquiry on the Bookingtable! while using a trigger spit may fix this issue the design should be fixed to remove the need for a trigger spit
Never underestimate the power of human stupidity