This topic has been reported for inappropriate content
February 2, 2010 at 11:26 am
Paul White (2/2/2010)
The only SQLCLR feature I haven't found a good use for so far is the SQLCLR trigger. Would anyone care if those were removed?
Agreed there. I think they COULD be useful, however, if some modifications were made:
What I would like to see is a SQLCLR analytic operator - something that streams rows in and out - like the Segment and Sequence Project operators used by the likes of RANK and ROW_NUMBER. A sort of combination UDA and STVF...
Agreed!
UDAs are already the fastest way to stream rows from SQL Server into a SQLCLR object - blowing away the context connection method.
Really? I'm surprised to hear that you've seen that much of a difference between the two. What exactly did you test?
IEnumerable-wrapped SqlDataAdapters are an alternative, but having to use an external connection is a serious downside (as is the fact that 2008 broke this method AFAIK). In an attempt to get as
I personally don't think the external connection is much of a problem in most common cases. However, I am messing around with some parallel algorithms at the moment and opening four or eight external connections is kind of a pain, plus if I want to operate on a temp table I have to make it global, which totally sucks. Here's a connect item:
SQL Server 2008 did not break the ability to do the external connection, it simply changed the rules a bit. You're now forced to include a SqlFunctionAttribute with DataAccessKind set to DataAccessKind.Read. In 2005 that option only applied to the context connection. More info:
1. Passing LOB streaming input to a SQLCLR TVF always throws a wrong thread exception (!)
Always?? I have methods that do this that don't throw exceptions; are you referring to UDAs and not TVFs here? I haven't tested there...
2. UDA output is only produced from the Terminate() method, which is only called when all rows have been passed to the UDA via the Accumulate method (and possibly Merge()). This means that LOB output from a UDA has to be fully built before it can start to stream. Kinda seems to defeat the purpose of providing a streaming interface, really.
Agreed, but this is necessary because--at least today--we have no guarantee of the order in which the rows will be passed to the UDA.
3. SQL Server doesn't call Dispose on any stream wrapped by a SqlChars or SqlBytes object. If a other operators (like hash join) can spill to disk, why can't I?! 😛
Whether or not Dispose is called on SqlChars/SqlBytes seems like an implementation detail; what are you doing where you're even noticing this?
By the way, here's a related issue:
As far as I can tell, a T-SQL scalar function that does not access data will always be slower than a SQLCLR equivalent. And, since T-SQL scalar functions should never access data, that's not much of a restriction. T-SQL is interpreted whereas SQLCLR is compiled - so T-SQL seems entirely the wrong tool for scalar functions.
I think rather than saying that a T-SQL scalar function "will always be slower" it's more accurate to say that the T-SQL version "will never be faster". They can be more or less equivalent, especially when the function isn't doing much (e.g. adding 1 to the input). But the more complex the logic gets, the further ahead SQLCLR versions tend to get. Whether T-SQL is the right or wrong tool is a bigger question and herein lies the problem. The SQL Server team didn't make it especially easy for DBAs and others to work with this stuff and for many groups there is too much of a barrier to entry. Here's a Connect item that attempts to address this:
SQLCLR UDTs are very powerful and much underused, though their serialization behaviour could use some work. I was hoping for some optimization in this area for 2008 (which allows UDTs to exceed 8000 bytes) but no. It also amuses me slightly that the Microsoft-provided 2008 SQLCLT UDTs use the UNSAFE permission set...
Agreed again! And here's another Connect item:
But what I really want is something along the lines of IComparable. Here's one for that:
--
Adam Machanic
whoisactive
February 3, 2010 at 10:49 am
Adam Machanic (2/2/2010)
Whether or not Dispose is called on SqlChars/SqlBytes seems like an implementation detail; what are you doing where you're even noticing this?
By the way, here's a related issue:
Paul:
I saw that you left a note in this item so I added some code to test. Would appreciate any insight you can add with regard to what's going on in this situation. Knowing that my code can leak connections, no matter what I do about it--including following Bob Dorr's advice--is not exactly a wonderful feeling (not that it's keeping me up at night either; it's just one more thing we need to monitor so that it can be corrected when and if it happens).
--
Adam Machanic
whoisactive
February 3, 2010 at 11:31 pm
Hi Adam, and thanks so much for taking the time to reply so fully! I have looked at the connect items you listed (as you saw) but am struggling for the time to work on a worthwhile reply (in-laws visiting from England...)
I will certainly take a very interested look at that issue (and the other stuff you wrote about) as soon as I get a little time to myself 😉
Cheers
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 4, 2010 at 3:14 am
Adam Machanic (2/2/2010)
Paul White
UDAs are already the fastest way to stream rows from SQL Server into a SQLCLR object - blowing away the context connection method.Really? I'm surprised to hear that you've seen that much of a difference between the two. What exactly did you test?
Here you go...:-)
Results averaged over ten runs of the procedure and aggregate over the whole SalesOrderDetail table from the AdventureWorks sample database (2008 SR4):
Procedure : 995ms worker time
Aggregate: 487ms worker time
I disabled parallelism since I thought that might give the aggregate an unfair advantage 😉
My take is that the aggregate is so fast since it is so directly connected to the query plan (inside the stream aggregate operator) whereas the context connection method is a little more indirect and probably requires more marshalling and stuff...anyway, on to the code. Please feel free to suggest improvements or alternatives if you see any flaws.
-- This sample database is required
USE AdventureWorks;
GO
-- Turn off stuff we don't want to affect the results
SET NOCOUNT ON;
SET STATISTICS IO, TIME OFF;
GO
-- CLR functionality required
IF NOT EXISTS(SELECT * FROM sys.configurations WHERE name = N'clr enabled' AND value_in_use = 1)
BEGIN
EXECUTE sp_configure 'clr enabled', 1;
RECONFIGURE;
END;
GO
-- Drop test objects if they weren't dropped on a previous run
IF OBJECT_ID(N'AggregateTest', N'AF') IS NOT NULL DROP AGGREGATE dbo.AggregateTest;
IF OBJECT_ID(N'ProcedureTest', N'PC') IS NOT NULL DROP PROCEDURE dbo.ProcedureTest;
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'InputTest') DROP ASSEMBLY InputTest;
GO
-- SQLCLR assmebly containing the test procedure and aggregate
CREATE ASSEMBLY [InputTest]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030021946A4B0000000000000000E00002210B0108000012000000060000000000001E310000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000C830000053000000004000000803000000000000000000000000000000000000006000000C0000001C3000001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000024110000002000000012000000020000000000000000000000000000200000602E7273726300000008030000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000018000000000000000000000000000040000042000000000000000000000000000000000031000000000000480000000200050088210000940E00000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000360216731500000A7D010000042A6602257B010000040F09281600000A281700000A7D010000042A6602257B010000040F017B01000004281700000A7D010000042A32027B01000004731800000A2A3602036F1900000A7D010000042A3603027B010000046F1A00000A2A001B300700A70000000100001116731500000A0A7201000070731C00000A0B731D00000A0C08076F1E00000A0872330000706F1F00000A076F2000000A086F2100000A0D0972F40100706F2200000A13042B14060911046F2300000AA504000001281700000A0A096F2400000A2DE4178D2A000001130611061672080200701B1F261C732500000AA21106732600000A1305110516066F2700000A282800000A11056F2900000ADE0A072C06076F2A00000ADC2A0001100000020012008A9C000A000000001E02282B00000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000014040000237E0000800400000C06000023537472696E6773000000008C0A00001402000023555300A00C0000100000002347554944000000B00C0000E401000023426C6F620000000000000002000001571702020900000000FA013300160000010000002F0000000300000001000000080000000E000000010000002B0000000F0000000100000002000000010000000200000000000A000100000000000600470040000A0078005D000600890040000600900040000A00B900A4000A00C200A4000A00CC00A4000A00D500A4000A00DE00A4000A00E900A4000A00F100A40006002201180106006501180106006D025B02060084025B020600A1025B020600C0025B020600D9025B020600F2025B0206000D035B02060028035B02060060034103060074034103060082035B0206009B035B020600CB03B8036B00DF03000006000E04EE0306002E04EE030600560440000A006C045D000A008D045D000600940441030600AA0441030A00D7045D000A000305ED040A001105ED040A003E052B050A0058052B050A006A05ED040A0086052B050A00A7055D000A00B30551000A00BD055D000A00D6055D000A00E1055D000600F70540000000000001000000000001000100092110001800000005000100010001001000260000000D0002000700010098000A0050200000000086009F000E0001005E20000000008600FD0012000100782000000000860008012C000C0092200000000086000E0132000D009F2000000000E1012F0137000D00AD2000000000E10172013D000E00BC20000000009600AA0143000F008021000000008618B8010E000F0000000100BE0100000200CB0100000300DE0100000400F40100000500FD0100000600070200000700160200000800200200000900320200000A003C0200000B004402000001005102000001005702000001005902020009001100600137001100A4013D007100B80147007900B80147008100B80147008900B80147009100B80147009900B8014700A100B8014700A900B8014700B100B8014C00B900B8014700C100B8014700C900B8014700D100B8015100E100B8015700E900B8010E00F100B8010E00F900B8015C000901B801D6002100B80157004900B504DD002100BF04E2004900B801EB006100CB04DD006900A401EB001901B8010E002101B80147002901B8010E0029011C05F600310148054700390165050E0029017805FD0049019305030149019E050801490160010D015101B80111016101B8011B016101CB0523016901E9052A017101F2053001790103060E001900B8010E002E00430062012E008B00C3012E001B004D012E0023005C012E002B005C012E0033005C012E003B004D012E004B005C012E005B005C012E0063007A012E007300A4012E007B00B1012E008300BA0143009B006300E000DB00F100370102000A00030002000C0005000480000001000000000000000000000000004C0400000200000000000000000000000100370000000000020000000000000000000000010051000000000000000000003C4D6F64756C653E00496E707574546573742E646C6C00416767726567617465546573740053746F72656450726F63656475726573006D73636F726C69620053797374656D0056616C7565547970650053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E536572766572004942696E61727953657269616C697A65004F626A65637400446563696D616C005F746F74616C00496E69740053797374656D2E446174612E53716C54797065730053716C496E7433320053716C537472696E670053716C496E7431360053716C4D6F6E65790053716C446563696D616C0053716C477569640053716C4461746554696D6500416363756D756C617465004D65726765005465726D696E6174650053797374656D2E494F0042696E617279526561646572004D6963726F736F66742E53716C5365727665722E5365727665722E4942696E61727953657269616C697A652E5265616400526561640042696E617279577269746572004D6963726F736F66742E53716C5365727665722E5365727665722E4942696E61727953657269616C697A652E57726974650057726974650050726F63656475726554657374002E63746F720053616C65734F7264657249440053616C65734F7264657244657461696C49440043617272696572547261636B696E674E756D626572004F726465725174790050726F647563744944005370656369616C4F66666572494400556E6974507269636500556E69745072696365446973636F756E74004C696E65546F74616C00526F7747756964004D6F64696669656444617465004F74686572007200770053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500496E707574546573740053657269616C697A61626C654174747269627574650053716C55736572446566696E656441676772656761746541747472696275746500466F726D6174005374727563744C61796F7574417474726962757465004C61796F75744B696E64006765745F56616C7565006F705F4164646974696F6E0052656164446563696D616C0053716C50726F6365647572654174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053716C436F6D6D616E64007365745F436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6D6D616E64007365745F436F6D6D616E6454657874004462436F6E6E656374696F6E004F70656E0053716C44617461526561646572004578656375746552656164657200446244617461526561646572004765744F7264696E616C006765745F4974656D0053716C4D657461446174610053716C4462547970650053716C446174615265636F726400536574446563696D616C0053716C436F6E746578740053716C50697065006765745F506970650053656E640049446973706F7361626C6500446973706F73650000003163006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065003B000081BF530045004C0045004300540020005B00530061006C00650073004F007200640065007200490044005D002C005B00530061006C00650073004F007200640065007200440065007400610069006C00490044005D002C005B00430061007200720069006500720054007200610063006B0069006E0067004E0075006D006200650072005D002C005B004F0072006400650072005100740079005D002C005B00500072006F006400750063007400490044005D002C005B005300700065006300690061006C004F006600660065007200490044005D002C005B0055006E0069007400500072006900630065005D002C005B0055006E00690074005000720069006300650044006900730063006F0075006E0074005D002C005B004C0069006E00650054006F00740061006C005D002C005B0072006F00770067007500690064005D002C005B004D006F0064006900660069006500640044006100740065005D00460052004F004D0020005B0041006400760065006E00740075007200650057006F0072006B0073005D002E005B00530061006C00650073005D002E005B00530061006C00650073004F007200640065007200440065007400610069006C005D0000134C0069006E00650054006F00740061006C00000B54006F00740061006C0000DF19ACC82ADD604D8A3B878CC0FB238D0008B77A5C561934E089030611110320000119200B01111511151119111D111511151121112111251129112D052001011108042000112505200101123105200101123503000001042001010E042001010205200101116D04200101080620010111808172010002000000050054080B4D61784279746553697A65100000005402174973496E76617269616E74546F4475706C696361746573005402124973496E76617269616E74546F4E756C6C73015402124973496E76617269616E74546F4F726465720154020D49734E756C6C4966456D707479010620010111808904200011110800021111111111110520010111110401000000062001011280910520001280A1042001080E0420011C0803200002092004010E1180AD0505072001011D1280A9062002010811110500001280B9062001011280B115070711111280911280951280A1081280B11D1280A90E010009496E70757454657374000005010000000017010012436F7079726967687420C2A920203230313000002901002435313631356464322D366139612D343533622D613062362D38353738663636643331303700000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000000021946A4B00000000020000008E00000038300000381200005253445307F9834B2A28B343A6A33205E758D62D05000000433A5C446F63756D656E747320616E642053657474696E67735C5061756C5C4D7920446F63756D656E74735C56697375616C2053747564696F20323030385C50726F6A656374735C496E707574546573745C496E707574546573745C6F626A5C52656C656173655C496E707574546573742E706462000000F030000000000000000000000E3100000020000000000000000000000000000000000000000000000031000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000B00200000000000000000000B00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00410020000010053007400720069006E006700460069006C00650049006E0066006F000000EC01000001003000300030003000300034006200300000003C000A000100460069006C0065004400650073006300720069007000740069006F006E000000000049006E0070007500740054006500730074000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D006500000049006E0070007500740054006500730074002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000049006E0070007500740054006500730074002E0064006C006C00000034000A000100500072006F0064007500630074004E0061006D0065000000000049006E0070007500740054006500730074000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000203100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
-- SQLCLR procedure
CREATE PROCEDURE dbo.ProcedureTest
AS EXTERNAL NAME InputTest.StoredProcedures.ProcedureTest;
GO
-- SQLCLR aggregate
CREATE AGGREGATE dbo.AggregateTest
(
@SalesOrderID INT,
@SalesOrderDetailID INT,
@CarrierTrackingNumber NVARCHAR(25),
@OrderQty SMALLINT,
@ProductID INT,
@SpecialOfferID INT,
@UnitPrice MONEY,
@UnitPriceDiscount MONEY,
@LineTotal DECIMAL(38,6),
@RowGuid UNIQUEIDENTIFIER,
@ModifiedDate DATETIME
)
RETURNS DECIMAL(38,6)
EXTERNAL NAME InputTest.AggregateTest;
GO
-- Flush dirty buffers to disk
CHECKPOINT;
-- Dump the buffer pool
DBCC DROPCLEANBUFFERS;
-- Dump the system caches
DBCC FREESYSTEMCACHE('ALL')
GO
-- Variables used as 'bit buckets'
DECLARE @SalesOrderID INT,
@SalesOrderDetailID INT,
@CarrierTrackingNumber NVARCHAR(25),
@OrderQty SMALLINT,
@ProductID INT,
@SpecialOfferID INT,
@UnitPrice MONEY,
@UnitPriceDiscount MONEY,
@LineTotal DECIMAL(38,6),
@RowGuid UNIQUEIDENTIFIER,
@ModifiedDate DATETIME;
-- Warm the buffer pool with all pages from the test table
SELECT @SalesOrderID = [SalesOrderID],
@SalesOrderDetailID = [SalesOrderDetailID],
@CarrierTrackingNumber = [CarrierTrackingNumber],
@OrderQty = [OrderQty],
@ProductID = [ProductID],
@SpecialOfferID = [SpecialOfferID],
@UnitPrice = [UnitPrice],
@UnitPriceDiscount = [UnitPriceDiscount],
@LineTotal = [LineTotal],
@RowGuid = [rowguid],
@ModifiedDate = [ModifiedDate]
FROM AdventureWorks.Sales.SalesOrderDetail;
GO
-- Run the aggregate test ten times
SELECT dbo.AggregateTest(
[SalesOrderID]
,[SalesOrderDetailID]
,[CarrierTrackingNumber]
,[OrderQty]
,[ProductID]
,[SpecialOfferID]
,[UnitPrice]
,[UnitPriceDiscount]
,[LineTotal]
,[rowguid]
,[ModifiedDate])
FROM AdventureWorks.Sales.SalesOrderDetail
OPTION (MAXDOP 1); -- Parallelism might give the aggregate an unfair advantage
GO 10
-- Test the procedure ten times
EXECUTE dbo.ProcedureTest;
GO 10
-- Results
SELECT ST.text,
QS.execution_count,
avg_elapsed_time_ms = QS.total_elapsed_time / QS.execution_count / 1000,
avg_logical_reads = QS.total_logical_reads / QS.execution_count,
avg_cpu_time_ms = QS.total_worker_time / QS.execution_count / 1000
FROM sys.dm_exec_query_stats QS
CROSS
APPLY sys.dm_exec_sql_text (QS.sql_handle) ST
WHERE ST.text LIKE '%aggregate test%'
AND ST.text NOT LIKE '%sys.dm_exec_query_stats%'
UNION ALL
SELECT ST.text,
PS.execution_count,
avg_elapsed_time_ms = PS.total_elapsed_time / PS.execution_count / 1000,
avg_logical_reads = PS.total_logical_reads / PS.execution_count,
avg_cpu_time_ms = PS.total_worker_time / PS.execution_count / 1000
FROM sys.dm_exec_procedure_stats PS
CROSS
APPLY sys.dm_exec_sql_text (PS.sql_handle) ST
WHERE ST.text = N'ProcedureTest -- StoredProcedures.ProcedureTest'
GO
-- Tidy up
IF OBJECT_ID(N'AggregateTest', N'AF') IS NOT NULL DROP AGGREGATE dbo.AggregateTest;
IF OBJECT_ID(N'ProcedureTest', N'PC') IS NOT NULL DROP PROCEDURE dbo.ProcedureTest;
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'InputTest') DROP ASSEMBLY InputTest;
GO
The C# code follows for anyone who prefers to compile it for themselves (procedure first, then aggregate):
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ProcedureTest()
{
decimal total = 0M;
using (SqlConnection conn = new SqlConnection("context connection=true;"))
{
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = @"" +
"SELECT [SalesOrderID]" +
",[SalesOrderDetailID]" +
",[CarrierTrackingNumber]" +
",[OrderQty]" +
",[ProductID]" +
",[SpecialOfferID]" +
",[UnitPrice]" +
",[UnitPriceDiscount]" +
",[LineTotal]" +
",[rowguid]" +
",[ModifiedDate]" +
"FROM [AdventureWorks].[Sales].[SalesOrderDetail]";
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
int ordinal = reader.GetOrdinal("LineTotal");
while (reader.Read())
{
total += (decimal)reader[ordinal];
}
SqlDataRecord sdr = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("Total", SqlDbType.Decimal, 38, 6) });
sdr.SetDecimal(0, total);
SqlContext.Pipe.Send(sdr);
}
}
};
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedAggregate
(
Format.UserDefined,
MaxByteSize = 16,
IsInvariantToDuplicates = false,
IsInvariantToNulls = true,
IsInvariantToOrder = true,
IsNullIfEmpty = true
)
]
public struct AggregateTest : IBinarySerialize
{
private decimal _total;
public void Init()
{
_total = 0M;
}
public void Accumulate
(
SqlInt32 SalesOrderID,
SqlInt32 SalesOrderDetailID,
SqlString CarrierTrackingNumber,
SqlInt16 OrderQty,
SqlInt32 ProductID,
SqlInt32 SpecialOfferID,
SqlMoney UnitPrice,
SqlMoney UnitPriceDiscount,
SqlDecimal LineTotal,
SqlGuid RowGuid,
SqlDateTime ModifiedDate
)
{
this._total += LineTotal.Value;
}
public void Merge(AggregateTest Other)
{
this._total += Other._total;
}
public SqlDecimal Terminate()
{
return new SqlDecimal(this._total);
}
#region IBinarySerialize Members
void IBinarySerialize.Read(System.IO.BinaryReader r)
{
this._total = r.ReadDecimal();
}
void IBinarySerialize.Write(System.IO.BinaryWriter w)
{
w.Write(this._total);
}
#endregion
}
Paul
edit: for code formatting, as usual
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 4, 2010 at 3:25 am
Adam Machanic (2/2/2010)
Paul White (2/2/2010)
Passing LOB streaming input to a SQLCLR TVF always throws a wrong thread exceptionAlways?? I have methods that do this that don't throw exceptions; are you referring to UDAs and not TVFs here? I haven't tested there...
I should have known better than to say 'always' - I know it wasn't clear, but I was referring to my recent experiences trying to pass the streaming LOB output from one SQLCLR component (an aggregate, as it happens) to a SQLCLR TVF. Passing 'ordinary' LOBs into a SQLCLR TVF is fine, of course 🙂
Sorry about the confusion there - I am still quite emotional about the amount of time and effort I 'wasted' on pursuing that idea only to be hit by the 'wrong thread' nonsense.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 4, 2010 at 3:37 am
Adam Machanic (2/2/2010)
Paul White (2/2/2010)
...This means that LOB output from a UDA has to be fully built before it can start to stream. Kinda seems to defeat the purpose of providing a streaming interface, really.Agreed, but this is necessary because--at least today--we have no guarantee of the order in which the rows will be passed to the UDA.
Absolutely - and the sooner IsInvariantToOrder is implemented the better.
What I was getting at though, is that without this order guarantee, I am quite happy to do a ROW_NUMBER OVER in the outer query to provide an ordering context for the UDA. If I am sensible with indexing, I can engineer it such that the ranking function is an extremely low-cost addition to the plan.
Now that a UDA can accept multiple parameters, I can write: SELECT dbo.UDA(@value, @row_number) and handle ordering issues inside the Accumulate method. The ranking function will tend to order the rows, and while I can't rely on that order being preserved, I can optimize for it.
So, I can write the Accumulate method to 'stream' if rows are received in natural or reverse sequence. Worst case, the plan somehow generates a 'random' row order into the UDA (a hash partitioning exchange for example) and my optimization fails. The UDA can then degrade gracefully, falling back to a less efficient algorithm that inevitably consumes more memory.
This does complicate the code, but the potential benefits made it seem worthwhile.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 4, 2010 at 4:05 am
Adam Machanic (2/2/2010)
Paul White (2/2/2010)
SQL Server doesn't call Dispose on any stream wrapped by a SqlChars or SqlBytes object.Whether or not Dispose is called on SqlChars/SqlBytes seems like an implementation detail; what are you doing where you're even noticing this?
Experimenting... :w00t:
Let me start by saying that this was entirely for my own learning purposes, and never intended for serious use!
It's the row-ordering input to a UDA problem again. Worst case, as I mentioned, is that rows arrive in an order which maximizes the number of rows I need to cache in Accumulate() - and maybe Merge(). Math is not my strong point, but I think the worst case involves me caching (N/2) - 1 rows for an N-row input set. For a large number of rows, I'm going to hurt the server if I try to do that in memory.
SqlChars and SqlBytes can wrap any System.IO.Stream - so in the very worst case, with millions of rows arriving in the worst order possible, I'd like my UDA to act a bit like a hash join that exceeds its memory grant: use a bit of disk space, and ultimately bail out completely by writing the whole set to disk via a (buffered) FileStream.
Bailing to disk is a bit pointless if I just have to read the whole file into memory inside Terminate(), so I wanted to return a SqlBytes wrapping the still-open FileStream from Terminate, and let the TVF deal with it.
This is where the need for a Dispose() comes in - to release the file handle and enable the file to delete itself (assuming the stream was created with that option). It just would have been nice if SQL Server had been a good citizen and always called Dispose() on classes derived from Stream. Stream does implement IDisposable, after all...
Talking of implementation details, it might interest/horrify you to know that I did get this to work - passing a FileStream reference inside SqlBytes from a UDA to a TVF.
It turns out that SQL Server reads the stream inside the SqlBytes asynchronously - via the BeginRead and EndRead methods. By creating a new class that inherited from FileStream, and overriding the EndRead method, I was able to call Dispose() at the correct time. In the overridden method, I just check the Position of the stream against its length, and dispose it if it is at the end.
Once dispose was called, the file closed and deleted itself, and everything worked. Yes, I know, I know, you don't have to say anything; I'm aware of how crazy this is, but it was, after all, done in the name of Science!
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 4, 2010 at 8:28 am
Last post for tonight (which is now very much this morning). I read the Connect items with great interest, and voted on all except the trigger one. I am still mulling that one over. The ThreadAbortException thing will be tomorrow evenings project I think. Nice to have something interesting to look at.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 5, 2010 at 4:16 am
Adam Machanic (2/3/2010)
I saw that you left a note in this item so I added some code to test. Would appreciate any insight you can add with regard to what's going on in this situation. Knowing that my code can leak connections, no matter what I do about it--including following Bob Dorr's advice--is not exactly a wonderful feeling (not that it's keeping me up at night either; it's just one more thing we need to monitor so that it can be corrected when and if it happens).
After a false start, I remembered the reason and solution from a few years ago:
A ThreadAbortException is thrown when the client sends an Attention (to cancel the batch), but it is only received by the foreground thread. When the SqlCommand is executing, it is running on the foreground thread, and totally ignores the exception (which makes sense if you think about it).
So, what we need to do is get the execution onto a background thread, and wait for it to complete. This way, our code waits on the foreground thread and can catch the ThreadAbortException. Handling it is easy - we just call Cancel on the SqlCommand and tidy up.
The procedure responds instantly to the Attention, and cleans up the connection correctly.
I posted code on your Connect item, but I'll reproduce it here just in case you look at this reply first. It's not beautiful code, but it'll have to do.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Threading;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void start_waiting()
{
using (SWImpl swi = new SWImpl())
{
swi.start_waiting();
}
}
private class SWImpl : IDisposable
{
ManualResetEvent mre;
SqlConnection conn;
SqlCommand comm;
public void start_waiting()
{
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
sb.DataSource = @".\SQL2008";
sb.IntegratedSecurity = true;
sb.Enlist = false;
conn = new SqlConnection(sb.ConnectionString);
conn.Open();
comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "WAITFOR DELAY '00:00:15'";
// Queue the command execution on a background thread
// The manual reset event will be signalled when it completes
mre = new ManualResetEvent(false);
ThreadPool.QueueUserWorkItem(new WaitCallback(delegate { comm.ExecuteNonQuery(); mre.Set(); }));
try
{
// Wait for the command to complete
mre.WaitOne(30000);
}
catch (System.Threading.ThreadAbortException)
{
// Maybe an attention, in any case, cancel the command
comm.Cancel();
}
finally
{
// Clean up
this.Dispose();
}
}
#region IDisposable Members
public void Dispose()
{
if (this.comm != null)
{
this.comm.Dispose();
}
if (this.conn != null)
{
SqlConnection.ClearPool(this.conn);
this.conn.Dispose();
}
this.comm = null;
this.conn = null;
}
#endregion
}
};
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 5, 2010 at 4:18 am
Pedro DeRose [MSFT] (1/22/2010)
Which, I think, may be a good slogan for SQLCLR: better than cutting boards with a hammer. 🙂
Do you mind if I adopt that gem into my signature? Love it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 5, 2010 at 7:46 am
Paul White (2/5/2010)
So, what we need to do is get the execution onto a background thread, and wait for it to complete.
Hi Paul,
Might want to re-read what Bob Dorr has to say about this technique:
😀
In Bob's case he was only working with a single background thread; in this example using the background thread is actually 50% worse because we're already spinning up a new thread for the linkback connection. So now there are three threads in play, per request... Not going to work too well if your app has to handle a lot of concurrent requests.
--
Adam Machanic
whoisactive
February 5, 2010 at 8:02 am
Paul White (2/4/2010)
Results averaged over ten runs of the procedure and aggregate over the whole SalesOrderDetail table from the AdventureWorks sample database (2008 SR4):Procedure : 995ms worker time
Aggregate: 487ms worker time
Your example shows that an aggregate can aggregate faster than a stored procedure, but is that really surprising? The stored procedure needs to do data access over the context connection, whereas the aggregate simply takes values directly from the query processor. What I expected when you mentioned this was some kind of streaming example where the procedure and aggregate would each spit out a set of rows that had been manipulated in some way--some kind of scenario where someone might actually consider whether to use an aggregate or a procedure.
--
Adam Machanic
whoisactive
February 5, 2010 at 8:10 am
Paul White (2/4/2010)
Now that a UDA can accept multiple parameters, I can write: SELECT dbo.UDA(@value, @row_number) and handle ordering issues inside the Accumulate method. The ranking function will tend to order the rows, and while I can't rely on that order being preserved, I can optimize for it.
This is certainly an interesting and promising idea and I'll have to play with it a bit. I'm not sure, however, whether it helps to solve windowing problems, which is what I had in mind for ordered aggregates. If you need a 1:1 "aggregation," like a running sum, you have to group by something distinct like the row number. But if you do that the QP will spin up a new instance of the aggregate per row, which means you won't have access to the previous row's value. And if you group by anything else you'll have fewer rows in the output than you will in the input. Have you gotten around that somehow? Or do you have some other class of problem where you're able to leverage this?
--
Adam Machanic
whoisactive
February 5, 2010 at 8:13 am
Paul White (2/4/2010)
SqlChars and SqlBytes can wrap any System.IO.Stream - so in the very worst case, with millions of rows arriving in the worst order possible, I'd like my UDA to act a bit like a hash join that exceeds its memory grant: use a bit of disk space, and ultimately bail out completely by writing the whole set to disk via a (buffered) FileStream.
Agreed, but don't you think the SQLCLR hosting environment should handle this itself and spill to tempdb just like everything else in SQL Server does? Pedro, are you listening? 🙂
--
Adam Machanic
whoisactive
February 5, 2010 at 8:37 am
Adam Machanic (2/5/2010)
Hi Paul,Might want to re-read what Bob Dorr has to say about this technique:
Tricky but Stupid - Don't do this.😀
In Bob's case he was only working with a single background thread; in this example using the background thread is actually 50% worse because we're already spinning up a new thread for the linkback connection. So now there are three threads in play, per request... Not going to work too well if your app has to handle a lot of concurrent requests.
Well yes that is true - and I am sorry if I misunderstood what you were trying to achieve.
When I read Bob's remarks, I did treat that as a separate issue because he was talking about doing rather more esoteric things than just calling Execute on a SqlCommand.
For the code presented in your connect item, I still think this is the best we can do currently. ThreadPool isn't too bad since its size is fixed and the threads tend to be around anyway. One can always code a manager class to stop things getting too out of hand.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply