A few things that might help.
1) Use set nocount on and off.
2) Calculate the datediff once instead of multiple times. (not too sure on
this, I have seen it recalc not at all or on every record whatever the sql
server decides).
Like this:
create procedure storedProc_Kill_91stDay
AS
Begin
set nocount on
set transaction isolation level serializable
set rowcount 30000
declare @myDateDiff bigint
select @myDateDiff = dateDiff(ss, '01/01/1970', dateAdd(d, -91,
getDate()))
delete from newBackupReports
where timeID <= @myDateDiff
set rowcount 0
set nocount off
end
This might speed it up. Note the set rowcount 0 should reset the rowcount
back to where it should be.
Also, what kind of field is timeID? There might be an even faster way
depending on the type. Also, is this table indexed? I have heard that if
you drop the index, do your delete and recreate the index that it will work
faster.
Steve
-----Original Message-----
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 05, 2004 11:55 AM
To: CF-Talk
Subject: Re: cfmx - request timeout setting
heres my planned stored proc....but from what i gather, set rowcount,
stops processing @ the rowcount limit...right?
anyway, here is the stored proc code.
CREATE PROCEDURE storedProc_Kill_91stDay
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ROWCOUNT 30000
delete
from newBackupReports
where timeID
<=
dateDiff(ss,'01/01/1970 00:00:00.000',DATEADD(Day, -91, GetDate()))
go
anyway i can tune this puppy up? or any ideas?
thanks!
tony
On Thu, 05 Aug 2004 15:35:15 +0000, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> This has been a sucky issue. If there are network issues or anything, CF
can't tell when accessing the DB and has no graceful way of handling it. We
use Oracle, if the TNS listner goes off line for like 5 minutes, the CF
server basically chokes and dies. Maybe there is a Java approach to this by
by of JDBC? I dunno.
>
> TW, maybe you can have that job chunk up the delete? Have it delete only
a few rows at a time to min the impact.
>
> --
>
> ---
> Douglas Knudsen
> http://www.cubicleman.com
> "If you don't like something, change it. If you can't change it, change
your attitude. Don't complain." - Maya Angelou
>
> its adv. sql server 2000
> its a sql job.
>
> that aside.
>
> my bigger problem with this...
>
> how does cfmx server/how should cfmx server handle a database that
> just is too busy to feed back a requested recordset? should it crash
> the cmfx server? or not?
>
> this is the bigger problem, i think.
>
> ill fix the db. whats the problem with cfmx?
>
> tw
>
>
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
