Optimize this Update Sql 1

[Tinkerers]

Hi,

I have this update routine that takes 1 1/2 hours to run in Sql Server 2000.

update W
set W.IP=X.IP
from wow.dbo.CSG_SBB_HSE_HSL_ACP_EQP_BASE W
inner join
(
SELECT A.Mac,A.Ip
FROM cdvsupport.dbo.tb_CmtsPollCm A
INNER JOIN cdvsupport.dbo.tb_CmtsPollIfc B ON A.CmtsKey = B.CmtsKey AND A.IfcSnmpIxUs = B.IfcSnmpIx
INNER JOIN cdvsupport.dbo.tb_CmtsPollSys C ON A.CmtsKey = C.CmtsKey
where A.CmtsKey is not null and B.CmtsKey is not null and C.CmtsKey is not null
and A.IfcSnmpIxUS is not null and B.IfcSnmpIx is not null and A.Mac is not null
) X
on W.Mac=X.Mac

The tables all have about 2 million records. Mac is a non clustered index. Is there any way to speed this up? Thanks, Paul

 

[bborissov]

WHy you need all these joins if bith fields MAC and IP are in one table. Also:

update W
set W.IP=X.IP
from wow.dbo.CSG_SBB_HSE_HSL_ACP_EQP_BASE W
inner join
    (
    SELECT A.Mac,A.Ip
    FROM cdvsupport.dbo.tb_CmtsPollCm A
    INNER JOIN cdvsupport.dbo.tb_CmtsPollIfc B ON A.CmtsKey = B.CmtsKey AND A.IfcSnmpIxUs = B.IfcSnmpIx
    INNER JOIN cdvsupport.dbo.tb_CmtsPollSys C ON A.CmtsKey = C.CmtsKey
    where A.CmtsKey is not null
    ) X
on W.Mac=X.Mac

I think this should be enough in WHERE clause of derived table. Of course you must TEST it.
Also I suggest you to see the execution plan of this statement.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Tinkerers]

The example I provided is a portion of the query. Other fields are utilized from the other joined tables.

I'm trying the query like below, now. Populating the #TempPB table took 6 minutes. The index portion took 10 seconds. The update has been running for almost 15 minutes and is not complete yet.

select X.mac,X.ip into #TempPB
from (SELECT A.Mac,A.Ip
FROM cdvsupport.dbo.tb_CmtsPollCm A
INNER JOIN cdvsupport.dbo.tb_CmtsPollIfc B ON A.CmtsKey = B.CmtsKey AND A.IfcSnmpIxUs = B.IfcSnmpIx
INNER JOIN cdvsupport.dbo.tb_CmtsPollSys C ON A.CmtsKey = C.CmtsKey
where A.CmtsKey is not null and B.CmtsKey is not null and C.CmtsKey is not null
and A.IfcSnmpIxUS is not null and B.IfcSnmpIx is not null and A.Mac is not null
and A.Mac NOT LIKE '00000000%'
) X
inner join wow.dbo.CSG_SBB_HSE_HSL_ACP_EQP_BASE W on X.Mac=W.Mac

CREATE INDEX IX_PB1_TEMP ON #TempPB (mac)

update W
set W.IP=X.IP
from wow.dbo.CSG_SBB_HSE_HSL_ACP_EQP_BASE W,#TempPB X
where W.Mac=X.Mac
drop table #TempPB

 

[Tinkerers]

I rewrote the query so it does an insert rather than an update. MUCH faster now. Updates are very slow.

 

[bborissov]

How about:

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (Mac [COLOR=blue]varchar[/color](200), Ip [COLOR=blue]varchar[/color](200), [COLOR=blue]PRIMARY[/color] [COLOR=blue]KEY[/color] (Mac))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test
[COLOR=blue]select[/color] X.mac,X.ip
[COLOR=blue]from[/color] 
   ([COLOR=blue]SELECT[/color] A.Mac, A.Ip
           [COLOR=blue]FROM[/color] cdvsupport.dbo.tb_CmtsPollCm A
           [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] cdvsupport.dbo.tb_CmtsPollIfc B [COLOR=blue]ON[/color] A.CmtsKey     = B.CmtsKey   AND 
                                                         A.IfcSnmpIxUs = B.IfcSnmpIx AND
                                                         B.IfcSnmpIx [COLOR=blue]IS[/color] NOT NULL
           [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] cdvsupport.dbo.tb_CmtsPollSys C [COLOR=blue]ON[/color] A.CmtsKey = C.CmtsKey
    [COLOR=blue]where[/color] A.CmtsKey [COLOR=blue]is[/color] not null AND
          A.Mac     [COLOR=blue]is[/color] not null AND
          [COLOR=#FF00FF]LEFT[/color](A.Mac,8) <> [COLOR=red]'00000000'[/color]
    ) X
[COLOR=blue]inner[/color] [COLOR=blue]join[/color] wow.dbo.CSG_SBB_HSE_HSL_ACP_EQP_BASE W [COLOR=blue]on[/color] X.Mac=W.Mac

[COLOR=blue]update[/color] W
[COLOR=blue]set[/color] W.IP=X.IP
[COLOR=blue]from[/color] wow.dbo.CSG_SBB_HSE_HSL_ACP_EQP_BASE W
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] @Test  X [COLOR=blue]ON[/color] W.Mac = X.Mac

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLSister]

You can't replace an update with an insert if you want to maintain data integrity.

Try doing the update in batches. There's an FAQ on how to do that.

"NOTHING is more important in a database than integrity." ESquared