How to speed up DELETE queries on MSSQL v7.0?
-
Hi Folks,
Sorry if this is a simplistic question but it has me stumped. Using the MSSQL v7.0 release, I am using the following query to delete a record from at least 1 million records in the database:
SQL> delete from retailmember where retailmemberid = '1234567'
It took at least 1-2 minutes to perform this single delete and the speed is the same consistenly. Is there something I am missing? Perviously the code used was:
SQL> delete from retailmember where retailmemberid = in ('1234567')
But I found that this took considerably longer and dropped the 'IN' keyword which ran faster.
Best regards,
Dan
-
Is retailmemberid indexed?
If not, the delete statement will have to search through the whole table looking for rows with the relevant retailmemberid.
-
Hello,
Does it make sense to index a primary key column (retailmemberid)? I tried that with and without indexing and it did not appear to make any difference.... as far as I can tell.
Dan
-
If it is a primary key, then it will already be indexed.
Are there any delete triggers on the table?
If so, do they do anything that is taking the time?
-
What data type of retailmemberid has? Check the execution plan to ensure index is used.
-
1) retailmemberid is INT data type
2) Execution plan shows a complex layout:
delete<-assert<-nested-loop-left-semi-join(0)<-table spool<-assert<-nested-loop-left-semi-join(1)<-nested-loop-left-semi-join(2)<-nested-loop-left-semi-join(3)<-nested-loop-left-semi-join(4)<-nested-loop-left-semi-join(5)<-nested-loop-left-semi-join(6)<-nested-loop-left-semi-join(7)<-table-delete<-index-seek
(0) table-scan
(1) table-scan
(2) index-seek
(3) table-scan
(4)-(7) index-seek
Hope all the above details helps explain things?
Dan
-
No idea why your delete statement generates such complex execution plan.
Here is my example.
delete from dat_audit where id = 1
|--Table Delete(OBJECT
[INITLC].[dbo].[DAT_AUDIT]))|--Top(ROWCOUNT est 0)
|--Index Seek(OBJECT
[INITLC].[dbo].[DAT_AUDIT].[DAT_AUDIT2]), SEEK[DAT_AUDIT].[ID]=Convert([@1])) ORDERED FORWARD)It performs index seek to locate the record it needs to delete.
-
Is RetailMember actually a view and not a table?
--Jonathan -
How can "retailmemberid" be Int data type? From your query, I believe it's a char type.
-
Hello,
1) retailmemberid is INT data type, 4 in length
2) the complexity involves other table relationships, which perhaps explains the delay; the delete perhaps has to ensure that records in the relationship tables are deleted (I am not sure, definitively)
3) retailmember is a table, not a view; it has 78 columns (!), and it has at least 10 other tables that has relationships with.
4) There are no triggers in this table.
If I could, I would like to cut/paste the execution path, showing the specific relationships, but alas too hard to type it all out by hand.
Below is the SQL script that shows how the table was created:
if exists (select * from dbo.sysobjects where id = object_id(N'[shopall].[FK_CATEGORY_RETAILMEMBER]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [shopall].[CATEGORY] DROP CONSTRAINT FK_CATEGORY_RETAILMEMBER
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[shopall].[FK_COUPONS_RETAILMEMBER]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [shopall].[COUPONS] DROP CONSTRAINT FK_COUPONS_RETAILMEMBER
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[shopall].[FK_MAILING_LIST_RETAILMEMBER]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [shopall].[MAILING_LIST] DROP CONSTRAINT FK_MAILING_LIST_RETAILMEMBER
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[shopall].[RMPARENT]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [shopall].[RETAILMEMBER] DROP CONSTRAINT RMPARENT
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[shopall].[RefRETAILMEMBER60]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [shopall].[RM_PLAN_VERSION] DROP CONSTRAINT RefRETAILMEMBER60
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[shopall].[RefRETAILMEMBER112]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [shopall].[RM_VERT_SUBVERT] DROP CONSTRAINT RefRETAILMEMBER112
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[shopall].[FK_RMPLAN_LOOKUP_RETAILMEMBER]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [shopall].[RMPLAN_LOOKUP] DROP CONSTRAINT FK_RMPLAN_LOOKUP_RETAILMEMBER
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[shopall].[FK_SCART_RETAILMEMBER]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [shopall].[SCART] DROP CONSTRAINT FK_SCART_RETAILMEMBER
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[shopall].[RETAILMEMBER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [shopall].[RETAILMEMBER]
GO
CREATE TABLE [shopall].[RETAILMEMBER] (
[RETAILMEMBERID] [int] IDENTITY (1000000, 1) NOT NULL ,
[BUSINESSNAME] [varchar] (50) COLLATE Compatibility_52_409_30003 NOT NULL ,
[GROUPID] [int] NULL ,
[SICCODE] [char] (8) COLLATE Compatibility_52_409_30003 NULL ,
[CITY] [varchar] (50) COLLATE Compatibility_52_409_30003 NOT NULL ,
[ZIP] [varchar] (10) COLLATE Compatibility_52_409_30003 NOT NULL ,
[COUNTRYID] [int] NOT NULL ,
[STATEID] [int] NOT NULL ,
[STATUSID] [int] NOT NULL ,
[BSTATEID] [int] NULL ,
[BCOUNTRYID] [int] NULL ,
[PARENTID] [int] NULL ,
[DISPLAYNAME] [varchar] (50) COLLATE Compatibility_52_409_30003 NULL ,
[CONTACTNAME] [varchar] (50) COLLATE Compatibility_52_409_30003 NULL ,
[CONTACTMETHOD] [varchar] (20) COLLATE Compatibility_52_409_30003 NULL ,
[CONTACTTIME] [varchar] (15) COLLATE Compatibility_52_409_30003 NULL ,
[CCNUMBER] [varchar] (25) COLLATE Compatibility_52_409_30003 NULL ,
[APPT#] [varchar] (10) COLLATE Compatibility_52_409_30003 NULL ,
[STREETADDRESS] [varchar] (75) COLLATE Compatibility_52_409_30003 NULL ,
[ZIPPLUS4] [varchar] (4) COLLATE Compatibility_52_409_30003 NULL ,
[PHONE] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
[FAX] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
[TAXID] [varchar] (20) COLLATE Compatibility_52_409_30003 NULL ,
[varchar] (75) COLLATE Compatibility_52_409_30003 NULL ,
[BLOCK] [char] (1) COLLATE Compatibility_52_409_30003 NULL ,
[BNAME] [varchar] (50) COLLATE Compatibility_52_409_30003 NULL ,
[BCONTACTNAME] [varchar] (50) COLLATE Compatibility_52_409_30003 NULL ,
[BAPPT#] [varchar] (10) COLLATE Compatibility_52_409_30003 NULL ,
[BSTREETADDRESS] [varchar] (75) COLLATE Compatibility_52_409_30003 NULL ,
[BCITY] [varchar] (50) COLLATE Compatibility_52_409_30003 NULL ,
[BZIP] [varchar] (10) COLLATE Compatibility_52_409_30003 NULL ,
[BZIPPLUS4] [char] (4) COLLATE Compatibility_52_409_30003 NULL ,
[BEMAIL] [varchar] (75) COLLATE Compatibility_52_409_30003 NULL ,
[BPHONE] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
[SAANAME] [varchar] (100) COLLATE Compatibility_52_409_30003 NULL ,
[BFAX] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
[SAAEMAIL] [varchar] (75) COLLATE Compatibility_52_409_30003 NULL ,
[PASSWORD] [varchar] (50) COLLATE Compatibility_52_409_30003 NULL ,
[HINT] [varchar] (50) COLLATE Compatibility_52_409_30003 NULL ,
[USESAAEMAILYN] [char] (1) COLLATE Compatibility_52_409_30003 NULL ,
[POPHOST] [varchar] (50) COLLATE Compatibility_52_409_30003 NULL ,
[PUBLISHEMAILYN] [char] (1) COLLATE Compatibility_52_409_30003 NULL ,
[PUBLISHFAXYN] [char] (1) COLLATE Compatibility_52_409_30003 NULL ,
[PUBLISHCONTACTYN] [char] (1) COLLATE Compatibility_52_409_30003 NULL ,
[LATITUDE] [numeric](8, 4) NULL ,
[LONGITUDE] [numeric](8, 4) NULL ,
[HOURSMONDAY] [varchar] (19) COLLATE Compatibility_52_409_30003 NULL ,
[HOURSTUESDAY] [varchar] (19) COLLATE Compatibility_52_409_30003 NULL ,
[HOURSWEDNESDAY] [varchar] (19) COLLATE Compatibility_52_409_30003 NULL ,
[HOURSTHURSDAY] [varchar] (19) COLLATE Compatibility_52_409_30003 NULL ,
[HOURSFRIDAY] [varchar] (19) COLLATE Compatibility_52_409_30003 NULL ,
[HOURSSATURDAY] [varchar] (19) COLLATE Compatibility_52_409_30003 NULL ,
[HOURSSUNDAY] [varchar] (19) COLLATE Compatibility_52_409_30003 NULL ,
[PAYMENTMETHODS] [varchar] (75) COLLATE Compatibility_52_409_30003 NULL ,
[LINK1] [varchar] (100) COLLATE Compatibility_52_409_30003 NULL ,
[LINK2] [varchar] (100) COLLATE Compatibility_52_409_30003 NULL ,
[LINK3] [varchar] (100) COLLATE Compatibility_52_409_30003 NULL ,
[KEYPHRASE1] [varchar] (150) COLLATE Compatibility_52_409_30003 NULL ,
[KEYPHRASE2] [varchar] (75) COLLATE Compatibility_52_409_30003 NULL ,
[KEYPHRASE3] [varchar] (75) COLLATE Compatibility_52_409_30003 NULL ,
[KEYPHRASE4] [varchar] (75) COLLATE Compatibility_52_409_30003 NULL ,
[KEYPHRASE5] [varchar] (75) COLLATE Compatibility_52_409_30003 NULL ,
[KEYPHRASE6] [varchar] (75) COLLATE Compatibility_52_409_30003 NULL ,
[KEYPHRASE7] [varchar] (75) COLLATE Compatibility_52_409_30003 NULL ,
[KEYWORD] [varchar] (2048) COLLATE Compatibility_52_409_30003 NULL ,
[SLOGAN] [varchar] (75) COLLATE Compatibility_52_409_30003 NULL ,
[APPROXINVENTORY] [money] NULL ,
[MAPYN] [char] (1) COLLATE Compatibility_52_409_30003 NULL ,
[PICTUREYN] [char] (1) COLLATE Compatibility_52_409_30003 NULL ,
[RMPLANID] [int] NULL ,
[AFFILIATEYN] [char] (1) COLLATE Compatibility_52_409_30003 NULL ,
[CREATIONDATE] [datetime] NULL ,
[CREATEDBY] [int] NULL ,
[LASTUPDATEDATE] [datetime] NULL ,
[LASTUPDATEBY] [int] NULL ,
[PASSWORD2] [varchar] (10) COLLATE Compatibility_52_409_30003 NULL ,
[ECMETHOD] [varchar] (75) COLLATE Compatibility_52_409_30003 NULL ,
[MINORDER] [money] NULL ,
[DELIVERY] [int] NULL
) ON [PRIMARY]
GO
Hope this helps shed some light....
-
Okay; it's probably the foreign keys. What indexes exist for the eight tables with foreign keys referencing the RetailMember table? What DRI options (e.g. CASCADE DELETE) exist for those constraints?
--Jonathan -
Jonathan
SQL7 -> no cascading deletes through DRI. That must be done using triggers.
Dan,
The DRI-checking will be done by sqlserver, so provide FK-indexes to support it.
If those indexes hurt performance to mutch, you might consider to add them only when needed (e.g.anual cleanup procedure).
If those deletes can be performed during downtime, maybe consider temporary diable of the FK-constraints during your procedure. Don't forget to reactivate them because they support db-data-consistency and be sure you have handled all dependant tables before you reactivate the constraints !
/*
To disable constraints and triggers , you can use sp_msforeachtable.
*/
-- sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? NOCHECK CONSTRAINT all"
-- sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? DISABLE TRIGGER all"
/*
To enable them back, you can reverse the function.
Let's get a little more creative this time.
We'll explicitly set the parameters and list each table before enabling
them so we can verify any errors easily.
*/
-- sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? CHECK CONSTRAINT all"
-- sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER all"
Use the correct datatype in you queries, so there is no need for implicite conversions.
delete from retailmember where retailmemberid = 1234567
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
You expect me to read the subject line?
--Jonathan
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply