Delete with WHERE - date, time and string comparison - very slow

performance, sql, sql-server, sql-server-2008, t-sql

Solution

Converting both sides to strings, then concatenating them into one big string, then comparing those results is not very efficient. Only do conversions where you have to. 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;

Problem

I have a slow performing query and was hoping someone with a bit more knowledge in sql might be able to help me improve the performance: I have 2 tables a Source and a Common, I load in some data which contains a Date, a Time and String (whch is a server name), plus some.. The Source table can contain 40k+ rows (it has 30 odd columns, a mix of ints, dates, times and some varchars (255)/(Max) I use the below query to remove any data from Common that is in 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) I.E SN1234 - Date varchar(255) I.E 2012-05-22 - Time varchar(255) I.E 08:12:21 The Common Fields are in this format: - ServerName varchar(255) I.E SN1234 - Date date I.E 2011-08-10 - Time time(7) I.E 14:25:34.0000000 Thanks

Original source