Using "Multiple Assignment Variables" for a Generic Compare Data Script

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Stephen Tirone » Using "Multiple Assignment...

16 posts, Page 2 of 2

««1 2

Using "Multiple Assignment Variables" for a Generic Compare Data Script

Rate Topic

Display Mode

Topic Options

Author

Message

stevemc

Posted Tuesday, January 26, 2010 10:46 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 3:00 PM
Points: 93, Visits: 502

Paul asks, @Steve McNamee: Are you saying that the technique used in the article is faster than FOR XML PATH for fewer than 500 elements? Not nit-picking; I just want to be clear on it. For me, the greater problem is that using variables in this way lacks an ORDER guarantee. Incorrect results, even when produced fractionally more quickly, are rarely to be desired

No I'm not saying that; forgive my offhand remark. To clarify, about 6 months ago (before reading the great threads here about shredding), I was helping test one of my colleage's CSV-shredding CLR functions. I used a variant of this topic's technique to generate test data. When I lazily included the CSV generating code within the timing of the CLR, the results caused me to doubt the effectiveness of the CLR. I finally figured out that this technique, while handy and cool, can take a looong time to generate large CSV strings. The number 500 sticks in my head, but I don't have any evidence to back that up. For sure, when I got up to about 50,000 elements, the technique would not even return results before I got tired of waiting. That's when I found the XML technique to generate CSV strings, which did not seem to suffer from the same performance limitation.

From an email I sent at the time:
Last week, I thought I had found a performance limit in the CLR parsing function when parsing strings larger than about 30k elements. I was wrong about that. The offending code was the TSQL code I used the create the csv list that I then parsed using the function. With a @CSVList list of 515847 members, a select count(*) from the CLR function takes about a second.

Post #854155

Paul White

Posted Tuesday, January 26, 2010 11:12 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:01 PM
Points: 10,990, Visits: 10,545

Cool stuff Steve - thanks for taking the time to write such a comprehensive answer - makes sense Smile

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #854174

scott mcnitt

Posted Wednesday, January 27, 2010 7:07 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:25 PM
Points: 29, Visits: 272

MAV query is a very useful techique. I am sure it is something I am doing wrong (or is there a database setting I am missing) but I only get one column in my output when I should get 117.

declare @col_list varchar(4000), @tbl_name sysname
select @tbl_name = 'Master', @col_list = ''

--list all the columns first
select '[' + column_name + ']' as col
from information_schema.columns
where table_name = @tbl_name

--MAV query
select @col_list = @col_list + col + ', '
from ( select '[' + column_name + ']' as col
from information_schema.columns
where table_name = @tbl_name) d
order by col

select @col_list
print @col_list

_________________________
(117 row(s) affected)

(1 row(s) affected)
[WorkPaidByAnother],

Post #854359

pbarbin

Posted Wednesday, January 27, 2010 8:01 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, December 13, 2012 7:23 AM
Points: 22, Visits: 349

@PaulWhite said: Absoutely - and I was going to include a quote from BOL to make the point that system views are preferred over INFORMATION_SCHEMA...but I didn't want to pile on too much. I agree that it is rarely better to use anything other than the system views like sys.columns in this case.

Thanks for all the good information you post, but I have to chime in on this one. Personally, I use system views almost exclusively, but there is a good reason to use INFORMATION_SCHEMA views and that is for portability of code. They are the SQL-92 standard. And when have you ever been afraid to pile on too much? Tongue

Post #854412

Paul White

Posted Wednesday, January 27, 2010 8:07 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:01 PM
Points: 10,990, Visits: 10,545

pbarbin (1/27/2010)

Ah...code portability. Pretty rare requirement in my experience, and there will likely be bigger challenges in porting to another database product than use of system views...but ok, it's a consideration I guess.

When have I ever been afraid of piling on too much? Just the once it seems Laugh

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #854870

Paul White

Posted Wednesday, January 27, 2010 8:18 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:01 PM
Points: 10,990, Visits: 10,545

scott mcnitt (1/27/2010)

MAV query is a very useful techique. I am sure it is something I am doing wrong (or is there a database setting I am missing) but I only get one column in my output when I should get 117.

That's another problem with this technique: it is possible for a plan to be produced where the desired 'concatenation' doesn't work, and just one of the candidate values ends up in the variable. The query you posted happened to work 'correctly' on the instance I tested it with - but it is not guaranteed.

Also, note that the ORDER BY clause in your query only applies to the final output of rows - it does not enforce an order for the multiple assignment operation.

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #854874

« Prev Topic | Next Topic »

16 posts, Page 2 of 2

««1 2

Permissions