Basic concat over 500 times slower than straight select?

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  April 14, 2011 at 9:51 am

  #1311699

  Ninja's_RGR'us (4/14/2011)

  ---

  No doubt there.

  That was supposed to be a quick 2 minutes "job" to test the outcome of full recompile.

  I'm well aware of logging limits and single thread, but why I really started the thread was more a wtf is going on here???

  Enjoy.

  The one great benefit from this is showing the SELECT @Exec = ... + @Exec ... concatenation working. I do wonder if that will ever be kaboshed in the engine.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• David Dubois

  Right there with Babe

  Points: 763

  More actions

  April 15, 2011 at 5:04 am

  #1312029

  Let's think about what might be going on inside when those concatenations are being done.

  I can't say that I know how this is implemented in this particular case, but here's my guess based on my experience in other programming environments.

  Let's pick an interation near the end, and let's start with the case where you were appending and not converting to varchar.

  You have a string in memory that is 100,000 characters, that takes 100,000 bytes.

  (Let's not count the overhead to track the size of the memory block, etc.)

  You ask to append a string that is 23 characters. ('Update Statistics...')

  The system allocates space for the new string, 100,023 bytes.

  It then copies the 100,000 bytes from the original string to the new string.

  It then copies 23 bytes into the new string.

  It then deallocates the 100,000 bytes for the original string.

  Now you append the file name. Let's say it's 20 characters.

  Here you are operating on a varchar and an nvarchar, so the first has to be converted to nvarchar first.

  Allocate 200,046 bytes to convert the string to nvarchar.

  Copy over the 100,023 characters from the varchar to nvarchar.

  Note that this involves copying one byte, inserting a zero byte, copying the next byte, inserting a zero byte, and so on.

  Once it's been converted to nvarchar, you can concatenate the file name.

  Allocate 200,086 bytes for the new nvarchar string.

  Copy the 200,046 bytes from the original string to the new.

  Copy the 40 bytes from the file name to the new string.

  Deallocate the 200,046 bytes from the original string.

  Now you append another string of 18 characters. ' with fullscan...'

  This is another nvarchar operation.

  Let's assume the 18-character string has already been converted to nvarchar.

  Allocate 200,122 bytes for the new string.

  Copy over 200,086 bytes from the old string to the new.

  Copy the 36 bytes into the new string.

  Deallocate the 200,086 bytes for the original string.

  Now you add a string with a SINGLE character, char(13).

  Allocate 200,124 bytes for the new string.

  Copy 200,122 bytes from the old string to the new string.

  Copy two bytes into the new string.

  Deallocate the 200,122 bytes from the original string.

  Repeat that again for another SINGLE character, char(10).

  Allocate 200,126 bytes for the new string.

  Copy 200,124 bytes from the old string to the new string.

  Copy two bytes into the new string.

  Deallocate the 200,124 bytes from the original string.

  Now you ask the system to store that back into a varchar.

  Allocate 100,062 bytes for the new string.

  Copy the characters from the nvarchar string into the varchar string.

  Certainly not a simple operation as each character has complex logic to deal with two-byte characters.

  Whew!

  All that to process one 20-character file name.

  Converting the filename to varchar eliminates a huge amount of work in converting these huge strings back and forth.

  Why is prepending so much faster.

  To repeat the example above ...

  You are concatenating a 23 character string, a 20 character string, an 18 byte string, a 1 byte string and a 1 byte string.

  And then you are performing a single long catenation of the resulting 63 character string with the 100,000 character string.

  Is this what's going on inside SQL Server when these operations are being performed?

  I can't tell you.

  But regardless, this gives some insight into what might be happening.

• David Dubois

  Right there with Babe

  Points: 763

  More actions

  April 15, 2011 at 5:09 am

  #1312030

  I would just like to point out that if any of your filenames has a ']' in it, your code will fail.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  April 15, 2011 at 5:13 am

  #1312033

  David Dubois (4/15/2011)

  ---

  I would just like to point out that if any of your filenames has a ']' in it, your code will fail.

  Thanks, fortunately they don't.

  The code's been running in prod for a couple days now and it's gone off without any issues so I'm real happy with it... even if it's not "perfect"

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  April 15, 2011 at 5:13 am

  #1312034

  David Dubois (4/15/2011)

  ---

  Let's think about what might be going on inside when those concatenations are being done.

  I can't say that I know how this is implemented in this particular case, but here's my guess based on my experience in other programming environments.

  ....

  David, that sounds fairly reasonable, except that there seems to be more complex logic going on regarding the specific implementation inside the engine.

  Having said that I think that yours is a sound estimation that - precise or not - does explain what is possibly going on and makes sense of the different resulting performance, thanks!

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]