Basic concat over 500 times slower than straight select?

  • I have also done a bit of testing and found that after your variable gets to a certain size (dependent on the amount of data you are adding each time!) you will start to see excessive reads and writes

    Here are the stats for a query on my test database - they will be different for yours:

    TOP indicates me using a TOP x clause

    As you can see, there comes a point where concatenating to the string becomes woefully inefficient and the reads and writes start to grow very quickly.

    It seems that what you are concatenating to the string is also important - NVARCHAR costs a LOT LOT more than VARCHAR and the length of the string you are concatenating makes a difference - a shorter string to be added for each row in the select actually seems to have more than a simple relationship to the tipping point.

    For example, your query on my database has a tipping point of 5691 rows - i.e. 5690 rows will produce no writes and reasonable reads/cpu, but 5691 rows will suddenly produce 10 times the reads and start writing. The length of the output for these row counts is shown below:

    5690 - 118782

    5691 - 118803

    However, if I change the query to concatenate a smaller string each time, I get longer resulting strings before the tipping point.

    For example, concatenating a varchar(20) each time tips at 12904 rows and reaches a length of 258080 bytes.

    Using a varchar(10) I can reach 77826 rows and a length of 778260 when it tips.

    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]

  • Bearing in mind the length considerations, this code now runs in 5 seconds for me for 9000 tables (the xml type fix doesn't work for me as I have generated tables with non printable characters and it really doesn't like them)

    DECLARE @Exec VARCHAR(MAX)

    SELECT @Exec = ''

    SELECT @Exec = @Exec + 'XX' + RTRIM(name) + 'YY'

    FROM

    sys.tables

    SELECT @Exec = replace(@Exec, 'XX', 'UPDATE STATISTICS dbo.[')

    SELECT @Exec = replace(@Exec, 'YY', '] WITH FULLSCAN;' + char(10))

    PRINT @Exec

    Obviously this relies on you being able to find two tags (XX and YY) that are not going to exist in your table names - which might be tricky!

    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]

  • mister.magoo (4/12/2011)


    Bearing in mind the length considerations, this code now runs in 5 seconds for me for 9000 tables (the xml type fix doesn't work for me as I have generated tables with non printable characters and it really doesn't like them)

    DECLARE @Exec VARCHAR(MAX)

    SELECT @Exec = ''

    SELECT @Exec = @Exec + 'XX' + RTRIM(name) + 'YY'

    FROM

    sys.tables

    SELECT @Exec = replace(@Exec, 'XX', 'UPDATE STATISTICS dbo.[')

    SELECT @Exec = replace(@Exec, 'YY', '] WITH FULLSCAN;' + char(10))

    PRINT @Exec

    Obviously this relies on you being able to find two tags (XX and YY) that are not going to exist in your table names - which might be tricky!

    I like the idea, but I wouldn't event call this a workaround. I know a client who'll have ± 50 000 tables once fully deployed. So even then your solution gets destroyed by the sheer data volume.

    The final deal for me is that SS is screwing this up :w00t:!!

  • Any reason why you don't use this?

    Exec sp_MSForEachTable 'Update Statistics ''?'' with FULLSCAN'

    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]

  • mister.magoo (4/13/2011)


    Any reason why you don't use this?

    Exec sp_MSForEachTable 'Update Statistics ''?'' with FULLSCAN'

    Never been my first choice since it's "unsupported". I like to make code that'll work forever whenever I can.

  • Silly question, why not stick the results into a Temp table, then haul out the commands on a line by line basis, since you have so many tables to deal with?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin (4/13/2011)


    Silly question, why not stick the results into a Temp table, then haul out the commands on a line by line basis, since you have so many tables to deal with?

    Was wondering the same thing....

  • This thread is not how can I make this work, it's about why the heck does this concat take so long.

    So in that respect I think we can close it.

    Thanks for all suggestions.

  • Ninja's_RGR'us (4/13/2011)


    This thread is not how can I make this work, it's about why the heck does this concat take so long.

    Ninja, I only asked because I thought the concat would be faster if you just stuck all the commands into the table. Didn't mean to offend you.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • mister.magoo (4/12/2011)


    I have also done a bit of testing and found that after your variable gets to a certain size (dependent on the amount of data you are adding each time!) you will start to see excessive reads and writes

    Here are the stats for a query on my test database - they will be different for yours:

    TOP indicates me using a TOP x clause

    As you can see, there comes a point where concatenating to the string becomes woefully inefficient and the reads and writes start to grow very quickly.

    It seems that what you are concatenating to the string is also important - NVARCHAR costs a LOT LOT more than VARCHAR and the length of the string you are concatenating makes a difference - a shorter string to be added for each row in the select actually seems to have more than a simple relationship to the tipping point.

    For example, your query on my database has a tipping point of 5691 rows - i.e. 5690 rows will produce no writes and reasonable reads/cpu, but 5691 rows will suddenly produce 10 times the reads and start writing. The length of the output for these row counts is shown below:

    5690 - 118782

    5691 - 118803

    However, if I change the query to concatenate a smaller string each time, I get longer resulting strings before the tipping point.

    For example, concatenating a varchar(20) each time tips at 12904 rows and reaches a length of 258080 bytes.

    Using a varchar(10) I can reach 77826 rows and a length of 778260 when it tips.

    The final string length is 670 535. So very small al things considered!

  • Brandie Tarvin (4/13/2011)


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


    This thread is not how can I make this work, it's about why the heck does this concat take so long.

    Ninja, I only asked because I thought the concat would be faster if you just stuck all the commands into the table. Didn't mean to offend you.

    I never take offense of forums. I know you're all here to help and I've gotten the help I need.

    I just don't see the point to relist the 100 other ways to make this script work ;-).

  • Ok, so just on a whim, I thought I would try something....Build the string by inserting instead of appending....WHAMO!

    Also include the CONVERSION to VARCHAR from NVARCHAR for "name" column to really make it fly!

    DECLARE @Exec VARCHAR(MAX)

    SELECT @Exec = ''

    SELECT @Exec = 'UPDATE STATISTICS dbo.[' + CONVERT(VARCHAR(200),name) + '] WITH FULLSCAN ; ' + CHAR(13) + CHAR(10) + @Exec FROM sys.tables

    Completes in about 1 second on my test of 9000 tables.

    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]

  • Genius!!!

    Exec size : 670535

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'sysschobjs'. Scan count 1, logical reads 413, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'sysidxstats'. Scan count 1, logical reads 458, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 375 ms, elapsed time = 377 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

  • That is definitely one to remember! Glad it worked for you as well, as there was the possibility in my mind that it might be version specific.

    I am using Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86) Sep 16 2010 20:09:22 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 6.1 <X86> (Build 7600: )

    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]

  • mister.magoo (4/13/2011)


    That is definitely one to remember! Glad it worked for you as well, as there was the possibility in my mind that it might be version specific.

    I am using Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86) Sep 16 2010 20:09:22 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 6.1 <X86> (Build 7600: )

    ... and I've been playing around with your idea and it's really the varchar that seems to clinch it on top of prepending. Anything as nvarchar in there and it shoots back up to 1 minute+.

    Microsoft SQL Server 2005 - 9.00.4035.00 (SP3) (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

  • Viewing 15 posts - 16 through 30 (of 53 total)

    You must be logged in to reply to this topic. Login to reply