question

mksmata avatar image
mksmata asked

Merge statement performance

Hi Dear, In my application, I use two table for store data one is for summary information and another is their details. details table has a reference id from summary table. write procedure for save data and supply value with parameter. details value is supplied in xml and check during save if exist then update for header table and and use merge for detail when match by source and destination then update else delete. i index on both header and detail table for search criteria and table has approx 20 lakh records in details and approx 1 lakh in header. I observe, it take time to execute save procedre. some time deadlock is also occur. Earliear i did not handle trans, due to this some time data is save in header but not in details. After handling transaction no such issue found. Regards Mukesh Kumar
merge
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
There's no way to tell you for sure what's wrong with the query without seeing the query, your data structures and the execution plan. My best suggestion would be to look at the execution plan to understand how your query is being processed and if there are tuning opportunities.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.