October 21, 2010 at 11:29 pm
gmrose (10/21/2010)
Thank you again to Lutz and Wayne. I updated my script per Lutz' suggestions to use table #tmpChecks instead of table variable @tmpChecks. I made some minor adjustments to exclude about 100,000 unneeded rows. Now the script runs as desired.For timing, yesterday's script ran for over 23 hours and was less than 5% complete when I halted it. Today's script succussfully completed in less than 4 minutes. That's what I call a real improvement.
gmrose
You are processing information for a bank. You might want to post your final code so we can double check for you. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2010 at 8:45 am
gmrose (10/21/2010)
Thank you again to Lutz and Wayne. I updated my script per Lutz' suggestions to use table #tmpChecks instead of table variable @tmpChecks. I made some minor adjustments to exclude about 100,000 unneeded rows. Now the script runs as desired.For timing, yesterday's script ran for over 23 hours and was less than 5% complete when I halted it. Today's script succussfully completed in less than 4 minutes. That's what I call a real improvement.
gmrose
Not sure if I'd be happy with that performance yet... (even without using the quirky update)
How long does each part of the two actually take? (timing for populating the temp table and for the final select)
Another question:
Are you sure that your WHERE condition "ISNUMERIC(PrintedCheckNum) = 1" will actually return the results as needed? What would you do with a row holding '100E12'??
At this point I'd really like to see the actual execution plans before making any suggestions...
October 22, 2010 at 9:32 am
gmrose (10/21/2010)
For timing, yesterday's script ran for over 23 hours and was less than 5% complete when I halted it. Today's script succussfully completed in less than 4 minutes. That's what I call a real improvement.gmrose
Heh Jeff's gonna hate me for this but I couldn't resist it.
400,000 rows in 15 seconds:
IF OBJECT_ID('tempdb..#tempstore') IS NOT NULL DROP TABLE #tempstore;
CREATE TABLE #tempstore (acct_nbr INT,
ckid INT,
stat varchar(10),
Grp int);
INSERT INTO #tempstore (acct_nbr, ckid, stat)
SELECT 1, 101 ,'Open' UNION ALL
SELECT 1, 102 ,'Open' UNION ALL
SELECT 1, 103 ,'Open' UNION ALL
SELECT 1, 104 ,'Void' UNION ALL
SELECT 1, 105 ,'Void' UNION ALL
SELECT 1, 106 ,'Open' UNION ALL
SELECT 1, 107 ,'Open' UNION ALL
SELECT 1, 108 ,'Open' UNION ALL
SELECT 1, 109 ,'Void' UNION ALL
SELECT 1, 110 ,'Open' UNION ALL
SELECT 2, 101 ,'Open' UNION ALL
SELECT 2, 102 ,'Open' UNION ALL
SELECT 2, 103 ,'Open' UNION ALL
SELECT 2, 104 ,'Void' UNION ALL
SELECT 3, 105 ,'Void' UNION ALL
SELECT 3, 106 ,'Open' UNION ALL
SELECT 3, 107 ,'Open' UNION ALL
SELECT 4, 108 ,'Open' UNION ALL
SELECT 4, 109 ,'Void' UNION ALL
SELECT 4, 110 ,'Open';
-- 400,000 rows of data
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
CREATE TABLE #temp (acct_nbr INT,
ckid INT,
stat varchar(10),
Grp int);
INSERT INTO #temp (acct_nbr, ckid, stat)
SELECT acct_nbr+(4*n), ckid+(20*n), stat
FROM #tempstore
CROSS JOIN (SELECT TOP 20000 [n] = ROW_NUMBER() OVER(ORDER BY a.[name])-1 FROM master.dbo.syscolumns a, master.dbo.syscolumns b) n
-- copy our data to a working table
SELECT rn = ROW_NUMBER() OVER (ORDER BY acct_nbr, ckid),
acct_nbr, ckid, stat
INTO #Workings
FROM #temp
ORDER BY acct_nbr, ckid
CREATE UNIQUE CLUSTERED INDEX CIrn ON #Workings ([rn] ASC)
-- get the results
;WITH Calculator AS (
SELECT rn, acct_nbr, ckid, stat, NewGroup = CAST(1 AS INT)
FROM #Workings WHERE rn = 1
UNION ALL
SELECT w.rn, w.acct_nbr, w.ckid, w.stat,
NewGroup = CASE
WHEN w.acct_nbr <> c.acct_nbr THEN 1
WHEN w.stat <> c.stat THEN c.NewGroup + 1
ELSE c.NewGroup END
FROM #Workings w
INNER JOIN Calculator c ON c.rn+1 = w.rn)
SELECT acct_nbr, ckid, stat, NewGroup
FROM Calculator
OPTION( MAXRECURSION 0)
-- 400,000 rows: 15 seconds
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 22, 2010 at 10:29 pm
Chris Morris-439714 (10/22/2010)
Heh Jeff's gonna hate me for this but I couldn't resist it.400,000 rows in 15 seconds:
Actually, I don't hate you. That's some good, solid, creative, fast code, Chris. There's just one thing... you code only displays the data. It doesn't update either table. Make it so you update one of the two tables and we'll have a drag race. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2010 at 12:11 am
Jeff Moden (10/22/2010)
Chris Morris-439714 (10/22/2010)
Heh Jeff's gonna hate me for this but I couldn't resist it.400,000 rows in 15 seconds:
Actually, I don't hate you. That's some good, solid, creative, fast code, Chris. There's just one thing... you code only displays the data. It doesn't update either table. Make it so you update one of the two tables and we'll have a drag race. 🙂
I'm not sure I see where the requirement to update the original table comes from?
If the results need to be persisted, simply change the SELECT into a SELECT INTO.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 23, 2010 at 6:24 am
Chris Morris-439714 (10/22/2010)
400,000 rows in 15 seconds...
This problem isn't ideal for a SQLCLR solution because it produces the same number of rows it consumes, and there is very little calculation effort involved. Nevertheless, reusing your handy 400,000 row test data, the SQLCLR solution runs on my machine in under 700ms - and even bulk-copies its results to an output table...
This solution uses Adam Machanic's Query Parallelizer engine, and runs on both SQL Server 2005 and 2008.
Test Data Creation
-- ========================================
-- Test data creation
-- (thanks to Chris Morris)
-- ========================================
USE [tempdb];
GO
IF OBJECT_ID (N'tempdb..#tempstore', N'U')
IS NOT NULL
DROP TABLE #tempstore;
GO
CREATE TABLE #tempstore
(
acct_nbr INTEGER NOT NULL,
ckid INTEGER NOT NULL,
stat VARCHAR(10) NOT NULL,
);
GO
INSERT INTO
#tempstore
(acct_nbr, ckid, stat)
SELECT 1, 101 ,'Open' UNION ALL
SELECT 1, 102 ,'Open' UNION ALL
SELECT 1, 103 ,'Open' UNION ALL
SELECT 1, 104 ,'Void' UNION ALL
SELECT 1, 105 ,'Void' UNION ALL
SELECT 1, 106 ,'Open' UNION ALL
SELECT 1, 107 ,'Open' UNION ALL
SELECT 1, 108 ,'Open' UNION ALL
SELECT 1, 109 ,'Void' UNION ALL
SELECT 1, 110 ,'Open' UNION ALL
SELECT 2, 101 ,'Open' UNION ALL
SELECT 2, 102 ,'Open' UNION ALL
SELECT 2, 103 ,'Open' UNION ALL
SELECT 2, 104 ,'Void' UNION ALL
SELECT 3, 105 ,'Void' UNION ALL
SELECT 3, 106 ,'Open' UNION ALL
SELECT 3, 107 ,'Open' UNION ALL
SELECT 4, 108 ,'Open' UNION ALL
SELECT 4, 109 ,'Void' UNION ALL
SELECT 4, 110 ,'Open';
GO
IF OBJECT_ID(N'tempdb..##temp')
IS NOT NULL
DROP TABLE ##temp;
GO
CREATE TABLE
##temp
(
acct_nbr INTEGER NOT NULL,
ckid INTEGER NOT NULL,
stat VARCHAR(10) NOT NULL,
);
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON ##temp (acct_nbr, ckid);
GO
-- 400,000 rows of data
INSERT INTO
##temp
WITH (TABLOCK)
(
acct_nbr,
ckid,
stat
)
SELECT acct_nbr = T.acct_nbr + (4 * Numbers.n),
ckid = T.ckid + (20 * Numbers.n),
stat = T.stat
FROM #tempstore T
CROSS
JOIN (
SELECT TOP (20000)
n = ROW_NUMBER()
OVER (
ORDER BY a.[name]
) - 1
FROM master.dbo.syscolumns a,
master.dbo.syscolumns b
) Numbers;
GO
SQLCLR security setup
-- ========================================
-- SQLCLR security : master database
-- ========================================
USE [master];
GO
IF EXISTS
(
SELECT *
FROM sys.server_principals
WHERE name = N'SQLCLR_Unsafe_Permission_Login'
AND type_desc = N'CERTIFICATE_MAPPED_LOGIN'
)
DROP LOGIN SQLCLR_Unsafe_Permission_Login;
GO
IF CERT_ID('SQLCLR_Unsafe_Permission_Cert')
IS NOT NULL
DROP CERTIFICATE SQLCLR_Unsafe_Permission_Cert;
GO
CREATE CERTIFICATE SQLCLR_Unsafe_Permission_Cert
ENCRYPTION BY PASSWORD = 'SQLCLRUnsafeCert'
WITH SUBJECT = 'SQLCLR Unsafe Assembly Permission',
START_DATE = '01/01/2000',
EXPIRY_DATE = '12/31/2999';
GO
CREATE LOGIN SQLCLR_Unsafe_Permission_Login
FROM CERTIFICATE SQLCLR_Unsafe_Permission_Cert;
GO
GRANT UNSAFE ASSEMBLY
TO SQLCLR_Unsafe_Permission_Login;
GO
-- These two files must not already exist
BACKUP CERTIFICATE SQLCLR_Unsafe_Permission_Cert
TO FILE = 'C:\temp\SQLCLR_Unsafe.cer'
WITH PRIVATE KEY
(
DECRYPTION BY PASSWORD = 'SQLCLRUnsafeCert',
FILE = 'C:\temp\SQLCLR_Unsafe.pvk',
ENCRYPTION BY PASSWORD = 'SQLCLRUnsafeKey'
);
GO
-- ========================================
-- SQLCLR security : tempdb
-- ========================================
USE [tempdb];
GO
IF OBJECT_ID(N'dbo.Sequence', N'FT')
IS NOT NULL
DROP FUNCTION dbo.Sequence;
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'GMRose')
DROP ASSEMBLY GMRose;
GO
IF CERT_ID('SQLCLR_Unsafe_Permission_Cert')
IS NOT NULL
DROP CERTIFICATE SQLCLR_Unsafe_Permission_Cert;
GO
CREATE CERTIFICATE SQLCLR_Unsafe_Permission_Cert
FROM FILE = 'C:\temp\SQLCLR_Unsafe.cer'
WITH PRIVATE KEY
(
FILE = 'C:\temp\SQLCLR_Unsafe.pvk',
DECRYPTION BY PASSWORD = 'SQLCLRUnsafeKey',
ENCRYPTION BY PASSWORD = 'SQLCLRUnsafeCertPassword'
);
SQLCLR solution objects
-- ========================================
-- SQLCLR object creation
-- ========================================
--
-- Uses Adam Machanic's Query Parallelizer engine
-- See http://sqlblog.com/files/folders/beta/entry29021.aspx
CREATE ASSEMBLY [QueryParallelizer]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103008D7FC24C0000000000000000E00002210B0108000052000000060000000000006E71000000200000008000000000400000200000000200000400000000000000040000000000000000C0000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000001C7100004F00000000800000900300000000000000000000000000000000000000A000000C000000607000001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000074510000002000000052000000020000000000000000000000000000200000602E7273726300000090030000008000000004000000540000000000000000000000000000400000402E72656C6F6300000C00000000A000000002000000580000000000000000000000000000400000420000000000000000000000000000000050710000000000004800000002000500303900003037000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B300400E900000001000011281300000A39D80000007201000070731400000A0A066F1500000A731600000A0B07066F1700000A0772350000706F1800000A076F1900000A72A70000701F1620800000006F1A00000A186F1B00000A076F1900000A72C10000701F1620800000006F1A00000A186F1B00000A076F1C00000A26731D00000A0C08076F1900000A166F1E00000A6F1F00000A74380000016F2000000A08076F1900000A176F1E00000A6F1F00000A74380000016F2100000A08176F2200000A08166F2300000A0820C80000006F2400000A086F2500000A8001000004DE10062C06066F2600000ADC1480010000042A0000000110000002001500C3D8000A000000001A7E010000042A1E027B2700000A2A6E027B2800000A2C0B72D3000070732900000A7A02037D2700000A2A1E027B2A00000A2AAA027B2800000A2C0B723F010070732900000A7A0316300B72A7010070732900000A7A02037D2A00000A2A1E027B2B00000A2AAA027B2800000A2C0B720D020070732900000A7A0316300B726F020070732900000A7A02037D2B00000A2A1E027B2C00000A2A6E027B2800000A2C0B72CF020070732900000A7A02037D2C00000A2A1E027B2D00000A2A6E027B2800000A2C0B722D030070732900000A7A02037D2D00000A2A1E027B2E00000A2A6E027B2800000A2C0B7297030070732900000A7A02037D2E00000A2A000000133002009300000002000011021F647D2A00000A021F647D2B00000A02282F00000A0E061732060E061F20310B7205040070733000000A7A0E056A0E046A59176A580A0E066A063104066910060E040E05310B7269040070733000000A7A02037D3100000A02047D3200000A02057D3300000A020E047D3400000A020E057D3500000A020E067D3600000A020E077D3700000A0228020000067D2700000A2A0003300E006E00000000000000027B2800000A2C0B72CB040070732900000A7A02177D2800000A027B3100000A027B3200000A027B3300000A027B3400000A027B3500000A027B3600000A027B3700000A027B2700000A027B2A00000A027B2B00000A027B2C00000A027B2D00000A027B2E00000A733800000A2A0000033004004B0000000000000002282F00000A02037D3900000A02047D3A00000A02057D3B00000A020E047D3C00000A020E057D3D00000A020E067D3E00000A0202FE063F00000A734000000A734100000A7D4200000A2A42027C4300000A284400000A166AFE022A1B3002003A00000003000011027C4300000A284500000A26027B3900000A6F4600000ADE0326DE0017734700000A0A066F4800000A027B4200000A6F4900000ADE0326DE002A0000011C000000000C000D19000301000001000029000D360003010000011B3003009801000004000011140A17734A00000A0B076F4800000A027B3900000A6F4B00000A0A027B3C00000A2C11066F1500000A027B3900000A6F4C00000A02284D00000A3A0E010000027B4E00000A6F4F00000A0C0839FC000000027B3900000A6F1900000A166F1E00000A087B5000000A8C410000016F5100000A027B3900000A6F1900000A176F1E00000A087B5200000A8C410000016F5100000A027B3C00000A2D06066F1500000A027B3900000A6F5300000A0D096F5400000A2C59027B3B00000A096F5500000A6F5600000A13072B1A11076F5700000A130402284D00000A2D1202110417285800000A11076F5900000A2DDDDE0C11072C0711076F2600000ADC021208FE150900001B110816285800000A2B18096F5A00000A262B07096F5A00000A26096F5B00000A2DF1027B3C00000A2C08096F5C00000A2B06066F5D00000A027B4E00000A176F5E00000A38E7FEFFFFDE311305723F0500701105735F00000A1306027B4E00000A11066F6000000A027B4E00000A176F5E00000A02286100000ADE00DE15027B3900000A6F6200000A062C06066F6200000ADC2A414C000002000000C800000027000000EF0000000C0000000000000000000000020000004D0100004F0100003100000016000001020000000200000080010000820100001500000000000000A202037D4E00000A027B4200000A6F6300000A2D1302284D00000A2D0B027B4200000A6F6400000A2A00000013300400E000000005000011027B6500000A2D1802027B3D00000A8D0900001B7D6500000A02157D6600000A042C1D027B6500000A02257B6600000A1758250D7D6600000A0903A40900001B027B6600000A027B3D00000A17592E06043A89000000027B6500000A027B6600000A736700000A0A027B3A00000A2C34027B3A00000A6F6800000A0B2B1E027B3A00000A07066F6900000A2C0902147D6500000A2B0E17286A00000A02284D00000A2CDA027B3E00000A2C33027B3E00000A6F6800000A0C2B1D027B3E00000A08066F6900000A2C0802147D6500000A2A17286A00000A02284D00000A2CDB2A133004005A0000000200001102282F00000A02037D6B00000A0203D48D0A00001B7D6C00000A02168D0900001B16736700000A7D6D00000A166A0A2B14027B6C00000A06D4027B6D00000AA206176A580A060332E802156A7D6E00000A02156A7D6F00000A2A62027C6E00000A284500000A027B6B00000A5D287000000A2A62027C6F00000A284500000A027B6B00000A5D287000000A2A8A027B6D00000A027B6C00000A03D48F0A00001B04027B6D00000A280100002BFE012A00133004002900000006000011027B6D00000A04027B6C00000A03D48F0A00001B027B6D00000A280200002B250A5106FE0116FE012A000000033007005C0000000000000002282F00000A02037D7300000A02047D7400000A02057D7500000A020E047D7600000A020E057D7700000A0202FE067800000A734000000A734100000A7D7900000A020E060E070E080E09027B7900000A737A00000A7D7B00000A2AB602037D7C00000A027B7900000A6F6300000A2D18027B7B00000A6F7D00000A2D0B027B7900000A6F6400000A2A32027B7B00000A6F7E00000A2A001B300300CA0000000700001117734A00000A0A066F4800000A027B7300000A027B7500000A737F00000A0B07027B7400000A6F8000000A07166F8100000A07027B7600000A6F8200000A027B7700000A2C49027B7700000A6F8300000A13052B1A11056F0200000A741A0000010C076F8400000A086F8500000A2611056F5900000A2DDDDE1511057503000001130611062C0711066F2600000ADC07027B7B00000A6F8600000ADE1D0D723F05007009735F00000A1304027B7C00000A11046F6000000ADE00DE0D027B7C00000A176F5E00000ADC2A00000128000002005300277A001500000000000000009D9D001D1600000102000000BCBC000D0000000042027C8800000A284400000A166AFE022A0000001B3002003700000003000011027C8800000A284500000A26027B8900000A6F6300000A2C1D17734700000A0A066F4800000A027B8900000A6F4900000ADE0326DE002A0001100000000026000D33000301000001CE02157D8A00000A02282F00000A02037D8B00000A02047D8C00000A02057D8D00000A020E047D8E00000A020E057D8900000A2A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A00133003008C00000008000011027B9000000A2D58027B8D00000A6F9100000A0A027B8D00000A06027C9000000A6F9200000A2C0902157D8A00000A2B2F027B9300000A2C02162A027B8E00000A176F9400000A2C0702177D9300000A02287D00000A2CBC02287E00000A2BB402257B8A00000A1758250B7D8A00000A07027B9000000A7B9500000A310C02147D9000000A3876FFFFFF172A1A738F00000A7A1A738F00000A7A1E027B8B00000A2A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A8E027B8C00000A027B9000000A7B9600000A027B8A00000AA30900001B036F9700000A2A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A5602282F00000A02037D5000000A02047D5200000A2A0000000330030072000000000000000216739800000A7D9900000A02156A7D9A00000A02282F00000A02037D9B00000A02047D9C00000A02057D9D00000A020E047D9E00000A020E057D9F00000A020E067DA000000A020E077DA100000A020E076FA200000A7DA300000A020E076FA200000A0E066FA200000A587DA400000A2A00001B3002002100000009000011027B9D00000A250B28A500000A02FE137BA600000A0ADE070728A700000ADC062A0000000110000002000D000B180007000000001B3002002A0000000A000011027B9D00000A250A28A500000A02FE137BA600000A2D090203FE137DA600000ADE070628A700000ADC2A00000110000002000D00152200070000000072032C0C027CA800000A284500000A26027B9900000A6FA900000A262A000000133002002100000002000011027C9A00000A284500000A0A06027BA300000A2F0A027BA100000A06D49A2A142A0000001B3002004B0000000B000011160A2B16027B9F00000A069A6F6100000ADE0326DE000617580A06027B9F00000A8E6932DF160B2B16027BA000000A079A6FAA00000ADE0326DE000717580B07027BA000000A8E6932DF2A00011C0000000004000F13000301000001000029000F380003010000011B300300F40000000C000011027B9F00000A13051613062B13110511069A0A06026FAB00000A110617581306110611058E6932E5027BA000000A13071613082B13110711089A0B07026FAC00000A110817581308110811078E6932E5027B9C00000A6FA900000A26027CA800000A284400000A0C08027BA300000A330C027B9E00000A6FA900000A2608027BA400000A2E32027B9B00000A6F6300000A2C0A02FE137BA600000A2C080228AD00000A2B13027B9900000A20EE0200006F9400000A262BA4DE1E0D727905007009735F00000A1304021104286000000A0228AD00000ADE00DE19027B9C00000A6FA900000A26027B9D00000A6FA900000A26DC2A011C000000000000BABA001E1600000102000000DADA00190000000003300300550000000000000002282F00000A0E051732060E051F20310B72C3050070733000000A7A02037DAE00000A02047DAF00000A02057DB000000A020E047DB100000A020E057DB200000A020E062C040E062B0528020000067DB300000A2A000000133007005D0000000D000011020304050E040E050E0628B400000A020E077DB500000A020E087DB600000A722D06007073B700000A0A02066F8400000A7DB800000A0E092C220E090C160D2B1508099A0B027BB800000A076F8500000A260917580D09088E6932E52A5602282F00000A02037D9600000A02047D9500000A2A0013300B00D80200000E00001102157DB900000A02282F00000A020E096A73BA00000A7DBB00000A021673BC00000A7DBD00000A140A1673BC00000A0B170C73BE00000A0D0E0D2C650E0D7BB100000A0C0E096A73BA00000A0A1713042B44090E0D7BB300000A0E0D7BAE00000A0E0D7BB500000A0E0D7BB600000A0E0D7BB800000A0E0D7BAF00000A0E0D7BB000000A060773BF00000A6FC000000A11041758130411040E0D7BB200000A31B173C100000A130573C200000A13060E056A0E046A59176A5813090E0B163DA600000011090E066A2F270E04130A2B161106110A110A73C300000A6FC400000A110A1758130A110A0E0531E438C800000016130B2B6B0E046A11090E066A5B110B6A5A58691307110B17580E062E160E046A176A5911090E066A5B110B17586A5A58692B020E051308110820FFFFFF7F2F1911072000000080311011081107175933080E06175910062B1011061107110873C300000A6FC400000A110B1758130B110B0E06328F2B500E04130711070E0B175958130811080E0531040E05130811061107110873C300000A6FC400000A11080E052E231108130C11081758130711070E0B17595813081108110C2F040E05130811070E0531BD16130D2B720E082C040E082B052802000006731400000A130E110E6FC500000A130F110F036F1800000A110F166FC600000A110F6F1900000A041E6FC700000A26110F6F1900000A051E6FC700000A261105110F082D03142B06027BBB00000A0E070E0C0E0A0673C800000A6FC900000A110D1758130D110D11066FCA00000A0E0632040E062B0711066FCA00000A3F71FFFFFF1673BC00000A13100228CB00000A1110027BBD00000A0711056FCC00000A096FCD00000A11066FCE00000A73CF00000A7DD000000A027BD000000AFE06D100000A734000000A734100000A131111116F6400000A111020881300006F9400000A1312027BD000000A6FD200000A2C0C027BD000000A6FD200000A7A11122D0B722F060070732900000A7A2A0A022A0A022A5E027BD300000A7B9600000A027BB900000AA30900001B2A72027BD300000A7B9600000A027BB900000AA30900001B8C0900001B2A00133003009500000008000011027BD300000A2D61027BBB00000A6F9100000A0A027BBB00000A06027CD300000A6F9200000A2C0902157DB900000A2B38027BD400000A2C02162A027BBD00000A176F9400000A2C0702177DD400000A027BD000000A6FD200000A2CB7027BD000000A6FD200000A7A02257BB900000A1758250B7DB900000A07027BD300000A7B9500000A310C02147DD300000A386DFFFFFF172A1A738F00000A7A36027BD000000A166F5E00000A2A000003300300540200000000000073D500000A80510000047E5100000416D04E00000128D600000A6FD700000A7E5100000417D01400001B28D600000A6FD700000A7E5100000418D05000000128D600000A6FD700000A7E5100000419D03800000128D600000A6FD700000A7E510000041F1FD01000000128D600000A6FD700000A7E510000041AD01000000128D600000A6FD700000A7E510000041F21D01000000128D600000A6FD700000A7E510000041F22D01000000128D600000A6FD700000A7E510000041BD01100000128D600000A6FD700000A7E510000041CD05100000128D600000A6FD700000A7E510000041DD01400001B28D600000A6FD700000A7E510000041ED04100000128D600000A6FD700000A7E510000041F09D01100000128D600000A6FD700000A7E510000041F0AD03800000128D600000A6FD700000A7E510000041F0BD03800000128D600000A6FD700000A7E510000041F0CD03800000128D600000A6FD700000A7E510000041F0DD05100000128D600000A6FD700000A7E510000041F0FD01000000128D600000A6FD700000A7E510000041F10D05200000128D600000A6FD700000A7E510000041F11D01100000128D600000A6FD700000A7E510000041F12D03800000128D600000A6FD700000A7E510000041F20D01000000128D600000A6FD700000A7E510000041F13D01400001B28D600000A6FD700000A7E510000041F14D05300000128D600000A6FD700000A7E510000041F0ED01300000128D600000A6FD700000A7E510000041F15D01400001B28D600000A6FD700000A7E510000041F16D03800000128D600000A6FD700000A2A327E51000004026FD800000A2A1E02282F00000A2A2A0203041428630000062A1B300700AC0200000F00001117734A00000A0A066F4800000A140B052C03052B052802000006731400000A0C086FC500000A0D0972990600706F1800000A086F1500000A096F1C00000A2609026F1800000A096F1900000A031E6FC700000A20000000808C410000016F5100000A096F1900000A041E6FC700000A20FFFFFF7F8C410000016F5100000A096F5300000A130411046FD900000A0B072D0B72B9060070732900000A7ADE0C11042C0711046F2600000ADCDE0A092C06096F2600000ADCDE0A082C06086F2600000ADC076FDA00000A6FDB00000A8D1D000001130516130638BB010000076FDA00000A11066FDC00000A72270700706FDD00000AA51C0000011307110717594505000000AE00000038010000AE000000380100002D00000011071F0A59450300000098000000220100009800000011071F155945020000008600000086000000380B01000011051106076FDA00000A11066FDC00000A72410700706FDD00000A7438000001076FDA00000A11066FDC00000A72270700706FDD00000AA51C000001076FDA00000A11066FDC00000A72570700706FDD00000AA552000001D2076FDA00000A11066FDC00000A72790700706FDD00000AA552000001D273DE00000AA238CC00000011051106076FDA00000A11066FDC00000A72410700706FDD00000A7438000001076FDA00000A11066FDC00000A72270700706FDD00000AA51C000001076FDA00000A11066FDC00000A72930700706FDD00000AA5410000016A20FFFFFF7F6A2E1F076FDA00000A11066FDC00000A72930700706FDD00000AA5410000016A2B02156A73DF00000AA22B4211051106076FDA00000A11066FDC00000A72410700706FDD00000A7438000001076FDA00000A11066FDC00000A72270700706FDD00000AA51C00000173E000000AA21106175813061106076FDA00000A6FDB00000A3F33FEFFFF11052A0128000002008600189E000C000000000200270085AC000A000000000200200098B8000A0000000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000F0150000237E00005C160000C812000023537472696E67730000000024290000AC07000023555300D0300000100000002347554944000000E03000005006000023426C6F6200000000000000020000015717A20B090E000000FA25330016000001000000560000001000000051000000630000008B00000008000000E00000000E0000000F000000060000001200000019000000020000001400000001000000030000000B0000000C0000000200000000000A0001000000000006003D0136010A005201460106005E0136010A006A0146010600760136010600A30188010600C401B1010600D00188010600DE01B1010A0043042D0406006F045E040A0054052D040A0067052D0406008A055E040A00290646010600CA0636010600DF0636010600FC06360106001707360106002A085E0406009208720806009D0836010A0051092D040600660936010600730936010A00010A2D040600D70A88010A00E40A46010A00200B050B06006F0B500B06006C0C5A0C0600830C5A0C0600A00C5A0C0600BF0C5A0C0600D80C5A0C0600F10C5A0C06000C0D5A0C0600270D5A0C0600400D500B0600540D500B0600620D5A0C06007B0D5A0C0600AB0D980DAF00BF0D00000600CE0D72080600EE0D72080A001E0E050B0A00390E2D040A005A0E470E0A007B0E470E0A00950E2D040A00BB0E2D040A00CC0E470E0A00D80E46010A00090F2D0406002E0F36010A00890F470E0600B80F36010600CA0F5E040600D60F5E0406000810EC0F06001B10EC0F06003B102B100A005D102D040600881036010A00A610470E0E00D510BF100600F11036010A0013112D0406004C11B10106007C115A0C0600931136010600AB115E040600BE1136010600D3115E040600E6115E040600FA11880106004612360106004C12360106007012360106007812360106007F1236010600851236010A008A1246010A00A51246010A00C012460100000000010000000000010001008101000020003200050001000100010010004A003200050002000300030010005E000000050010001100030010006B00000005001B001700030010007C000000050020001C000300100087000000050028002000030010009A00000005003100440003001000A600000005003300450002010000B4000000150040004C0002010000C500000015004000500002001000DD00000005004000540003001000EE00000005004900560003001000FA00000005004B005700010100000D013200050051005F00810110001B0132000500520062003100EA01180021003802180021003E02180021004A02180021005602270021005F022700210068022700210079022A0001008202320001008F0218000100A60227000100B40227000100C00227000100CA0232000100DA02350021004E04B30021005304B700210079022A0021007604BF002100CA0232002100B402270021007D04B70001008604C30001008E04CB000100970427000100A404D0002100F404D0002100FF04E80021000705F10001000D05D00001001A05D0002100EA01180021009B0518002100AC0533012100C00227002100B80537012100C2053B0121007604BF0001008604C3002100D10527002100DC05430121007D04B7002100E4054B0121007604BF000100F805F1000100FF05270001000D0632000100A404D0002600B00727002600BD0727002100CA07BF002100D5074B012100E9074B012100E4054B0121006802D0002100F907D000210009080E0221001108170221001D08200201003908290201004E08D00001005F08D0000100A7082D022600970918002600A00727002600A80943012600B00932002600CF0927002600DA0918002600EB0933012600060427002600F709370126008E04CB0026001A0A270021005304B7002100E9074B012100240AC3000100F805F1000100FF05270001000D0632003100EE0AF1025020000000009118FB011B000100582100000000960802021F0001005F21000000008608E7023D000100672100000000860802034100010083210000000086081D03460002008B210000000086082F034A000200B621000000008608410346000300BE2100000000860851034A000300E921000000008608610346000400F1210000000086086F034A0004000D220000000086087D034F00050015220000000086089103530005003122000000008608A503580006003922000000008608B603610006005822000000008618C7036B000700F822000000008600CD037B000E007423000000008618C70399000E00CB23000000008608B6044F001400DC23000000008600C904D30014004024000000008600D004D30014003026000000008600D704D70014005C26000000008100DD04E10015004827000000008618C703F9001700AE270000000086002805FE001800C7270000000086003805FE001800E02700000000860049050201180004280000000086004F050D011A003C28000000008618C70319011C00A428000000008600D704D7002500D228000000008600C904D3002600E028000000008600D004D3002600E029000000008608B6044F002600F429000000008600C904D3002600482A000000008618C7034F0126007C2A00000000E6011906D3002B00832A00000000E6091F0646002B008A2A00000000E601330664012B00912A00000000E60942064F002B00982A00000000E6014F064F002B00A02A00000000E6014F054F002B00382B00000000E6095A0646002B003F2B00000000E6016E06D3002B00462B00000000E609760646002B004E2B00000000E601850669012B00552B00000000E60190066E012C005C2B00000000E601980673012D00632B00000000E601A1067D0132006A2B00000000E601A90682013300712B00000000E601B2068C013800782B00000000E601BA06920139007F2B00000000E601D30697013A00862B00000000E601E7069D013B008D2B00000000E601F206A3013C00942B00000000E6010107A8013D009B2B00000000E6010E07AE013E00A22B00000000E6011C07B3013F00A92B00000000E6012407B9014000B02B00000000E6012D07BE014100B72B00000000E6013607C3014200BE2B00000000E6013F0792014300C52B00000000E6014707C8014400CC2B00000000E601520792014500D32B00000000E6015C07CD014600F72B00000000E6016507D2014700FE2B00000000E6016F0769014800052C00000000E6097807D80149000C2C00000000E6097807CD014A00132C000000008618C703E7014B002C2C000000008618C703ED014D00AC2C000000008608BD0833025400EC2C000000008608D70838025400342D000000008600F10853005500542D00000000860004093E025600842D0000000081001709D3005600F82D0000000086002809D30056000000000003008618C7034C025600000000000300C6015F0952025800000000000300C60181095D025900000000000300C6018D0967025C000000000003008618C7034C025D00000000000300C6015F0972025F00000000000300C601810979026100000000000300C6018D0984026500142F000000008618C7038A026600782F000000008618C70399026C00E12F000000008618C703AE027500F82F000000008618C703B6027700DC3200000000E601270AD1028400DF3200000000E101350ADA028400E23200000000E609620ADF028400FA3200000000E1096E0AE4028400183300000000E601990A4F008400B93300000000E601A20AD3008400C03300000000E6016E06D3008400D033000000009118FB011B0084003036000000009600F40AFB0284003D36000000008618C703D300850045360000000096002C0B0203850050360000000096002C0B0B038800000001003D0B000001003D0B000001003D0B000001003D0B000001003D0B000001003D0B000001003802000002003E02000003004A02000004005602000005005F02000006006802000007007902000001004E0400000200530400000300790200000400CA0200000500B402000006007D0400000100860400000100430B00000200DD0400000100F40400000100470B000002003D0B00000100470B020002003D0B00000100EA01000002009B0500000300AC0500000400C00200000500B80500000600D10500000700DC05000008007D0400000900E40500000100860400000100D10500000200DC05000003007D0400000400E405000005007604000001007C0B000001007C0B000001007C0B000002007E0B000003008A0B00000400910B000005009E0B000001007C0B000001007C0B00000200A50B000003008A0B00000400910B000005009E0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B00000100B10B000001007C0B000001007C0B00000100B60B000001007C0B00000100B10B000001007C0B00000100B00700000200BD0700000100CA0700000200D50700000300E90700000400BD0B000005000908000006001108000007001D08000001003D0B00000100D30B00000100EE0B00000200F50B00000100FC0B00000100FC0B00000200060C00000300EE0B00000100F80500000100EE0B00000200F50B00000100430B000002000F0C00000100430B000002000F0C00000300060C00000400EE0B00000100F805000001009B0500000200D10500000300DC05000004001D0C000005003C0C00000600EA01000001009B0500000200D10500000300DC05000004001D0C000005003C0C00000600EA0100000700AC0500000800C00200000900B805000001008E04000002001A0A000001003802000002003E02000003004A02000004005602000005005F02000006006802000007007902000008008F0200000900A60200000A00B40200000B00C00200000C00CA0200000D00DA0200000100470C000001003802000002004C0C00000300530C000001003802000002004C0C00000300530C000004008F020700090007000D00070011000E0006000E001D000E000A000E000D000E0025003900270ADA024900620AE402F100C703D300F900C70341000101C70341000901C70341001101C70341001901C70341002101C70341002901C70341003101C70341003901C70353004101C70341004901C70341005101C70341005901C70315036901C7034A007101C703D3007901290E1C038101C70341008901670ED3005100C703D30051006C0E20039101850E41005100AC0E27039901C80E2D03A901EB0E37039101F90E4600B901C703D300990178073E03A901240FE402B901350F4100B901440F4100B901570F5300B9016E0F5300B901790F4A00C901A30F3D0019006E06D3001C008F0218001C0082023200B100C70341001C00A60227001C00B40227001C00C00227001C00CA0232001C00DA0235000900C703D300D101C70341001C00380218001C003E0218001C004A0218001C00560227001C005F0227001C00680227001C0079022A002400C703B6022C004E04B3002C005304B7002C0079022A002C00CA0232002C00B40227002C007D04B7002C00D004D300D901C7034C025900C70369032C007604BF002C00A404D000E1014F057003E101E20F70039101C904D300E901C7037603F9015010D30059005710D3000102C703760351007110830391018010D3002C00B6044F002C008604C300340004093E023C00B0072700A9018E1097033C00BD072700510098109C031102B3104F0044005F0952020C00270AD1021400620ADF022C00DD04E1004900990A4F0011024F054F0011024F064F0011026E06D30089011906D3003400F1085300B100C703AB033400D70838022C00C904D30019026E06D3005900DF104F005900D704D3002C008E04CB002C00970427005400C703AE025C003805FE005C00490502015900EB10DF035C00F404D0005C00FF04E8005C000705F1005C000D05D0005C001A05D0002102F610F003E101FA10F503E1010A110B046400EA01180064009B0518006400AC0533016400C00227006400B80537016400D004D30064007604BF006C00C7034F016400C2053B0164008604C3006C00B6044F006C00C904D3002902C7032D0429021F114100290238114A0029026F034A003102270ADA0229025B1134046900C80E390429026E1140043902C70341006C00A404D0006C007604BF006C00FF0527006C00D10527006C00DC0543016C007D04B7006C00E4054B014102C703D3006C00F805F1005C002805FE005C004F050D016C000D0632004902B611690154001A0A270054008E04CB0074005F097202A100C703530034003908290234004E08D0003400CA07BF003400D5074B013400E9074B013400E4054B01340009080E0234001108170234001D0820025102C411FE0034006802D0003400F907D0005902DB116F043400A7082D025902E1116F0434005F08D0006102F6114F006400C904D3002C00D704D7006400D704D70034001709D3007C00970918007C00A00727007C00A80943017C00B00932007C00CF0927007C00DA0918007C00C7038A027C00EB0933017C00060427002902C70341007C00F70937012400FF0527005C00C703F90024005304B7007100C70353002400E9074B018400C703D3006400C70319018400C80EC8048C00C703D3009400C703D3003C00C703E7019400C80EC80481010112E60491010F124A009901C80EEB042C00C70399008C00C80EC80494002212460059002C12F4048C003E12F90484003E12F90494003E12F9043400C703ED012400240AC30034002809D3003400BD0833022400F805F10024000D0632009C00C703D30091005E1247059C00C80E4F059C0078075A0511023306640179009C126105A90222124600A10278076705B1027807D801E900C7036E05E900C7037705E900C7037F052E004B00C7052E0023009B052E002B00B2052E003300B2052E003B00B8052E0043009B052E006B00E8052E005300B2052E006300B2052E0083001F062E00930031062E007B0012062E008B002806E3003B045904450357037D03B203E40316044604630474047B0480048504B004FF04860502000100030002000400080007000900090010000E00110000001F0223000000D50384000000EC0388000000FA038800000006048800000010048C000000200490000000E5048C000000E5048C00000081078800000087078C000000900788000000A00788000000AB07DD010000AB07E20100003B0947020000A80AE8020000B00AED0202000200030001000400050002000300050002000500070001000600070002000700090001000800090001000A000B00020009000B0002000B000D0001000C000D0001000E000F0002000D000F0002001200110002002000130002002400150002002600170002002900190002002B001B00020042001D00020043001F0002004600210001004700210002005A00230002005B0025000E00B20003000E00B60005000A00110050035B03620389039003A103A803D103D8031F0426046804A904BC04CE04DA043E0557050480000001000000000000000000000000000C0E000002000000000000000000000001002D0100000000020000000000000000000000010046010000000002000000000000000000000001003601000000000400030005000300060003000700060008000300090003000A0003000B0003000C0003000D0003000E00030000000000060044010000000008004401000000000A004401000000000C004401000000000E00440100000000100044010000000012004401000000001400440100000000160044010000000018004401000000001A004401000000001C004401E3000204E50002040000003C4D6F64756C653E005175657279506172616C6C656C697A65722E646C6C00436F6E6E656374696F6E4275696C646572004164616D4D616368616E69632E5175657279546F6F6C73005175657279506172616C6C656C697A6572603100776F726B657254687265616400726F756E64526F62696E4275666665720062756C6B54687265616400696E7465726E616C4461746152656164657200776F726B5061636B616765006D6F6E69746F7254687265616400526F774C6F67696344656C65676174650042756C6B436F70794D617070696E6744656C65676174650042756C6B436F707953657474696E6773006974656D5061636B61676500696E7465726E616C456E756D657261746F720054797065436F6E766572746572004D6574616461746147656E657261746F72006D73636F726C69620053797374656D004F626A65637400540053797374656D2E446174610049446174615265616465720049446973706F7361626C650049446174615265636F7264004D756C74696361737444656C65676174650053797374656D2E436F6C6C656374696F6E732E47656E657269630049456E756D657261626C6560310053797374656D2E436F6C6C656374696F6E730049456E756D657261626C650049456E756D657261746F7260310049456E756D657261746F7200636F6E6E656374696F6E537472696E67002E6363746F72006765745F4C6F6F706261636B436F6E6E656374696F6E537472696E67004C6F6F706261636B436F6E6E656374696F6E537472696E67007175657279006D696E5661726961626C65006D61785661726961626C65006D696E56616C7565006D617856616C7565006E756D576F726B65725468726561647300726F774C6F67696300697350726F63657373696E6700746172676574436F6E6E656374696F6E537472696E6700726F7742756666657253697A65007061636B61676553697A6500626174636853697A65007265757365436F6E6E656374696F6E0062756C6B53657474696E6773006765745F546172676574436F6E6E656374696F6E537472696E67007365745F546172676574436F6E6E656374696F6E537472696E67006765745F526F7742756666657253697A65007365745F526F7742756666657253697A65006765745F5061636B61676553697A65007365745F5061636B61676553697A65006765745F426174636853697A65007365745F426174636853697A65006765745F5265757365436F6E6E656374696F6E007365745F5265757365436F6E6E656374696F6E006765745F42756C6B53657474696E6773007365745F42756C6B53657474696E6773002E63746F720050726F6365737300546172676574436F6E6E656374696F6E537472696E6700526F7742756666657253697A65005061636B61676553697A6500426174636853697A65005265757365436F6E6E656374696F6E0042756C6B53657474696E67730053797374656D2E446174612E53716C436C69656E740053716C436F6D6D616E6400636F6D6D006F7574707574526F77730053797374656D2E546872656164696E670054687265616400776F726B65720062756C6B526F7773006D6F6E69746F72006974656D4C697374006C697374506F736974696F6E0074687265616443616E63656C436F756E74006765745F54687265616443616E63656C65640043616E63656C00646F576F726B00537461727400656E71756575650054687265616443616E63656C65640062756666657253697A65006275666665727300626C616E6B00726561644C6F636174696F6E0077726974654C6F636174696F6E00476574526561644C6F636174696F6E0047657457726974654C6F636174696F6E00577269746500526561640053716C42756C6B436F70794F7074696F6E730053716C42756C6B436F7079436F6C756D6E4D617070696E67436F6C6C656374696F6E004D616E75616C52657365744576656E740064657374696E6174696F6E5461626C6500636F70794F7074696F6E7300636F6C756D6E4D61700062756C6B44617461526561646572006669656C64436F756E7400646174614D61700062756C6B436F6D706C6574696F6E4576656E7400726573756C740063757272656E74526573756C74006D6F6E69746F724578697400436C6F7365006765745F446570746800446174615461626C6500476574536368656D615461626C65006765745F4973436C6F736564004E657874526573756C74006765745F5265636F726473416666656374656400446973706F7365006765745F4669656C64436F756E7400476574426F6F6C65616E0047657442797465004765744279746573004765744368617200476574436861727300476574446174610047657444617461547970654E616D65004461746554696D65004765744461746554696D6500446563696D616C00476574446563696D616C00476574446F75626C650054797065004765744669656C645479706500476574466C6F61740047756964004765744775696400476574496E74313600476574496E74333200476574496E743634004765744E616D65004765744F7264696E616C00476574537472696E670047657456616C75650047657456616C75657300497344424E756C6C006765745F4974656D004465707468004973436C6F736564005265636F7264734166666563746564004669656C64436F756E74004974656D0072616E67654D696E696D756D0072616E67654D6178696D756D006D61696E54687265616400696E697469616C697A6174696F6E4576656E7400636F6D706C6574696F6E4576656E74006E756D546F74616C5468726561647300776F726B6572730062756C6B576F726B65727300776F726B5061636B61676573004175746F52657365744576656E7400776F726B6572436F6D706C657465644576656E74006E657874576F726B6572546872656164006E756D46696E6973686564546872656164730053797374656D2E52756E74696D652E436F6D70696C65725365727669636573004973566F6C6174696C6500457863657074696F6E00776F726B6572546872656164457863657074696F6E006765745F576F726B6572546872656164457863657074696F6E007365745F576F726B6572546872656164457863657074696F6E00536574576F726B6572436F6D706C65746564004765744E657874576F726B5061636B6167650063616E63656C416C6C546872656164730053657475704D6F6E69746F7254687265616400576F726B6572546872656164457863657074696F6E0053716C4461746152656164657200496E766F6B6500494173796E63526573756C74004173796E6343616C6C6261636B00426567696E496E766F6B6500456E64496E766F6B650044657374696E6174696F6E5461626C6500446174614D6170004F7574707574526F7773546F45787465726E616C456E756D657261746F72004E756D5468726561647300436F6E6E656374696F6E537472696E6700436F70794F7074696F6E7300436F6C756D6E4D61700053716C42756C6B436F7079436F6C756D6E4D617070696E67006974656D436F756E74006D7000476574456E756D657261746F720053797374656D2E436F6C6C656374696F6E732E49456E756D657261626C652E476574456E756D657261746F72006765745F43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E6765745F43757272656E74004D6F76654E6578740052657365740043757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E43757272656E740044696374696F6E61727960320053716C44625479706500747970657300436F6E76657274546F4E657454797065004D6963726F736F66742E53716C5365727665722E5365727665720053716C4D657461446174610047657451756572794D657461646174610076616C756500726F77006C6F636174696F6E0053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650069006669656C644F666673657400627566666572006275666665726F6666736574006C656E677468006669656C646F6666736574006E616D650076616C7565730062756C6B436F6D706C65746974696F6E4576656E7400696E6372656D656E74436F6D706C6574696F6E436F756E746572006F626A656374006D6574686F6400696E707574446174610063616C6C6261636B00636F6C756D6E4F7264696E616C006F7574707574526F7773546F45787465726E616C456E756D657261746F72006E756D546872656164730074797065006D696E566172006D61785661720053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F64657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005175657279506172616C6C656C697A65720053716C436F6E74657874006765745F4973417661696C61626C650053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E007365745F436F6E6E656374696F6E004462436F6D6D616E64007365745F436F6D6D616E64546578740053716C506172616D65746572436F6C6C656374696F6E006765745F506172616D65746572730053716C506172616D6574657200416464004462506172616D6574657200506172616D65746572446972656374696F6E007365745F446972656374696F6E00457865637574654E6F6E51756572790053716C436F6E6E656374696F6E537472696E674275696C646572006765745F56616C756500537472696E67007365745F44617461536F75726365007365745F496E697469616C436174616C6F67007365745F496E74656772617465645365637572697479007365745F456E6C697374007365745F4D6178506F6F6C53697A65004462436F6E6E656374696F6E537472696E674275696C646572006765745F436F6E6E656374696F6E537472696E6700417267756D656E74457863657074696F6E00546872656164537461727400496E7465726C6F636B656400496E6372656D656E740053797374656D2E53656375726974792E5065726D697373696F6E730053656375726974795065726D697373696F6E005065726D697373696F6E53746174650053797374656D2E536563757269747900436F64654163636573735065726D697373696F6E004173736572740041626F72740053716C436C69656E745065726D697373696F6E006765745F436F6E6E656374696F6E005072657061726500496E743332007365745F56616C7565004578656375746552656164657200446244617461526561646572006765745F486173526F77730053797374656D2E436F6D706F6E656E744D6F64656C00436F6D706F6E656E74006765745F4973416C69766500536C656570004D6174680041627300436F6D7061726545786368616E67650045786368616E67650053716C42756C6B436F7079007365745F44657374696E6174696F6E5461626C654E616D65007365745F42756C6B436F707954696D656F757400436F6C6C656374696F6E42617365006765745F436F6C756D6E4D617070696E6773005772697465546F5365727665720044656661756C744D656D626572417474726962757465004E6F74496D706C656D656E746564457863657074696F6E005761697448616E646C6500576169744F6E65004172726179006765745F4C6F6E674C656E677468004D6F6E69746F7200456E7465720045786974004576656E745761697448616E646C6500536574004C697374603100437265617465436F6D6D616E64007365745F436F6D6D616E6454696D656F7574006765745F436F756E74006765745F43757272656E7454687265616400546F417272617900496E7436340052756E74696D655479706548616E646C65004765745479706546726F6D48616E646C6500426F6F6C65616E00446F75626C6500496E74313600427974650044617461526F77436F6C6C656374696F6E006765745F526F777300496E7465726E616C44617461436F6C6C656374696F6E426173650044617461526F7700003363006F006E007400650078007400200063006F006E006E0065006300740069006F006E0020003D00200074007200750065000071530045004C00450043005400200040007300650072007600650072005F006E0061006D00650020003D002000400040005300450052005600450052004E0041004D0045002C0020004000640062005F006E0061006D00650020003D002000440042005F004E0041004D00450028002900001940007300650072007600650072005F006E0061006D00650000114000640062005F006E0061006D006500006B43006F006E006E0065006300740069006F006E00200073007400720069006E0067002000630061006E0020006F006E006C007900200062006500200073006500740020007000720069006F007200200074006F002000700072006F00630065007300730069006E006700006752006F00770020006200750066006600650072002000730069007A0065002000630061006E0020006F006E006C007900200062006500200073006500740020007000720069006F007200200074006F002000700072006F00630065007300730069006E006700006552006F00770020006200750066006600650072002000730069007A00650020006D007500730074002000620065002000670072006500610074006500720020007400680061006E0020006F007200200065007100750061006C00200074006F002000310000615000610063006B006100670065002000730069007A0065002000630061006E0020006F006E006C007900200062006500200073006500740020007000720069006F007200200074006F002000700072006F00630065007300730069006E006700005F5000610063006B006100670065002000730069007A00650020006D007500730074002000620065002000670072006500610074006500720020007400680061006E0020006F007200200065007100750061006C00200074006F0020003100005D420061007400630068002000730069007A0065002000630061006E0020006F006E006C007900200062006500200073006500740020007000720069006F007200200074006F002000700072006F00630065007300730069006E006700006943006F006E006E0065006300740069006F006E002000720065007500730065002000630061006E0020006F006E006C007900200062006500200073006500740020007000720069006F007200200074006F002000700072006F00630065007300730069006E006700006D420075006C006B00200063006F00700079002000730065007400740069006E00670073002000630061006E0020006F006E006C007900200062006500200073006500740020007000720069006F007200200074006F002000700072006F00630065007300730069006E00670000634E0075006D0062006500720020006F006600200077006F0072006B00650072002000740068007200650061006400730020006D0075007300740020006200650020006200650074007700650065006E0020003100200061006E00640020003300320000614D0069006E00560061006C007500650020006D0075007300740020006200650020006C0065007300730020007400680061006E0020006F007200200065007100750061006C00200074006F0020004D0061007800560061006C00750065002E000073500072006F00630065007300730069006E0067002000630061006E0020006F006E006C00790020006200650020007300740061007200740065006400200061002000730069006E0067006C0065002000740069006D0065002000700065007200200069006E007300740061006E0063006500003945007800630065007000740069006F006E0020006F006300630075007200720065006400200069006E00200077006F0072006B0065007200004945007800630065007000740069006F006E0020006F006300630075007200720065006400200069006E0020006D006F006E00690074006F007200200074006800720065006100640000694E0075006D0062006500720020006F0066002000620075006C006B00200063006F00700079002000740068007200650061006400730020006D0075007300740020006200650020006200650074007700650065006E0020003100200061006E006400200033003200000100694D006F006E00690074006F007200200074006800720065006100640020006600610069006C0065006400200074006F00200069006E0069007400690061006C0069007A0065002000770069007400680069006E002000350020007300650063006F006E0064007300001F530045005400200046004D0054004F004E004C00590020004F004E003B00006D4D0065007400610064006100740061002000470065006E0065007200610074006F007200200066006F0075006E00640020006E006F00200063006F006C0075006D006E007300200069006E002000740068006500200069006E007000750074002000710075006500720079000019500072006F00760069006400650072005400790070006500001543006F006C0075006D006E004E0061006D00650000214E0075006D00650072006900630050007200650063006900730069006F006E0000194E0075006D0065007200690063005300630061006C006500001543006F006C0075006D006E00530069007A00650000000000AE9DE95E7E303F428427037A29423B560008B77A5C561934E089061512190113000615122101130002060E030000010300000E0308000E020608070615122801130002060207061512300113000320000E042001010E032000080420010108032000020420010102082000151230011300092001011512300113000F2007010E0E0E0808081512280113000820001512190113000328000E032800080328000208280015123001130019200601122915121401130015122801130002081512140113000306122907061512140113000306122D070615122401130004061D130002060A03200001092001011512240113000620020113000208061D1512340113000706151234011300042001010A0320000A0A2002020A1512340113000B2002020A10151234011300192009010E0E11310812350815122C01130015121401130012390306113103061235070615121C011300070615122C01130003061239142005010815122C0113001512140113001239122D042000123D042001020804200105080920050A080A1D05080804200103080920050A080A1D0308080520011209080420010E080520011141080520011145080420010D080520011249080420010C08052001114D08042001060804200108080420010A08042001080E0420011C08052001081D1C0420011C0E0428011C0E0428011C0805200201080820200701122D1239123912391D1512100113001D1512180113001D15122001130008061D15121001130008061D15121801130008061D1512200113000306125105061F55125904200012590520010112590820001512200113000428001259052002011C180A2001151219011300125D0920031261125D12651C0A200115121901130012610620021C1300080A2004126113000812651C0520011C12610E2006010E0815122C01130002080E142009010E0815122C01130002080E1131081D1269072002011D1300081A200D010E0E0E0808081512280113000E08080802151230011300082000151221011300042000122504200013000320001C04280013000328001C090615126D0211711249060001124911710800031D12750E0E0E0900041D12750E0E0E0E062001011180B103000002062001011280C10520001280CD0920031280D10E117108062001011180D90620011280D1080A07031280C112291280DD0615120C0113000307010A0615123801130006151210011300062001011280ED0500010A100A062001011180F90507011280F50520001280C10615122401130006151220011300042001011C042000125D06151228011300021300062002010E12591E07091280C1128101151220011300125D1300125912591512210113001300061512340113000615121401130004000101080B07041512340113000A0A080400010A0A0C1001031E00101E001E001E00080A011512340113000A1001021E00101E001E00080701151234011300061512180113000615121C011300062002010E11310420001235062001126912690520010112091207071281011281151269125912591225120D090100044974656D00000407020A080615122C011300040001011C06070212591239040701123904070208082307091512100113001512180113000A125912591D151210011300081D15121801130008061512300113000B070412811512691D1269080B15128135011512180113000520010113000B15128135011512100113000B151281350115122001130004200012290820021280D10E1171040000122D0520001D13003E07131512140113001239021512813501151218011300081512813501151210011300151281350115122001130008080A080808081280C112291239122D020815126D0211711249070001124911813D0720020113001301021D050620011301130005200012815106200112815908082004010E11710505072003010E11710A062002010E1171140708128101123D1280C11229125D1D1275081171160100115175657279506172616C6C656C697A657200000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313000002901002465343635336131332D386538362D343932372D626463312D65613337353665613435363300000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000008D7FC24C00000000020000009E0000007C7000007C52000052534453CB9B358FF267194295914B7BDE3E336301000000433A5C55736572735C5061756C2057686974655C446F63756D656E74735C56697375616C2053747564696F20323031305C50726F6A656374735C5175657279506172616C6C656C697A65725F62657461315C5175657279506172616C6C656C697A65725C6F626A5C52656C656173655C5175657279506172616C6C656C697A65722E7064620000004471000000000000000000005E71000000200000000000000000000000000000000000000000000050710000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058800000380300000000000000000000380334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00498020000010053007400720069006E006700460069006C00650049006E0066006F00000074020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F006600740000004C0012000100460069006C0065004400650073006300720069007000740069006F006E00000000005100750065007200790050006100720061006C006C0065006C0069007A00650072000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000004C001600010049006E007400650072006E0061006C004E0061006D00650000005100750065007200790050006100720061006C006C0065006C0069007A00650072002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F006600740020003200300031003000000000005400160001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005100750065007200790050006100720061006C006C0065006C0069007A00650072002E0064006C006C000000440012000100500072006F0064007500630074004E0061006D006500000000005100750065007200790050006100720061006C006C0065006C0069007A00650072000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007000000C000000703100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE ASSEMBLY [GMRose]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300B7C2C24C0000000000000000E00002210B010800001600000006000000000000AE3500000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000005835000053000000004000004003000000000000000000000000000000000000006000000C000000C43400001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B4150000002000000016000000020000000000000000000000000000200000602E7273726300000040030000004000000004000000180000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001C00000000000000000000000000004000004200000000000000000000000000000000903500000000000048000000020005009423000030110000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300B00750000000100001173070000060A0F00281200000A0F01281200000A0F02281200000A0F03281300000A0F04281300000A0F05281300000A06FE0608000006731400000A731500000A0B070F06281200000A1914FE0602000006731600000A1618141A1614731700000A6F1800000A07176F1900000A076F1A00000A2A000000133002004500000002000011030A064503000000020000000E0000001A0000002B24027B010000048C1F0000012A027B020000048C1F0000012A027B030000048C1F0000012A7201000070731B00000A7A000000133002003B000000030000110274030000020A03067B01000004281C00000A810400000104067B02000004281C00000A810400000105067B03000004281C00000A81040000012A1E02281D00000A2A1E02281D00000A2A7202281D00000A02037D0100000402047D0200000402057D030000042A1E02281D00000A2A133002004800000004000011282600000A6F2700000A027B060000043315027B050000041FFE330B02167D05000004020A2B131673100000060A06027B100000047D1000000406027B080000047D07000004062A1E0228090000062A133005002B01000002000011027B050000040A06450200000005000000E5000000380F01000002157D0500000402177D0900000402177D0A00000402167D0B00000402147D0C00000402167D0D00000402167D0E00000402147D0F00000438C200000002027B07000004166F2800000A7D0D00000402027B07000004176F2800000A7D0E00000402027B07000004186F2900000A7D0F000004027B090000042C0902167D090000042B36027B0D000004027B0B0000042E0702177D0A000004027B0F000004027B0C000004282A00000A2C0E02257B0A00000417587D0A00000402027B0D000004027B0E000004027B0A00000473060000067D0400000402177D05000004172A02157D0500000402027B0D0000047D0B00000402027B0F0000047D0C000004027B070000046F2B00000A3A2EFFFFFF162A1E027B040000042A1A732C00000A7A062A1E027B040000042A7A02281D00000A02037D0500000402282600000A6F2700000A7D060000042A001330020018000000040000111FFE73100000060A06027D1000000406037D08000004062A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000A4050000237E0000100600004808000023537472696E677300000000580E00001800000023555300700E0000100000002347554944000000800E0000B002000023426C6F6200000000000000020000015717A20B0902000000FA253300160000010000002900000005000000100000001000000012000000050000002D0000001500000004000000010000000200000002000000070000000600000001000000030000000100000000000A00010000000000060056004F00060070005D000A009D0088000A00A700880006001601FB000A003A0124010600CF01B0010600F301E10106000A02E10106002702E10106004602E10106005F02E10106007802E10106009302E1010600AE02E1010600C702B0010600DB02B0010600E902E10106000203E101060032031F035300460300000600750355030600950355030A00D503BA030E001E04060467003204000067004304000067005B0400000A006C0424010A007F0424010600C5044F000600CB044F000600EE04FB000600FC045D00060008054F00060073071F0306009C078B070A00DC07C9070600FC074F00060016084F0006002C08550300000000010000000000010001000100100015000000050001000100010010002A000000050001000500000010003700000005000400070003011000E10400000500040009000600DA003D000600E8003D000600F0003D0001009B05BB01010043063D0001004E063D000600DC01CD0106008E06CD0106009806D1010600AA063D000600B9063D000600CB06D4010600DC063D000600EA063D000600F806D40106000507D7015020000000009600B0000A000100D420000000009600B9001D0008002821000000009600C70024000A006F21000000008618D40032000E007721000000008618D40032000E007F21000000008618D40036000E009C21000000008618D400320011007023000000008600480140001100A42100000000E10114059D011200F82100000000E1016505B1011200002200000000E1019205B7011200372300000000E109A805BF0112003F2300000000E101F50532001200462300000000E101200632001200482300000000E1096306C90112005023000000008618D4005B001200000001004F01000002005501000003006101000004006D01000005007601000006007F01000007008D0100000100A20100000200A60100000100AE0102000200DA0002000300E80002000400F00000000100DA0000000200E80000000300F00000000100DC01000001004306050016000500090005001A000500890005008D003900D40032004100D4004B004900D4004B005100D4004B005900D4004B006100D4004B006900D4004B007100D4004B007900D4004B008100D40050008900D4004B009100D4004B009900D4004B00A100D4005500B100D4005B00B900D4003200C100D40032001900EA0312012100EA0316010C00D40021011400D4002E011C00D40021012400D4004C011400980461011400A90450001400BD046B010101D4004B002100D50483010900D40032002C005705A70111005705B10111019205B7013400E905C40111011A06320019013B0632001101E905C9012101D40032002901A307E9012901B50716013101E907F4013101F207F90139010308FE0131011108B7014101D40032004901D400320020008B0060002E00230010022E002B0016022E0083008F022E003B0025022E00130004022E001B0010022E00530010022E007B0086022E00330004022E00430010022E005B0046022E006B0070022E0073007D02A3006B01E40120012B01E40140012B01E40180012B01E401A0012B01E401E0012B01E40100022B01E40174017F018901EF010500010000000F07DB0100004C07E00102000C00030002000F000500050012003D00050014003F0005001600410005001800430005001A00450005001C00470005001E0049001A0127013E0145018E019501048000000100000000000000000000000000B3030000020000000000000000000000010046000000000002000000000000000000000001007C00000000000100000000000000000000000000F40300000000050004000000003C4D6F64756C653E00474D526F73652E646C6C0055736572446566696E656446756E6374696F6E73004F75747075745265636F726400706172616C6C656C576F726B6572006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261626C650053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670053716C496E7433320053657175656E6365004465636F64654F7264696E616C0053657175656E636546696C6C002E63746F72004163636F756E744E756D62657200436865636B49640053657175656E636549640053797374656D2E436F6C6C656374696F6E732E47656E657269630049456E756D657261626C6560310053797374656D2E446174612E53716C436C69656E740053716C4461746152656164657200646F576F726B007175657279006D696E5661726961626C65006D61785661726961626C65006D696E56616C7565006D617856616C756500776F726B6572546872656164730064657374696E6174696F6E5461626C654E616D6500726F77006F7264696E616C006F0053797374656D2E52756E74696D652E496E7465726F705365727669636573004F757441747472696275746500726F77730053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500474D526F7365004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F56616C7565005175657279506172616C6C656C697A6572004164616D4D616368616E69632E5175657279546F6F6C73005175657279506172616C6C656C697A6572603100526F774C6F67696344656C65676174650042756C6B436F70794D617070696E6744656C65676174650042756C6B436F707953657474696E67730053716C42756C6B436F70794F7074696F6E730053716C42756C6B436F7079436F6C756D6E4D617070696E67007365745F42756C6B53657474696E6773007365745F5265757365436F6E6E656374696F6E0050726F6365737300496E74333200457863657074696F6E006F705F496D706C69636974003C646F576F726B3E645F5F300049456E756D657261746F7260310049456E756D657261746F720049446973706F7361626C650053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261626C653C4F75747075745265636F72643E2E476574456E756D657261746F7200476574456E756D657261746F720053797374656D2E436F6C6C656374696F6E732E49456E756D657261626C652E476574456E756D657261746F72004D6F76654E657874003C3E325F5F63757272656E740053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261746F723C4F75747075745265636F72643E2E6765745F43757272656E74006765745F43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E52657365740052657365740053797374656D2E49446973706F7361626C652E446973706F736500446973706F7365003C3E315F5F7374617465003C3E6C5F5F696E697469616C54687265616449640053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E6765745F43757272656E74003C3E335F5F726F7773003C66697273745265636F72643E355F5F31003C73657175656E63653E355F5F32003C6C6173744163636F756E743E355F5F33003C6C6173745374617475733E355F5F34003C6163636F756E743E355F5F35003C636865636B49643E355F5F36003C7374617475733E355F5F37003C3E345F5F746869730053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261746F723C4F75747075745265636F72643E2E43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E43757272656E7400446562756767657248696464656E4174747269627574650053797374656D2E546872656164696E6700546872656164006765745F43757272656E74546872656164006765745F4D616E6167656454687265616449640053797374656D2E446174612E436F6D6D6F6E0044624461746152656164657200476574496E74333200476574537472696E6700537472696E67006F705F496E657175616C6974790052656164004E6F74537570706F72746564457863657074696F6E00436F6D70696C657247656E657261746564417474726962757465000000134E006F0020007300750063006800200069000000000071BF6CF9B7A9454E8C533A1E7D037F3D0008B77A5C561934E0891200071209110D110D110D111111111111110D0600021C120C080D0004011C10111110111110111103200001062003010808080206080A200115121501120C1219042001010E0420010102052001011155042001010880B0010002005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A4461746141636365737301000000540E1146696C6C526F774D6574686F644E616D650C53657175656E636546696C6C0320000E032000080615126901120C052002011C180615126501120C0F2007010E0E0E0808081512690113000615126D01120C0615127101120C142009010E0815126D01130002080E1175081D1279092001011512710113000820001512150113000A0702121015126501120C03070108050001111108040701120C0615121501120C071512808501120C0920001512808501120C09200015128085011300052000128089032000020306120C042000120C04200013000320001C0306121902060202060E03061210042800120C0328001C0401000000050000128095040701121404200108080420010E08050002020E0E0B010006474D526F736500000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313000002901002438663639323232382D393939362D346332622D616464332D37363635656138336139396300000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000B7C2C24C000000000200000077000000E0340000E016000052534453AC3390009EAF614ABC3D6E95120DFA0208000000433A5C55736572735C5061756C2057686974655C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C474D526F73655C474D526F73655C6F626A5C52656C656173655C474D526F73652E70646200008035000000000000000000009E3500000020000000000000000000000000000000000000000000009035000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000E80200000000000000000000E80234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00448020000010053007400720069006E006700460069006C00650049006E0066006F00000024020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F00660074000000380007000100460069006C0065004400650073006300720069007000740069006F006E000000000047004D0052006F007300650000000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000038000B00010049006E007400650072006E0061006C004E0061006D006500000047004D0052006F00730065002E0064006C006C00000000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310030000000000040000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000047004D0052006F00730065002E0064006C006C0000000000300007000100500072006F0064007500630074004E0061006D0065000000000047004D0052006F007300650000000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000B03500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
-- Sign the QP engine
ADD SIGNATURE
TO ASSEMBLY::QueryParallelizer
BY CERTIFICATE SQLCLR_Unsafe_Permission_Cert
WITH PASSWORD = 'SQLCLRUnsafeCertPassword';
GO
-- Give QP engine UNSAFE permissions (for threading)
ALTER ASSEMBLY QueryParallelizer
WITH PERMISSION_SET = UNSAFE,
VISIBILITY = OFF;
GO
-- The SQLCLR function
CREATE FUNCTION
dbo.Sequence
(
@Query NVARCHAR(4000),
@MinVariable NVARCHAR(4000),
@MaxVariable NVARCHAR(4000),
@MinValue INTEGER,
@MaxValue INTEGER,
@WorkerThreads INTEGER,
@Destimation NVARCHAR(4000)
)
RETURNS TABLE
(
AccountNumber INTEGER,
CheckId INTEGER,
SequenceId INTEGER
)
AS EXTERNAL NAME GMRose.UserDefinedFunctions.Sequence;
Test run
-- ========================================
-- Test run
-- ========================================
IF OBJECT_ID(N'tempdb..##Results', N'U')
IS NOT NULL
DROP TABLE ##Results;
GO
-- Results will be bulk-copied to this table
CREATE TABLE
##Results
(
AccountNumber INTEGER NOT NULL,
CheckId INTEGER NOT NULL,
SequenceId INTEGER NOT NULL,
);
GO
DECLARE @StartTime DATETIME,
@min-2 INTEGER,
@max-2 INTEGER;
-- Set the test start time, and find the range
-- of account numbers in the input data set
SELECT @StartTime = GETUTCDATE(),
@min-2 = MIN(acct_nbr),
@max-2 = MAX(acct_nbr)
FROM ##temp;
-- The SQLCLR function
-- Produces no output (bulk-copied to ##Results instead)
SELECT *
FROM dbo.Sequence
(
N'
SELECT acct_nbr, ckid, stat
FROM ##temp
WHERE acct_nbr BETWEEN @low AND @high
ORDER BY
acct_nbr,
ckid
OPTION (MAXDOP 1);',
'@low',
'@high',
8,
N'##Results'
) S;
-- Show the execution time
-- Disregard results from the first few runs
-- The SQLCLR function is just-in-time compiled
-- from MSIL to native machine code
SELECT execution_time =
DATEDIFF(MILLISECOND, @StartTime, GETUTCDATE());
SELECT result_table_rows =
COUNT_BIG(*)
FROM ##Results;
-- Show the output
--SELECT AccountNumber,
-- CheckId,
-- SequenceId
--FROM ##Results
--ORDER BY
-- AccountNumber,
-- CheckId;
Clean-up code
-- ========================================
-- Clean-up code
-- ========================================
USE [tempdb];
GO
DROP TABLE #tempstore;
GO
DROP TABLE ##Results;
GO
DROP FUNCTION dbo.Sequence;
GO
DROP ASSEMBLY GMRose;
GO
DROP CERTIFICATE SQLCLR_Unsafe_Permission_Cert;
GO
USE [master];
GO
DROP LOGIN SQLCLR_Unsafe_Permission_Login;
GO
DROP CERTIFICATE SQLCLR_Unsafe_Permission_Cert;
GO
-- ========================================
-- Delete the certificate backup files
-- from C:\temp manually
-- ========================================
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 23, 2010 at 6:36 am
Forgot the attach the C# source code (the meat of the thing is in the parallelWorker class, at the bottom):
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using AdamMachanic.QueryTools;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
/// <summary>
/// License:
///
/// This code sample is part of the QueryParallelizer project. You are free to use or modify this code for
/// educational and internal corporate purposes, as long as this header is preserved. Sale or redistribution
/// of this code to third parties in its original form, modified form, or compiled form, is strictly prohibited
/// without written consent by Adam Machanic.
///
/// (C) 2010 Adam Machanic
/// </summary>
[SqlFunction
(
DataAccess = DataAccessKind.Read,
FillRowMethodName = "SequenceFill"
)
]
public static IEnumerable Sequence
(
SqlString query,
SqlString minVariable,
SqlString maxVariable,
SqlInt32 minValue,
SqlInt32 maxValue,
SqlInt32 workerThreads,
SqlString destinationTableName
)
{
parallelWorker worker = new parallelWorker();
QueryParallelizer<OutputRecord> qp =
new QueryParallelizer<OutputRecord>
(
query.Value,
minVariable.Value,
maxVariable.Value,
minValue.Value,
maxValue.Value,
workerThreads.Value,
new QueryParallelizer<OutputRecord>.RowLogicDelegate(worker.doWork)
);
qp.BulkSettings = new QueryParallelizer<OutputRecord>.BulkCopySettings
(
destinationTableName.Value,
3,
DecodeOrdinal,
false,
2,
null,
SqlBulkCopyOptions.TableLock,
0,
null
);
qp.ReuseConnection = true;
return (qp.Process());
}
public static object DecodeOrdinal(OutputRecord row, int ordinal)
{
switch (ordinal)
{
case 0:
return (row.AccountNumber);
case 1:
return (row.CheckId);
case 2:
return (row.SequenceId);
default:
throw new Exception("No such i");
}
}
public static void SequenceFill
(
object o,
out SqlInt32 AccountNumber,
out SqlInt32 CheckId,
out SqlInt32 SequenceId
)
{
OutputRecord or = (OutputRecord)o;
AccountNumber = or.AccountNumber;
CheckId = or.CheckId;
SequenceId = or.SequenceId;
}
};
public class OutputRecord
{
public OutputRecord()
{
}
public OutputRecord
(
int AccountNumber,
int CheckId,
int SequenceId
)
{
this.AccountNumber = AccountNumber;
this.CheckId = CheckId;
this.SequenceId = SequenceId;
}
public int AccountNumber;
public int CheckId;
public int SequenceId;
}
class parallelWorker
{
public parallelWorker() { }
public IEnumerable<OutputRecord> doWork(SqlDataReader rows)
{
bool firstRecord = true;
int sequence = 1;
int lastAccount = default(int);
string lastStatus = default(string);
int account = default(int);
int checkId = default(int);
string status = default(string);
while (rows.Read())
{
// Read the columns
account = rows.GetInt32(0);
checkId = rows.GetInt32(1);
status = rows.GetString(2);
if (firstRecord)
{
firstRecord = false;
}
else
{
if (account != lastAccount)
{
sequence = 1;
}
if (status != lastStatus)
{
sequence++;
}
}
yield return (new OutputRecord(account, checkId, sequence));
lastAccount = account;
lastStatus = status;
}
}
}
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 23, 2010 at 8:02 am
Paul White NZ (10/23/2010)
I'm not sure I see where the requirement to update the original table comes from?
True enough. I suppose the output could be consumed by other code.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2010 at 8:13 am
Paul White NZ (10/23/2010)
If the results need to be persisted, simply change the SELECT into a SELECT INTO.
Heh... Of course... I've really got to stop posting when I'm tired. Thanks, Paul.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2010 at 8:41 am
Paul,
Do the source (##temp) and destination (##Results) tables need to be global tables in order for the solution that you posted to work?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2010 at 9:52 am
Jeff Moden (10/23/2010)
Do the source (##temp) and destination (##Results) tables need to be global tables in order for the solution that you posted to work?
Hi Jeff,
The most accurate answer is to say that #tables and table variables won't work because they are private to a particular session. Normal tables and ##tables work fine. The other reason I chose a ##table is because tempdb is always in SIMPLE mode, so bulk loading will be minimally logged (the SQLCLR code takes a table lock).
The multiple reading and writing threads use different sessions, so a #table or variable would not be visible.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 23, 2010 at 2:59 pm
Paul White NZ (10/23/2010)
.......The multiple reading and writing threads use different sessions, so a #table or variable would not be visible.
Paul
That is the ultimate, one and only valid reason to use global temporary tables.
Great feedback !
T-14 and counting for SQLPass
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 23, 2010 at 3:47 pm
Out of curiosity, how many records are you processing? And did you try out the script that I posted?
Wayne
Thank you (and all of the others) for your suggestions. There were about 300,000 records processed. I looked at your script and saw that it was centered on a field you acctnum. There really isn't any such field in my table. Perhaps you saw that field in my table called BankAcctId which one could could think is an account number. For my application, that field contains the same value for all of my 300,000 records. (It had a second value for the other 100,000+ records that I later excluded.)
Also, since my script might be run by more than one user at the same time, I wanted to avoid having it create and then drop permanent tables.
gmrose
October 23, 2010 at 5:51 pm
gmrose (10/23/2010)
Out of curiosity, how many records are you processing? And did you try out the script that I posted?
Wayne
Thank you (and all of the others) for your suggestions. There were about 300,000 records processed. I looked at your script and saw that it was centered on a field you acctnum. There really isn't any such field in my table. Perhaps you saw that field in my table called BankAcctId which one could could think is an account number. For my application, that field contains the same value for all of my 300,000 records. (It had a second value for the other 100,000+ records that I later excluded.)
Also, since my script might be run by more than one user at the same time, I wanted to avoid having it create and then drop permanent tables.
gmrose
Thanks for the feedback.
In the script I posted here, I was only using the ckid and stat fields. However, you would need to populate a temp table with the data you want processed, and put a clustered index on it. (The other script I posted was to try to get ColdCoffee to practice this, and doesn't apply to your issue.)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 24, 2010 at 9:44 am
Jeff Moden (10/22/2010)
... we'll have a drag race. 🙂
Quirky Update would win this one Jeff, by a factor of about 7 - we both know that 🙂
I don't think we'll find a requirement which can be met by both methods and won on performance by a rCTE. Had the OP's requirement been for an update then I wouldn't have bothered but since it was for a select, it was marginally worth it. Thing is, it's so darned easy to get the correct results out of a rCTE. It's only when you want it to run quickly that you have to resort to the same warm cosy cosseted environment that the quirky update requires.
700ms! Paul you do make me laugh! 24hrs --> 5mins --> 16secs --> (Quirky Update, about 2secs) --> 700ms. Not bad at all.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply