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.

Inefficient Query Plan

17 posts in Windows NT Last posting was on 2000-05-04 00:13:18.0Z
Mark Maslow Posted on 2000-05-02 18:50:38.0Z
From: "Mark Maslow" <mark.maslow@sierraclub.org>
Subject: Inefficient Query Plan
Date: Tue, 2 May 2000 11:50:38 -0700
Lines: 28
Organization: Sierra Club
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <YGJuJhGt$GA.269@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: machine001.sierraclub.org 207.90.163.1
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2222
Article PK: 1089654

We are using ASE 11.0.3.3.

We have one table with about 2.5 million rows. One of the indexes is on a
char(25) column.

If we do a query like:

SELECT * FROM bigtable WHERE indexedcolumn IN ("ABC","DEF",GHI")

the query uses the index, and all is well.

However, if we build a table with 3 rows and issue a query like:

SELECT * FROM bigtable, littletable WHERE
bigtable.indexedcolumn=littletable.id

Then the query plan says it will do a table scan of bigtable. And, of
course, the query takes a LONG time.

Is there some way to force ASE to do this query intelligently?

TIA

Mark Maslow
Lead Programmer/Analyst
Sierra Club


Peter Veilleux Posted on 2000-05-02 18:57:12.0Z
Message-ID: <390F2508.1766C43B@nvestservices.com>
Date: Tue, 02 May 2000 14:57:12 -0400
From: Peter Veilleux <pveilleux@nvestservices.com>
X-Mailer: Mozilla 4.72 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Inefficient Query Plan
References: <YGJuJhGt$GA.269@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 23
NNTP-Posting-Host: nefclient149.mutualfunds.com 206.136.227.149
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2221
Article PK: 1089653

Mark,

what are we looking at here for the datatypes of the 2 columns being joined?
Is there a useful index on littletable? Also, do you have any 16k i/o pools
defined?

Peter

Mark Maslow wrote:

> We are using ASE 11.0.3.3.
>
> We have one table with about 2.5 million rows. One of the indexes is on a
> char(25) column.
>
> If we do a query like:
>
> SELECT * FROM bigtable WHERE indexedcolumn IN ("ABC","DEF",GHI")
>
> the query uses the index, and all is well.
>
> However, if we build a table with 3 rows and issue a query like:
>
> SELECT * FROM bigtable, littletable WHERE
> bigtable.indexedcolumn=littletable.id
>
> Then the query plan says it will do a table scan of bigtable. And, of
> course, the query takes a LONG time.
>
> Is there some way to force ASE to do this query intelligently?
>
> TIA
>
> Mark Maslow
> Lead Programmer/Analyst
> Sierra Club


Mark Maslow Posted on 2000-05-02 19:04:51.0Z
From: "Mark Maslow" <mark.maslow@sierraclub.org>
References: <YGJuJhGt$GA.269@forums.sybase.com> <390F2508.1766C43B@nvestservices.com>
Subject: Re: Inefficient Query Plan
Date: Tue, 2 May 2000 12:04:51 -0700
Lines: 56
Organization: Sierra Club
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <6b2yFpGt$GA.263@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: machine001.sierraclub.org 207.90.163.1
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2220
Article PK: 854789

The columns being joined are both char(25).

There was no index on littletable (why would a 3 row table need an index?).
Just for yucks, I tried indexing the column in littletable. The plan still
shows a scan of bigtable - but now uses only the index on littletable (no
longer needs to read base table). That's not exactly a huge performance
boost.

What's a 16k i/o pool?

Mark Maslow

Peter Veilleux <pveilleux@nvestservices.com> wrote in message
news:390F2508.1766C43B@nvestservices.com...
> Mark,
>
> what are we looking at here for the datatypes of the 2 columns being
joined?
> Is there a useful index on littletable? Also, do you have any 16k i/o
pools
> defined?
>
> Peter
>
> Mark Maslow wrote:
>
> > We are using ASE 11.0.3.3.
> >
> > We have one table with about 2.5 million rows. One of the indexes is on
a
> > char(25) column.
> >
> > If we do a query like:
> >
> > SELECT * FROM bigtable WHERE indexedcolumn IN ("ABC","DEF",GHI")
> >
> > the query uses the index, and all is well.
> >
> > However, if we build a table with 3 rows and issue a query like:
> >
> > SELECT * FROM bigtable, littletable WHERE
> > bigtable.indexedcolumn=littletable.id
> >
> > Then the query plan says it will do a table scan of bigtable. And, of
> > course, the query takes a LONG time.
> >
> > Is there some way to force ASE to do this query intelligently?
> >
> > TIA
> >
> > Mark Maslow
> > Lead Programmer/Analyst
> > Sierra Club
>


Peter Veilleux Posted on 2000-05-02 20:24:57.0Z
Message-ID: <390F3999.37589EE4@nvestservices.com>
Date: Tue, 02 May 2000 16:24:57 -0400
From: Peter Veilleux <pveilleux@nvestservices.com>
X-Mailer: Mozilla 4.72 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Inefficient Query Plan
References: <YGJuJhGt$GA.269@forums.sybase.com> <390F2508.1766C43B@nvestservices.com> <6b2yFpGt$GA.263@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 70
NNTP-Posting-Host: nefclient149.mutualfunds.com 206.136.227.149
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2215
Article PK: 1089647

Mark,

every data cache has one or more buffer pools. The default is a 2k i/o pool.
What this does is it reads in a page at a time. A 16k i/o pool will read in 8
pages. It's not quite prefetch, but it treats the 16k i/o pool as one unit,
allowing for large i/o. You can read more about this in Sybooks
(sybooks.sybase.com).

Anyway, run sp_cacheconfig "default data cache" to see if you have a 16k i/o
pool defined. You can also replace "default data cache" with a named cache, if
you have one. There was a bug/design limitation in 11.0.x with huge tables and
16k i/o. There is a trace flag you can turn on to change how the optimizer
handles data/index page clustering ratios, which was the root of the problem.
But I'd like to verify you have a 16k i/o pool first. If not, I suggest you
turn on 302, 310, 317 and showplan for this and post it (the 317 shouldn't make
the output too large in this case).

And for future reference, post these questions to
sybase.public.sqlserver.performance+tuning. I know the optimizer guys hang out
in there more than in the NT newsgroup....

Peter

So, if you have a 50 meg default data cache,

Mark Maslow wrote:

> The columns being joined are both char(25).
>
> There was no index on littletable (why would a 3 row table need an index?).
> Just for yucks, I tried indexing the column in littletable. The plan still
> shows a scan of bigtable - but now uses only the index on littletable (no
> longer needs to read base table). That's not exactly a huge performance
> boost.
>
> What's a 16k i/o pool?
>
> Mark Maslow
>
> Peter Veilleux <pveilleux@nvestservices.com> wrote in message
> news:390F2508.1766C43B@nvestservices.com...
> > Mark,
> >
> > what are we looking at here for the datatypes of the 2 columns being
> joined?
> > Is there a useful index on littletable? Also, do you have any 16k i/o
> pools
> > defined?
> >
> > Peter
> >
> > Mark Maslow wrote:
> >
> > > We are using ASE 11.0.3.3.
> > >
> > > We have one table with about 2.5 million rows. One of the indexes is on
> a
> > > char(25) column.
> > >
> > > If we do a query like:
> > >
> > > SELECT * FROM bigtable WHERE indexedcolumn IN ("ABC","DEF",GHI")
> > >
> > > the query uses the index, and all is well.
> > >
> > > However, if we build a table with 3 rows and issue a query like:
> > >
> > > SELECT * FROM bigtable, littletable WHERE
> > > bigtable.indexedcolumn=littletable.id
> > >
> > > Then the query plan says it will do a table scan of bigtable. And, of
> > > course, the query takes a LONG time.
> > >
> > > Is there some way to force ASE to do this query intelligently?
> > >
> > > TIA
> > >
> > > Mark Maslow
> > > Lead Programmer/Analyst
> > > Sierra Club
> >


Mark Maslow Posted on 2000-05-02 21:17:19.0Z
From: "Mark Maslow" <mark.maslow@sierraclub.org>
References: <YGJuJhGt$GA.269@forums.sybase.com> <390F2508.1766C43B@nvestservices.com> <6b2yFpGt$GA.263@forums.sybase.com> <390F3999.37589EE4@nvestservices.com>
Subject: Re: Inefficient Query Plan
Date: Tue, 2 May 2000 14:17:19 -0700
Lines: 130
Organization: Sierra Club
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <Qx2DHzHt$GA.182@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: machine001.sierraclub.org 207.90.163.1
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2214
Article PK: 1089648

Not really clear on what it all means, but the output from sp_cacheconfig
follows. What, exactly, is your recommendation for a next step? Sorry to
be so dense, but it would be helpful if you could give the actual syntax.

Thanks very much for your help

Mark Maslow

Cache Name Status Type Config Value Run Value
------------------ ------ ------- ------------ ------------
default data cache Active Default 0.00 Mb 138.94 Mb

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

Total 0.00 Mb 138.94 Mb

==========================================================================

Cache: default data cache, Status: Active, Type: Default

Config Size: 0.00 Mb, Run Size: 138.94 Mb

IO Size Wash Size Config Size Run Size
-------- --------- ------------ ------------
2 Kb 512 Kb 0.00 Mb 118.94 Mb
4 Kb 1024 Kb 10.00 Mb 10.00 Mb
16 Kb 4096 Kb 10.00 Mb 10.00 Mb

Peter Veilleux <pveilleux@nvestservices.com> wrote in message
news:390F3999.37589EE4@nvestservices.com...
> Mark,
>
> every data cache has one or more buffer pools. The default is a 2k i/o
pool.
> What this does is it reads in a page at a time. A 16k i/o pool will read
in 8
> pages. It's not quite prefetch, but it treats the 16k i/o pool as one
unit,
> allowing for large i/o. You can read more about this in Sybooks
> (sybooks.sybase.com).
>
> Anyway, run sp_cacheconfig "default data cache" to see if you have a 16k
i/o
> pool defined. You can also replace "default data cache" with a named
cache, if
> you have one. There was a bug/design limitation in 11.0.x with huge
tables and
> 16k i/o. There is a trace flag you can turn on to change how the
optimizer
> handles data/index page clustering ratios, which was the root of the
problem.
> But I'd like to verify you have a 16k i/o pool first. If not, I suggest
you
> turn on 302, 310, 317 and showplan for this and post it (the 317 shouldn't
make
> the output too large in this case).
>
> And for future reference, post these questions to
> sybase.public.sqlserver.performance+tuning. I know the optimizer guys
hang out
> in there more than in the NT newsgroup....
>
> Peter
>
> So, if you have a 50 meg default data cache,
>
> Mark Maslow wrote:
>
> > The columns being joined are both char(25).
> >
> > There was no index on littletable (why would a 3 row table need an
index?).
> > Just for yucks, I tried indexing the column in littletable. The plan
still
> > shows a scan of bigtable - but now uses only the index on littletable
(no
> > longer needs to read base table). That's not exactly a huge performance
> > boost.
> >
> > What's a 16k i/o pool?
> >
> > Mark Maslow
> >
> > Peter Veilleux <pveilleux@nvestservices.com> wrote in message
> > news:390F2508.1766C43B@nvestservices.com...
> > > Mark,
> > >
> > > what are we looking at here for the datatypes of the 2 columns being
> > joined?
> > > Is there a useful index on littletable? Also, do you have any 16k i/o
> > pools
> > > defined?
> > >
> > > Peter
> > >
> > > Mark Maslow wrote:
> > >
> > > > We are using ASE 11.0.3.3.
> > > >
> > > > We have one table with about 2.5 million rows. One of the indexes
is on
> > a
> > > > char(25) column.
> > > >
> > > > If we do a query like:
> > > >
> > > > SELECT * FROM bigtable WHERE indexedcolumn IN ("ABC","DEF",GHI")
> > > >
> > > > the query uses the index, and all is well.
> > > >
> > > > However, if we build a table with 3 rows and issue a query like:
> > > >
> > > > SELECT * FROM bigtable, littletable WHERE
> > > > bigtable.indexedcolumn=littletable.id
> > > >
> > > > Then the query plan says it will do a table scan of bigtable. And,
of
> > > > course, the query takes a LONG time.
> > > >
> > > > Is there some way to force ASE to do this query intelligently?
> > > >
> > > > TIA
> > > >
> > > > Mark Maslow
> > > > Lead Programmer/Analyst
> > > > Sierra Club
> > >
>


Peter Veilleux Posted on 2000-05-03 12:49:07.0Z
Message-ID: <39102043.85C2C951@nvestservices.com>
Date: Wed, 03 May 2000 08:49:07 -0400
From: Peter Veilleux <pveilleux@nvestservices.com>
X-Mailer: Mozilla 4.72 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Inefficient Query Plan
References: <YGJuJhGt$GA.269@forums.sybase.com> <390F2508.1766C43B@nvestservices.com> <6b2yFpGt$GA.263@forums.sybase.com> <390F3999.37589EE4@nvestservices.com> <Qx2DHzHt$GA.182@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 148
NNTP-Posting-Host: nefclient149.mutualfunds.com 206.136.227.149
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2206
Article PK: 1089639

I did give you the syntax: sp_cacheconfig "default data cache"

anyway, you do have a 16k i/o pool configured. What this output is saying is,
within default data cache, i/o is going to be performed 3 ways: in 2k chunks, 4k
chunks (usually used for the log) and 16k chunks. The config size for 2k is 0
because you can't really configure this. It is calculated automagically by the
server, something like (total memory - overhead - proc cache %age = default data
cache). Run size is what is currently going on in the server.

so, let's try removing this pool and re-running the query. I'll give you the
syntax to remove the pool and to add it back:

sp_poolconfig "default data cache", "0M", "16K" - this is saying, in default
data cache, make the 16k i/o pool 0 meg

sp_poolconfig "default data cache", "10M", "16K" - this is saying, in default
data cache, create a 16k i/o pool of 10 meg

pls note all of this affects the 2k i/o pool, os when you remove it, the 2k i/o
pool will grow by 10 meg.

Bret offers some good advice in his posts. When the site is back up, I would
grab that optimizer doc. Eric Miner did a great job writing that. If this is
the problem I think it is, the forceindex is a good workaround for the problem.
For some people, it is the only solution, depending on how they need their
caches configured. So, if the best thing for your business is to have a 16k i/o
pool, then code in the forceindex. Unless it's a dynamic query....but I
digress....

good luck....

Peter

Mark Maslow wrote:

> Not really clear on what it all means, but the output from sp_cacheconfig
> follows. What, exactly, is your recommendation for a next step? Sorry to
> be so dense, but it would be helpful if you could give the actual syntax.
>
> Thanks very much for your help
>
> Mark Maslow
>
> Cache Name Status Type Config Value Run Value
> ------------------ ------ ------- ------------ ------------
> default data cache Active Default 0.00 Mb 138.94 Mb
>
> ------------ ------------
>
> Total 0.00 Mb 138.94 Mb
>
> ==========================================================================
>
> Cache: default data cache, Status: Active, Type: Default
>
> Config Size: 0.00 Mb, Run Size: 138.94 Mb
>
> IO Size Wash Size Config Size Run Size
> -------- --------- ------------ ------------
> 2 Kb 512 Kb 0.00 Mb 118.94 Mb
> 4 Kb 1024 Kb 10.00 Mb 10.00 Mb
> 16 Kb 4096 Kb 10.00 Mb 10.00 Mb
>
> Peter Veilleux <pveilleux@nvestservices.com> wrote in message
> news:390F3999.37589EE4@nvestservices.com...
> > Mark,
> >
> > every data cache has one or more buffer pools. The default is a 2k i/o
> pool.
> > What this does is it reads in a page at a time. A 16k i/o pool will read
> in 8
> > pages. It's not quite prefetch, but it treats the 16k i/o pool as one
> unit,
> > allowing for large i/o. You can read more about this in Sybooks
> > (sybooks.sybase.com).
> >
> > Anyway, run sp_cacheconfig "default data cache" to see if you have a 16k
> i/o
> > pool defined. You can also replace "default data cache" with a named
> cache, if
> > you have one. There was a bug/design limitation in 11.0.x with huge
> tables and
> > 16k i/o. There is a trace flag you can turn on to change how the
> optimizer
> > handles data/index page clustering ratios, which was the root of the
> problem.
> > But I'd like to verify you have a 16k i/o pool first. If not, I suggest
> you
> > turn on 302, 310, 317 and showplan for this and post it (the 317 shouldn't
> make
> > the output too large in this case).
> >
> > And for future reference, post these questions to
> > sybase.public.sqlserver.performance+tuning. I know the optimizer guys
> hang out
> > in there more than in the NT newsgroup....
> >
> > Peter
> >
> > So, if you have a 50 meg default data cache,
> >
> > Mark Maslow wrote:
> >
> > > The columns being joined are both char(25).
> > >
> > > There was no index on littletable (why would a 3 row table need an
> index?).
> > > Just for yucks, I tried indexing the column in littletable. The plan
> still
> > > shows a scan of bigtable - but now uses only the index on littletable
> (no
> > > longer needs to read base table). That's not exactly a huge performance
> > > boost.
> > >
> > > What's a 16k i/o pool?
> > >
> > > Mark Maslow
> > >
> > > Peter Veilleux <pveilleux@nvestservices.com> wrote in message
> > > news:390F2508.1766C43B@nvestservices.com...
> > > > Mark,
> > > >
> > > > what are we looking at here for the datatypes of the 2 columns being
> > > joined?
> > > > Is there a useful index on littletable? Also, do you have any 16k i/o
> > > pools
> > > > defined?
> > > >
> > > > Peter
> > > >
> > > > Mark Maslow wrote:
> > > >
> > > > > We are using ASE 11.0.3.3.
> > > > >
> > > > > We have one table with about 2.5 million rows. One of the indexes
> is on
> > > a
> > > > > char(25) column.
> > > > >
> > > > > If we do a query like:
> > > > >
> > > > > SELECT * FROM bigtable WHERE indexedcolumn IN ("ABC","DEF",GHI")
> > > > >
> > > > > the query uses the index, and all is well.
> > > > >
> > > > > However, if we build a table with 3 rows and issue a query like:
> > > > >
> > > > > SELECT * FROM bigtable, littletable WHERE
> > > > > bigtable.indexedcolumn=littletable.id
> > > > >
> > > > > Then the query plan says it will do a table scan of bigtable. And,
> of
> > > > > course, the query takes a LONG time.
> > > > >
> > > > > Is there some way to force ASE to do this query intelligently?
> > > > >
> > > > > TIA
> > > > >
> > > > > Mark Maslow
> > > > > Lead Programmer/Analyst
> > > > > Sierra Club
> > > >
> >


Mark Maslow Posted on 2000-05-03 19:40:45.0Z
From: "Mark Maslow" <mark.maslow@sierraclub.org>
References: <YGJuJhGt$GA.269@forums.sybase.com> <390F2508.1766C43B@nvestservices.com> <6b2yFpGt$GA.263@forums.sybase.com> <390F3999.37589EE4@nvestservices.com> <Qx2DHzHt$GA.182@forums.sybase.com> <39102043.85C2C951@nvestservices.com>
Subject: Re: Inefficient Query Plan
Date: Wed, 3 May 2000 12:40:45 -0700
Lines: 219
Organization: Sierra Club
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <8rlt1hTt$GA.261@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: machine001.sierraclub.org 207.90.163.1
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2202
Article PK: 1089635

Thanks very much for all of the information. Will try removing the 16K pool
to see what happens.

Unfortunately, forceindex merely results in scanning the entire table by
index, positioning at the start of the index, resulting in an even longer
running query.

I suspect that the problem has to do with the fact that the index in
question has a high number of duplicates (high density). The optimizer does
get it right if I supply a list of values using IN, but the query time goes
from a couple of seconds to some 20 minutes if the list of values is in a
joined table instead of a list of values, apparently because the optimizer
has been told to always do a scan rather than use an index that has a high
density to do a join.

Has this been refined in later releases of ASE?

Mark Maslow

Peter Veilleux <pveilleux@nvestservices.com> wrote in message
news:39102043.85C2C951@nvestservices.com...
> I did give you the syntax: sp_cacheconfig "default data cache"
>
> anyway, you do have a 16k i/o pool configured. What this output is saying
is,
> within default data cache, i/o is going to be performed 3 ways: in 2k
chunks, 4k
> chunks (usually used for the log) and 16k chunks. The config size for 2k
is 0
> because you can't really configure this. It is calculated automagically
by the
> server, something like (total memory - overhead - proc cache %age =
default data
> cache). Run size is what is currently going on in the server.
>
> so, let's try removing this pool and re-running the query. I'll give you
the
> syntax to remove the pool and to add it back:
>
> sp_poolconfig "default data cache", "0M", "16K" - this is saying, in
default
> data cache, make the 16k i/o pool 0 meg
>
> sp_poolconfig "default data cache", "10M", "16K" - this is saying, in
default
> data cache, create a 16k i/o pool of 10 meg
>
> pls note all of this affects the 2k i/o pool, os when you remove it, the
2k i/o
> pool will grow by 10 meg.
>
> Bret offers some good advice in his posts. When the site is back up, I
would
> grab that optimizer doc. Eric Miner did a great job writing that. If
this is
> the problem I think it is, the forceindex is a good workaround for the
problem.
> For some people, it is the only solution, depending on how they need their
> caches configured. So, if the best thing for your business is to have a
16k i/o
> pool, then code in the forceindex. Unless it's a dynamic query....but I
> digress....
>
> good luck....
>
> Peter
>
> Mark Maslow wrote:
>
> > Not really clear on what it all means, but the output from
sp_cacheconfig
> > follows. What, exactly, is your recommendation for a next step? Sorry
to
> > be so dense, but it would be helpful if you could give the actual
syntax.
> >
> > Thanks very much for your help
> >
> > Mark Maslow
> >
> > Cache Name Status Type Config Value Run Value
> > ------------------ ------ ------- ------------ ------------
> > default data cache Active Default 0.00 Mb 138.94 Mb
> >
>
> ------------ ----------
--
> >
> > Total 0.00 Mb
138.94 Mb
> >
> >
==========================================================================
> >
> > Cache: default data cache, Status: Active, Type: Default
> >
> > Config Size: 0.00 Mb, Run Size: 138.94 Mb
> >
> > IO Size Wash Size Config Size Run Size
> > -------- --------- ------------ ------------
> > 2 Kb 512 Kb 0.00 Mb 118.94 Mb
> > 4 Kb 1024 Kb 10.00 Mb 10.00 Mb
> > 16 Kb 4096 Kb 10.00 Mb 10.00 Mb
> >
> > Peter Veilleux <pveilleux@nvestservices.com> wrote in message
> > news:390F3999.37589EE4@nvestservices.com...
> > > Mark,
> > >
> > > every data cache has one or more buffer pools. The default is a 2k
i/o
> > pool.
> > > What this does is it reads in a page at a time. A 16k i/o pool will
read
> > in 8
> > > pages. It's not quite prefetch, but it treats the 16k i/o pool as one
> > unit,
> > > allowing for large i/o. You can read more about this in Sybooks
> > > (sybooks.sybase.com).
> > >
> > > Anyway, run sp_cacheconfig "default data cache" to see if you have a
16k
> > i/o
> > > pool defined. You can also replace "default data cache" with a named
> > cache, if
> > > you have one. There was a bug/design limitation in 11.0.x with huge
> > tables and
> > > 16k i/o. There is a trace flag you can turn on to change how the
> > optimizer
> > > handles data/index page clustering ratios, which was the root of the
> > problem.
> > > But I'd like to verify you have a 16k i/o pool first. If not, I
suggest
> > you
> > > turn on 302, 310, 317 and showplan for this and post it (the 317
shouldn't
> > make
> > > the output too large in this case).
> > >
> > > And for future reference, post these questions to
> > > sybase.public.sqlserver.performance+tuning. I know the optimizer guys
> > hang out
> > > in there more than in the NT newsgroup....
> > >
> > > Peter
> > >
> > > So, if you have a 50 meg default data cache,
> > >
> > > Mark Maslow wrote:
> > >
> > > > The columns being joined are both char(25).
> > > >
> > > > There was no index on littletable (why would a 3 row table need an
> > index?).
> > > > Just for yucks, I tried indexing the column in littletable. The
plan
> > still
> > > > shows a scan of bigtable - but now uses only the index on
littletable
> > (no
> > > > longer needs to read base table). That's not exactly a huge
performance
> > > > boost.
> > > >
> > > > What's a 16k i/o pool?
> > > >
> > > > Mark Maslow
> > > >
> > > > Peter Veilleux <pveilleux@nvestservices.com> wrote in message
> > > > news:390F2508.1766C43B@nvestservices.com...
> > > > > Mark,
> > > > >
> > > > > what are we looking at here for the datatypes of the 2 columns
being
> > > > joined?
> > > > > Is there a useful index on littletable? Also, do you have any 16k
i/o
> > > > pools
> > > > > defined?
> > > > >
> > > > > Peter
> > > > >
> > > > > Mark Maslow wrote:
> > > > >
> > > > > > We are using ASE 11.0.3.3.
> > > > > >
> > > > > > We have one table with about 2.5 million rows. One of the
indexes
> > is on
> > > > a
> > > > > > char(25) column.
> > > > > >
> > > > > > If we do a query like:
> > > > > >
> > > > > > SELECT * FROM bigtable WHERE indexedcolumn IN ("ABC","DEF",GHI")
> > > > > >
> > > > > > the query uses the index, and all is well.
> > > > > >
> > > > > > However, if we build a table with 3 rows and issue a query like:
> > > > > >
> > > > > > SELECT * FROM bigtable, littletable WHERE
> > > > > > bigtable.indexedcolumn=littletable.id
> > > > > >
> > > > > > Then the query plan says it will do a table scan of bigtable.
And,
> > of
> > > > > > course, the query takes a LONG time.
> > > > > >
> > > > > > Is there some way to force ASE to do this query intelligently?
> > > > > >
> > > > > > TIA
> > > > > >
> > > > > > Mark Maslow
> > > > > > Lead Programmer/Analyst
> > > > > > Sierra Club
> > > > >
> > >
>


Peter Veilleux Posted on 2000-05-03 19:54:48.0Z
Message-ID: <39108408.96E8E42@nvestservices.com>
Date: Wed, 03 May 2000 15:54:48 -0400
From: Peter Veilleux <pveilleux@nvestservices.com>
X-Mailer: Mozilla 4.72 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Inefficient Query Plan
References: <YGJuJhGt$GA.269@forums.sybase.com> <390F2508.1766C43B@nvestservices.com> <6b2yFpGt$GA.263@forums.sybase.com> <390F3999.37589EE4@nvestservices.com> <Qx2DHzHt$GA.182@forums.sybase.com> <39102043.85C2C951@nvestservices.com> <8rlt1hTt$GA.261@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 224
NNTP-Posting-Host: nefclient149.mutualfunds.com 206.136.227.149
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2201
Article PK: 1089632

Mark,

I think you just solved your own problem. I would still try the 16k thing, but
let me explain.....

the high density of your table.column is the key. Remember, this is a
cost-based optimizer Sybase has. Appearantly, it has determined that it is much
cheaper, in terms of i/o, to table scan TableL for every row in TableS, rather
than go up and down the index tree for each row in TableS. And, there is a way
to see how the optimizer was thinking. Run the following statements. Could you
send me the output? You can send it directly, or you can post it:

dbcc traceon(3604, 302, 310, 317)
go
set showplan on
go
YOUR QUERY HERE
go
set showplan off
go
dbcc traceoff(3604, 302, 310, 317)
go

it's much easier for me to explain it to you if I have something to look at in
front of me.

As far as future releases handling density, go to 11.9.x or 12.0. The optimizer
now reads stats from 2 system tables, systabstats and sysstatistics, which store
much, much more information than the distribution page. You can use optdiag to
see what is stored for density, cluster ratios, etc. Read up on how to
interpret the histograms. Lots of good info on optdiag on the web site. But in
a nutshell, you need at least 11.9.x to handle density issues better.

HTH,
Peter

Mark Maslow wrote:

> Thanks very much for all of the information. Will try removing the 16K pool
> to see what happens.
>
> Unfortunately, forceindex merely results in scanning the entire table by
> index, positioning at the start of the index, resulting in an even longer
> running query.
>
> I suspect that the problem has to do with the fact that the index in
> question has a high number of duplicates (high density). The optimizer does
> get it right if I supply a list of values using IN, but the query time goes
> from a couple of seconds to some 20 minutes if the list of values is in a
> joined table instead of a list of values, apparently because the optimizer
> has been told to always do a scan rather than use an index that has a high
> density to do a join.
>
> Has this been refined in later releases of ASE?
>
> Mark Maslow
>
> Peter Veilleux <pveilleux@nvestservices.com> wrote in message
> news:39102043.85C2C951@nvestservices.com...
> > I did give you the syntax: sp_cacheconfig "default data cache"
> >
> > anyway, you do have a 16k i/o pool configured. What this output is saying
> is,
> > within default data cache, i/o is going to be performed 3 ways: in 2k
> chunks, 4k
> > chunks (usually used for the log) and 16k chunks. The config size for 2k
> is 0
> > because you can't really configure this. It is calculated automagically
> by the
> > server, something like (total memory - overhead - proc cache %age =
> default data
> > cache). Run size is what is currently going on in the server.
> >
> > so, let's try removing this pool and re-running the query. I'll give you
> the
> > syntax to remove the pool and to add it back:
> >
> > sp_poolconfig "default data cache", "0M", "16K" - this is saying, in
> default
> > data cache, make the 16k i/o pool 0 meg
> >
> > sp_poolconfig "default data cache", "10M", "16K" - this is saying, in
> default
> > data cache, create a 16k i/o pool of 10 meg
> >
> > pls note all of this affects the 2k i/o pool, os when you remove it, the
> 2k i/o
> > pool will grow by 10 meg.
> >
> > Bret offers some good advice in his posts. When the site is back up, I
> would
> > grab that optimizer doc. Eric Miner did a great job writing that. If
> this is
> > the problem I think it is, the forceindex is a good workaround for the
> problem.
> > For some people, it is the only solution, depending on how they need their
> > caches configured. So, if the best thing for your business is to have a
> 16k i/o
> > pool, then code in the forceindex. Unless it's a dynamic query....but I
> > digress....
> >
> > good luck....
> >
> > Peter
> >
> > Mark Maslow wrote:
> >
> > > Not really clear on what it all means, but the output from
> sp_cacheconfig
> > > follows. What, exactly, is your recommendation for a next step? Sorry
> to
> > > be so dense, but it would be helpful if you could give the actual
> syntax.
> > >
> > > Thanks very much for your help
> > >
> > > Mark Maslow
> > >
> > > Cache Name Status Type Config Value Run Value
> > > ------------------ ------ ------- ------------ ------------
> > > default data cache Active Default 0.00 Mb 138.94 Mb
> > >
> >
> > ------------ ----------
> --
> > >
> > > Total 0.00 Mb
> 138.94 Mb
> > >
> > >
> ==========================================================================
> > >
> > > Cache: default data cache, Status: Active, Type: Default
> > >
> > > Config Size: 0.00 Mb, Run Size: 138.94 Mb
> > >
> > > IO Size Wash Size Config Size Run Size
> > > -------- --------- ------------ ------------
> > > 2 Kb 512 Kb 0.00 Mb 118.94 Mb
> > > 4 Kb 1024 Kb 10.00 Mb 10.00 Mb
> > > 16 Kb 4096 Kb 10.00 Mb 10.00 Mb
> > >
> > > Peter Veilleux <pveilleux@nvestservices.com> wrote in message
> > > news:390F3999.37589EE4@nvestservices.com...
> > > > Mark,
> > > >
> > > > every data cache has one or more buffer pools. The default is a 2k
> i/o
> > > pool.
> > > > What this does is it reads in a page at a time. A 16k i/o pool will
> read
> > > in 8
> > > > pages. It's not quite prefetch, but it treats the 16k i/o pool as one
> > > unit,
> > > > allowing for large i/o. You can read more about this in Sybooks
> > > > (sybooks.sybase.com).
> > > >
> > > > Anyway, run sp_cacheconfig "default data cache" to see if you have a
> 16k
> > > i/o
> > > > pool defined. You can also replace "default data cache" with a named
> > > cache, if
> > > > you have one. There was a bug/design limitation in 11.0.x with huge
> > > tables and
> > > > 16k i/o. There is a trace flag you can turn on to change how the
> > > optimizer
> > > > handles data/index page clustering ratios, which was the root of the
> > > problem.
> > > > But I'd like to verify you have a 16k i/o pool first. If not, I
> suggest
> > > you
> > > > turn on 302, 310, 317 and showplan for this and post it (the 317
> shouldn't
> > > make
> > > > the output too large in this case).
> > > >
> > > > And for future reference, post these questions to
> > > > sybase.public.sqlserver.performance+tuning. I know the optimizer guys
> > > hang out
> > > > in there more than in the NT newsgroup....
> > > >
> > > > Peter
> > > >
> > > > So, if you have a 50 meg default data cache,
> > > >
> > > > Mark Maslow wrote:
> > > >
> > > > > The columns being joined are both char(25).
> > > > >
> > > > > There was no index on littletable (why would a 3 row table need an
> > > index?).
> > > > > Just for yucks, I tried indexing the column in littletable. The
> plan
> > > still
> > > > > shows a scan of bigtable - but now uses only the index on
> littletable
> > > (no
> > > > > longer needs to read base table). That's not exactly a huge
> performance
> > > > > boost.
> > > > >
> > > > > What's a 16k i/o pool?
> > > > >
> > > > > Mark Maslow
> > > > >
> > > > > Peter Veilleux <pveilleux@nvestservices.com> wrote in message
> > > > > news:390F2508.1766C43B@nvestservices.com...
> > > > > > Mark,
> > > > > >
> > > > > > what are we looking at here for the datatypes of the 2 columns
> being
> > > > > joined?
> > > > > > Is there a useful index on littletable? Also, do you have any 16k
> i/o
> > > > > pools
> > > > > > defined?
> > > > > >
> > > > > > Peter
> > > > > >
> > > > > > Mark Maslow wrote:
> > > > > >
> > > > > > > We are using ASE 11.0.3.3.
> > > > > > >
> > > > > > > We have one table with about 2.5 million rows. One of the
> indexes
> > > is on
> > > > > a
> > > > > > > char(25) column.
> > > > > > >
> > > > > > > If we do a query like:
> > > > > > >
> > > > > > > SELECT * FROM bigtable WHERE indexedcolumn IN ("ABC","DEF",GHI")
> > > > > > >
> > > > > > > the query uses the index, and all is well.
> > > > > > >
> > > > > > > However, if we build a table with 3 rows and issue a query like:
> > > > > > >
> > > > > > > SELECT * FROM bigtable, littletable WHERE
> > > > > > > bigtable.indexedcolumn=littletable.id
> > > > > > >
> > > > > > > Then the query plan says it will do a table scan of bigtable.
> And,
> > > of
> > > > > > > course, the query takes a LONG time.
> > > > > > >
> > > > > > > Is there some way to force ASE to do this query intelligently?
> > > > > > >
> > > > > > > TIA
> > > > > > >
> > > > > > > Mark Maslow
> > > > > > > Lead Programmer/Analyst
> > > > > > > Sierra Club
> > > > > >
> > > >
> >


Mark Maslow Posted on 2000-05-04 00:13:18.0Z
From: "Mark Maslow" <mark.maslow@sierraclub.org>
References: <YGJuJhGt$GA.269@forums.sybase.com> <390F2508.1766C43B@nvestservices.com> <6b2yFpGt$GA.263@forums.sybase.com> <390F3999.37589EE4@nvestservices.com> <Qx2DHzHt$GA.182@forums.sybase.com> <39102043.85C2C951@nvestservices.com> <8rlt1hTt$GA.261@forums.sybase.com> <39108408.96E8E42@nvestservices.com>
Subject: Re: Inefficient Query Plan
Date: Wed, 3 May 2000 17:13:18 -0700
Lines: 332
Organization: Sierra Club
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <u#3yI6Vt$GA.202@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: machine001.sierraclub.org 207.90.163.1
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2200
Article PK: 1089633

Well, this certainly has been educational. I am especially glad to know how
to determine which plans were considered by the optimizer before coming up
with the final one. That TechDoc about the optimizer is also highly
informative.

This all actually makes sense, now that I understand a little more about how
costing works. I guess I should explain the situation.

This 2.5 million row table has a unique key, which we'll call Id. From time
to time, we identify duplicate rows, and merge them. When this occurs, the
Id from the merged row is placed in another column of the retained row.
Most rows have never been merged into, so the column is empty.

I guess the optimizer can't know that the table of old Ids that I'm attempt
to join to does not include the "empty" id. If it did, the plan that the
optimizer comes up with would make perfect sense.

Interesting if I include the list of values directly in the query -
something like:

Id IN ("a","b","c")

the server actually uses the index just as I want it to.

Anyway, I think my question is now completely answered. Thanks *very* much
for all of this information.

Mark Maslow

Peter Veilleux <pveilleux@nvestservices.com> wrote in message
news:39108408.96E8E42@nvestservices.com...
> Mark,
>
> I think you just solved your own problem. I would still try the 16k
thing, but
> let me explain.....
>
> the high density of your table.column is the key. Remember, this is a
> cost-based optimizer Sybase has. Appearantly, it has determined that it
is much
> cheaper, in terms of i/o, to table scan TableL for every row in TableS,
rather
> than go up and down the index tree for each row in TableS. And, there is
a way
> to see how the optimizer was thinking. Run the following statements.
Could you
> send me the output? You can send it directly, or you can post it:
>
> dbcc traceon(3604, 302, 310, 317)
> go
> set showplan on
> go
> YOUR QUERY HERE
> go
> set showplan off
> go
> dbcc traceoff(3604, 302, 310, 317)
> go
>
> it's much easier for me to explain it to you if I have something to look
at in
> front of me.
>
> As far as future releases handling density, go to 11.9.x or 12.0. The
optimizer
> now reads stats from 2 system tables, systabstats and sysstatistics, which
store
> much, much more information than the distribution page. You can use
optdiag to
> see what is stored for density, cluster ratios, etc. Read up on how to
> interpret the histograms. Lots of good info on optdiag on the web site.
But in
> a nutshell, you need at least 11.9.x to handle density issues better.
>
> HTH,
> Peter
>
> Mark Maslow wrote:
>
> > Thanks very much for all of the information. Will try removing the 16K
pool
> > to see what happens.
> >
> > Unfortunately, forceindex merely results in scanning the entire table by
> > index, positioning at the start of the index, resulting in an even
longer
> > running query.
> >
> > I suspect that the problem has to do with the fact that the index in
> > question has a high number of duplicates (high density). The optimizer
does
> > get it right if I supply a list of values using IN, but the query time
goes
> > from a couple of seconds to some 20 minutes if the list of values is in
a
> > joined table instead of a list of values, apparently because the
optimizer
> > has been told to always do a scan rather than use an index that has a
high
> > density to do a join.
> >
> > Has this been refined in later releases of ASE?
> >
> > Mark Maslow
> >
> > Peter Veilleux <pveilleux@nvestservices.com> wrote in message
> > news:39102043.85C2C951@nvestservices.com...
> > > I did give you the syntax: sp_cacheconfig "default data cache"
> > >
> > > anyway, you do have a 16k i/o pool configured. What this output is
saying
> > is,
> > > within default data cache, i/o is going to be performed 3 ways: in 2k
> > chunks, 4k
> > > chunks (usually used for the log) and 16k chunks. The config size for
2k
> > is 0
> > > because you can't really configure this. It is calculated
automagically
> > by the
> > > server, something like (total memory - overhead - proc cache %age =
> > default data
> > > cache). Run size is what is currently going on in the server.
> > >
> > > so, let's try removing this pool and re-running the query. I'll give
you
> > the
> > > syntax to remove the pool and to add it back:
> > >
> > > sp_poolconfig "default data cache", "0M", "16K" - this is saying, in
> > default
> > > data cache, make the 16k i/o pool 0 meg
> > >
> > > sp_poolconfig "default data cache", "10M", "16K" - this is saying, in
> > default
> > > data cache, create a 16k i/o pool of 10 meg
> > >
> > > pls note all of this affects the 2k i/o pool, os when you remove it,
the
> > 2k i/o
> > > pool will grow by 10 meg.
> > >
> > > Bret offers some good advice in his posts. When the site is back up,
I
> > would
> > > grab that optimizer doc. Eric Miner did a great job writing that. If
> > this is
> > > the problem I think it is, the forceindex is a good workaround for the
> > problem.
> > > For some people, it is the only solution, depending on how they need
their
> > > caches configured. So, if the best thing for your business is to have
a
> > 16k i/o
> > > pool, then code in the forceindex. Unless it's a dynamic query....but
I
> > > digress....
> > >
> > > good luck....
> > >
> > > Peter
> > >
> > > Mark Maslow wrote:
> > >
> > > > Not really clear on what it all means, but the output from
> > sp_cacheconfig
> > > > follows. What, exactly, is your recommendation for a next step?
Sorry
> > to
> > > > be so dense, but it would be helpful if you could give the actual
> > syntax.
> > > >
> > > > Thanks very much for your help
> > > >
> > > > Mark Maslow
> > > >
> > > > Cache Name Status Type Config Value Run Value
> > > > ------------------ ------ ------- ------------ ------------
> > > > default data cache Active Default 0.00 Mb 138.94 Mb
> > > >
> > >
> >
> ------------ ----------
> > --
> > > >
> > > > Total 0.00 Mb
> > 138.94 Mb
> > > >
> > > >
> >
==========================================================================
> > > >
> > > > Cache: default data cache, Status: Active, Type: Default
> > > >
> > > > Config Size: 0.00 Mb, Run Size: 138.94 Mb
> > > >
> > > > IO Size Wash Size Config Size Run Size
> > > > -------- --------- ------------ ------------
> > > > 2 Kb 512 Kb 0.00 Mb 118.94 Mb
> > > > 4 Kb 1024 Kb 10.00 Mb 10.00 Mb
> > > > 16 Kb 4096 Kb 10.00 Mb 10.00 Mb
> > > >
> > > > Peter Veilleux <pveilleux@nvestservices.com> wrote in message
> > > > news:390F3999.37589EE4@nvestservices.com...
> > > > > Mark,
> > > > >
> > > > > every data cache has one or more buffer pools. The default is a
2k
> > i/o
> > > > pool.
> > > > > What this does is it reads in a page at a time. A 16k i/o pool
will
> > read
> > > > in 8
> > > > > pages. It's not quite prefetch, but it treats the 16k i/o pool as
one
> > > > unit,
> > > > > allowing for large i/o. You can read more about this in Sybooks
> > > > > (sybooks.sybase.com).
> > > > >
> > > > > Anyway, run sp_cacheconfig "default data cache" to see if you have
a
> > 16k
> > > > i/o
> > > > > pool defined. You can also replace "default data cache" with a
named
> > > > cache, if
> > > > > you have one. There was a bug/design limitation in 11.0.x with
huge
> > > > tables and
> > > > > 16k i/o. There is a trace flag you can turn on to change how the
> > > > optimizer
> > > > > handles data/index page clustering ratios, which was the root of
the
> > > > problem.
> > > > > But I'd like to verify you have a 16k i/o pool first. If not, I
> > suggest
> > > > you
> > > > > turn on 302, 310, 317 and showplan for this and post it (the 317
> > shouldn't
> > > > make
> > > > > the output too large in this case).
> > > > >
> > > > > And for future reference, post these questions to
> > > > > sybase.public.sqlserver.performance+tuning. I know the optimizer
guys
> > > > hang out
> > > > > in there more than in the NT newsgroup....
> > > > >
> > > > > Peter
> > > > >
> > > > > So, if you have a 50 meg default data cache,
> > > > >
> > > > > Mark Maslow wrote:
> > > > >
> > > > > > The columns being joined are both char(25).
> > > > > >
> > > > > > There was no index on littletable (why would a 3 row table need
an
> > > > index?).
> > > > > > Just for yucks, I tried indexing the column in littletable. The
> > plan
> > > > still
> > > > > > shows a scan of bigtable - but now uses only the index on
> > littletable
> > > > (no
> > > > > > longer needs to read base table). That's not exactly a huge
> > performance
> > > > > > boost.
> > > > > >
> > > > > > What's a 16k i/o pool?
> > > > > >
> > > > > > Mark Maslow
> > > > > >
> > > > > > Peter Veilleux <pveilleux@nvestservices.com> wrote in message
> > > > > > news:390F2508.1766C43B@nvestservices.com...
> > > > > > > Mark,
> > > > > > >
> > > > > > > what are we looking at here for the datatypes of the 2 columns
> > being
> > > > > > joined?
> > > > > > > Is there a useful index on littletable? Also, do you have any
16k
> > i/o
> > > > > > pools
> > > > > > > defined?
> > > > > > >
> > > > > > > Peter
> > > > > > >
> > > > > > > Mark Maslow wrote:
> > > > > > >
> > > > > > > > We are using ASE 11.0.3.3.
> > > > > > > >
> > > > > > > > We have one table with about 2.5 million rows. One of the
> > indexes
> > > > is on
> > > > > > a
> > > > > > > > char(25) column.
> > > > > > > >
> > > > > > > > If we do a query like:
> > > > > > > >
> > > > > > > > SELECT * FROM bigtable WHERE indexedcolumn IN
("ABC","DEF",GHI")
> > > > > > > >
> > > > > > > > the query uses the index, and all is well.
> > > > > > > >
> > > > > > > > However, if we build a table with 3 rows and issue a query
like:
> > > > > > > >
> > > > > > > > SELECT * FROM bigtable, littletable WHERE
> > > > > > > > bigtable.indexedcolumn=littletable.id
> > > > > > > >
> > > > > > > > Then the query plan says it will do a table scan of
bigtable.
> > And,
> > > > of
> > > > > > > > course, the query takes a LONG time.
> > > > > > > >
> > > > > > > > Is there some way to force ASE to do this query
intelligently?
> > > > > > > >
> > > > > > > > TIA
> > > > > > > >
> > > > > > > > Mark Maslow
> > > > > > > > Lead Programmer/Analyst
> > > > > > > > Sierra Club
> > > > > > >
> > > > >
> > >
>


"Mark A. Parsons" <pegasys Posted on 2000-05-02 20:07:16.0Z
Message-ID: <390F3574.22BA9CE@_internet.co.nz>
Date: Wed, 03 May 2000 08:07:16 +1200
From: "Mark A. Parsons" <pegasys@_internet.co.nz>
Organization: Pegasys Ltd
X-Mailer: Mozilla 4.72 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Inefficient Query Plan
References: <YGJuJhGt$GA.269@forums.sybase.com> <390F2508.1766C43B@nvestservices.com> <6b2yFpGt$GA.263@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 36
NNTP-Posting-Host: p448.ipa1-n8-16.iconz.net.nz 210.48.25.192
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2218
Article PK: 1089652

> The columns being joined are both char(25).

One other consideration ...

If a column is defined as 'char(25)' and NULLable ... then it's stored
internally as a 'varchar(25)'. (duh, Mark!?)

If another column is defined as 'char(25)' and NOT NULLable ... then
it's stored internally as a 'char(25)'.

At this point, trying to join the 2 dislike-type columns in a query may
cause some problems with the optimizer's query plan. (Granted, you'd
think it was smart enough to see that one table has 3 rows and another
2.5 million rows?)

So an additional question ...

Are *BOTH* columns of the same NULLability, i.e., are both 'NULL', or
both 'NOT NULL'?

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

One other question ... are index statistics are up to date on the 2.5
million row table, i.e., does the optimizer *know* that this table does,
in fact, have 2.5 million rows? For example ... if the table had been
created, index(es) built on said table with no data, then data loaded
into table ... without running 'update statistics' after the data load,
the table looks (to the optimizer) like it doesn't have any data. (This
doesn't happen if the index was created after the data load since a
'create index', in effect, does an 'update statistics' as it creates the
index.)

--
Mark A. Parsons

Iron Horse, Inc. iron_horse@compuserve.com
Pegasys (200), Ltd pegasys@internet.co.nz


Mark Maslow Posted on 2000-05-02 20:21:51.0Z
From: "Mark Maslow" <mark.maslow@sierraclub.org>
References: <YGJuJhGt$GA.269@forums.sybase.com> <390F2508.1766C43B@nvestservices.com> <6b2yFpGt$GA.263@forums.sybase.com> <390F3574.22BA9CE@_internet.co.nz>
Subject: Re: Inefficient Query Plan
Date: Tue, 2 May 2000 13:21:51 -0700
Lines: 55
Organization: Sierra Club
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <#hWVHUHt$GA.185@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: machine001.sierraclub.org 207.90.163.1
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2216
Article PK: 1089649

In bigtable, the column is defined as char(25) NULL. So I created the Id
column in littletable the same. Same result - ascending table scan of
bigtable positioning at start of table.

Statistics have been updated fairly recently. And this table started out
(after a conversion several years ago) with nearly 2 million rows anyway -
it's not like this table suddenly went from 0 to 2 mil recently.

ASE, or at least the version we're running, just seems to go braindead in
some situations. Real bummer - this one doesn't appear to have any
workaround.

Mark Maslow

Mark A. Parsons <pegasys@_internet.co.nz> wrote in message
news:390F3574.22BA9CE@_internet.co.nz...
> > The columns being joined are both char(25).
>
> One other consideration ...
>
> If a column is defined as 'char(25)' and NULLable ... then it's stored
> internally as a 'varchar(25)'. (duh, Mark!?)
>
> If another column is defined as 'char(25)' and NOT NULLable ... then
> it's stored internally as a 'char(25)'.
>
> At this point, trying to join the 2 dislike-type columns in a query may
> cause some problems with the optimizer's query plan. (Granted, you'd
> think it was smart enough to see that one table has 3 rows and another
> 2.5 million rows?)
>
> So an additional question ...
>
> Are *BOTH* columns of the same NULLability, i.e., are both 'NULL', or
> both 'NOT NULL'?
>
> -----------------
>
> One other question ... are index statistics are up to date on the 2.5
> million row table, i.e., does the optimizer *know* that this table does,
> in fact, have 2.5 million rows? For example ... if the table had been
> created, index(es) built on said table with no data, then data loaded
> into table ... without running 'update statistics' after the data load,
> the table looks (to the optimizer) like it doesn't have any data. (This
> doesn't happen if the index was created after the data load since a
> 'create index', in effect, does an 'update statistics' as it creates the
> index.)
>
> --
> Mark A. Parsons
>
> Iron Horse, Inc. iron_horse@compuserve.com
> Pegasys (200), Ltd pegasys@internet.co.nz


Bret Halford Posted on 2000-05-02 21:55:14.0Z
Message-ID: <390F4EC2.2B279639@sybase.com>
Date: Tue, 02 May 2000 15:55:14 -0600
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en,ja
MIME-Version: 1.0
Subject: Re: Inefficient Query Plan
References: <YGJuJhGt$GA.269@forums.sybase.com> <390F2508.1766C43B@nvestservices.com> <6b2yFpGt$GA.263@forums.sybase.com> <390F3574.22BA9CE@_internet.co.nz> <#hWVHUHt$GA.185@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 32
NNTP-Posting-Host: bret-pc.sybase.com 157.133.80.211
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2213
Article PK: 1089645


Mark Maslow wrote:

> In bigtable, the column is defined as char(25) NULL. So I created the Id
> column in littletable the same. Same result - ascending table scan of
> bigtable positioning at start of table.
>
> Statistics have been updated fairly recently. And this table started out
> (after a conversion several years ago) with nearly 2 million rows anyway -
> it's not like this table suddenly went from 0 to 2 mil recently.
>
> ASE, or at least the version we're running, just seems to go braindead in
> some situations. Real bummer - this one doesn't appear to have any
> workaround.
>

Hi Mark,

Have you checked out Technote 3602 "Analyzing and Resolving Optimizer
Problems" (available at http://techinfo.sybase.com)?

Have you tried the FORCEINDEX syntax? (In 11.0.x, put the index-id (indid
from sysindexes) of
the index that should be used in parenthesis after the tablename in the from
clause. In 11.5 and higher,
use the new forceindex syntax of keyword "index" followed by index name, all
in parenthesis ie (index c_mytable). In 12.0, check out the entire "Abstract
Plans" feature.

SELECT * FROM bigtable (1), littletable WHERE
bigtable.indexedcolumn=littletable.id

-bret


Mark Maslow Posted on 2000-05-02 22:14:55.0Z
From: "Mark Maslow" <mark.maslow@sierraclub.org>
References: <YGJuJhGt$GA.269@forums.sybase.com> <390F2508.1766C43B@nvestservices.com> <6b2yFpGt$GA.263@forums.sybase.com> <390F3574.22BA9CE@_internet.co.nz> <#hWVHUHt$GA.185@forums.sybase.com> <390F4EC2.2B279639@sybase.com>
Subject: Re: Inefficient Query Plan
Date: Tue, 2 May 2000 15:14:55 -0700
Lines: 16
Organization: Sierra Club
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <EZXATTIt$GA.263@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: machine001.sierraclub.org 207.90.163.1
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2210
Article PK: 1089642

Had no luck finding Technote 3602, or searching tech docs for "Optimizer".
If you can provide a URL where I can find this, it would be much
appreciated.

Thanks.

Mark Maslow

> Hi Mark,
>
> Have you checked out Technote 3602 "Analyzing and Resolving Optimizer
> Problems" (available at http://techinfo.sybase.com)?
>


Bret Halford Posted on 2000-05-02 23:29:22.0Z
Message-ID: <390F64D2.EF6AFCDD@sybase.com>
Date: Tue, 02 May 2000 17:29:22 -0600
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en,ja
MIME-Version: 1.0
Subject: Re: Inefficient Query Plan
References: <YGJuJhGt$GA.269@forums.sybase.com> <390F2508.1766C43B@nvestservices.com> <6b2yFpGt$GA.263@forums.sybase.com> <390F3574.22BA9CE@_internet.co.nz> <#hWVHUHt$GA.185@forums.sybase.com> <390F4EC2.2B279639@sybase.com> <EZXATTIt$GA.263@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 14
NNTP-Posting-Host: bret-pc.sybase.com 157.133.80.211
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2208
Article PK: 1089640

The techinfo site seems to be acting strangely today, I can't get to the
technote either.
I'm looking to see if I have any copies floating around.

-bret

Mark Maslow wrote:

> Had no luck finding Technote 3602, or searching tech docs for "Optimizer".
> If you can provide a URL where I can find this, it would be much
> appreciated.
>
> Thanks.
>
> Mark Maslow
>
> > Hi Mark,
> >
> > Have you checked out Technote 3602 "Analyzing and Resolving Optimizer
> > Problems" (available at http://techinfo.sybase.com)?
> >


Bret Halford Posted on 2000-05-03 16:40:03.0Z
Message-ID: <39105663.55BF89CE@sybase.com>
Date: Wed, 03 May 2000 10:40:03 -0600
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en,ja
MIME-Version: 1.0
Subject: Re: Inefficient Query Plan
References: <YGJuJhGt$GA.269@forums.sybase.com> <390F2508.1766C43B@nvestservices.com> <6b2yFpGt$GA.263@forums.sybase.com> <390F3574.22BA9CE@_internet.co.nz> <#hWVHUHt$GA.185@forums.sybase.com> <390F4EC2.2B279639@sybase.com> <EZXATTIt$GA.263@forums.sybase.com> <390F64D2.EF6AFCDD@sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 21
NNTP-Posting-Host: bret-pc.sybase.com 157.133.80.211
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2204
Article PK: 1089637

Techinfo.sybase.com seems to be working OK now, but I did make a mistake on the
technote number, it is actually 2602. Search for that or for "optimizer" and
the technote
should pop up.

-bret

Bret Halford wrote:

> The techinfo site seems to be acting strangely today, I can't get to the
> technote either.
> I'm looking to see if I have any copies floating around.
>
> -bret
>
> Mark Maslow wrote:
>
> > Had no luck finding Technote 3602, or searching tech docs for "Optimizer".
> > If you can provide a URL where I can find this, it would be much
> > appreciated.
> >
> > Thanks.
> >
> > Mark Maslow
> >
> > > Hi Mark,
> > >
> > > Have you checked out Technote 3602 "Analyzing and Resolving Optimizer
> > > Problems" (available at http://techinfo.sybase.com)?
> > >


Mark Maslow Posted on 2000-05-02 22:08:40.0Z
From: "Mark Maslow" <mark.maslow@sierraclub.org>
References: <YGJuJhGt$GA.269@forums.sybase.com> <390F2508.1766C43B@nvestservices.com> <6b2yFpGt$GA.263@forums.sybase.com> <390F3574.22BA9CE@_internet.co.nz> <#hWVHUHt$GA.185@forums.sybase.com> <390F4EC2.2B279639@sybase.com>
Subject: Re: Inefficient Query Plan
Date: Tue, 2 May 2000 15:08:40 -0700
Lines: 26
Organization: Sierra Club
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <TJlqzPIt$GA.185@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: machine001.sierraclub.org 207.90.163.1
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2211
Article PK: 1089643

Thanks for the FORCEINDEX idea. Unfortunately, all it buys is that now it
is going to scan the entire bigtable by the specified index, rather than in
physical order. So this actually makes the query run slower. Still appears
that there is no workaround for this extremely dumb behavior.

Mark Maslow

> Have you tried the FORCEINDEX syntax? (In 11.0.x, put the index-id
(indid
> from sysindexes) of
> the index that should be used in parenthesis after the tablename in the
from
> clause. In 11.5 and higher,
> use the new forceindex syntax of keyword "index" followed by index name,
all
> in parenthesis ie (index c_mytable). In 12.0, check out the entire
"Abstract
> Plans" feature.
>
> SELECT * FROM bigtable (1), littletable WHERE
> bigtable.indexedcolumn=littletable.id
>
> -bret
>


Peter Veilleux Posted on 2000-05-02 20:13:49.0Z
Message-ID: <390F36FD.55AC656C@nvestservices.com>
Date: Tue, 02 May 2000 16:13:49 -0400
From: Peter Veilleux <pveilleux@nvestservices.com>
X-Mailer: Mozilla 4.72 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Inefficient Query Plan
References: <YGJuJhGt$GA.269@forums.sybase.com> <390F2508.1766C43B@nvestservices.com> <6b2yFpGt$GA.263@forums.sybase.com> <390F3574.22BA9CE@_internet.co.nz>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 42
NNTP-Posting-Host: nefclient149.mutualfunds.com 206.136.227.149
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2217
Article PK: 1089650

Mark,

thank you for clarifying the datatype thing. Very common mistake many
people make char() NULL is the same thing as a varchar().

Peter

"Mark A. Parsons" wrote:

> > The columns being joined are both char(25).
>
> One other consideration ...
>
> If a column is defined as 'char(25)' and NULLable ... then it's stored
> internally as a 'varchar(25)'. (duh, Mark!?)
>
> If another column is defined as 'char(25)' and NOT NULLable ... then
> it's stored internally as a 'char(25)'.
>
> At this point, trying to join the 2 dislike-type columns in a query may
> cause some problems with the optimizer's query plan. (Granted, you'd
> think it was smart enough to see that one table has 3 rows and another
> 2.5 million rows?)
>
> So an additional question ...
>
> Are *BOTH* columns of the same NULLability, i.e., are both 'NULL', or
> both 'NOT NULL'?
>
> -----------------
>
> One other question ... are index statistics are up to date on the 2.5
> million row table, i.e., does the optimizer *know* that this table does,
> in fact, have 2.5 million rows? For example ... if the table had been
> created, index(es) built on said table with no data, then data loaded
> into table ... without running 'update statistics' after the data load,
> the table looks (to the optimizer) like it doesn't have any data. (This
> doesn't happen if the index was created after the data load since a
> 'create index', in effect, does an 'update statistics' as it creates the
> index.)
>
> --
> Mark A. Parsons
>
> Iron Horse, Inc. iron_horse@compuserve.com
> Pegasys (200), Ltd pegasys@internet.co.nz