question

mksmata avatar image
mksmata asked

Deadlock

Dear Friends, In my Application I have a ManifestArrival table which have transaction data. this table gets maximum read/write by user. I use merge statement for input data in this table. when table has 1000000-1500000 rows then works normal, and approx 10000 rows inserted every day in this table. when rows count increase more then 1500000 then responce of table becomes slow. and we found table becomes deadlock during entry our updation. I create Index on key field which increase speed of serching record. when we delete 400000 - 500000 row then it comes at normal stage. plz sugest me how I can handle this position. Regards Mukesh Kumar
deadlock
1 comment
10 |1200

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

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
If any of the answers below is helpful, please indicate that by clicking on the thumbs up next to each helpful answer. If any of the answers solved your problem, indicate that by clicking on the checkbox next to that answer.
0 Likes 0 ·
ruancra avatar image
ruancra answered
It sounds like you are working with large datasets, i would suggest: 1.Create a archive table to store historic data. 2.Doing the update batches 3.Creating Indexes to speed up tghe update process and for data retrieval
10 |1200

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

Grant Fritchey avatar image
Grant Fritchey answered
You need to evaluate why the queries you have are running slow. You can't just throw indexes at the table and hope that it works well. You need to look at the execution plan to identify where you may be experiencing bottlenecks. It could be issues with your T-SQL code or it could be structural (in which case indexes can help). But without the full knowledge of what's wrong, you can't know what needs fixing. Once you've identified a problematic query, the next step is to look at the execution plan for that query. I have a book on this topic. You can get a [free copy of it here.][1] [1]: http://www.sqlservercentral.com/articles/books/94937/
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.