Why is this Sybase IQ Update Statment So Slow?

I have one table (Table1) with some info and a string ID

I have another table (Table2) with some more info and a similar string ID (it is missing an extra char in the middle).

I was originally joining the tables on

t2.StringID = substring(t1.StringID,0,2)+substring(t1.StringID,4,7)

But that was too slow, so I decided to create a new column on Table1 which is already mapped to the PrimaryID of Table2, and then index that col.

So, to update that new column I do this:

select distinct PrimaryID, 
                substring(t2.StringID,0,2)+
                substring(t2.StringID,4,7)) as StringIDFixed
into #temp
from Table2 t2

update Table1 tl
set t1.T2PrimaryID = is开发者_JAVA百科null(t.PrimaryID, 0)
from Table1 t11, #temp t
where t11.StringID = t.StringIDFixed
and t1.T2PrimaryID is null  

It creates the temp table in a few seconds, but the update has been running for 25 minutes now, and I dont know if it will even ever finish.

Table 1 has 45MM rows, Table 2 has 1.5MM

I know that's a chunky amount of data, but still, i feel like this shouldnt be that hard.

It's Sybase IQ 12.7

Any ideas?

Thanks.

Created an index on the temp table which took a few seconds, and then re ran the same update which then only took 7 seconds.

create index idx_temp_temp on #temp (StringIDFixed)

I hate Sybase.

select distinct isnull(t2.PrimaryID, 0),
                substring(t2.StringID,0,2)+
                substring(t2.StringID,4,7)) as StringIDFixed
into #temp
from Table2 t2

create HG index idx_temp_temp_HG on #temp (StringIDFixed)
or 
create LF index idx_temp_temp_LF on #temp (StringIDFixed)

--check if in Table1 exists index HG or LF in StringID if not.. create index

update Table1 tl
set t1.T2PrimaryID = t.PrimaryID
from Table1 t11, #temp t
where t11.StringID = t.StringIDFixed

-- check if is necesary 
-- and t1.T2PrimaryID is null  replace for t11.T2PrimaryID is null  

Consider replacing your update with an inner join to avoid the isnull() function on a big dataset.

update Table1 
set a.T2PrimaryID = b.PrimaryID
from        Table1  a
inner join  #temp   b
on a.StringID = b.StringIDFixed