Update query depend on result of sub query Execution Doesnt Stop even after long time

Question

Following is my Query unable to update when Executed table gets lock Execution does not stop even for an hour.

update Employees 
set Status = 'Close'
where statusid IN (select statusid 
                from MyView 
                where DownloadedDate ='2014-07-27 00:00:00.000')

here Employee contains 3,00,000 of records and Subquery return 1,50,000 Empid

i tried for 2 days with various ways but unable to solve it Even Using Cursor its not working

What is "lakhs"? Also, do you mean that the subquery returns 1.5 million rows? Is there an index on Empid on the Employees table? — Mark Wojciechowicz, Aug 06 '14 at 18:26
Try to run the subquery separately? If it's slow, that's probably due to the view, the code for which you have not shown us. — Andomar, Aug 06 '14 at 18:28
sub query returns 1,50,000 rows and there is no index on the column in where clause — vishal, Aug 07 '14 at 03:32

score 0 · Answer 1 · edited May 23 '17 at 10:26

Try exists instead of in:

update Employees 
set Status = 'Close'
where exists(select 1 
                from MyView 
                where DownloadedDate ='2014-07-27 00:00:00.000'
                      and MyView.statusid = Employees.statusid)

If it doesn't work, you should create a table instead the view, to find out is there any problem of the view?

Use query plan to analyze the steps and costs . reference

Update query depend on result of sub query Execution Doesnt Stop even after long time

1 Answers1