February 9, 2012 at 6:16 am
Query 1 :update a set lb ,b.Pl*-1 from dctran a ,cont_prof_loss b where a.new_hpano=b.hpa_no and a.new_hpano between '300001' and '400000'
Query2 : update a set lb =b.pb*-1 from dctran a ,cont_prof_loss b where a.new_hpano=b.hpa_no and a.new_hpano between '3000000' and '3099999'
Query 1 is taking 2 sec to execute but Query 2 takes more than 13 min to execute.
I have clustered index on both table on and new_hpano hpa_no columns respectively.
Output of some queries:
select lb =b.pl*-1 from dctran a ,cont_prof_loss b where a.new_hpano=b.hpa_no and a.new_hpano between '300001' and '400000'
records: 29580
Execution time: 1 sec
select lb =b.pl*-1 from dctran a ,cont_prof_loss b where a.new_hpano=b.hpa_no and a.new_hpano between '3000000' and '3099999'
records: 3200
execution time 0 secs
total records in cont_prof_loss : 312855
According to execution plan of 1st query actual number of rows shows 312855 for cont_prof_loss table
but in execution plan of 2nd query actual number of plan increased to 3126985725.
I am confused how same tables same query showing difference in actual number of rows affected.
Please help me
February 9, 2012 at 8:26 am
Actual row count is the sum of the rows affected across all executions. So if the operator affected 10 rows each time it executed and it executed 200 times, the actual row count would be 2000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2012 at 12:32 pm
hi,
My problem got solved now. after changing datatype of cont_prof_table to nvarchar query is executing within 1 sec.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply