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.

Query Optimization: Ideas needed

6 posts in Performance and Tuning Last posting was on 2007-11-23 09:42:24.0Z
Himanshu Jani Posted on 2007-11-22 21:26:58.0Z
From: "Himanshu Jani" <himanshujani@hotmail.com>
Newsgroups: sybase.public.ase.performance+tuning
Subject: Query Optimization: Ideas needed
Lines: 39
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: cpc1-cowc3-0-0-cust806.renf.cable.ntl.com
X-Original-NNTP-Posting-Host: cpc1-cowc3-0-0-cust806.renf.cable.ntl.com
Message-ID: <4745f422$1@forums-1-dub>
Date: 22 Nov 2007 13:26:58 -0800
X-Trace: forums-1-dub 1195766818 81.99.139.39 (22 Nov 2007 13:26:58 -0800)
X-Original-Trace: 22 Nov 2007 13:26:58 -0800, cpc1-cowc3-0-0-cust806.renf.cable.ntl.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10485
Article PK: 89106

Hi all,

Well, I am kinda in a weired situation. I am working in optimizing queries.
Below are the conditions I have to keep in mind before I suggest anything.

a. no new indexes
b. no parallelism
c. no named caches,
d. no multiple tempdbs
e. all tables are heap tables
f. no large pools.

Now I sit down to investigate what can be done.

There are frequent searches on a 35 million row table on three columns,
unfortunately only two of them are part of an index, so for the third column
it has to read data pages. SARG on two column fetches around 12 million of
rows and third one filters out 11.5 million of them.

ASE is 12.5.4, tables has 6 logical partitions.

Can someone please suggest some tips how to handle this situation?

Well, praying god is already suggested and we are working on it. Mean time
it would be great to have some idea.

There are many other queries which are victim of missing columns in indexes
and there are many areas beyond queries that can be tuned but as of now its
only queries.

I will keep posting my problems.
All suggestions, ideas, comments are welcomed.



Thank you very much in advanced and best regards
Himanshu


Isabella Posted on 2007-11-22 21:55:48.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Query Optimization: Ideas needed
References: <4745f422$1@forums-1-dub>
In-Reply-To: <4745f422$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: fw.hia.nrc.ca
X-Original-NNTP-Posting-Host: fw.hia.nrc.ca
Message-ID: <4745fae4@forums-1-dub>
Date: 22 Nov 2007 13:55:48 -0800
X-Trace: forums-1-dub 1195768548 204.174.103.3 (22 Nov 2007 13:55:48 -0800)
X-Original-Trace: 22 Nov 2007 13:55:48 -0800, fw.hia.nrc.ca
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10486
Article PK: 89110

Hi,

Will:' update stats ALL ' improve some of the performance with unindexed
col ?
Isabella

Himanshu Jani wrote:
> Hi all,
>
> Well, I am kinda in a weired situation. I am working in optimizing queries.
> Below are the conditions I have to keep in mind before I suggest anything.
>
> a. no new indexes
> b. no parallelism
> c. no named caches,
> d. no multiple tempdbs
> e. all tables are heap tables
> f. no large pools.
>
> Now I sit down to investigate what can be done.
>
> There are frequent searches on a 35 million row table on three columns,
> unfortunately only two of them are part of an index, so for the third column
> it has to read data pages. SARG on two column fetches around 12 million of
> rows and third one filters out 11.5 million of them.
>
> ASE is 12.5.4, tables has 6 logical partitions.
>
> Can someone please suggest some tips how to handle this situation?
>
> Well, praying god is already suggested and we are working on it. Mean time
> it would be great to have some idea.
>
> There are many other queries which are victim of missing columns in indexes
> and there are many areas beyond queries that can be tuned but as of now its
> only queries.
>
> I will keep posting my problems.
> All suggestions, ideas, comments are welcomed.
>
>
>
> Thank you very much in advanced and best regards
> Himanshu
>
>


Himanshu Jani Posted on 2007-11-22 22:08:52.0Z
From: "Himanshu Jani" <himanshujani@hotmail.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <4745f422$1@forums-1-dub> <4745fae4@forums-1-dub>
Subject: Re: Query Optimization: Ideas needed
Lines: 68
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: cpc1-cowc3-0-0-cust806.renf.cable.ntl.com
X-Original-NNTP-Posting-Host: cpc1-cowc3-0-0-cust806.renf.cable.ntl.com
Message-ID: <4745fdf4$1@forums-1-dub>
Date: 22 Nov 2007 14:08:52 -0800
X-Trace: forums-1-dub 1195769332 81.99.139.39 (22 Nov 2007 14:08:52 -0800)
X-Original-Trace: 22 Nov 2007 14:08:52 -0800, cpc1-cowc3-0-0-cust806.renf.cable.ntl.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10487
Article PK: 89111

Hi,
This point is on card, but the take is 'this is 24x7' system with a window
of just few hours on weekend. And most of the time goes in reorg.

Would like to put one more point, this is OLTP system with 50-100
transactions per second, so inserts/upds are very high and its not possible
to update stats all 5-10 important colums (out of 50+) of this large
denormalized table. Well, we know that its bad db design, OLTP with
denormalized table. But have to live with it for time being.

Thanks and best regards
Himanshu

--


Thank you very much and best regards
Himanshu Jani

"Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
news:4745fae4@forums-1-dub...
> Hi,
>
> Will:' update stats ALL ' improve some of the performance with unindexed
> col ?
> Isabella
> Himanshu Jani wrote:
>> Hi all,
>>
>> Well, I am kinda in a weired situation. I am working in optimizing
>> queries.
>> Below are the conditions I have to keep in mind before I suggest
>> anything.
>>
>> a. no new indexes
>> b. no parallelism
>> c. no named caches,
>> d. no multiple tempdbs
>> e. all tables are heap tables
>> f. no large pools.
>>
>> Now I sit down to investigate what can be done.
>>
>> There are frequent searches on a 35 million row table on three columns,
>> unfortunately only two of them are part of an index, so for the third
>> column it has to read data pages. SARG on two column fetches around 12
>> million of rows and third one filters out 11.5 million of them.
>>
>> ASE is 12.5.4, tables has 6 logical partitions.
>>
>> Can someone please suggest some tips how to handle this situation?
>>
>> Well, praying god is already suggested and we are working on it. Mean
>> time it would be great to have some idea.
>>
>> There are many other queries which are victim of missing columns in
>> indexes and there are many areas beyond queries that can be tuned but as
>> of now its only queries.
>>
>> I will keep posting my problems.
>> All suggestions, ideas, comments are welcomed.
>>
>>
>>
>> Thank you very much in advanced and best regards
>> Himanshu
>>


A. M. Posted on 2007-11-23 09:42:24.0Z
Message-ID: <4746A082.CAF033E5@gmail.com>
From: "A. M." <amforums@gmail.com>
X-Mailer: Mozilla 4.61 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Query Optimization: Ideas needed
References: <4745f422$1@forums-1-dub>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: cust4138.nsw01.dataco.com.au
X-Original-NNTP-Posting-Host: cust4138.nsw01.dataco.com.au
Date: 23 Nov 2007 01:42:24 -0800
X-Trace: forums-1-dub 1195810944 203.171.77.42 (23 Nov 2007 01:42:24 -0800)
X-Original-Trace: 23 Nov 2007 01:42:24 -0800, cust4138.nsw01.dataco.com.au
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10491
Article PK: 89112


Himanshu Jani wrote:
>
> Well, I am kinda in a weired situation. I am working in optimizing queries.
> Below are the conditions I have to keep in mind before I suggest anything.
>
> a. no new indexes
> b. no parallelism
> c. no named caches,
> d. no multiple tempdbs
> e. all tables are heap tables
> f. no large pools.
>
> Now I sit down to investigate what can be done.

What about modifying an existing index?

> There are frequent searches on a 35 million row table on three columns,
> unfortunately only two of them are part of an index, so for the third column
> it has to read data pages. SARG on two column fetches around 12 million of
> rows and third one filters out 11.5 million of them.

So you need to change that index.

> ASE is 12.5.4, tables has 6 logical partitions.
>
> Can someone please suggest some tips how to handle this situation?
>
> Well, praying god is already suggested and we are working on it. Mean time
> it would be great to have some idea.

Start by sacking the fool who's passing himself off
as a data modeler.

-am © 2007


"Mark A. Parsons" <iron_horse Posted on 2007-11-23 00:02:47.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Query Optimization: Ideas needed
References: <4745f422$1@forums-1-dub>
In-Reply-To: <4745f422$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: ool-4357fce9.dyn.optonline.net
X-Original-NNTP-Posting-Host: ool-4357fce9.dyn.optonline.net
Message-ID: <474618a7$4@forums-1-dub>
Date: 22 Nov 2007 16:02:47 -0800
X-Trace: forums-1-dub 1195776167 67.87.252.233 (22 Nov 2007 16:02:47 -0800)
X-Original-Trace: 22 Nov 2007 16:02:47 -0800, ool-4357fce9.dyn.optonline.net
Lines: 68
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10488
Article PK: 89115


Himanshu Jani wrote:
> Hi all,
>
> Well, I am kinda in a weired situation. I am working in optimizing queries.
> Below are the conditions I have to keep in mind before I suggest anything.
>
> a. no new indexes
> b. no parallelism
> c. no named caches,
> d. no multiple tempdbs
> e. all tables are heap tables
> f. no large pools.

If you're not allowed to make changes in production then setup a test/staging area with an exact copy of your production
system. Then do your tuning in this 'new' system. Once you get the desired query response times plan some down time to
make the necessary changes in the production system.

If the users want better performance they're going to have to let you make some changes. They can take a (relatively)
small hit up front to tune/adjust the system in return for faster queries on down the road ... or continue to live with
the current situation.

> Now I sit down to investigate what can be done.
>
> There are frequent searches on a 35 million row table on three columns,
> unfortunately only two of them are part of an index, so for the third column
> it has to read data pages. SARG on two column fetches around 12 million of
> rows and third one filters out 11.5 million of them.

You mention in another post that this is an OLTP system, but you don't do 12 million row (or 0.5 million row) queries in
an OLTP system. This sounds like the users are running some sort of reporting and/or batch jobs against the OLTP
database. If this is the case I can think of a couple options ... tune the system to support reporting/batch jobs ...
or copy the data off onto another database that can be tuned to support the reporting/batch jobs (eg, use replication to
keep another database in sync with the OLTP database).

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

You mention that this is a heap table, but you also mention that 2 SARGs are used in one particular query. Unless you
can only fit one record on a data page I would expect that the retrieval of 12 million rows (out of 35 million rows)
would lead to a table scan, ie, no index would be used. While it is possible that an index could be used on a 12
million row query, I get the feeling that too much information is missing from which to make a educated guesstimate
about what's going on. (Or are we talking about multiple/different queries?)

> ASE is 12.5.4, tables has 6 logical partitions.
>
> Can someone please suggest some tips how to handle this situation?
>
> Well, praying god is already suggested and we are working on it. Mean time
> it would be great to have some idea.
>
> There are many other queries which are victim of missing columns in indexes
> and there are many areas beyond queries that can be tuned but as of now its
> only queries.

The first step would be to look at the current query plans and ascertain whether they could be improved upon based
solely on rewriting queries.

Assuming the queries are using the best possible query plans, then *tuning* may very well require new/modified indexes,
changes in caches (eg, add a large IO pool, setup separate data caches), and allowing for parallel query processing
(especially against the partitioned table).

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

I'd also suggest running some sp_sysmon sessions during periods of 'slow activity' to see if you can spot some
bottlenecks in the dataserver. Granted, 'fixing' any such bottlenecks will probably require making changes to items on
your forbidden list.


Himanshu Jani Posted on 2007-11-23 09:11:01.0Z
From: "Himanshu Jani" <himanshujani@hotmail.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <4745f422$1@forums-1-dub> <474618a7$4@forums-1-dub>
Subject: Re: Query Optimization: Ideas needed
Lines: 117
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: cpc1-cowc3-0-0-cust806.renf.cable.ntl.com
X-Original-NNTP-Posting-Host: cpc1-cowc3-0-0-cust806.renf.cable.ntl.com
Message-ID: <47469925$1@forums-1-dub>
Date: 23 Nov 2007 01:11:01 -0800
X-Trace: forums-1-dub 1195809061 81.99.139.39 (23 Nov 2007 01:11:01 -0800)
X-Original-Trace: 23 Nov 2007 01:11:01 -0800, cpc1-cowc3-0-0-cust806.renf.cable.ntl.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10490
Article PK: 89117

Hello Mark,
Thanks a lot for load of advice.

Well, I have a test server to test my queries only, the worst part is test
server has only 700MB data cache (thats all total mem) and 2 engines whereas
db size is 200GB. So my test bed include testing only query plan, physical
and logical IO and no timings.

I still have to convince them just by changing seats of a small car it wont
run like a ferrari. Changes are required at the engine.

Its an OLTP system, with batch jobs selecting range of data for processing
as there is no index on date column. So it retrieves all data from last
archived date and then filters out records. No case of index covering.

Fetching 10+ of millions or rows causes table scan, but we have forced
indexes with forceplan to make sure that larger table is read first and
scanned only once or minimal time. And the queries causing frustration are
simple select from three-four tables with join condition on indexed columns
but filter condition on unindexed column.

I will try to get sp_sysmon to study outside database and more on server
level too.

I will note down point of using replicated server for batch jobs.

--


Thank you very much and best regards
Himanshu Jani

"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
news:474618a7$4@forums-1-dub...
>
>
> Himanshu Jani wrote:
>> Hi all,
>>
>> Well, I am kinda in a weired situation. I am working in optimizing
>> queries.
>> Below are the conditions I have to keep in mind before I suggest
>> anything.
>>
>> a. no new indexes
>> b. no parallelism
>> c. no named caches,
>> d. no multiple tempdbs
>> e. all tables are heap tables
>> f. no large pools.
>
> If you're not allowed to make changes in production then setup a
> test/staging area with an exact copy of your production system. Then do
> your tuning in this 'new' system. Once you get the desired query response
> times plan some down time to make the necessary changes in the production
> system.
>
> If the users want better performance they're going to have to let you make
> some changes. They can take a (relatively) small hit up front to
> tune/adjust the system in return for faster queries on down the road ...
> or continue to live with the current situation.
>
>> Now I sit down to investigate what can be done.
>>
>> There are frequent searches on a 35 million row table on three columns,
>> unfortunately only two of them are part of an index, so for the third
>> column it has to read data pages. SARG on two column fetches around 12
>> million of rows and third one filters out 11.5 million of them.
>
> You mention in another post that this is an OLTP system, but you don't do
> 12 million row (or 0.5 million row) queries in an OLTP system. This
> sounds like the users are running some sort of reporting and/or batch jobs
> against the OLTP database. If this is the case I can think of a couple
> options ... tune the system to support reporting/batch jobs ... or copy
> the data off onto another database that can be tuned to support the
> reporting/batch jobs (eg, use replication to keep another database in sync
> with the OLTP database).
>
> --------------
>
> You mention that this is a heap table, but you also mention that 2 SARGs
> are used in one particular query. Unless you can only fit one record on a
> data page I would expect that the retrieval of 12 million rows (out of 35
> million rows) would lead to a table scan, ie, no index would be used.
> While it is possible that an index could be used on a 12 million row
> query, I get the feeling that too much information is missing from which
> to make a educated guesstimate about what's going on. (Or are we talking
> about multiple/different queries?)
>
>> ASE is 12.5.4, tables has 6 logical partitions.
>>
>> Can someone please suggest some tips how to handle this situation?
>>
>> Well, praying god is already suggested and we are working on it. Mean
>> time it would be great to have some idea.
>>
>> There are many other queries which are victim of missing columns in
>> indexes and there are many areas beyond queries that can be tuned but as
>> of now its only queries.
>
> The first step would be to look at the current query plans and ascertain
> whether they could be improved upon based solely on rewriting queries.
>
> Assuming the queries are using the best possible query plans, then
> *tuning* may very well require new/modified indexes, changes in caches
> (eg, add a large IO pool, setup separate data caches), and allowing for
> parallel query processing (especially against the partitioned table).
>
> --------------
>
> I'd also suggest running some sp_sysmon sessions during periods of 'slow
> activity' to see if you can spot some bottlenecks in the dataserver.
> Granted, 'fixing' any such bottlenecks will probably require making
> changes to items on your forbidden list.
>