Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Using "Multiple Assignment Variables" for a Generic Compare Data Script Expand / Collapse
Author
Message
Posted Tuesday, January 26, 2010 10:46 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 10:31 AM
Points: 94, Visits: 562
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
Posted Tuesday, January 26, 2010 11:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 11,168, Visits: 10,931
Cool stuff Steve - thanks for taking the time to write such a comprehensive answer - makes sense



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #854174
Posted Wednesday, January 27, 2010 7:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:03 PM
Points: 39, Visits: 352
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
Posted Wednesday, January 27, 2010 8:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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?

Post #854412
Posted Wednesday, January 27, 2010 8:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 11,168, Visits: 10,931
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?

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




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #854870
Posted Wednesday, January 27, 2010 8:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 11,168, Visits: 10,931
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 »

Add to briefcase ««12

Permissions Expand / Collapse