Performance problem deleting records

Hi,
I delete from a SQL 2000 db 48.000 records with the following SQL statement
(NO SPO!):
Delete * From myTable where dateTimeField < anyDateTimeValue
The dateTimeField has an index. I do not use transactions. Also there is no
trigger on the table.
I receive an timeout after 30 seconds. The complete delete operation
requires about 5 minutes.
What can I do to speed up the operation?
Thanks
ChristianChristian
> Delete * From myTable where dateTimeField < anyDateTimeValue
It is ACCESS's syntax
What kind of index , is it Clustered on Nonclusterd?
Try
Delete From myTable where dateTimeField >'19000101' and dateTimeField <
anyDateTimeValue
"Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
message news:89BD70AD-1FD4-488B-9608-6C351A0557FC@.microsoft.com...
> Hi,
> I delete from a SQL 2000 db 48.000 records with the following SQL
> statement
> (NO SPO!):
> Delete * From myTable where dateTimeField < anyDateTimeValue
> The dateTimeField has an index. I do not use transactions. Also there is
> no
> trigger on the table.
> I receive an timeout after 30 seconds. The complete delete operation
> requires about 5 minutes.
> What can I do to speed up the operation?
> Thanks
> Christian|||Delete in small batches:
SET ROWCOUNT 5000
WHILE @.@.ROWCOUNT > 0
BEGIN
DELETE FROM MyTable WHERE dateTimeField < anyDateTimeValue
END
SET ROWCOUNT 0
If you are on SQL2005 use DELETE TOP 5000 instead.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
message news:89BD70AD-1FD4-488B-9608-6C351A0557FC@.microsoft.com...
> Hi,
> I delete from a SQL 2000 db 48.000 records with the following SQL
> statement
> (NO SPO!):
> Delete * From myTable where dateTimeField < anyDateTimeValue
> The dateTimeField has an index. I do not use transactions. Also there is
> no
> trigger on the table.
> I receive an timeout after 30 seconds. The complete delete operation
> requires about 5 minutes.
> What can I do to speed up the operation?
> Thanks
> Christian|||Andrew
> If you are on SQL2005 use DELETE TOP 5000 instead.
Do you think it is "safe" using TOP without ORDER BY clause? I think we do
not have contol ove what gets deleted.
I got used to use ORDER BY with TOP clause
I'd prefer something liker that
WITH OrdersRN AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
FROM dbo.MyOrders
)
DELETE FROM OrdersRN
WHERE RowNum <= 3;
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O%23QZyp3$HHA.4956@.TK2MSFTNGP06.phx.gbl...
> Delete in small batches:
> SET ROWCOUNT 5000
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> DELETE FROM MyTable WHERE dateTimeField < anyDateTimeValue
> END
> SET ROWCOUNT 0
> If you are on SQL2005 use DELETE TOP 5000 instead.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
> message news:89BD70AD-1FD4-488B-9608-6C351A0557FC@.microsoft.com...
>> Hi,
>> I delete from a SQL 2000 db 48.000 records with the following SQL
>> statement
>> (NO SPO!):
>> Delete * From myTable where dateTimeField < anyDateTimeValue
>> The dateTimeField has an index. I do not use transactions. Also there is
>> no
>> trigger on the table.
>> I receive an timeout after 30 seconds. The complete delete operation
>> requires about 5 minutes.
>> What can I do to speed up the operation?
>> Thanks
>> Christian
>|||> I delete from a SQL 2000 db 48.000 records with the following SQL
> statement
> (NO SPO!):
> Delete * From myTable where dateTimeField < anyDateTimeValue
> The dateTimeField has an index. I do not use transactions. Also there is
> no
> trigger on the table.
> I receive an timeout after 30 seconds. The complete delete operation
> requires about 5 minutes.
> What can I do to speed up the operation?
Verify that a poorly written trigger (or triggers) is not contributing to
the problem.|||On Sep 25, 9:02 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Andrew
> > If you are on SQL2005 use DELETE TOP 5000 instead.
> Do you think it is "safe" using TOP without ORDER BY clause? I think we do
> not have contol ove what gets deleted.
> I got used to use ORDER BY with TOP clause
> I'd prefer something liker that
> WITH OrdersRN AS
> (
> SELECT *, ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
> FROM dbo.MyOrders
> )
> DELETE FROM OrdersRN
> WHERE RowNum <= 3;
> "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com> wrote in messagenews:O%23QZyp3$HHA.4956@.TK2MSFTNGP06.phx.gbl...
> > Delete in small batches:
> > SET ROWCOUNT 5000
> > WHILE @.@.ROWCOUNT > 0
> > BEGIN
> > DELETE FROM MyTable WHERE dateTimeField < anyDateTimeValue
> > END
> > SET ROWCOUNT 0
> > If you are on SQL2005 use DELETE TOP 5000 instead.
> > --
> > Andrew J. Kelly SQL MVP
> > Solid Quality Mentors
> > "Christian Havel" <ChristianHa...@.discussions.microsoft.com> wrote in
> > messagenews:89BD70AD-1FD4-488B-9608-6C351A0557FC@.microsoft.com...
> >> Hi,
> >> I delete from a SQL 2000 db 48.000 records with the following SQL
> >> statement
> >> (NO SPO!):
> >> Delete * From myTable where dateTimeField < anyDateTimeValue
> >> The dateTimeField has an index. I do not use transactions. Also there is
> >> no
> >> trigger on the table.
> >> I receive an timeout after 30 seconds. The complete delete operation
> >> requires about 5 minutes.
> >> What can I do to speed up the operation?
> >> Thanks
> >> Christian
Hi Uri,
Why do want to control which rows get deleted? At the end of the day
all the rows that match the WHERE clause will gone, as in:
create table #t(i int)
insert #t values(1)
insert #t values(2)
GO
delete top (1) from #t where i > 0
delete top (1) from #t where i > 0
I think Andrew's approach is perfectly safe. Pls correct me if I am
wrong.|||> Do you think it is "safe" using TOP without ORDER BY clause? I think we do
> not have contol ove what gets deleted.
> I got used to use ORDER BY with TOP clause
The WHERE clause will determine what gets deleted in this case not the order
by since we want all of them that meet the where clause to go. It may be
beneficial to use an ORDER BY if it helps to find the 5000 rows faster but
it may not. I haven't seen the actual DDL so they should try it both ways
to see. But either way it is safe as it will only delete what the WHERE
clause says to delete.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%2301lZ23$HHA.4844@.TK2MSFTNGP02.phx.gbl...
> Andrew
>> If you are on SQL2005 use DELETE TOP 5000 instead.
> Do you think it is "safe" using TOP without ORDER BY clause? I think we do
> not have contol ove what gets deleted.
> I got used to use ORDER BY with TOP clause
> I'd prefer something liker that
> WITH OrdersRN AS
> (
> SELECT *, ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
> FROM dbo.MyOrders
> )
> DELETE FROM OrdersRN
> WHERE RowNum <= 3;
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O%23QZyp3$HHA.4956@.TK2MSFTNGP06.phx.gbl...
>> Delete in small batches:
>> SET ROWCOUNT 5000
>> WHILE @.@.ROWCOUNT > 0
>> BEGIN
>> DELETE FROM MyTable WHERE dateTimeField < anyDateTimeValue
>> END
>> SET ROWCOUNT 0
>> If you are on SQL2005 use DELETE TOP 5000 instead.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
>> message news:89BD70AD-1FD4-488B-9608-6C351A0557FC@.microsoft.com...
>> Hi,
>> I delete from a SQL 2000 db 48.000 records with the following SQL
>> statement
>> (NO SPO!):
>> Delete * From myTable where dateTimeField < anyDateTimeValue
>> The dateTimeField has an index. I do not use transactions. Also there is
>> no
>> trigger on the table.
>> I receive an timeout after 30 seconds. The complete delete operation
>> requires about 5 minutes.
>> What can I do to speed up the operation?
>> Thanks
>> Christian
>|||Christian,
Such a query might take a long time if:
- the table is heavily used during the delete (which causes blocking)
- the table has many indexes (the rows need to be deleted from the
indexes too)
- the index on dateTimeField is nonclustered, and myTable has many rows
- there are tables referencing myTable and their foreign key is not
indexed
- there are tables referencing myTable and the foreign key is defined
with ON DELETE CASCADE
If you are deleting a small percentage of the table, then you could
delete in batches (as suggested by others). If you are deleting a large
percentage of the table, you could consider dropping all nonclustered
indexes, execute the delete and then recreate all dropped indexes.
If you have tables with a foreign key constraint to myTable, then make
sure these foreign keys are indexed.
HTH,
Gert-Jan
Christian Havel wrote:
> Hi,
> I delete from a SQL 2000 db 48.000 records with the following SQL statement
> (NO SPO!):
> Delete * From myTable where dateTimeField < anyDateTimeValue
> The dateTimeField has an index. I do not use transactions. Also there is no
> trigger on the table.
> I receive an timeout after 30 seconds. The complete delete operation
> requires about 5 minutes.
> What can I do to speed up the operation?
> Thanks
> Christian|||Why not just increase the timeout threshold? Deleting in smaller batches may
not speed up 'the operation'.
Linchi
"Andrew J. Kelly" wrote:
> Delete in small batches:
> SET ROWCOUNT 5000
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> DELETE FROM MyTable WHERE dateTimeField < anyDateTimeValue
> END
> SET ROWCOUNT 0
> If you are on SQL2005 use DELETE TOP 5000 instead.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
> message news:89BD70AD-1FD4-488B-9608-6C351A0557FC@.microsoft.com...
> > Hi,
> >
> > I delete from a SQL 2000 db 48.000 records with the following SQL
> > statement
> > (NO SPO!):
> >
> > Delete * From myTable where dateTimeField < anyDateTimeValue
> >
> > The dateTimeField has an index. I do not use transactions. Also there is
> > no
> > trigger on the table.
> > I receive an timeout after 30 seconds. The complete delete operation
> > requires about 5 minutes.
> > What can I do to speed up the operation?
> >
> > Thanks
> > Christian
>