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
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.