Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Update query not using the index

8 posts in General Discussion Last posting was on 2010-12-02 21:43:46.0Z
Sybase_User Posted on 2010-11-29 19:22:57.0Z
Sender: 424.4cf3fc0d.1804289383@sybase.com
From: sybase_user
Newsgroups: sybase.public.ase.general
Subject: Update query not using the index
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cf3fd91.4ff.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 29 Nov 2010 11:22:57 -0800
X-Trace: forums-1-dub 1291058577 10.22.241.41 (29 Nov 2010 11:22:57 -0800)
X-Original-Trace: 29 Nov 2010 11:22:57 -0800, 10.22.241.41
Lines: 64
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29737
Article PK: 78970

We have the below query filling up the transaction log. DBA
has some stats which show high i/o while executing this
query which in turn should be the reason for filling up log.
I believe that’s because the query is not using the index
(There is an index on Security_adp_nbr).
Wondering if that’s because all the rows on the
t_abc_activity need to be updated ! ( so the cost of using
the index is higher than scan ?)

The table t_abc_activity is truncated and re-populated
everyday prior to this update. t_abc_activity has about 600K
rows and psecurity_esm has 3.5 million rows.

Appreciate any help !

-- update t_abc_activity
-- set ESM_product_id = isnull(S.esmp,ESM_product_id),
-- Isin = S.isin,
-- Sedol = S.sedol,
-- Cusip = S.cusip,
-- Instrument_name = S.description,
-- Instrument_market_sector = S.market_sector_desc,
-- Instrument_security_type = S.security_type
-- from t_abc_activity T, psecurity_esm S
-- where T.Security_adp_nbr = S.security_adp_nbr
-- and S.axon_last_mod_change_cd in ('I','U')

[NONCLUSTERED INDEX security_adp_nbr_idx ON
dbo.t_abc_activity(Security_adp_nbr)]

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is UPDATE.
The update mode is deferred_varcol.

FROM TABLE
t_abc_activity
T
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
psecurity_esm
S
EXISTS TABLE : nested iteration.
Index : IX_tsecurity_esm_adp_nbr
Forward scan.
Positioning by key.
Keys are:
security_adp_nbr ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf
pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
t_abc_activity
Using I/O Size 2 Kbytes for data pages.


Sherlock, Kevin [TeamSybase] Posted on 2010-11-29 20:12:23.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4cf3fd91.4ff.1681692777@sybase.com>
Subject: Re: Update query not using the index
Lines: 137
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4cf40927$1@forums-1-dub>
Date: 29 Nov 2010 12:12:23 -0800
X-Trace: forums-1-dub 1291061543 10.22.241.152 (29 Nov 2010 12:12:23 -0800)
X-Original-Trace: 29 Nov 2010 12:12:23 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29739
Article PK: 78965

First off, there are no valid search arguments in your query. Therefore,
your only options are that one table is chosen as the outer table, and (as
in your case) an index is a chosen path for your inner table.

It appears that you want the psecurity_esm table used as the outer table,
which would require a table scan of that table (there are no valid search
arguments for that table), and then have that joined to t_abc_activity based
on the join clause. However, it also appears that the indexes on the
security_adp_nbr fields are not unique, so the relationship between these
two tables is at base one-many, and at worst many-many. That would result
in WAY more updates (updating same row more than once) and would fill your
tran log even more.

The plan as displayed makes the most sense (t_abc_activity as outer table
and using an EXISTENCE check on the much larger and index table
psecurity_esm), and the fact that your tran log is filling means that you
are updating more t_abc_activity rows than your tran log can hold in a
single transaction. Nothing really to do with a table scan per se, just the
fact that your criteria requires a large number of rows to be updated.

As it's designed (which could probably be reviewed to see if your process
can be refactored), it's probably best to break this up with two or more
separate update statements. And between the two updates, either dump the
tran log (if "trunc log" option is not set), or truncate the log if you
don't care about recoverability (again, this is all part of your process
design which could probably be reviewed to see if there's a better way to
manage this data).

If there is a reasonable split between S.axon_last_mod_change_cd values 'I'
and 'U' then

-- you might consider including axon_last_mod_change_cd in the index on
psecurity_esm (security_adp_nbr,axon_last_mod_change_cd)


update t_abc_activity
set ESM_product_id = isnull(S.esmp,ESM_product_id),
Isin = S.isin,
Sedol = S.sedol,
Cusip = S.cusip,
Instrument_name = S.description, Instrument_market_sector =
S.market_sector_desc,
Instrument_security_type = S.security_type
from t_abc_activity T, psecurity_esm S
where T.Security_adp_nbr = S.security_adp_nbr
and S.axon_last_mod_change_cd = 'I'

-- followed by "dump tran", "dump tran with truncate only", or set "trunc
log on checkpoint" for db
-- then

update t_abc_activity
set ESM_product_id = isnull(S.esmp,ESM_product_id),
Isin = S.isin,
Sedol = S.sedol,
Cusip = S.cusip,
Instrument_name = S.description, Instrument_market_sector =
S.market_sector_desc,
Instrument_security_type = S.security_type
from t_abc_activity T, psecurity_esm S
where T.Security_adp_nbr = S.security_adp_nbr
and S.axon_last_mod_change_cd = 'U'

There are some other alternatives as well. But as I type this, I'm
wondering if you shouldn't be asking a few other questions of your
developers, like "what if there is more than one 'I' or 'U' record in the
psecurity_esm table for a security_adp_nbr? Which record get's put into
t_abc_activity? Etc. Is S.security_adp_nbr column unique? Too many
variables here to answer definitively.

<sybase_user> wrote in message news:4cf3fd91.4ff.1681692777@sybase.com...
We have the below query filling up the transaction log. DBA
has some stats which show high i/o while executing this
query which in turn should be the reason for filling up log.
I believe that's because the query is not using the index
(There is an index on Security_adp_nbr).
Wondering if that's because all the rows on the
t_abc_activity need to be updated ! ( so the cost of using
the index is higher than scan ?)

The table t_abc_activity is truncated and re-populated
everyday prior to this update. t_abc_activity has about 600K
rows and psecurity_esm has 3.5 million rows.

Appreciate any help !

-- update t_abc_activity
-- set ESM_product_id = isnull(S.esmp,ESM_product_id),
-- Isin = S.isin,
-- Sedol = S.sedol,
-- Cusip = S.cusip,
-- Instrument_name = S.description,
-- Instrument_market_sector = S.market_sector_desc,
-- Instrument_security_type = S.security_type
-- from t_abc_activity T, psecurity_esm S
-- where T.Security_adp_nbr = S.security_adp_nbr
-- and S.axon_last_mod_change_cd in ('I','U')

[NONCLUSTERED INDEX security_adp_nbr_idx ON
dbo.t_abc_activity(Security_adp_nbr)]

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is UPDATE.
The update mode is deferred_varcol.

FROM TABLE
t_abc_activity
T
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
psecurity_esm
S
EXISTS TABLE : nested iteration.
Index : IX_tsecurity_esm_adp_nbr
Forward scan.
Positioning by key.
Keys are:
security_adp_nbr ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf
pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
t_abc_activity
Using I/O Size 2 Kbytes for data pages.


Sybase_User Posted on 2010-11-29 23:01:11.0Z
Sender: 424.4cf3fc0d.1804289383@sybase.com
From: sybase_user
Newsgroups: sybase.public.ase.general
Subject: Re: Update query not using the index
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cf430b7.fbd.1681692777@sybase.com>
References: <4cf40927$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 29 Nov 2010 15:01:11 -0800
X-Trace: forums-1-dub 1291071671 10.22.241.41 (29 Nov 2010 15:01:11 -0800)
X-Original-Trace: 29 Nov 2010 15:01:11 -0800, 10.22.241.41
Lines: 159
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29741
Article PK: 78968

Thanks much Kevin, for the detailed response.

As you have rightly pointed out, it turns out to be a many
to many. i.e. security_adp_nbr is not unique in either of
the tables.

and there isnt a meaningful way to split ( "I" has has 95%
of the rows while "U" is around 5%) so planning to add more
log space, as I dont see any other alternatives.

> First off, there are no valid search arguments in your
> query. Therefore, your only options are that one table
> is chosen as the outer table, and (as in your case) an
> index is a chosen path for your inner table.
>
> It appears that you want the psecurity_esm table used as
> the outer table, which would require a table scan of that
> table (there are no valid search arguments for that
> table), and then have that joined to t_abc_activity based
> on the join clause. However, it also appears that the
> indexes on the security_adp_nbr fields are not unique, so
> the relationship between these two tables is at base
> one-many, and at worst many-many. That would result in
> WAY more updates (updating same row more than once) and
> would fill your tran log even more.
>
> The plan as displayed makes the most sense (t_abc_activity
> as outer table and using an EXISTENCE check on the much
> larger and index table psecurity_esm), and the fact that
> your tran log is filling means that you are updating more
> t_abc_activity rows than your tran log can hold in a
> single transaction. Nothing really to do with a table
> scan per se, just the fact that your criteria requires a
> large number of rows to be updated.
>
> As it's designed (which could probably be reviewed to see
> if your process can be refactored), it's probably best to
> break this up with two or more separate update
> statements. And between the two updates, either dump the
> tran log (if "trunc log" option is not set), or truncate
> the log if you don't care about recoverability (again,
> this is all part of your process design which could
> probably be reviewed to see if there's a better way to
> manage this data).
>
> If there is a reasonable split between
> S.axon_last_mod_change_cd values 'I' and 'U' then
>
> -- you might consider including axon_last_mod_change_cd in
> the index on psecurity_esm (security_adp_nbr
> ,axon_last_mod_change_cd)
>
>
> update t_abc_activity
> set ESM_product_id = isnull(S.esmp,ESM_product_id),
> Isin = S.isin,
> Sedol = S.sedol,
> Cusip = S.cusip,
> Instrument_name = S.description, Instrument_market_sector
> = S.market_sector_desc,
> Instrument_security_type = S.security_type
> from t_abc_activity T, psecurity_esm S
> where T.Security_adp_nbr = S.security_adp_nbr
> and S.axon_last_mod_change_cd = 'I'
>
> -- followed by "dump tran", "dump tran with truncate only"
> , or set "trunc log on checkpoint" for db
> -- then
>
> update t_abc_activity
> set ESM_product_id = isnull(S.esmp,ESM_product_id),
> Isin = S.isin,
> Sedol = S.sedol,
> Cusip = S.cusip,
> Instrument_name = S.description, Instrument_market_sector
> = S.market_sector_desc,
> Instrument_security_type = S.security_type
> from t_abc_activity T, psecurity_esm S
> where T.Security_adp_nbr = S.security_adp_nbr
> and S.axon_last_mod_change_cd = 'U'
>
> There are some other alternatives as well. But as I type
> this, I'm wondering if you shouldn't be asking a few
> other questions of your developers, like "what if there
> is more than one 'I' or 'U' record in the psecurity_esm
> table for a security_adp_nbr? Which record get's put into
> t_abc_activity? Etc. Is S.security_adp_nbr column
> unique? Too many variables here to answer definitively.
>
> <sybase_user> wrote in message
> news:4cf3fd91.4ff.1681692777@sybase.com... We have the
> below query filling up the transaction log. DBA has some
> stats which show high i/o while executing this query which
> in turn should be the reason for filling up log. I believe
> that's because the query is not using the index (There is
> an index on Security_adp_nbr). Wondering if that's because
> all the rows on the t_abc_activity need to be updated ! (
> so the cost of using the index is higher than scan ?)
>
> The table t_abc_activity is truncated and re-populated
> everyday prior to this update. t_abc_activity has about
> 600K rows and psecurity_esm has 3.5 million rows.
>
> Appreciate any help !
>
> -- update t_abc_activity
> -- set ESM_product_id = isnull(S.esmp,ESM_product_id),
> -- Isin = S.isin,
> -- Sedol = S.sedol,
> -- Cusip = S.cusip,
> -- Instrument_name = S.description,
> -- Instrument_market_sector = S.market_sector_desc,
> -- Instrument_security_type = S.security_type
> -- from t_abc_activity T, psecurity_esm S
> -- where T.Security_adp_nbr = S.security_adp_nbr
> -- and S.axon_last_mod_change_cd in ('I','U')
>
> [NONCLUSTERED INDEX security_adp_nbr_idx ON
> dbo.t_abc_activity(Security_adp_nbr)]
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is UPDATE.
> The update mode is deferred_varcol.
>
> FROM TABLE
> t_abc_activity
> T
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM TABLE
> psecurity_esm
> S
> EXISTS TABLE : nested iteration.
> Index : IX_tsecurity_esm_adp_nbr
> Forward scan.
> Positioning by key.
> Keys are:
> security_adp_nbr ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index
> leaf pages.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data
> pages.
> TO TABLE
> t_abc_activity
> Using I/O Size 2 Kbytes for data pages.
>
>


Bret Halford Posted on 2010-11-29 23:25:23.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Update query not using the index
References: <4cf40927$1@forums-1-dub> <4cf430b7.fbd.1681692777@sybase.com>
In-Reply-To: <4cf430b7.fbd.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4cf43663$1@forums-1-dub>
Date: 29 Nov 2010 15:25:23 -0800
X-Trace: forums-1-dub 1291073123 10.22.241.152 (29 Nov 2010 15:25:23 -0800)
X-Original-Trace: 29 Nov 2010 15:25:23 -0800, vip152.sybase.com
Lines: 171
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29742
Article PK: 78971

There are probably lots of alternatives, for instance
you might split it by using
Instrument_name < "M"
Instrument_name >= "N"

(or whatever actual values make sense)

-bret

On 11/29/2010 4:01 PM, sybase_user wrote:
> Thanks much Kevin, for the detailed response.
>
> As you have rightly pointed out, it turns out to be a many
> to many. i.e. security_adp_nbr is not unique in either of
> the tables.
>
> and there isnt a meaningful way to split ( "I" has has 95%
> of the rows while "U" is around 5%) so planning to add more
> log space, as I dont see any other alternatives.
>
>
>> First off, there are no valid search arguments in your
>> query. Therefore, your only options are that one table
>> is chosen as the outer table, and (as in your case) an
>> index is a chosen path for your inner table.
>>
>> It appears that you want the psecurity_esm table used as
>> the outer table, which would require a table scan of that
>> table (there are no valid search arguments for that
>> table), and then have that joined to t_abc_activity based
>> on the join clause. However, it also appears that the
>> indexes on the security_adp_nbr fields are not unique, so
>> the relationship between these two tables is at base
>> one-many, and at worst many-many. That would result in
>> WAY more updates (updating same row more than once) and
>> would fill your tran log even more.
>>
>> The plan as displayed makes the most sense (t_abc_activity
>> as outer table and using an EXISTENCE check on the much
>> larger and index table psecurity_esm), and the fact that
>> your tran log is filling means that you are updating more
>> t_abc_activity rows than your tran log can hold in a
>> single transaction. Nothing really to do with a table
>> scan per se, just the fact that your criteria requires a
>> large number of rows to be updated.
>>
>> As it's designed (which could probably be reviewed to see
>> if your process can be refactored), it's probably best to
>> break this up with two or more separate update
>> statements. And between the two updates, either dump the
>> tran log (if "trunc log" option is not set), or truncate
>> the log if you don't care about recoverability (again,
>> this is all part of your process design which could
>> probably be reviewed to see if there's a better way to
>> manage this data).
>>
>> If there is a reasonable split between
>> S.axon_last_mod_change_cd values 'I' and 'U' then
>>
>> -- you might consider including axon_last_mod_change_cd in
>> the index on psecurity_esm (security_adp_nbr
>> ,axon_last_mod_change_cd)
>>
>>
>> update t_abc_activity
>> set ESM_product_id = isnull(S.esmp,ESM_product_id),
>> Isin = S.isin,
>> Sedol = S.sedol,
>> Cusip = S.cusip,
>> Instrument_name = S.description, Instrument_market_sector
>> = S.market_sector_desc,
>> Instrument_security_type = S.security_type
>> from t_abc_activity T, psecurity_esm S
>> where T.Security_adp_nbr = S.security_adp_nbr
>> and S.axon_last_mod_change_cd = 'I'
>>
>> -- followed by "dump tran", "dump tran with truncate only"
>> , or set "trunc log on checkpoint" for db
>> -- then
>>
>> update t_abc_activity
>> set ESM_product_id = isnull(S.esmp,ESM_product_id),
>> Isin = S.isin,
>> Sedol = S.sedol,
>> Cusip = S.cusip,
>> Instrument_name = S.description, Instrument_market_sector
>> = S.market_sector_desc,
>> Instrument_security_type = S.security_type
>> from t_abc_activity T, psecurity_esm S
>> where T.Security_adp_nbr = S.security_adp_nbr
>> and S.axon_last_mod_change_cd = 'U'
>>
>> There are some other alternatives as well. But as I type
>> this, I'm wondering if you shouldn't be asking a few
>> other questions of your developers, like "what if there
>> is more than one 'I' or 'U' record in the psecurity_esm
>> table for a security_adp_nbr? Which record get's put into
>> t_abc_activity? Etc. Is S.security_adp_nbr column
>> unique? Too many variables here to answer definitively.
>>
>> <sybase_user> wrote in message
>> news:4cf3fd91.4ff.1681692777@sybase.com... We have the
>> below query filling up the transaction log. DBA has some
>> stats which show high i/o while executing this query which
>> in turn should be the reason for filling up log. I believe
>> that's because the query is not using the index (There is
>> an index on Security_adp_nbr). Wondering if that's because
>> all the rows on the t_abc_activity need to be updated ! (
>> so the cost of using the index is higher than scan ?)
>>
>> The table t_abc_activity is truncated and re-populated
>> everyday prior to this update. t_abc_activity has about
>> 600K rows and psecurity_esm has 3.5 million rows.
>>
>> Appreciate any help !
>>
>> -- update t_abc_activity
>> -- set ESM_product_id = isnull(S.esmp,ESM_product_id),
>> -- Isin = S.isin,
>> -- Sedol = S.sedol,
>> -- Cusip = S.cusip,
>> -- Instrument_name = S.description,
>> -- Instrument_market_sector = S.market_sector_desc,
>> -- Instrument_security_type = S.security_type
>> -- from t_abc_activity T, psecurity_esm S
>> -- where T.Security_adp_nbr = S.security_adp_nbr
>> -- and S.axon_last_mod_change_cd in ('I','U')
>>
>> [NONCLUSTERED INDEX security_adp_nbr_idx ON
>> dbo.t_abc_activity(Security_adp_nbr)]
>>
>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>
>>
>> STEP 1
>> The type of query is UPDATE.
>> The update mode is deferred_varcol.
>>
>> FROM TABLE
>> t_abc_activity
>> T
>> Nested iteration.
>> Table Scan.
>> Forward scan.
>> Positioning at start of table.
>> Using I/O Size 16 Kbytes for data pages.
>> With LRU Buffer Replacement Strategy for data
>> pages.
>>
>> FROM TABLE
>> psecurity_esm
>> S
>> EXISTS TABLE : nested iteration.
>> Index : IX_tsecurity_esm_adp_nbr
>> Forward scan.
>> Positioning by key.
>> Keys are:
>> security_adp_nbr ASC
>> Using I/O Size 2 Kbytes for index leaf pages.
>> With LRU Buffer Replacement Strategy for index
>> leaf pages.
>> Using I/O Size 2 Kbytes for data pages.
>> With LRU Buffer Replacement Strategy for data
>> pages.
>> TO TABLE
>> t_abc_activity
>> Using I/O Size 2 Kbytes for data pages.
>>
>>


stupidone Posted on 2010-11-30 16:43:25.0Z
Sender: 6f2e.4cf3c8de.1804289383@sybase.com
From: stupidone
Newsgroups: sybase.public.ase.general
Subject: Re: Update query not using the index
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cf529ad.4328.1681692777@sybase.com>
References: <4cf3fd91.4ff.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 30 Nov 2010 08:43:25 -0800
X-Trace: forums-1-dub 1291135405 10.22.241.41 (30 Nov 2010 08:43:25 -0800)
X-Original-Trace: 30 Nov 2010 08:43:25 -0800, 10.22.241.41
Lines: 100
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29746
Article PK: 78974

some stupid thoughts...

Are you sure the query is doing what you want it to do?

Table scan has nothing to do with log filling up (in my
opinion), table scan should just take longer(in certain
cases).
The actual update activity is what is going to fill
logsegment.

The values in your table dont seem to be unique i.e.
If you have 'xyz' Security_adp_nbr in table t_abc_activity
and have multiple 'xyz' rows in psecurity_esm then what
values do you expect to be updated?

if the Where criteria and data is good (which i doubt it is,
u will hv to ask other devlopers in your team to understand
the tables and data better), then you could add additional
filters to the update
query e.g
-- where T.Security_adp_nbr = S.security_adp_nbr
-- and S.axon_last_mod_change_cd in ('I','U')
-- and S.security_adp_nbr between <RANGE>
(This could be in a loop and RANGE can be manipulated within
a loop)

I recall seeing this stuff with TO Bank a few years back, u
can always shorten the updated rows.

stupid developers always ask logsegment to be increased
rather than increasing their understanding of application
schema and data hehehehe

> We have the below query filling up the transaction log.
> DBA has some stats which show high i/o while executing
> this query which in turn should be the reason for filling
> up log. I believe that’s because the query is not using
> the index (There is an index on Security_adp_nbr).
> Wondering if that’s because all the rows on the
> t_abc_activity need to be updated ! ( so the cost of
> using the index is higher than scan ?)
>
> The table t_abc_activity is truncated and re-populated
> everyday prior to this update. t_abc_activity has about
> 600K rows and psecurity_esm has 3.5 million rows.
>
> Appreciate any help !
>
> -- update t_abc_activity
> -- set ESM_product_id = isnull(S.esmp,ESM_product_id),
> -- Isin = S.isin,
> -- Sedol = S.sedol,
> -- Cusip = S.cusip,
> -- Instrument_name = S.description,
> -- Instrument_market_sector = S.market_sector_desc,
> -- Instrument_security_type = S.security_type
> -- from t_abc_activity T, psecurity_esm S
> -- where T.Security_adp_nbr = S.security_adp_nbr
> -- and S.axon_last_mod_change_cd in ('I','U')
>
> [NONCLUSTERED INDEX security_adp_nbr_idx ON
> dbo.t_abc_activity(Security_adp_nbr)]
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is UPDATE.
> The update mode is deferred_varcol.
>
> FROM TABLE
> t_abc_activity
> T
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM TABLE
> psecurity_esm
> S
> EXISTS TABLE : nested iteration.
> Index : IX_tsecurity_esm_adp_nbr
> Forward scan.
> Positioning by key.
> Keys are:
> security_adp_nbr ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index
> leaf pages.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data
> pages.
> TO TABLE
> t_abc_activity
> Using I/O Size 2 Kbytes for data pages.


Sybase_User Posted on 2010-11-30 21:28:45.0Z
Sender: 424.4cf3fc0d.1804289383@sybase.com
From: sybase_user
Newsgroups: sybase.public.ase.general
Subject: Re: Update query not using the index
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cf56c8d.5181.1681692777@sybase.com>
References: <4cf529ad.4328.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 30 Nov 2010 13:28:45 -0800
X-Trace: forums-1-dub 1291152525 10.22.241.41 (30 Nov 2010 13:28:45 -0800)
X-Original-Trace: 30 Nov 2010 13:28:45 -0800, 10.22.241.41
Lines: 121
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29747
Article PK: 78976

Stupidone,

why would you think the values are not unique ?
Nothing in the query says that there are multiple
security_adp_nbrs in psecurity_esm. Infact, thats the
expectation for writing this update. And there are no
additional filters that can be added ( as I said I dont want
to add additional columns just for the sake of batching the
update ideally, unless its the only way left )

"
The values in your table dont seem to be unique i.e.
If you have 'xyz' Security_adp_nbr in table t_abc_activity
and have multiple 'xyz' rows in psecurity_esm then what
values do you expect to be updated?
"

> some stupid thoughts...
>
> Are you sure the query is doing what you want it to do?
>
> Table scan has nothing to do with log filling up (in my
> opinion), table scan should just take longer(in certain
> cases).
> The actual update activity is what is going to fill
> logsegment.
>
> The values in your table dont seem to be unique i.e.
> If you have 'xyz' Security_adp_nbr in table t_abc_activity
> and have multiple 'xyz' rows in psecurity_esm then what
> values do you expect to be updated?
>
> if the Where criteria and data is good (which i doubt it
> is, u will hv to ask other devlopers in your team to
> understand the tables and data better), then you could add
> additional filters to the update
> query e.g
> -- where T.Security_adp_nbr = S.security_adp_nbr
> -- and S.axon_last_mod_change_cd in ('I','U')
> -- and S.security_adp_nbr between <RANGE>
> (This could be in a loop and RANGE can be manipulated
> within a loop)
>
> I recall seeing this stuff with TO Bank a few years back,
> u can always shorten the updated rows.
>
> stupid developers always ask logsegment to be increased
> rather than increasing their understanding of application
> schema and data hehehehe
>
>
> > We have the below query filling up the transaction log.
> > DBA has some stats which show high i/o while executing
> > this query which in turn should be the reason for
> > filling up log. I believe that’s because the query is
> > not using the index (There is an index on
> > Security_adp_nbr). Wondering if that’s because all the
> > rows on the t_abc_activity need to be updated ! ( so
> > the cost of using the index is higher than scan ?)
> >
> > The table t_abc_activity is truncated and re-populated
> > everyday prior to this update. t_abc_activity has about
> > 600K rows and psecurity_esm has 3.5 million rows.
> >
> > Appreciate any help !
> >
> > -- update t_abc_activity
> > -- set ESM_product_id = isnull(S.esmp,ESM_product_id),
> > -- Isin = S.isin,
> > -- Sedol = S.sedol,
> > -- Cusip = S.cusip,
> > -- Instrument_name = S.description,
> > -- Instrument_market_sector = S.market_sector_desc,
> > -- Instrument_security_type = S.security_type
> > -- from t_abc_activity T, psecurity_esm S
> > -- where T.Security_adp_nbr = S.security_adp_nbr
> > -- and S.axon_last_mod_change_cd in ('I','U')
> >
> > [NONCLUSTERED INDEX security_adp_nbr_idx ON
> > dbo.t_abc_activity(Security_adp_nbr)]
> >
> > QUERY PLAN FOR STATEMENT 1 (at line 1).
> >
> >
> > STEP 1
> > The type of query is UPDATE.
> > The update mode is deferred_varcol.
> >
> > FROM TABLE
> > t_abc_activity
> > T
> > Nested iteration.
> > Table Scan.
> > Forward scan.
> > Positioning at start of table.
> > Using I/O Size 16 Kbytes for data pages.
> > With LRU Buffer Replacement Strategy for data
> > pages.
> >
> > FROM TABLE
> > psecurity_esm
> > S
> > EXISTS TABLE : nested iteration.
> > Index : IX_tsecurity_esm_adp_nbr
> > Forward scan.
> > Positioning by key.
> > Keys are:
> > security_adp_nbr ASC
> > Using I/O Size 2 Kbytes for index leaf pages.
> > With LRU Buffer Replacement Strategy for index
> > leaf pages.
> > Using I/O Size 2 Kbytes for data pages.
> > With LRU Buffer Replacement Strategy for data
> > pages.
> > TO TABLE
> > t_abc_activity
> > Using I/O Size 2 Kbytes for data pages.


Carl Kayser Posted on 2010-12-01 13:54:32.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4cf529ad.4328.1681692777@sybase.com> <4cf56c8d.5181.1681692777@sybase.com>
Subject: Re: Update query not using the index
Lines: 35
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4cf65398$1@forums-1-dub>
Date: 1 Dec 2010 05:54:32 -0800
X-Trace: forums-1-dub 1291211672 10.22.241.152 (1 Dec 2010 05:54:32 -0800)
X-Original-Trace: 1 Dec 2010 05:54:32 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29748
Article PK: 78977

<sybase_user> wrote in message news:4cf56c8d.5181.1681692777@sybase.com...
Stupidone,

why would you think the values are not unique ?
Nothing in the query says that there are multiple
security_adp_nbrs in psecurity_esm. Infact, thats the
expectation for writing this update. And there are no
additional filters that can be added ( as I said I dont want
to add additional columns just for the sake of batching the
update ideally, unless its the only way left )

(SNIP)

I'm not Stupidone but from your reply to Kevin:

"As you have rightly pointed out, it turns out to be a many to many. i.e.
security_adp_nbr is not unique in either of the tables."

which seems to be a contradiction. But perhaps not.

Are the (security_adp_nbr, axon_last_mod_change_cd) values unique on
psecurity_esm?

If not unique then can there then be both "U" and "I"
axon_last_mod_change_cd values for a security_adp_nbr value? If "yes" then
I would agree with Stupidone that you may be updating t_abc_activity rows
mutiple times with different values (from different "U" and "I" rows with
the same security_adp_nbr values).


Sybase_User Posted on 2010-12-02 21:43:46.0Z
Sender: 424.4cf3fc0d.1804289383@sybase.com
From: sybase_user
Newsgroups: sybase.public.ase.general
Subject: Re: Update query not using the index
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cf81312.67ae.1681692777@sybase.com>
References: <4cf65398$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 2 Dec 2010 13:43:46 -0800
X-Trace: forums-1-dub 1291326226 10.22.241.41 (2 Dec 2010 13:43:46 -0800)
X-Original-Trace: 2 Dec 2010 13:43:46 -0800, 10.22.241.41
Lines: 46
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29760
Article PK: 78988

There were a minor %age of duplicate security_adp_nbrs that
are expected to be removed by data provider soon. Even now,
the longer update/log fillup cant be attributed to those
dupes as its around less than 2%.

As I mentioned, the query was written with an understanding
that there will be only one entry per security_adp_nbr in
psecurity_esm

> <sybase_user> wrote in message
> news:4cf56c8d.5181.1681692777@sybase.com... Stupidone,
>
> why would you think the values are not unique ?
> Nothing in the query says that there are multiple
> security_adp_nbrs in psecurity_esm. Infact, thats the
> expectation for writing this update. And there are no
> additional filters that can be added ( as I said I dont
> want to add additional columns just for the sake of
> batching the update ideally, unless its the only way left
> )
>
> (SNIP)
>
> I'm not Stupidone but from your reply to Kevin:
>
> "As you have rightly pointed out, it turns out to be a
> many to many. i.e. security_adp_nbr is not unique in
> either of the tables."
>
> which seems to be a contradiction. But perhaps not.
>
> Are the (security_adp_nbr, axon_last_mod_change_cd) values
> unique on psecurity_esm?
>
> If not unique then can there then be both "U" and "I"
> axon_last_mod_change_cd values for a security_adp_nbr
> value? If "yes" then I would agree with Stupidone that
> you may be updating t_abc_activity rows mutiple times with
> different values (from different "U" and "I" rows with the
> same security_adp_nbr values).
>
>
>
>
>
>