Basic concat over 500 times slower than straight select?

Question

Basic concat over 500 times slower than straight select?

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 SSC-Forever Points: 47068 More actions · Answer 1

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);

mister.magoo SSC-Forever Points: 47068 More actions · Answer 2

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);

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

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:!!

mister.magoo SSC-Forever Points: 47068 More actions · Answer 4

Any reason why you don't use this?

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

MM

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

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.

Brandie Tarvin SSC Guru Points: 173003 More actions · Answer 6

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.

grahamc SSCertifiable Points: 6762 More actions · Answer 7

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....

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

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.

Brandie Tarvin SSC Guru Points: 173003 More actions · Answer 9

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

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!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

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 ;-).

mister.magoo SSC-Forever Points: 47068 More actions · Answer 12

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);

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

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.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 14

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.

MM

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 15

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+.