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 picking wrong index

3 posts in General Discussion Last posting was on 2010-09-07 17:12:45.0Z
shree Posted on 2010-09-07 16:05:58.0Z
Sender: 5ec3.4c865838.1804289383@sybase.com
From: Shree
Newsgroups: sybase.public.ase.general
Subject: Query picking wrong index
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c8662e6.5fab.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Sep 2010 09:05:58 -0700
X-Trace: forums-1-dub 1283875558 10.22.241.41 (7 Sep 2010 09:05:58 -0700)
X-Original-Trace: 7 Sep 2010 09:05:58 -0700, 10.22.241.41
Lines: 10
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29540
Article PK: 78773

Hi,

I come across the strange situation when one of my query on
huge table having millions of records started performing
poorly all of a sudden and when checked its using wrong
index. Can anyone have any idea why its picking wrong index
and how to fix it.

Thanks in advance.
Shree


Sherlock, Kevin [TeamSybase] Posted on 2010-09-07 17:01:38.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4c8662e6.5fab.1681692777@sybase.com>
Subject: Re: Query picking wrong index
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c866ff2$1@forums-1-dub>
Date: 7 Sep 2010 10:01:38 -0700
X-Trace: forums-1-dub 1283878898 10.22.241.152 (7 Sep 2010 10:01:38 -0700)
X-Original-Trace: 7 Sep 2010 10:01:38 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29541
Article PK: 78772

Can't do that without the usual details necessary. You should post the
following to start with:

1. Exact version of ASE ( output of : select @@version )
2. The EXACT code of the query being executed, and whether it is a stored
procedure, or SQL Language command
3. The current showplan output of your query
4. The entire un-edited output of "sp_help <table>" of all of the tables
involved.

Later, it might be necessary to also post the "optdiag" output of the tables
invovled as well, but we'll see if that's necessary later.

<Shree> wrote in message news:4c8662e6.5fab.1681692777@sybase.com...
> Hi,
>
> I come across the strange situation when one of my query on
> huge table having millions of records started performing
> poorly all of a sudden and when checked its using wrong
> index. Can anyone have any idea why its picking wrong index
> and how to fix it.
>
> Thanks in advance.
> Shree


Jason L. Froebe [TeamSybase] Posted on 2010-09-07 17:12:45.0Z
From: "Jason L. Froebe [TeamSybase]" <jason.froebe@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.11) Gecko/20100713 Thunderbird/3.0.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Query picking wrong index
References: <4c8662e6.5fab.1681692777@sybase.com> <4c866ff2$1@forums-1-dub>
In-Reply-To: <4c866ff2$1@forums-1-dub>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c86728d@forums-1-dub>
Date: 7 Sep 2010 10:12:45 -0700
X-Trace: forums-1-dub 1283879565 10.22.241.152 (7 Sep 2010 10:12:45 -0700)
X-Original-Trace: 7 Sep 2010 10:12:45 -0700, vip152.sybase.com
Lines: 19
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29543
Article PK: 78774


On 09/07/2010 12:01 PM, Sherlock, Kevin [TeamSybase] wrote:
> Can't do that without the usual details necessary. You should post the
> following to start with:
>
> 1. Exact version of ASE ( output of : select @@version )
> 2. The EXACT code of the query being executed, and whether it is a stored
> procedure, or SQL Language command
> 3. The current showplan output of your query
> 4. The entire un-edited output of "sp_help<table>" of all of the tables
> involved.
>
> Later, it might be necessary to also post the "optdiag" output of the tables
> invovled as well, but we'll see if that's necessary later.

In addition to what Kevin said, when was the last time update stats
(preferably update INDEX stats) and reorg/recreate of indexes performed?

jason