Re: sql server 2000 Delete performance

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Steve Gadoury (steve.gadoury_at_sympatico.ca)
Date: 05/13/04 

Date: Thu, 13 May 2004 16:51:58 -0400

Hi Andrew, what I wanted to say it, there is no child to my main table. The
FK reference to an appropriate primary key in each table. Disk has 10 gig
free space.
I tried the delete you give me and it take almost the same time(10 seconds
less). I tried also truncate table and it take 0 seconds, but I can't use
it...
Do you know if there is a setting to optimize deletes statements?

Thank you
Steve

"Andrew J. Kelly" <sqlmvpnoooospam@shadhawk.com> a écrit dans le message de
news:OP0hJcROEHA.2844@tk2msftngp13.phx.gbl...
> Well for one you state there is no FK but the DDL show 2 of them with
> cascading. Even if what you meant was there were no rows in these other
> tables SQL Server still must do a seek to be sure for each row. Do the
> columns in these other tables that are referenced via these FK's have
> appropriate indexes? Have you looked to be sure there is no blocking
going
> on? What aboutdisk queues? The amount of free disk space means littel in
> an operation such as this but where your logs are located does. Have you
> tried doing the deletes in smaller batches?
>
> SET ROWCOUNT = 5000
>
> WHILE 1 = 1
> BEGIN
>
> DELETE FROM YourTable
>
> IF @@ROWCOUNT = 0
> BREAK
> END
>
> SET ROWCOUNT 0
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Steve Gadoury" <steve.gadoury@sympatico.ca> wrote in message
> news:bhOoc.59879$FH5.1420523@news20.bellglobal.com...
> > Hi all,
> >
> > I have a simple delete who take 2:30 minutes to execute. My table
> > contain 50 000 records.
> > configuration: Sql Server 2000 SP, Computer: Dual processor Zeon
> 2.4GHZ(each
> > processor), 4Gig RAM, Hard disk 60Gig. there is no foreign key
> referencing
> > this table and this table does not contain triggers. We can't use
> Truncate
> > Table instead of delete....
> >
> > Here is the table:
> >
> > CREATE TABLE [ERRRELEV] (
> > [XNo_Ass_Soc] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [XNo_Dossier] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [XDate_Even] [datetime] NULL ,
> > [XDate_Debut] [datetime] NULL ,
> > [XDate_Fin] [datetime] NULL ,
> > [XMnt_Frais] [numeric](10, 2) NULL ,
> > [HOUR_MODIF] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [USER_MODIF] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [XNo_Dos_Fin] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> > [XCode_Trx] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [XType_Frais] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [fk_ErrorId_ErrorsMonthlyReport] [int] NULL ,
> > [pk_ErrorId] [int] NOT NULL ,
> > [errorParameter1] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> > ,
> > [Ximputation] [numeric](3, 0) NULL ,
> > [XNom_Ben] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [XPrenom_Ben] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> > [fk_TransitId_Transit] [int] NOT NULL ,
> > [fk_EventId_Evenem] [int] NULL ,
> > CONSTRAINT [PK_ERRRELEV] PRIMARY KEY CLUSTERED
> > (
> > [pk_ErrorId]
> > ) ON [PRIMARY] ,
> > CONSTRAINT [FK_ERRRELEV_ErrorsMonthlyReport] FOREIGN KEY
> > (
> > [fk_ErrorId_ErrorsMonthlyReport]
> > ) REFERENCES [ErrorsMonthlyReport] (
> > [pk_ErrorId]
> > ) ON DELETE CASCADE ON UPDATE CASCADE ,
> > CONSTRAINT [FK_ERRRELEV_TRANSIT] FOREIGN KEY
> > (
> > [fk_TransitId_Transit]
> > ) REFERENCES [TRANSIT] (
> > [pk_TransitID]
> > ) ON DELETE CASCADE
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> >
> > Here is the Delete:
> > Delete from ls.ErrRelev
> >
> > In this example, there is no where on my Delete statement, but it
> > possible(but not necessary) to have one in my app.
> >
> > Thank you
> > Steve Gadoury
> > Sigma-RH Solutions
> > Montreal, Quebec, Canada
> >
> >
>
>

Relevant Pages

  • Re: what am i doing wrong here?
    ... > Jeremy Watts wrote: ... >>>This adds a reference to the Boolean object to the ... >> Hi Andrew, ...
    (comp.lang.java.programmer)
  • Re: Democrats destroying America ...
    ... Andrew wrote: ... have to remind you that every reference you have made to it so far has ... Is that anything like the World Weekly News? ...
    (comp.os.vms)
  • Re: Server Roles Process administrators
    ... BooksOnLine is probably the best reference. ... Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.security)
  • Re: Why C#?
    ... Pondering the eternal question of "Hobnobs or Rich Tea?", Andrew finally ... It usually sits no more than a foot away from me ... It's far from a complete reference though. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re-POST: How to close forms opened from other workbooks...?
    ... | or reference a listbox item if it is open. ... Prev by Date: ...
    (microsoft.public.excel.programming)