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.

SQL delete problem

2 posts in General Discussion Last posting was on 2012-11-28 23:17:50.0Z
jbuhl Posted on 2012-11-28 20:46:53.0Z
Sender: 7f9d.50b66288.1804289383@sybase.com
From: jbuhl
Newsgroups: sybase.public.ase.general
Subject: SQL delete problem
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50b6783d.712.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 28 Nov 2012 12:46:53 -0800
X-Trace: forums-1-dub 1354135613 172.20.134.41 (28 Nov 2012 12:46:53 -0800)
X-Original-Trace: 28 Nov 2012 12:46:53 -0800, 172.20.134.41
Lines: 30
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31549
Article PK: 74437

Hello,

versoin 15.03
solaris Sparc

I have a simple procedure that I want to run to trim off a
tables records that are older than 180 days. I set the
rowcount to 25,000 in the proc. Lets say there are 54,000
rows older than 180 days. When I execute the proc the first
2 times it runs fine and reports back 25000 rows affected
each time. But when I run it the 3rd time to get the last
4000 rows it hangs. My experience has been that if there
are less rows than what rowcount is set to it just finished
up and reports back what actually was affected. Anybody
have any ideas why it would hang? I have tried this on
multiple servers with the same results.

proc code below:

set rowcount 25000

delete device_event
where event_dt < dateadd(dy,-180,getdate())

print "number of rows affected = '%1!'",@@rowcount

set rowcount 0

return 0
go


"Mark A. Parsons" <iron_horse Posted on 2012-11-28 23:17:50.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: SQL delete problem
References: <50b6783d.712.1681692777@sybase.com>
In-Reply-To: <50b6783d.712.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 121124-1, 11/24/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50b69b9e$1@forums-1-dub>
Date: 28 Nov 2012 15:17:50 -0800
X-Trace: forums-1-dub 1354144670 172.20.134.152 (28 Nov 2012 15:17:50 -0800)
X-Original-Trace: 28 Nov 2012 15:17:50 -0800, vip152.sybase.com
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31550
Article PK: 74439

I would expect the query to delete the last 4000 and return.

Just the obvious (?) questions ...

- have you verified it's not being blocked?

- have you verified it's not doing a table scan or expensive deferred update?

- have you taken a few snapshots of monProcessWaits to see where it's spending most of its time while 'hung'?

- is a trigger being fired and if so, are there any performance/blocking issues with the trigger's query(s)?

- what else does this proc (or parent process) do besides the delete? [could the 'hanging' be occurring elsewhere?]

On 11/28/2012 13:46, jbuhl wrote:
> Hello,
>
> versoin 15.03
> solaris Sparc
>
> I have a simple procedure that I want to run to trim off a
> tables records that are older than 180 days. I set the
> rowcount to 25,000 in the proc. Lets say there are 54,000
> rows older than 180 days. When I execute the proc the first
> 2 times it runs fine and reports back 25000 rows affected
> each time. But when I run it the 3rd time to get the last
> 4000 rows it hangs. My experience has been that if there
> are less rows than what rowcount is set to it just finished
> up and reports back what actually was affected. Anybody
> have any ideas why it would hang? I have tried this on
> multiple servers with the same results.
>
> proc code below:
>
> set rowcount 25000
>
> delete device_event
> where event_dt< dateadd(dy,-180,getdate())
>
> print "number of rows affected = '%1!'",@@rowcount
>
> set rowcount 0
>
> return 0
> go