Performance Update versus Update using table variable

10 Feb 2006 9:44 AM

LeBaron

I am looking for a explanation on the following:
I have the following code (on a dataset of 1 million rows). It runs very
efficiently:

----
declare @tussentabel table (contractnummer nvarchar(25), positienummer
nvarchar(25), messagetime datetime, ean nvarchar(30))

insert into @tussentabel
select servicecontractnummer, positienummer, max(messagetime), max(connectean)
from s_tbl_rswim_berichten_04x
where exists (
select 'X'
from t_tbl_inhuizen
where s_tbl_rswim_berichten_04x.servicecontractnummer =
t_tbl_inhuizen.contractnummer and
       s_tbl_rswim_berichten_04x.positienummer =
t_tbl_inhuizen.positienummer
)
and
( s_tbl_rswim_berichten_04x.msgtypeid = '044'
or s_tbl_rswim_berichten_04x.msgtypeid = '1044'
or s_tbl_rswim_berichten_04x.msgtypeid = '1045'
or s_tbl_rswim_berichten_04x.msgtypeid = '045' )
group by servicecontractnummer, positienummer

UPDATE   t_tbl_inhuizen
SET      t_tbl_inhuizen.RSWIM_04x= tt.MESSAGETIME
FROM     t_tbl_inhuizen
INNER JOIN @tussentabel tt ON
(tt.contractnummer = t_tbl_inhuizen.Contractnummer
AND tt.Positienummer = t_tbl_inhuizen.Positienummer)
---

when I use a recursive update it is very slow

-------------
UPDATE   t_tbl_inhuizen
SET      t_tbl_inhuizen.RSWIM_04x= tt.MESSAGETIME
FROM     t_tbl_inhuizen inner join
s_tbl_rswim_berichten_04x on contractnr=servicecontractnummer and
t_tbl_inhuizen.positienummer=s_tbl_rswim_berichten_04x.
positienummer
where
s_tbl_rswim_berichten_04x.msgtypeid = '044'
or s_tbl_rswim_berichten_04x.msgtypeid = '1044'
or s_tbl_rswim_berichten_04x.msgtypeid = '1045'
or s_tbl_rswim_berichten_04x.msgtypeid = '045'
-----

Can you explain why? Has it to do with locking?

Thanks in advance.

10 Feb 2006 10:07 AM

Enric

Perhaps here you can find some answers... http://www.aspfaq.com/2475

Show quoteHide quote

"LeBaron" wrote:

> I am looking for a explanation on the following:
> I have the following code (on a dataset of 1 million rows). It runs very
> efficiently:
>
> ----
> declare @tussentabel table (contractnummer nvarchar(25), positienummer
> nvarchar(25), messagetime datetime, ean nvarchar(30))
>
> insert into @tussentabel
> select servicecontractnummer, positienummer, max(messagetime), max(connectean)
> from s_tbl_rswim_berichten_04x
> where exists (
> select 'X'
> from t_tbl_inhuizen
> where s_tbl_rswim_berichten_04x.servicecontractnummer =
>   t_tbl_inhuizen.contractnummer and
>        s_tbl_rswim_berichten_04x.positienummer =
>   t_tbl_inhuizen.positienummer
> )
> and
> ( s_tbl_rswim_berichten_04x.msgtypeid = '044'
>   or s_tbl_rswim_berichten_04x.msgtypeid = '1044'
>   or s_tbl_rswim_berichten_04x.msgtypeid = '1045'
>   or s_tbl_rswim_berichten_04x.msgtypeid = '045' )
> group by servicecontractnummer, positienummer
>
> UPDATE   t_tbl_inhuizen
> SET      t_tbl_inhuizen.RSWIM_04x= tt.MESSAGETIME
> FROM     t_tbl_inhuizen
> INNER JOIN @tussentabel tt ON
> (tt.contractnummer = t_tbl_inhuizen.Contractnummer
> AND tt.Positienummer = t_tbl_inhuizen.Positienummer)
> ---
>
> when I use a recursive update it is very slow
>
>
> -------------
> UPDATE   t_tbl_inhuizen
> SET      t_tbl_inhuizen.RSWIM_04x= tt.MESSAGETIME
> FROM     t_tbl_inhuizen inner join
> s_tbl_rswim_berichten_04x on contractnr=servicecontractnummer and
> t_tbl_inhuizen.positienummer=s_tbl_rswim_berichten_04x.
> positienummer
> where
> s_tbl_rswim_berichten_04x.msgtypeid = '044'
>   or s_tbl_rswim_berichten_04x.msgtypeid = '1044'
>   or s_tbl_rswim_berichten_04x.msgtypeid = '1045'
>   or s_tbl_rswim_berichten_04x.msgtypeid = '045'
> -----
>
> Can you explain why? Has it to do with locking?
>
> Thanks in advance.
>
>
>
>