CODEWITHSUNDEEP
sql-serversql-server-2008query-optimization
user863952
user863952

Reputation: 113

sql server 2000/2008 query optimization

can you please help me to optimize the following query. Its taking a very long time.

delete from dbo.tgrid_detl  
    where F_TRANS_CD = 'R'
    and SOURCE_ID = 'BVT'
    and not exists
        (select rr.BVT_SOURCE_KEY
             from nurdevusrt.VBVT_RNEWL_RTENTN_DETL RR
         where dbo.tgrid_detl.BVT_SOURCE_KEY = rr.BVT_SOURCE_KEY
         and  YEAR(RR.X_POLICY_XPRTN_DT)>=2009 
         and rr.F_BVT_DIV_NO not in (37,65,88))

EDIT

I am using sql server 2008 but I can use the syntax of either sql server 2000 or sql server 2008 to optimize the query. Could you please help with some solution.

Thanks

Upvotes: 1

Views: 780

Answers (2)

gbn
gbn

Reputation: 432271

Immediate thought:

  • indexes are poor or not there
  • you have triggers
  • VBVT_RNEWL_RTENTN_DETL is some horrendous view

Also, change

YEAR(RR.X_POLICY_XPRTN_DT)>=2009

to

 RR.X_POLICY_XPRTN_DT >= '20090101' AND RR.X_POLICY_XPRTN_DT < '20100101'

You index column x, not f(x) where f is a function

Upvotes: 3

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

You could try following equivalent statement but most likely either

  • you don't have sufficient indexes or
  • your tables are really big or
  • your server can't keep up

Delete script

BEGIN TRAN

DELETE FROM dbo.tgrid_detl
FROM    dbo.tgrid_detl
        LEFT OUTER JOIN nurdevusrt.VBVT_RNEWL_RTENTN_DETL RR
          ON dbo.tgrid_detl.BVT_SOURCE_KEY = rr.BVT_SOURCE_KEY 
             and YEAR(RR.X_POLICY_XPRTN_DT) >= 2009 
             and rr.F_BVT_DIV_NO not in (37,65,88)
WHERE   rr.BVT_SOURCE_KEY IS NULL
        and F_TRANS_CD = 'R' 
        and SOURCE_ID = 'BVT' 

ROLLBACK TRAN

Upvotes: 2

Related Questions