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.

Dynamic EXEC

6 posts in General Discussion Last posting was on 2010-10-11 15:31:34.0Z
RGS Posted on 2010-10-08 20:22:22.0Z
Sender: 6d5e.4caf7c37.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Dynamic EXEC
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4caf7d7e.6d83.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Oct 2010 13:22:22 -0700
X-Trace: forums-1-dub 1286569342 10.22.241.41 (8 Oct 2010 13:22:22 -0700)
X-Original-Trace: 8 Oct 2010 13:22:22 -0700, 10.22.241.41
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29634
Article PK: 78868

Hi!

I am working with ASE 15.0.3

I have a stored procedure:

cursor for 1 million on records

select @w = "string with a insert comand on a regular
table"
exec (@w)

end cursor

The program run very well until 300,000 records but suddenly
program stop....then programa continue...then stop..... It
never end

Dynamic exec() has any problem with a lot of executions?

Thanks!!


Rob V [ Sybase ] Posted on 2010-10-09 11:10:02.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.9) Gecko/20100915 Thunderbird/3.1.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Dynamic EXEC
References: <4caf7d7e.6d83.1681692777@sybase.com>
In-Reply-To: <4caf7d7e.6d83.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: <4cb04d8a$1@forums-1-dub>
Date: 9 Oct 2010 04:10:02 -0700
X-Trace: forums-1-dub 1286622602 10.22.241.152 (9 Oct 2010 04:10:02 -0700)
X-Original-Trace: 9 Oct 2010 04:10:02 -0700, vip152.sybase.com
Lines: 48
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29639
Article PK: 78867


On 08-Oct-2010 22:22, RGS wrote:
> Hi!
>
> I am working with ASE 15.0.3
>
> I have a stored procedure:
>
> cursor for 1 million on records
>
> select @w = "string with a insert comand on a regular
> table"
> exec (@w)
>
> end cursor
>
> The program run very well until 300,000 records but suddenly
> program stop....then programa continue...then stop..... It
> never end
>
> Dynamic exec() has any problem with a lot of executions?
>
> Thanks!!

I've done much more repeated invocations of exec() than 300k, without
any issues.
As Mark says, there must be something else happening. Did you check if
yout transaction log is full?

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------


Bret Halford Posted on 2010-10-11 15:31:34.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.9) Gecko/20100915 Thunderbird/3.1.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Dynamic EXEC
References: <4caf7d7e.6d83.1681692777@sybase.com> <4cb04d8a$1@forums-1-dub>
In-Reply-To: <4cb04d8a$1@forums-1-dub>
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: <4cb32dd6$1@forums-1-dub>
Date: 11 Oct 2010 08:31:34 -0700
X-Trace: forums-1-dub 1286811094 10.22.241.152 (11 Oct 2010 08:31:34 -0700)
X-Original-Trace: 11 Oct 2010 08:31:34 -0700, vip152.sybase.com
Lines: 66
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29643
Article PK: 78872

Does it process just a single record every 30 minutes
once it hits this point, or does it get through larger
sets?

I'm wondering if perhaps the cursor is losing its position
and the time is taken repositioning (although it seems like
a long time for that)

Is this a sensitive or insensitive cursor?
How complex is the cursor definition?

-bret

On 10/9/2010 5:10 AM, Rob V [ Sybase ] wrote:
> On 08-Oct-2010 22:22, RGS wrote:
>> Hi!
>>
>> I am working with ASE 15.0.3
>>
>> I have a stored procedure:
>>
>> cursor for 1 million on records
>>
>> select @w = "string with a insert comand on a regular
>> table"
>> exec (@w)
>>
>> end cursor
>>
>> The program run very well until 300,000 records but suddenly
>> program stop....then programa continue...then stop..... It
>> never end
>>
>> Dynamic exec() has any problem with a lot of executions?
>>
>> Thanks!!
>
> I've done much more repeated invocations of exec() than 300k, without
> any issues.
> As Mark says, there must be something else happening. Did you check if
> yout transaction log is full?
>
> HTH,
>
> Rob V.
> -----------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
> and Replication Server 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at www.sypron.nl/shop):
> "Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference Guide"
>
> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> http://www.sypron.nl
> Sypron B.V., Amersfoort, The Netherlands
> Chamber of Commerce 27138666
> -----------------------------------------------------------------
>


"Mark A. Parsons" <iron_horse Posted on 2010-10-08 20:38:03.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.general
Subject: Re: Dynamic EXEC
References: <4caf7d7e.6d83.1681692777@sybase.com>
In-Reply-To: <4caf7d7e.6d83.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: <4caf812b$1@forums-1-dub>
Date: 8 Oct 2010 13:38:03 -0700
X-Trace: forums-1-dub 1286570283 10.22.241.152 (8 Oct 2010 13:38:03 -0700)
X-Original-Trace: 8 Oct 2010 13:38:03 -0700, vip152.sybase.com
Lines: 32
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29635
Article PK: 78870

Before looking into potential issues with exec(), you may want to get a better handle on the nature of the stops.

Could the 'stop' really be another process blocking your spid? or perhaps contention for disk or CPU?

How long is the 'stop'?

Are the inserts causing a trigger to fire, and if so, have you monitored the trigger for any performance issues?

Have you tried pulling snapshots of monProcessWaits to see where the delays are coming from?

RGS wrote:
> Hi!
>
> I am working with ASE 15.0.3
>
> I have a stored procedure:
>
> cursor for 1 million on records
>
> select @w = "string with a insert comand on a regular
> table"
> exec (@w)
>
> end cursor
>
> The program run very well until 300,000 records but suddenly
> program stop....then programa continue...then stop..... It
> never end
>
> Dynamic exec() has any problem with a lot of executions?
>
> Thanks!!