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

Ninja's_RGR'us (4/13/2011)
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)

Maybe we do have some version specific issues then because on mine, prepending is definitely required - conversion to varchar alone doesn't help....

I might try it on SQL2005 as well...

MM

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

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

mister.magoo (4/13/2011)
Ninja's_RGR'us (4/13/2011)
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)
Maybe we do have some version specific issues then because on mine, prepending is definitely required - conversion to varchar alone doesn't help....
I might try it on SQL2005 as well...

I guess I wasn't clear. It takes both. Prepending alone is not enough, it takes EVERYTHING varchar. Even a single nchar in there screws it up again.

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

Posting final code... prepending with "correct" order of the tables...

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 ORDER BY name DESC

PRINT LEN(@Exec)

PRINT @Exec

--EXEC (@Exec)

--27 minutes to run on 18 GB db, over 33 000 stats to update, on very slow time of day.

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

Ninja's_RGR'us (4/13/2011)
I guess I wasn't clear. It takes both. Prepending alone is not enough, it takes EVERYTHING varchar. Even a single nchar in there screws it up again.

Oh, right - yes - all VARCHAR is a requirement for this to work quickly - agreed.

And thanks for posting the final code - I will definitely be using this in the future !

MM

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

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

mister.magoo (4/13/2011)
Ninja's_RGR'us (4/13/2011)
I guess I wasn't clear. It takes both. Prepending alone is not enough, it takes EVERYTHING varchar. Even a single nchar in there screws it up again.
Oh, right - yes - all VARCHAR is a requirement for this to work quickly - agreed.
And thanks for posting the final code - I will definitely be using this in the future !

Ya, it's simple but you got to remember to do order by DESC to get the ASC order :w00t:.

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

Does the order really matter?

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.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Ninja's_RGR'us (4/12/2011)
How much data did you test with? I have over 8300 tables to concatenate. On top of that the table names or fairly long, here's an "average" name.

Certainly not enough to match 8300 long table names. I'll setup a test, though.

Having a {gasp!} case sensitive DB shouldn't matter for this concatenation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Brandie Tarvin (4/13/2011)
Does the order really matter?

It actually could. If it fails to finish in the allowed time I need to be able to start back where it was.

If you know which statement failed (IE syntax or whatknot), you can restart the sequence from there on a manual run later on.

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

Jeff Moden (4/13/2011)
Ninja's_RGR'us (4/12/2011)
How much data did you test with? I have over 8300 tables to concatenate. On top of that the table names or fairly long, here's an "average" name.
Certainly not enough to match 8300 long table names. I'll setup a test, though.
Having a {gasp!} case sensitive DB shouldn't matter for this concatenation.

Thank god for SQL PROMPT and the toolbelt for that matter!!! (not on 2008 yet and can't use 2008 to connect on 2005 for intellisense :sick:).

My final string size is around 670K. And if you read the statistics IO you get almost 0.6 TB of processed pages!!! :w00t:

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

Ninja's_RGR'us (4/13/2011)
Brandie Tarvin (4/13/2011)
Does the order really matter?
It actually could. If it fails to finish in the allowed time I need to be able to start back where it was.
If you know which statement failed (IE syntax or whatknot), you can restart the sequence from there on a manual run later on.

In that case, (IIRC) Brandi is spot on with storing the individual commands (or perhaps just the table names) in a table and running a WHILE loop over it. A WHILE loop isn't going to kill anything for speed here and a proc that uses it could take an optional parameter of which table it should start with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brandie Tarvin SSC Guru Points: 172995 More actions · Answer 11

Ninja's_RGR'us (4/13/2011)
Brandie Tarvin (4/13/2011)
Does the order really matter?
It actually could. If it fails to finish in the allowed time I need to be able to start back where it was.
If you know which statement failed (IE syntax or whatknot), you can restart the sequence from there on a manual run later on.

Ah. I forgot about the human factor needs. I was too busy trying to figure out how order affected the performance. @=)

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 SSC Guru Points: 172995 More actions · Answer 12

Jeff Moden (4/13/2011)
Ninja's_RGR'us (4/13/2011)
Brandie Tarvin (4/13/2011)
Does the order really matter?
It actually could. If it fails to finish in the allowed time I need to be able to start back where it was.
If you know which statement failed (IE syntax or whatknot), you can restart the sequence from there on a manual run later on.
In that case, (IIRC) Brandi is spot on with storing the individual commands (or perhaps just the table names) in a table and running a WHILE loop over it. A WHILE loop isn't going to kill anything for speed here and a proc that uses it could take an optional parameter of which table it should start with.

I'd stick it all in a job that also scanned for new tables (and added them to the table) before running the code.

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 13

Brandie Tarvin (4/13/2011)
Ninja's_RGR'us (4/13/2011)
Brandie Tarvin (4/13/2011)
Does the order really matter?
It actually could. If it fails to finish in the allowed time I need to be able to start back where it was.
If you know which statement failed (IE syntax or whatknot), you can restart the sequence from there on a manual run later on.
Ah. I forgot about the human factor needs. I was too busy trying to figure out how order affected the performance. @=)

Agreed, but I'm on a yellow light to run this... just because I'm seeing discrepencies between estimates and actual # of rows in 1 query.

Now since running fullscan updates on the whole db runs in less than 30 minutes I'll just leave it as is for now. My immediate problem solved.

My real intent was to implement it in my reindexing job from sql fool. But I didn't want to mess with it. Moreover I only have 4-5 index rebuild / day and all the rest is reorganize. So updating everything is not that much longer at this point.

Case closed for me... even if it's not perfect by my book.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

Gotta say that I am absolutely stunned that we have spent this much effort to try to concatenate a string that has a cursor-driven solution that offers many benefits:

1) incredibly simple to code

2) negligible performance 'hit', ESPECIALLY compared to the relatively HUGE amount of time the actual work will take!!!!!!!

3) offers the ability to run multiple windows for concurrent execution of the work by simple name or object_id range partitioning of the cursor. Yes, you can do this with an exectuted built string too.

4) doesn't suffer from the large compile time and huge plan that will happen when you try to execute that 670K string. I wouldn't be surprised if the compile time didn't take longer than the 'overhead' associated with the cursor.

This is definitely a case where I say use the right tool for the job and I strongly believe that a cursor is the right tool for the job! 😎

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

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

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.