deleting rows

I have a table with approx 5 million rows and 36 columns. It takes approx
4 minutes to delete 1 row. The table has 3 indexes in addition to it's primary key and has twelve foreign key constraints. We are still using sequel 7.
There is a backup run every night as part of the nightly maintenence that
reorg/reindexes and checks the database integrity. Any thoughts?

Thankswell I depends what your deleting on. Make sure that you're using an indexed column, and check the execution plan to make sure that it's doing a table seek and not a table scan.

What are you stats for this table set to ?|||I am using a simple delete such as delete from tablename where transsk = 1002
with transsk being the primary key. This has only become a problem once the table grew over a mil rows.|||what kind of index, clustered or non-clustered ?

and did you set a fill factor on the table ?

setting your index properly should bring down your delete to a few seconds.
I've got tables that are 6mil+ rows and a delete takes < 15 secs.|||The primary key is non-clustered with a fill factor of 90.
There are also three indexes. Two non-clustered and one clustered, all three with a fill factor of 90. It's also odd to me that inserting rows is not a problem.|||Inserting a row shouldn't be much of a problem as you don't have to seek to insert a row. If you've got a clustered index, there is a little bit of overhead as the data needs to be arranged logically. IE, it may have to shuffle other rows around to properly fit in the one you are inserting. With a non-clustered index, it can just append the row to the logical group and add an entry into the tree.

What you may want to try for benchmarking purposes is to remove the clustered index and see if you get a performance increase when inserting or deleting. I don't think you'll get much, but it's worth a shot...

have you taken a look at the execution plan for a simple delete like the one you posted ?|||Thanks, I will give that a try by removing the clustered index.
Do you have tables with as many foreign key constraints? I didn't know if 12 was a unusually large amount.

Also, I guess I'm an idiot, what do you mean by execution plan?|||If you open query analyzer, there is a button at the top that will show you the proposed execution plan that SQL server will use when you run that SQL. The execution plan is created based on statistics.

Also, I think 12 FK constraints on one table is *a lot*. You should really only have 1 to 3. That's likely the reason it's taking so long to delete anything, it's got many constraints to check before deleting a row.

Cheers,
-Kilka|||Use this sample and apply your own code and cut and paste what it returns

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
GO

SET SHOWPLAN_TEXT ON
GO

DELETE FROM myTable99 WHERE Col1 = 2
GO

SET SHOWPLAN_TEXT OFF
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

deleting multiple databases on 2000

Hi,
I need to delete approx 50 db on the same server. Instead of deleting one
at a time, is there a better way? Is there a script that someone can suggest
many thx
Assuming you have only a couple of database that you want to keep, you could
create a script that uses a cursor to to select the databases
(master..sysdatabases) that you want to keep and then dynamically drop
eveything that is 'not in' your selected databases to keep.
Be warned!!! - make sure you eliminate the system databases.
If it were me, i'd just drop them manually just to be safe!
Immy
p.s. Ensure you have them all backed up! ;)
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> Hi,
> I need to delete approx 50 db on the same server. Instead of deleting one
> at a time, is there a better way? Is there a script that someone can
> suggest
> many thx
|||you could also use the ms undocumented command sp_foreachDB
but would still need to check for system databases first.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> Assuming you have only a couple of database that you want to keep, you
> could create a script that uses a cursor to to select the databases
> (master..sysdatabases) that you want to keep and then dynamically drop
> eveything that is 'not in' your selected databases to keep.
> Be warned!!! - make sure you eliminate the system databases.
> If it were me, i'd just drop them manually just to be safe!
> Immy
> p.s. Ensure you have them all backed up! ;)
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
>
|||Hi,
Please find the script for the same:
------
--Name : s_RebuildIndices
--Author : Pallavi
--Description: Rebuilds all table indices
--Notes:
--Date: 28 March 2006
Create PROCEDURE dbo.s_DropUserdatabases
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @.sTableName SYSNAME
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
INSERT @.t_TableNames_Temp
SELECT name
FROM SYSDATABASES
WHERE name not in ('master','msdb','model','tempdb')
ORDER BY name
--Getting row count from table
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
SELECT @.sTableName = table_name from @.t_TableNames_Temp
SELECT @.sSQL = 'DROP DATABASE '+@.sTableName
EXEC (@.sSQL)
DELETE FROM @.t_TableNames_Temp WHERE @.sTableName = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
RETURN 0
SET NOCOUNT OFF
GO
"Mark Broadbent" wrote:

> you could also use the ms undocumented command sp_foreachDB
> but would still need to check for system databases first.
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
>
>

deleting multiple databases on 2000

Hi,
I need to delete approx 50 db on the same server. Instead of deleting one
at a time, is there a better way? Is there a script that someone can sugges
t
many thxAssuming you have only a couple of database that you want to keep, you could
create a script that uses a cursor to to select the databases
(master..sysdatabases) that you want to keep and then dynamically drop
eveything that is 'not in' your selected databases to keep.
Be warned!!! - make sure you eliminate the system databases.
If it were me, i'd just drop them manually just to be safe!
Immy
p.s. Ensure you have them all backed up! ;)
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> Hi,
> I need to delete approx 50 db on the same server. Instead of deleting one
> at a time, is there a better way? Is there a script that someone can
> suggest
> many thx|||you could also use the ms undocumented command sp_foreachDB
but would still need to check for system databases first.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> Assuming you have only a couple of database that you want to keep, you
> could create a script that uses a cursor to to select the databases
> (master..sysdatabases) that you want to keep and then dynamically drop
> eveything that is 'not in' your selected databases to keep.
> Be warned!!! - make sure you eliminate the system databases.
> If it were me, i'd just drop them manually just to be safe!
> Immy
> p.s. Ensure you have them all backed up! ;)
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
>|||Hi,
Please find the script for the same:
----
--
--Name : s_RebuildIndices
--Author : Pallavi
--Description : Rebuilds all table indices
--Notes :
--Date : 28 March 2006
Create PROCEDURE dbo.s_DropUserdatabases
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @.sTableName SYSNAME
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
INSERT @.t_TableNames_Temp
SELECT name
FROM SYSDATABASES
WHERE name not in ('master','msdb','model','tempdb')
ORDER BY name
--Getting row count from table
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
SELECT @.sTableName = table_name from @.t_TableNames_Temp
SELECT @.sSQL = 'DROP DATABASE '+@.sTableName
EXEC (@.sSQL)
DELETE FROM @.t_TableNames_Temp WHERE @.sTableName = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
RETURN 0
SET NOCOUNT OFF
GO
"Mark Broadbent" wrote:

> you could also use the ms undocumented command sp_foreachDB
> but would still need to check for system databases first.
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
>
>

deleting multiple databases on 2000

Hi,
I need to delete approx 50 db on the same server. Instead of deleting one
at a time, is there a better way? Is there a script that someone can suggest
many thxAssuming you have only a couple of database that you want to keep, you could
create a script that uses a cursor to to select the databases
(master..sysdatabases) that you want to keep and then dynamically drop
eveything that is 'not in' your selected databases to keep.
Be warned!!! - make sure you eliminate the system databases.
If it were me, i'd just drop them manually just to be safe!
Immy
p.s. Ensure you have them all backed up! ;)
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> Hi,
> I need to delete approx 50 db on the same server. Instead of deleting one
> at a time, is there a better way? Is there a script that someone can
> suggest
> many thx|||you could also use the ms undocumented command sp_foreachDB
but would still need to check for system databases first.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> Assuming you have only a couple of database that you want to keep, you
> could create a script that uses a cursor to to select the databases
> (master..sysdatabases) that you want to keep and then dynamically drop
> eveything that is 'not in' your selected databases to keep.
> Be warned!!! - make sure you eliminate the system databases.
> If it were me, i'd just drop them manually just to be safe!
> Immy
> p.s. Ensure you have them all backed up! ;)
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
>> Hi,
>> I need to delete approx 50 db on the same server. Instead of deleting
>> one
>> at a time, is there a better way? Is there a script that someone can
>> suggest
>> many thx
>|||Hi,
Please find the script for the same:
------
--Name : s_RebuildIndices
--Author : Pallavi
--Description : Rebuilds all table indices
--Notes :
--Date : 28 March 2006
Create PROCEDURE dbo.s_DropUserdatabases
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @.sTableName SYSNAME
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
INSERT @.t_TableNames_Temp
SELECT name
FROM SYSDATABASES
WHERE name not in ('master','msdb','model','tempdb')
ORDER BY name
--Getting row count from table
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
SELECT @.sTableName = table_name from @.t_TableNames_Temp
SELECT @.sSQL = 'DROP DATABASE '+@.sTableName
EXEC (@.sSQL)
DELETE FROM @.t_TableNames_Temp WHERE @.sTableName = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
RETURN 0
SET NOCOUNT OFF
GO
"Mark Broadbent" wrote:
> you could also use the ms undocumented command sp_foreachDB
> but would still need to check for system databases first.
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> > Assuming you have only a couple of database that you want to keep, you
> > could create a script that uses a cursor to to select the databases
> > (master..sysdatabases) that you want to keep and then dynamically drop
> > eveything that is 'not in' your selected databases to keep.
> >
> > Be warned!!! - make sure you eliminate the system databases.
> >
> > If it were me, i'd just drop them manually just to be safe!
> > Immy
> > p.s. Ensure you have them all backed up! ;)
> >
> >
> > "stoney" <stoney@.discussions.microsoft.com> wrote in message
> > news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> >> Hi,
> >>
> >> I need to delete approx 50 db on the same server. Instead of deleting
> >> one
> >> at a time, is there a better way? Is there a script that someone can
> >> suggest
> >>
> >> many thx
> >
> >
>
>