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.

Index Degradation

3 posts in General Discussion Last posting was on 2011-07-28 16:37:28.0Z
RGS Posted on 2011-07-11 22:31:59.0Z
Sender: 5b67.4e1b7792.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Index Degradation
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e1b79df.5b9f.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Jul 2011 15:31:59 -0700
X-Trace: forums-1-dub 1310423519 10.22.241.41 (11 Jul 2011 15:31:59 -0700)
X-Original-Trace: 11 Jul 2011 15:31:59 -0700, 10.22.241.41
Lines: 25
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30337
Article PK: 72515

Hi!

I am using ASE 15.0.3

I have a batch process, it works like that:

1) Truncate table "results_table"
2) Via cursor:
2.1) Insert 1'000.000 records on "results_table"
2.2) Execute some validations on table "results_table" via
SELECT command, all of the querys are support by an index

The problem is, in the middle of the execution, the DBA
watch a degradation on the access of the table
"results_table" and is necessary to execute the command:
"update index statistics" a lot of times, every 3 minutes,
until the program is finish

Why occur this degradation? It is because I truncate the
table and then insert 1'000.000 of records in only one batch
program?

How can I avoid it?

Thanks!


Bret Halford Posted on 2011-07-12 15:32:17.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Index Degradation
References: <4e1b79df.5b9f.1681692777@sybase.com>
In-Reply-To: <4e1b79df.5b9f.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e1c6901$1@forums-1-dub>
Date: 12 Jul 2011 08:32:17 -0700
X-Trace: forums-1-dub 1310484737 10.22.241.152 (12 Jul 2011 08:32:17 -0700)
X-Original-Trace: 12 Jul 2011 08:32:17 -0700, vip152.sybase.com
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30338
Article PK: 72516


On 7/11/2011 4:31 PM, RGS wrote:
> Hi!
>
> I am using ASE 15.0.3
>
> I have a batch process, it works like that:
>
> 1) Truncate table "results_table"
> 2) Via cursor:
> 2.1) Insert 1'000.000 records on "results_table"
> 2.2) Execute some validations on table "results_table" via
> SELECT command, all of the querys are support by an index
>
> The problem is, in the middle of the execution, the DBA
> watch a degradation on the access of the table
> "results_table" and is necessary to execute the command:
> "update index statistics" a lot of times, every 3 minutes,
> until the program is finish
>
> Why occur this degradation? It is because I truncate the
> table and then insert 1'000.000 of records in only one batch
> program?
>
> How can I avoid it?
>
> Thanks!

I think we need more details on the process.

Does the cursor insert a million rows multiple times, such
as once per each fetch?

Statistics should certainly be updated after the insert, but
if data is only inserted once and not subsequently modified,
I would not expect running update stats multiple times to help anything.

What query is the cursor itself based on? Sensitive or insensitive cursor?

How exactly does the DBA observe this degradation in performance?
Can you post some sample output of the diagnostics when performance
is good vs when it is bad?

-bret


RGS Posted on 2011-07-28 16:37:28.0Z
Sender: 1e9e.4e3188aa.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Re: Index Degradation
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e319048.2382.1681692777@sybase.com>
References: <4e1c6901$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Jul 2011 09:37:28 -0700
X-Trace: forums-1-dub 1311871048 10.22.241.41 (28 Jul 2011 09:37:28 -0700)
X-Original-Trace: 28 Jul 2011 09:37:28 -0700, 10.22.241.41
Lines: 66
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30387
Article PK: 72566

Hi!

My answers:

> Does the cursor insert a million rows multiple times, such
> as once per each fetch?

Yes, un record per each fetch

Data is not modified after the insert command

The command: "create cursor" doesn't have any clause of
sensitive or non-sensitive, I am using ASE 15.0.3

DBA only watch the number of records executed per minute and
then discover the problem


Thanks!


> On 7/11/2011 4:31 PM, RGS wrote:
> > Hi!
> >
> > I am using ASE 15.0.3
> >
> > I have a batch process, it works like that:
> >
> > 1) Truncate table "results_table"
> > 2) Via cursor:
> > 2.1) Insert 1'000.000 records on "results_table"
> > 2.2) Execute some validations on table
> > "results_table" via SELECT command, all of the querys
> are support by an index >
> > The problem is, in the middle of the execution, the DBA
> > watch a degradation on the access of the table
> > "results_table" and is necessary to execute the command:
> > "update index statistics" a lot of times, every 3
> > minutes, until the program is finish
> >
> > Why occur this degradation? It is because I truncate the
> > table and then insert 1'000.000 of records in only one
> > batch program?
> >
> > How can I avoid it?
> >
> > Thanks!
>
> I think we need more details on the process.
>
> Does the cursor insert a million rows multiple times, such
> as once per each fetch?
>
> Statistics should certainly be updated after the insert,
> but if data is only inserted once and not subsequently
> modified, I would not expect running update stats multiple
> times to help anything.
>
> What query is the cursor itself based on? Sensitive or
> insensitive cursor?
>
> How exactly does the DBA observe this degradation in
> performance? Can you post some sample output of the
> diagnostics when performance is good vs when it is bad?
>
> -bret