Join query taking too long time to execute

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Join query taking too long time to execute

Author

Topic

papu
Starting Member

6 Posts

Posted - 2011-10-13 : 01:36:23

i Have simple join statement in which i am joining three tables . Two of the tables having 8 lacs of records and one table is having 48 lacs of records. When I am executing the below join query it is taking long time to execute .. Please help me on this .. Thanks in Advance..

UPDATE table1 A SET DID = C.DAID, CA_D = C.MP_CC
FROM table1 A
INNER JOIN table2 B ON A.CirID = B.CirID
AND A.ANet = B.ANet
AND A.DataHost = B.DataHost
INNER JOIN table3 C ON B.DataHost = C.DataHost
AND B.CA_D = C.MP_CC WHERE B.Intdomain = 'N'

Please help me on this .. IS there any keyword required like (with no rowlock) . But i have tried with no rowlock it is taking same time..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 01:43:39

do you have proper indexes on joining columns? what does the execution plan indicate?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

papu
Starting Member

6 Posts

Posted - 2011-10-13 : 01:50:32

Yes , we have proper indexing.. Can we do it using merge statement ..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 02:15:47

using MERGE just for update alone is not necessary. even then it does update on background so effect remains the same.
Can you check your isoltaion level setting? Also are you doing update inside a transaction?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-13 : 03:52:57

Can you post the results of the following:



set showplan_text on
go

UPDATE A SET 
  DID = C.DAID, 
  CA_D = C.MP_CC 
FROM table1 A 
  INNER JOIN table2 B 
    ON A.CirID = B.CirID 
      AND A.ANet = B.ANet 
      AND A.DataHost = B.DataHost 
  INNER JOIN table3 C 
    ON B.DataHost = C.DataHost 
      AND B.CA_D = C.MP_CC 
WHERE B.Intdomain = 'N'
go

set showplan_text off
go

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-13 : 03:53:49

And how long is "long time"?

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources