quote:
1. I originally wrote it as a single select. Unfortunately, the view has nolocks so SQL Server would put together the view first and then get information from the audit table, which was changed at the same time the view was put together, and join the data, which was then out of sync. As I explained in my earlier post, this created problems in our app. Using the temp table solved the problem, as much as it pained me to do.
This seems to be an issue with the architecture of the application. You could always take the view's statement and remove the NOLOCK and use it in your procedure.
quote:
3. I'll try without the convert. Will it be that much of a boost for the problem I'm having though?
Nope, just a suggestion.
quote:
4. Unfortunately, your suggestion won't work for us because this will only pull the last row changed and not all of the rows changed since the last time the proc was run. See 1 for the other reason I can't use it.
Yep. See your point. I missed the GROUP BY clause in the derived table. apologies. I still don't think the temp table is necessary, however. And I'm still unsure how your transaction code demonstrates what is getting updated that isn't appearing in your select. I only see things getting added. The triggers firing could be a problem; don't really know. I've seen a couple situations where the triggers have caused problems in locking. Your original problem was:
quote:
The problem is coming from the SELECT into the temp table. The stranger part of the behavior is that new records added during the TRAN are picked up, but not all of the updated records.
I don't see where any records are being updated. If you are referring to the deletes from the transactions, then I would look into how triggers affect concurrency and isolation issues. It seems like a very complicated process indeed. Not possible to simplify it?