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.

table scan !

4 posts in Performance and Tuning Last posting was on 2008-06-25 22:30:19.0Z
dev Posted on 2008-06-21 14:33:09.0Z
Sender: 34d3.485d0f41.1804289383@sybase.com
From: dev
Newsgroups: sybase.public.ase.performance+tuning
Subject: table scan !
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <485d1125.34ff.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Jun 2008 07:33:09 -0700
X-Trace: forums-1-dub 1214058789 10.22.241.41 (21 Jun 2008 07:33:09 -0700)
X-Original-Trace: 21 Jun 2008 07:33:09 -0700, 10.22.241.41
Lines: 168
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10969
Article PK: 89579

We have index on LogDynamicCurr on CurrnId, why the delete
statement goes for a table scan and not use the index?
I rebuild the index and updated the statistics using "udpate
statistics LogDynamicCurr"

delete from LogDynamicCurr
where CurrcMode='gold' and CurrnId=90 and
DIRCurrencyMd='NYC' and FinAttrib=' ' and NewOptio is NULL
and OptionDescn='BUY.B.NYC' and ActDone='Update' and
WhoUpdateRecor='ratdbo' and WHenUpdate='20070611
21:19:34:316'

Our appl uses all columns in delete, table has index col, we
do not want to change it to use just the index col. I guess
the delete query should use the index;


QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is DELETE.
The update mode is direct.

FROM TABLE
dbo.LogDynamicCurr
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.
TO TABLE
dbo.LogDynamicCurr
Using I/O Size 2 Kbytes for data pages.



1> sp_help LogDynamicCurr
2> go
Name Owner
Object_type
------------------------------
------------------------------
--------------------------------
LogDynamicCurr dbo
user table

(1 row affected)
Data_located_on_segment When_created
------------------------------ -------------------
default Jan 01 2000 1:32PM

Column_name Type Length Prec Scale
Nulls Default_name Rule_name Access_Rule_name
Identity
--------------- --------------- ----------- ---- -----
----- --------------- ---------------
------------------------------ --------
CurrcMode varchar 50 NULL NULL
0 NULL NULL NULL
0
CurrnId varchar 4 NULL NULL
0 NULL NULL NULL
0
DIRCurrencyMd varchar 12 NULL NULL
0 NULL NULL NULL
0
FinAttrib varchar 100 NULL NULL
1 NULL NULL NULL
0
NewOptio varchar 4 NULL NULL
1 NULL NULL NULL
0
OptionDescn varchar 250 NULL NULL
0 NULL NULL NULL
0
ActDone varchar 250 NULL NULL
0 NULL NULL NULL
0
WhoUpdateRecor varchar 30 NULL NULL
0 NULL NULL NULL
0
WHenUpdate datetime 8 NULL NULL
0 NULL NULL NULL
0

index_name index_description
index_keys



index_max_rows_per_page
index_fillfactor index_reservepagegap index_created
--------------------
--------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------- ----------------
-------------------- -------------------
LogDynamicCurr nonclustered located on default
CurrnId



0
0 0 Jan 11 2007 1:42AM
No defined keys for this object.
Object is not partitioned.
Lock scheme Datarows


(1 row affected)
exp_row_size reservepagegap fillfactor max_rows_per_page
identity_gap
------------ -------------- ---------- -----------------
------------
0 0 0 0
0

(1 row affected)
concurrency_opt_threshold optimistic_index_lock
dealloc_first_txtpg
------------------------- ---------------------
-------------------
15 0
0

(return status = 0)
1> sp_spaceused LogDynamicCurr,1
2> go
index_name size reserved unused
-------------------- ---------- ---------- ----------
LogDynamicCurr 0 KB 17180 KB 44 KB
LogDynamicCurr 1140 KB 1200 KB 60 KB

(1 row affected)
name rowtotal reserved data
index_size unused
-------------------- ----------- ---------------
--------------- --------------- ---------------
LogDynamicCurr 153443 18380 KB 17136 KB
1140 KB 104 KB

(return status = 0)
1> sp_helpindex LogDynamicCurr
2> go
index_name index_description
index_keys



index_max_rows_per_page
index_fillfactor index_reservepagegap index_created
--------------------
--------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------- ----------------
-------------------- -------------------
LogDynamicCurr nonclustered located on default
CurrnId



0
0 0 Jan 11 2007 1:42AM

(1 row affected, return status = 0)
1>


"Mark A. Parsons" <iron_horse Posted on 2008-06-21 17:54:55.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.14 (Windows/20071210)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: table scan !
References: <485d1125.34ff.1681692777@sybase.com>
In-Reply-To: <485d1125.34ff.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: <485d406f$1@forums-1-dub>
Date: 21 Jun 2008 10:54:55 -0700
X-Trace: forums-1-dub 1214070895 10.22.241.152 (21 Jun 2008 10:54:55 -0700)
X-Original-Trace: 21 Jun 2008 10:54:55 -0700, vip152.sybase.com
Lines: 199
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10970
Article PK: 89581

Given that CurrnId is defined as varchar(4), but in the DELETE statement you compare it to a number (CurrnId=90), you
should be getting an error message instead of a query plan.

Could you doublecheck the information you've posted (DELETE statement, sp_help output) for correctness? Are you sure
you're deleting from the same table that's mentioned in the sp_help output?

--------------------------

Assuming for the momeent that the DELETE statement contains a typo and should have been:

... and CurrnId='90' and ...

How many records in LogDynamicCurr have CurrnId='90'?

If you have more than a couple thousand records with CurrnId='90', the optimizer has probably decided that a large IO
table scan would be more efficient than (in a worse case scenario) a couple thousand small IO hops between the index and
the data pages.

--------------------------

Assuming CurrnId is not unique, I'd suggest you consider adding a unique index to the table to represent the primary key
of the table. (Assuming the table has a primary key ... ???)

With a primary key (unique index) in place this kind of delete should be fast as the optimizer will now have an index
with which to quickly zero in on a single record (assuming of course that the proposed DELETE statement is only going to
find 1 record, ie, there are no duplicate records in the table).

dev wrote:
> We have index on LogDynamicCurr on CurrnId, why the delete
> statement goes for a table scan and not use the index?
> I rebuild the index and updated the statistics using "udpate
> statistics LogDynamicCurr"
>
> delete from LogDynamicCurr
> where CurrcMode='gold' and CurrnId=90 and
> DIRCurrencyMd='NYC' and FinAttrib=' ' and NewOptio is NULL
> and OptionDescn='BUY.B.NYC' and ActDone='Update' and
> WhoUpdateRecor='ratdbo' and WHenUpdate='20070611
> 21:19:34:316'
>
> Our appl uses all columns in delete, table has index col, we
> do not want to change it to use just the index col. I guess
> the delete query should use the index;
>
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is DELETE.
> The update mode is direct.
>
> FROM TABLE
> dbo.LogDynamicCurr
> 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.
> TO TABLE
> dbo.LogDynamicCurr
> Using I/O Size 2 Kbytes for data pages.
>
>
>
> 1> sp_help LogDynamicCurr
> 2> go
> Name Owner
> Object_type
> ------------------------------
> ------------------------------
> --------------------------------
> LogDynamicCurr dbo
> user table
>
> (1 row affected)
> Data_located_on_segment When_created
> ------------------------------ -------------------
> default Jan 01 2000 1:32PM
>
> Column_name Type Length Prec Scale
> Nulls Default_name Rule_name Access_Rule_name
> Identity
> --------------- --------------- ----------- ---- -----
> ----- --------------- ---------------
> ------------------------------ --------
> CurrcMode varchar 50 NULL NULL
> 0 NULL NULL NULL
> 0
> CurrnId varchar 4 NULL NULL
> 0 NULL NULL NULL
> 0
> DIRCurrencyMd varchar 12 NULL NULL
> 0 NULL NULL NULL
> 0
> FinAttrib varchar 100 NULL NULL
> 1 NULL NULL NULL
> 0
> NewOptio varchar 4 NULL NULL
> 1 NULL NULL NULL
> 0
> OptionDescn varchar 250 NULL NULL
> 0 NULL NULL NULL
> 0
> ActDone varchar 250 NULL NULL
> 0 NULL NULL NULL
> 0
> WhoUpdateRecor varchar 30 NULL NULL
> 0 NULL NULL NULL
> 0
> WHenUpdate datetime 8 NULL NULL
> 0 NULL NULL NULL
> 0
>
> index_name index_description
> index_keys
>
>
>
> index_max_rows_per_page
> index_fillfactor index_reservepagegap index_created
> --------------------
> --------------------------------------------------------
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> ----------------------- ----------------
> -------------------- -------------------
> LogDynamicCurr nonclustered located on default
> CurrnId
>
>
>
> 0
> 0 0 Jan 11 2007 1:42AM
> No defined keys for this object.
> Object is not partitioned.
> Lock scheme Datarows
>
>
> (1 row affected)
> exp_row_size reservepagegap fillfactor max_rows_per_page
> identity_gap
> ------------ -------------- ---------- -----------------
> ------------
> 0 0 0 0
> 0
>
> (1 row affected)
> concurrency_opt_threshold optimistic_index_lock
> dealloc_first_txtpg
> ------------------------- ---------------------
> -------------------
> 15 0
> 0
>
> (return status = 0)
> 1> sp_spaceused LogDynamicCurr,1
> 2> go
> index_name size reserved unused
> -------------------- ---------- ---------- ----------
> LogDynamicCurr 0 KB 17180 KB 44 KB
> LogDynamicCurr 1140 KB 1200 KB 60 KB
>
> (1 row affected)
> name rowtotal reserved data
> index_size unused
> -------------------- ----------- ---------------
> --------------- --------------- ---------------
> LogDynamicCurr 153443 18380 KB 17136 KB
> 1140 KB 104 KB
>
> (return status = 0)
> 1> sp_helpindex LogDynamicCurr
> 2> go
> index_name index_description
> index_keys
>
>
>
> index_max_rows_per_page
> index_fillfactor index_reservepagegap index_created
> --------------------
> --------------------------------------------------------
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> ----------------------- ----------------
> -------------------- -------------------
> LogDynamicCurr nonclustered located on default
> CurrnId
>
>
>
> 0
> 0 0 Jan 11 2007 1:42AM
>
> (1 row affected, return status = 0)
> 1>


dev Posted on 2008-06-21 21:13:41.0Z
Sender: 34d3.485d0f41.1804289383@sybase.com
From: dev
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: table scan !
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <485d6f05.3e71.1681692777@sybase.com>
References: <485d406f$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Jun 2008 14:13:41 -0700
X-Trace: forums-1-dub 1214082821 10.22.241.41 (21 Jun 2008 14:13:41 -0700)
X-Original-Trace: 21 Jun 2008 14:13:41 -0700, 10.22.241.41
Lines: 229
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10971
Article PK: 89582

The other table details i had captured using isql -o
The delete was on the windows telnet session and not large
so i ended up typing it instead of copy-past, the 90 should
be '90'.

Now i check all '90' records are deleted so not sure how
many were there.

Thanks

> Given that CurrnId is defined as varchar(4), but in the
> DELETE statement you compare it to a number (CurrnId=90),
> you should be getting an error message instead of a query
> plan.
>
> Could you doublecheck the information you've posted
> (DELETE statement, sp_help output) for correctness? Are
> you sure you're deleting from the same table that's
> mentioned in the sp_help output?
>
> --------------------------
>
> Assuming for the momeent that the DELETE statement
> contains a typo and should have been:
>
> ... and CurrnId='90' and ...
>
> How many records in LogDynamicCurr have CurrnId='90'?
>
> If you have more than a couple thousand records with
> CurrnId='90', the optimizer has probably decided that a
> large IO table scan would be more efficient than (in a
> worse case scenario) a couple thousand small IO hops
> between the index and the data pages.
>
> --------------------------
>
> Assuming CurrnId is not unique, I'd suggest you consider
> adding a unique index to the table to represent the
> primary key of the table. (Assuming the table has a
> primary key ... ???)
>
> With a primary key (unique index) in place this kind of
> delete should be fast as the optimizer will now have an
> index with which to quickly zero in on a single record
> (assuming of course that the proposed DELETE statement is
> only going to find 1 record, ie, there are no duplicate
> records in the table).
>
>
>
>
> dev wrote:
> > We have index on LogDynamicCurr on CurrnId, why the
> > delete statement goes for a table scan and not use the
> > index? I rebuild the index and updated the statistics
> > using "udpate statistics LogDynamicCurr"
> >
> > delete from LogDynamicCurr
> > where CurrcMode='gold' and CurrnId=90 and
> > DIRCurrencyMd='NYC' and FinAttrib=' ' and NewOptio is
> > NULL and OptionDescn='BUY.B.NYC' and ActDone='Update'
> > and WhoUpdateRecor='ratdbo' and WHenUpdate='20070611
> > 21:19:34:316'
> >
> > Our appl uses all columns in delete, table has index col
> > , we do not want to change it to use just the index col.
> > I guess the delete query should use the index;
> >
> >
> > QUERY PLAN FOR STATEMENT 1 (at line 1).
> >
> >
> > STEP 1
> > The type of query is DELETE.
> > The update mode is direct.
> >
> > FROM TABLE
> > dbo.LogDynamicCurr
> > 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. TO TABLE
> > dbo.LogDynamicCurr
> > Using I/O Size 2 Kbytes for data pages.
> >
> >
> >
> > 1> sp_help LogDynamicCurr
> > 2> go
> > Name Owner
> > Object_type
> > ------------------------------
> > ------------------------------
> > --------------------------------
> > LogDynamicCurr dbo
> > user table
> >
> > (1 row affected)
> > Data_located_on_segment When_created
> > ------------------------------ -------------------
> > default Jan 01 2000 1:32PM
> >
> > Column_name Type Length Prec Scale
> > Nulls Default_name Rule_name Access_Rule_name
> > Identity
> > --------------- --------------- ----------- ---- -----
> > ----- --------------- ---------------
> > ------------------------------ --------
> > CurrcMode varchar 50 NULL NULL
> > 0 NULL NULL NULL
> > 0
> > CurrnId varchar 4 NULL NULL
> > 0 NULL NULL NULL
> > 0
> > DIRCurrencyMd varchar 12 NULL NULL
> > 0 NULL NULL NULL
> > 0
> > FinAttrib varchar 100 NULL NULL
> > 1 NULL NULL NULL
> > 0
> > NewOptio varchar 4 NULL NULL
> > 1 NULL NULL NULL
> > 0
> > OptionDescn varchar 250 NULL NULL
> > 0 NULL NULL NULL
> > 0
> > ActDone varchar 250 NULL NULL
> > 0 NULL NULL NULL
> > 0
> > WhoUpdateRecor varchar 30 NULL NULL
> > 0 NULL NULL NULL
> > 0
> > WHenUpdate datetime 8 NULL NULL
> > 0 NULL NULL NULL
> > 0
> >
> > index_name index_description
> > index_keys
> >
> >
> >
> >
> > index_max_rows_per_page index_fillfactor
> > index_reservepagegap index_created --------------------
> > --------------------------------------------------------
> >
> ----------------------------------------------------------
> ----------------------------------------------------------
> ----------------------------------------------------------
> ----------------------------------------------------------
> > ----------------------- -----------------------
> > ---------------- --------------------
> > ------------------- LogDynamicCurr nonclustered
> > located on default CurrnId
> >
> >
> >
> >
> > 0 0
> > 0 Jan 11 2007 1:42AM No defined keys
> > for this object. Object is not partitioned.
> > Lock scheme Datarows
> >
> >
> > (1 row affected)
> > exp_row_size reservepagegap fillfactor
> > max_rows_per_page identity_gap
> > ------------ -------------- ----------
> > ----------------- ------------
> > 0 0 0
> > 0 0
> >
> > (1 row affected)
> > concurrency_opt_threshold optimistic_index_lock
> > dealloc_first_txtpg
> > ------------------------- ---------------------
> > -------------------
> > 15 0
> > 0
> >
> > (return status = 0)
> > 1> sp_spaceused LogDynamicCurr,1
> > 2> go
> > index_name size reserved unused
> > -------------------- ---------- ---------- ----------
> > LogDynamicCurr 0 KB 17180 KB 44 KB
> > LogDynamicCurr 1140 KB 1200 KB 60 KB
> >
> > (1 row affected)
> > name rowtotal reserved data
> > index_size unused
> > -------------------- ----------- ---------------
> > --------------- --------------- ---------------
> > LogDynamicCurr 153443 18380 KB 17136
> > KB 1140 KB 104 KB
> >
> > (return status = 0)
> > 1> sp_helpindex LogDynamicCurr
> > 2> go
> > index_name index_description
> > index_keys
> >
> >
> >
> >
> > index_max_rows_per_page index_fillfactor
> > index_reservepagegap index_created --------------------
> > --------------------------------------------------------
> >
> ----------------------------------------------------------
> ----------------------------------------------------------
> ----------------------------------------------------------
> ----------------------------------------------------------
> > ----------------------- -----------------------
> > ---------------- --------------------
> > ------------------- LogDynamicCurr nonclustered
> > located on default CurrnId
> >
> >
> >
> >
> > 0 0
> > 0 Jan 11 2007 1:42AM
> > (1 row affected, return status = 0)
> > 1>


peter collard Posted on 2008-06-25 22:30:19.0Z
Message-ID: <4862c6fb@forums-1-dub>
From: peter collard <peterrrrrr@glossop.org>
Subject: Re: table scan !
Newsgroups: sybase.public.ase.performance+tuning
References: <485d406f$1@forums-1-dub> <485d6f05.3e71.1681692777@sybase.com>
Lines: 18
User-Agent: KNode/0.10.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 25 Jun 2008 15:30:19 -0700
X-Trace: forums-1-dub 1214433019 10.22.241.152 (25 Jun 2008 15:30:19 -0700)
X-Original-Trace: 25 Jun 2008 15:30:19 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10976
Article PK: 89587


dev wrote:

> The other table details i had captured using isql -o
> The delete was on the windows telnet session and not large
> so i ended up typing it instead of copy-past, the 90 should
> be '90'.

As the index is non-clustered you need to look at the number of rows to be
deleted vs the number of pages in the table. If the former is greater then
a scan is likely.

However this is not the entire picture - but its still quite a good rule of
thumb despite all the 'improvements' made to the optimiser in the last 15
years.

BTW Unless you've got very good reasons I would make the index clustered,
but add an extra column to make it more unique (to avoid overflow pages
which slow down inserts.)