Delete with WHERE - comparing date, time and strings - very slow

Question

I have a slow executable query, and I was hoping that someone with a little more knowledge in sql would be able to help me improve performance:

I have 2 source and general tables, I upload some data containing the date, time and row (whch is the server name), as well as some ..

The source table may contain 40k + rows (has 30 odd columns, a combination of indexes, dates, times and some varchars (255) / (Max)

I use the following query to delete any data from Common that resides in the source:

'Delete from Common where convert(varchar(max),Date,102)+convert(varchar(max),Time,108)+[ServerName] in (Select convert(varchar(max),[date],102)+convert(varchar(max),time,108)+ServerName from Source where sc_status < 300)'

The source fields are in this format:

• ServerName varchar (255) IE SN1234
• Date varchar (255) IE 2012-05-22
• Varchar time (255) IE 08:12:21

Common fields are in this format:

• ServerName varchar (255) IE SN1234
• Date date IE 2011-08-10
• Time (7) IE 14: 25: 34.0000000

thanks

Answer 1

Converting both sides to strings, and then combining them into one large string, then comparing these results is not very effective. Only do the conversions you should. Try this example and see how it compares:

 DELETE c FROM dbo.Common AS c INNER JOIN dbo.Source AS s ON s.ServerName = c.ServerName AND CONVERT(DATE, s.[Date]) = c.[Date] AND CONVERT(TIME(7), s.[Time]) = c.[Time] WHERE s.sc_status < 300; 

Answer 2

All these conversions to VARCHAR(MAX) are unnecessary and probably slow down. I would start with something like this:

 DELETE c from [Common] c WHERE EXISTS( SELECT 1 FROM Source WHERE CAST([Date] AS DATE)=c.[Date] AND CAST([Time] AS TIME(7))=c.[Time] AND [ServerName]=c.[ServerName] AND sc_status < 300 ); 

Answer 3

Sort of

 Delete from Common inner join Source On Common.ServerName = Source.ServerName and Common.Date = Convert(Date,Source.Date) and Common.Time = Convert(Time, Source.Time) And Source.sc_Status < 300 

If it is too slow after that, you will need some indexes possible in both tables.

Answer 4

Removing unnecessary transformations will help a lot, as indicated in Aaron's answer. You can also consider creating an indexed view at the top of the log table, since you probably don't have much flexibility in this scheme or you insert DML from the log analyzer.

A simple example:

 create table dbo.[Source] (LogId int primary key, servername varchar(255), [date] varchar(255), [time] varchar(255)); insert into dbo.[Source] values (1, 'SN1234', '2012-05-22', '08:12:21'), (2, 'SN5678', '2012-05-23', '09:12:21') go create view dbo.vSource with schemabinding as select [LogId], [servername], [date], [time], [actualDateTime] = convert(datetime, [date]+' '+[time], 120) from dbo.[Source]; go create unique clustered index UX_Source on vSource(LogId); create nonclustered index IX_Source on vSource(actualDateTime); 

This will give you an indexed datetime column in which you can search and significantly improve your execution plans due to some insertion performance.