problem.
its only deleteing 30000 rows, not all of them.
how can this work, deleting all, and not stopping @ 30000 rows deleted?
thanks.
tony
On Thu, 5 Aug 2004 14:12:39 -0400, Tony Weeg <[EMAIL PROTECTED]> wrote:
> well...
>
> thank you steve.
>
> ive just got it down to like 5 min to delete 2million rows.
>
> timeID is an int, and yes its indexed.
>
> thanks for the ideas...
>
> tony
>
>
>
>
> On Thu, 5 Aug 2004 14:06:42 -0400, DURETTE, STEVEN J (AIT)
> <[EMAIL PROTECTED]> wrote:
> > Tony,
> >
> > 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]
