SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
stevemc
stevemc
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 699
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.
Paul White
Paul White
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61118 Visits: 11397
Cool stuff Steve - thanks for taking the time to write such a comprehensive answer - makes sense :-)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
scott mcnitt
scott mcnitt
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 Visits: 437
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],
pbarbin
pbarbin
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 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? :-P
Paul White
Paul White
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61118 Visits: 11397
pbarbin (1/27/2010)
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? :-P

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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61118 Visits: 11397
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search