Understand why my query takes so long

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Understand why my query takes so long

Author

Topic

ecsmoore
Starting Member

5 Posts

Posted - 2010-05-03 : 13:44:32

I have a table with 15 million rows.
There are 5 columns. There is a clustered index on 2 columns.
I have to delete about 30 rows every hour.
The deletion takes 30 mins. So I get help from our SQL guru and he explains how to do the deletion and used the index. To test this new query/method I do a Select Into a new table. Of course the new table does not have any indexes due to the method it was created. I run the new query anyway, on the new table, and it finishes in 2 secs flat (there are 15million rows in it). I run it on the original table and it still takes 30 mins... with the old query or with the new query. I don't understand why it is so fast with the table with no index but a very long time on the original table with the index. I have added the table creation scripts below and the query. Can anyone explain this?

Original table:

USE [Reporting]
GO
/****** Object:  Table [dbo].[TagDataDaily]    Script Date: 05/03/2010 12:41:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TagDataDaily](
	[Tagkey] [int] NOT NULL CONSTRAINT [DF_TagDataDaily_Tagkey]  DEFAULT (0),
	[DateTime] [smalldatetime] NOT NULL,
	[Value] [real] NULL,
	[Quality] [tinyint] NULL,
	[RevLevel] [tinyint] NULL,
 CONSTRAINT [NoDailyDuplicates] UNIQUE NONCLUSTERED 
(
	[Tagkey] ASC,
	[DateTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[TagDataDaily]  WITH NOCHECK ADD  CONSTRAINT [FK_TagDataDaily_Tag] FOREIGN KEY([Tagkey])
REFERENCES [dbo].[Tag] ([TagKey])
GO
ALTER TABLE [dbo].[TagDataDaily] CHECK CONSTRAINT [FK_TagDataDaily_Tag]

New table:


USE [Reporting]
GO
/****** Object:  Table [dbo].[tagdatadailyTest3]    Script Date: 05/03/2010 12:42:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tagdatadailyTest3](
	[Tagkey] [int] NOT NULL,
	[DateTime] [smalldatetime] NOT NULL,
	[Value] [real] NULL,
	[Quality] [tinyint] NULL,
	[RevLevel] [tinyint] NULL
) ON [PRIMARY]

Deletion query


delete   METSINFO.REPORTING.DBO.tagdatadailytest3
	   where (tagkey = 14846 and [datetime] = dateadd(dy,@thisDay,DATEADD(yy, DATEDIFF(yy,0,@thisYear), 0)))
	      
	delete   METSINFO.REPORTING.DBO.tagdatadailytest3
	  where (tagkey = 14844 and [datetime] = dateadd(dy,@thisDay,DATEADD(yy, DATEDIFF(yy,0,@thisYear), 0)))
	      
	delete   METSINFO.REPORTING.DBO.tagdatadailytest3
	  where (tagkey = 14845 and [datetime] = dateadd(dy,@thisDay,DATEADD(yy, DATEDIFF(yy,0,@thisYear), 0)))

thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-03 : 13:52:40

What's the execution plan show? If the plan looks bad, have you tested adding an index hint? Is there any blocking?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-03 : 14:43:48

When you delete data, you also reorganize the indexes.
In your case, both the clustered index and the nonclustered index.

For 15 million records, a delete "from the beginning" of the clustered index may be very costly.

Also, the foreign key to the Tag table may have something to do with this.
In some cases, before a delete, I happen to drop the FK, do the delete, and then recreate the FK.
For my system, it is the difference between a 2 hour delete or 2 second delete for a few thousand records in a 900 million record table.

N 56°04'39.26"
E 12°55'05.63"

ecsmoore
Starting Member

5 Posts

Posted - 2010-05-03 : 14:52:54

the execution plan indicates that the last 3 deletion queries are taking a lot of cpu time. The only difference in these queries from the first 3 queries is >= VS just =.
The EP says this is a REMOTE SCAN. Scanning a table on a database other than the current database server.
I tried it on the other server and it is much faster. Maybe that is all that is wrong? But why the difference in the 2 sets of queries?
Any idea about this?

quote:
Originally posted by tkizer

What's the execution plan show? If the plan looks bad, have you tested adding an index hint? Is there any blocking?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-03 : 15:03:59

Could you instead put the delete queries into a stored procedure on the remote server, and then call the remote stored procedure from the current server?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources