DATALENGTH much faster than LEN in where clause with varchar(max)

• l.poerio

  SSC Veteran

  Points: 293

  More actions

  August 27, 2013 at 5:26 pm

  #395380

  hy,

  I read a lot of previous post, but I could not find why the running time is so different,

  making some changes to the "where" condition

  I need to delete some record from a table with an exact match.

  here an example

  CREATE TABLE TO_DROP_BIG_HTML(

  [BIG_HTML] [varchar](max) NULL

  ) ON [PRIMARY]

  select count(*), avg(DATALENGTH(BIG_HTML)) from TO_DROP_BIG_HTML

  -- 5000, 16215

  here the t-sql with duration estimation

  declare @start_time datetime

  select @start_time = getdate()

  -- ORIGINAL QUERY

  deleteTO_DROP_BIG_HTML

  whereBIG_HTML = 'NOREC'

  selectDATEDIFF(ms, @start_time, getdate())

  -- 1366

  select @start_time = getdate()

  -- adding LEN()

  deleteTO_DROP_BIG_HTML

  whereLEN(BIG_HTML) < 20

  andBIG_HTML = 'NOREC'

  select DATEDIFF(ms, @start_time, getdate())

  -- 43

  select @start_time = getdate()

  -- replacing with DATALENGTH()

  deleteTO_DROP_BIG_HTML

  whereDATALENGTH(BIG_HTML) < 20

  andBIG_HTML = 'NOREC'

  select DATEDIFF(ms, @start_time, getdate())

  -- 26

  every query in the Exection plan does a Table Scan cost 70-79%

  I can understand that adding more condition in the where I improve

  performance,

  but I don't understand the big difference between DATALENGTH and LEN

  Remarks that the original table has 180k record and 5 gb data:

  43 vs 26 ms become huge

  more than 10 min vs 12 sec

  can you explain me why?

  thank you

  L

• MyDoggieJessie

  SSC-Forever

  Points: 44486

  More actions

  August 27, 2013 at 7:58 pm

  #1645388

  Take the following example:DECLARE @String varchar(15)

  SET @String = ' 012 4567 '

  SELECT LEN(@String), DATALENGTH(@String)

  (LEN())(DATALENGTH())

  910

  LEN() excludes trailing spaces, so when you use it in your WHERE clause I believe it's actually RTRIM'ing the trailing spaces off of each record it evaluates. DATALENGTH() includes the actual number of bytes used in the string/expression being evaluated against (this include the trailing spaces) - so I believe the performance hit is incurred using the LEN()

  Also, DATALENGTH() works on text and ntext data types, LEN() doesn't not

  ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience