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 times slow - index problem?

9 posts in General Discussion Last posting was on 2007-08-29 20:01:52.0Z
David Sutherland Posted on 2007-08-29 10:29:48.0Z
Sender: cd5.46d548c5.1804289383@sybase.com
From: David Sutherland
Newsgroups: ianywhere.public.general
Subject: Query times slow - index problem?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <46d54a9c.d0e.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 29 Aug 2007 03:29:48 -0700
X-Trace: forums-1-dub 1188383388 10.22.241.41 (29 Aug 2007 03:29:48 -0700)
X-Original-Trace: 29 Aug 2007 03:29:48 -0700, 10.22.241.41
Lines: 15
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6258
Article PK: 2801

Hi

I've upgraded from 8.0.1 to 8.0.3 EBF 5568, and noticed a
dramatic decrease in query performance.

60k selects were previously taking just over 2 mins, and now
its taking 38 mins!!

I'm using the same repository, is there a step I've missed
out?

How do you rebuild the indexes? I only have primary-key
indexes.

thanks in advance


Breck Carter [Team iAnywhere] Posted on 2007-08-29 12:22:47.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Query times slow - index problem?
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <fkoad3lk4gtercpdij84rhhjfrp5h17qk6@4ax.com>
References: <46d54a9c.d0e.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 29 Aug 2007 05:22:47 -0700
X-Trace: forums-1-dub 1188390167 64.7.134.118 (29 Aug 2007 05:22:47 -0700)
X-Original-Trace: 29 Aug 2007 05:22:47 -0700, bcarter.sentex.ca
Lines: 125
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6259
Article PK: 4678

Off The Wall Suggestion: Make sure this setting is in effect:

SET OPTION PUBLIC.Optimization_goal = 'All-rows';

You can use this to reorganize the primary key of a table "t":

REORGANIZE TABLE t PRIMARY KEY;

You can also unload and reload the database, which reorganizes
everything.

If you know which query is slow, you can view the "graphical plan with
statistics" after running it in dbisql. See this section in the Help:
=====
Adaptive Server Anywhere SQL User's Guide
11. Query Optimization and Execution
Reading access plans
Graphical plans
=====

If you want help interpreting the plan, send me the XML file; see the
"How to..." below.

Breck

=====
How to Save a Graphical Plan

For Version 10:

- In dbisql, choose Tools - Options... - Plan.

- Check "Graphical plan with full statistics".

- Check "Assume read-only cursor" if that applies.

- Pick "Assume cursor is: Asensitive" if that applies.

- Click "OK".

- Run CALL sa_flush_cache() if desired.

- Run your query (see Tip below).

- Do File - Save As - Save as type: XML (*.xml).

For Version 9:

- In dbisql, choose Tools - Options... - Plan.

- Check "Graphical plan with statistics".

- Check "Assume read-only cursor" if that applies.

- Pick "Assume cursor is: Asensitive" if that applies.

- Click "Make Permanent".

- Run CALL sa_flush_cache() if desired.

- Run your query (see Tip below).

- Do File - Save As - Save as type: XML (*.xml).

For Version 8:

- In dbisql, choose Tools - Options... - Plan.

- Check "Graphical plan with statistics".

- Check "Assume read-only cursor" if that applies.

- Pick "Assume cursor is: Asensitive" if that applies.

- Uncheck "Show UltraLite plan" unless you want that.

- Click "Make Permanent".

- Run CALL sa_flush_cache() if desired.

- Run your query (see Tip below).

- Do File - Save As - Save as type: XML (*.xml).

Tip: If you do use dbisql to execute a query and
capture a graphical plan with statistics, use
SQL - Get Plan (Shift+F5) instead of
SQL - Execute (F5 or F9). If you use Execute,
the query will be executed twice: once to
determine the result set and a second time to
capture the plan. This plan may be completely
different from the one used the first time the
query was executed, and not just because the
cache contains the rows. For example, if the
plan is for a DELETE, the first execution will
actually delete the rows so the plan may be for
the completely different "zero rows" case.
If you use Get Plan (Shift+F5) the query will
only be run once, and the plan will match.
=====

On 29 Aug 2007 03:29:48 -0700, David Sutherland wrote:

>Hi
>
>I've upgraded from 8.0.1 to 8.0.3 EBF 5568, and noticed a
>dramatic decrease in query performance.
>
>60k selects were previously taking just over 2 mins, and now
>its taking 38 mins!!
>
>I'm using the same repository, is there a step I've missed
>out?
>
>How do you rebuild the indexes? I only have primary-key
>indexes.
>
>thanks in advance

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


Rob Waywell Posted on 2007-08-29 12:48:42.0Z
From: "Rob Waywell" <rwaywell_no_spam_please@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <46d54a9c.d0e.1681692777@sybase.com> <fkoad3lk4gtercpdij84rhhjfrp5h17qk6@4ax.com>
Subject: Re: Query times slow - index problem?
Lines: 154
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.3138
NNTP-Posting-Host: rwaywell-xp2.sybase.com
X-Original-NNTP-Posting-Host: rwaywell-xp2.sybase.com
Message-ID: <46d56b2a$1@forums-1-dub>
Date: 29 Aug 2007 05:48:42 -0700
X-Trace: forums-1-dub 1188391722 10.25.98.235 (29 Aug 2007 05:48:42 -0700)
X-Original-Trace: 29 Aug 2007 05:48:42 -0700, rwaywell-xp2.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6260
Article PK: 4679

If I recall correctly, we added support for clustered indexes in 8.0.2,
which did require an unload/reload to take proper advantage of. So moving
from 8.0.1 to 8.0.3 is a place where we would recommend running an
unload/reload of the database to move to the new file format.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:fkoad3lk4gtercpdij84rhhjfrp5h17qk6@4ax.com...
> Off The Wall Suggestion: Make sure this setting is in effect:
>
> SET OPTION PUBLIC.Optimization_goal = 'All-rows';
>
> You can use this to reorganize the primary key of a table "t":
>
> REORGANIZE TABLE t PRIMARY KEY;
>
> You can also unload and reload the database, which reorganizes
> everything.
>
> If you know which query is slow, you can view the "graphical plan with
> statistics" after running it in dbisql. See this section in the Help:
> =====
> Adaptive Server Anywhere SQL User's Guide
> 11. Query Optimization and Execution
> Reading access plans
> Graphical plans
> =====
>
> If you want help interpreting the plan, send me the XML file; see the
> "How to..." below.
>
> Breck
>
> =====
> How to Save a Graphical Plan
>
> For Version 10:
>
> - In dbisql, choose Tools - Options... - Plan.
>
> - Check "Graphical plan with full statistics".
>
> - Check "Assume read-only cursor" if that applies.
>
> - Pick "Assume cursor is: Asensitive" if that applies.
>
> - Click "OK".
>
> - Run CALL sa_flush_cache() if desired.
>
> - Run your query (see Tip below).
>
> - Do File - Save As - Save as type: XML (*.xml).
>
> For Version 9:
>
> - In dbisql, choose Tools - Options... - Plan.
>
> - Check "Graphical plan with statistics".
>
> - Check "Assume read-only cursor" if that applies.
>
> - Pick "Assume cursor is: Asensitive" if that applies.
>
> - Click "Make Permanent".
>
> - Run CALL sa_flush_cache() if desired.
>
> - Run your query (see Tip below).
>
> - Do File - Save As - Save as type: XML (*.xml).
>
> For Version 8:
>
> - In dbisql, choose Tools - Options... - Plan.
>
> - Check "Graphical plan with statistics".
>
> - Check "Assume read-only cursor" if that applies.
>
> - Pick "Assume cursor is: Asensitive" if that applies.
>
> - Uncheck "Show UltraLite plan" unless you want that.
>
> - Click "Make Permanent".
>
> - Run CALL sa_flush_cache() if desired.
>
> - Run your query (see Tip below).
>
> - Do File - Save As - Save as type: XML (*.xml).
>
> Tip: If you do use dbisql to execute a query and
> capture a graphical plan with statistics, use
> SQL - Get Plan (Shift+F5) instead of
> SQL - Execute (F5 or F9). If you use Execute,
> the query will be executed twice: once to
> determine the result set and a second time to
> capture the plan. This plan may be completely
> different from the one used the first time the
> query was executed, and not just because the
> cache contains the rows. For example, if the
> plan is for a DELETE, the first execution will
> actually delete the rows so the plan may be for
> the completely different "zero rows" case.
> If you use Get Plan (Shift+F5) the query will
> only be run once, and the plan will match.
> =====
>
> On 29 Aug 2007 03:29:48 -0700, David Sutherland wrote:
>
>>Hi
>>
>>I've upgraded from 8.0.1 to 8.0.3 EBF 5568, and noticed a
>>dramatic decrease in query performance.
>>
>>60k selects were previously taking just over 2 mins, and now
>>its taking 38 mins!!
>>
>>I'm using the same repository, is there a step I've missed
>>out?
>>
>>How do you rebuild the indexes? I only have primary-key
>>indexes.
>>
>>thanks in advance
>
> --
> Breck Carter [Team iAnywhere]
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
> The book:
> http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> breck.carter@risingroad.com


"Frank Ploessel" <fpl... Posted on 2007-08-29 18:36:55.0Z
Subject: News agent ( was: Re: Query times slow - index problem?)
From: "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
References: <fkoad3lk4gtercpdij84rhhjfrp5h17qk6@4ax.com> <46d587ea.1472.1681692777@sybase.com> <op.txtvksxfj0bybf@bonw00164.internal.imsglobal.com> <3r8bd3teshqn1g63jcpoopgk8ktt0e19ku@4ax.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.txt3brhzj0bybf@bonw00164.internal.imsglobal.com>
User-Agent: Opera Mail/9.21 (Win32)
NNTP-Posting-Host: mail.taskarena-software-engineering.net
X-Original-NNTP-Posting-Host: mail.taskarena-software-engineering.net
Date: 29 Aug 2007 11:36:55 -0700
X-Trace: forums-1-dub 1188412615 217.76.101.130 (29 Aug 2007 11:36:55 -0700)
X-Original-Trace: 29 Aug 2007 11:36:55 -0700, mail.taskarena-software-engineering.net
Lines: 109
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6265
Article PK: 4680

Breck,

I am using Opera 9.21.

Did that just appear with my last message, or in other messages posted by
me as well?

I just checked some of my recent messages via the Sybase web interface and
via Google groups using Opera and IE6, and did not find any issue like
this. Could it be that Forte does not accept the maximum line length used
by Opera? I cannot find a configuration setting for this in Opera, but it
seems that lines appear strange for you if they are longer than 72
characters. Wasn't that a limit back in the old Fortran times?

My settings for outgoing messages are "Automatically wrap outgoing
messages" and default encoding iso8859-15. Actually, these should be the
default settings, I am not aware of having changed anything for these.

Are other users experiencing the same issues with my postings?

Frank


On Wed, 29 Aug 2007 18:52:56 +0200, Breck Carter [Team iAnywhere]

<NOSPAM__bcarter@risingroad.com> wrote:

> Frank, for what it's worth, when I view your postings using Forte
> Agent 2, they exhibit some strange formatting (see example below). I'm
> not sure where it's coming from, especially since the reply ">"
> quoting looks OK (see further down).
>
> How are you posting messages?
>
> Breck
>
> ===== copy and paste of your message as viewed by my client s/w...
> Hi David,
>
> This setting tells the optimizer if it should try to deliver the first
> f=
> ew =
>
> rows as quickly as possible (useful if the user e. g. in ISQL testing
> =
>
> statements anyway only looks at the first few rows), or if it should
> try=
> =
>
> to get all rows as quickly as possible, and you can accept a slightly
> =
>
> longer waiting time for the first few rows.
>
> By the way, you can change the behavior on statement level (if, in =
>
> contrast to the general option setting, you need some selects to
> behave =
> =
>
> differently) using optimizer hints in the FROM clause. See the =
>
> documentation on the FROM clause in the documentation for details.
>
> Frank
> =====
>
> On 29 Aug 2007 08:49:31 -0700, "Frank Ploessel"
> <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> wrote:
>
>> Hi David,
>>
>> This setting tells the optimizer if it should try to deliver the first
>> few
>> rows as quickly as possible (useful if the user e. g. in ISQL testing
>> statements anyway only looks at the first few rows), or if it should try
>> to get all rows as quickly as possible, and you can accept a slightly
>> longer waiting time for the first few rows.
>>
>> By the way, you can change the behavior on statement level (if, in
>> contrast to the general option setting, you need some selects to behave
>> differently) using optimizer hints in the FROM clause. See the
>> documentation on the FROM clause in the documentation for details.
>>
>> Frank


Breck Carter [Team iAnywhere] Posted on 2007-08-29 19:37:27.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: News agent ( was: Re: Query times slow - index problem?)
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <uvhbd31nkg8e3jiqlggrenugv6g3715ghq@4ax.com>
References: <fkoad3lk4gtercpdij84rhhjfrp5h17qk6@4ax.com> <46d587ea.1472.1681692777@sybase.com> <op.txtvksxfj0bybf@bonw00164.internal.imsglobal.com> <3r8bd3teshqn1g63jcpoopgk8ktt0e19ku@4ax.com> <op.txt3brhzj0bybf@bonw00164.internal.imsglobal.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 29 Aug 2007 12:37:27 -0700
X-Trace: forums-1-dub 1188416247 64.7.134.118 (29 Aug 2007 12:37:27 -0700)
X-Original-Trace: 29 Aug 2007 12:37:27 -0700, bcarter.sentex.ca
Lines: 106
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6266
Article PK: 4682

It's just your messages... not all of them, but many. Maybe you could
post some tests from the other NNTP clients you use, to pin it down.
AFAIK Forte Agent does not have any problem with incoming line length;
most stuff is wrapped but I think that is only because the sender
wraps it, and some incoming messages are not wrapped at all and extend
off into space (Martin Baur's are sometimes like that, no offense
Martin :)

FWIW *this* message of yours, which looks strange in Agent, looks ok
in the Sybase web browser interface.

On 29 Aug 2007 11:36:55 -0700, "Frank Ploessel"

<fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> wrote:

>Breck,
>
>I am using Opera 9.21.
>
>Did that just appear with my last message, or in other messages posted by
>me as well?
>
>I just checked some of my recent messages via the Sybase web interface and
>via Google groups using Opera and IE6, and did not find any issue like
>this. Could it be that Forte does not accept the maximum line length used
>by Opera? I cannot find a configuration setting for this in Opera, but it
>seems that lines appear strange for you if they are longer than 72
>characters. Wasn't that a limit back in the old Fortran times?
>
>My settings for outgoing messages are "Automatically wrap outgoing
>messages" and default encoding iso8859-15. Actually, these should be the
>default settings, I am not aware of having changed anything for these.
>
>Are other users experiencing the same issues with my postings?
>
>Frank
>
>
>On Wed, 29 Aug 2007 18:52:56 +0200, Breck Carter [Team iAnywhere]
><NOSPAM__bcarter@risingroad.com> wrote:
>
>> Frank, for what it's worth, when I view your postings using Forte
>> Agent 2, they exhibit some strange formatting (see example below). I'm
>> not sure where it's coming from, especially since the reply ">"
>> quoting looks OK (see further down).
>>
>> How are you posting messages?
>>
>> Breck
>>
>> ===== copy and paste of your message as viewed by my client s/w...
>> Hi David,
>>
>> This setting tells the optimizer if it should try to deliver the first
>> f=
>> ew =
>>
>> rows as quickly as possible (useful if the user e. g. in ISQL testing
>> =
>>
>> statements anyway only looks at the first few rows), or if it should
>> try=
>> =
>>
>> to get all rows as quickly as possible, and you can accept a slightly
>> =
>>
>> longer waiting time for the first few rows.
>>
>> By the way, you can change the behavior on statement level (if, in =
>>
>> contrast to the general option setting, you need some selects to
>> behave =
>> =
>>
>> differently) using optimizer hints in the FROM clause. See the =
>>
>> documentation on the FROM clause in the documentation for details.
>>
>> Frank
>> =====
>>
>> On 29 Aug 2007 08:49:31 -0700, "Frank Ploessel"
>> <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> wrote:
>>
>>> Hi David,
>>>
>>> This setting tells the optimizer if it should try to deliver the first
>>> few
>>> rows as quickly as possible (useful if the user e. g. in ISQL testing
>>> statements anyway only looks at the first few rows), or if it should try
>>> to get all rows as quickly as possible, and you can accept a slightly
>>> longer waiting time for the first few rows.
>>>
>>> By the way, you can change the behavior on statement level (if, in
>>> contrast to the general option setting, you need some selects to behave
>>> differently) using optimizer hints in the FROM clause. See the
>>> documentation on the FROM clause in the documentation for details.
>>>
>>> Frank

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com