InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 01:41:19 GMT20http://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]gmrose (10/28/2010)[/b][hr]Thanks again for everyone's suggestions. I ended up rewriting the script to use Wayne's "quirky update solution". That script processed over 300,000 records in 18 seconds. I don't need anything faster than that. :-)[/quote]No worries - an interesting problem will often get more than one great solution posted for it. I think my preference would have been for the recursive CTE solution overall, but there you go. I'm also a little surprised that the QU solution takes that long on such a small number of records.Make sure you understand exactly how the QU method works, and that it is an undocumented quirk of SQL Server that might disappear at some point. Be sure to test it explicitly whenever you apply a hotfix, cumulative update, service pack, or upgrade to a new version. You should also plan to handle the 'divide by zero' error that may be thrown if something breaks the QU method.Thu, 28 Oct 2010 19:40:45 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]WayneS (10/23/2010)[/b][hr][quote][b]gmrose (10/23/2010)[/b][hr][quote]Out of curiosity, how many records are you processing? And did you try out the script that I posted? Wayne[/quote]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[/quote]Thanks for the feedback.In the script I posted [url=http://www.sqlservercentral.com/Forums/FindPost1008146.aspx][u]here[/u][/url], 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.)[/quote]Thanks again for everyone's suggestions. I ended up rewriting the script to use Wayne's "quirky update solution". That script processed over 300,000 records in 18 seconds. I don't need anything faster than that. :-)Thu, 28 Oct 2010 17:05:21 GMTgmrosehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]Paul White NZ (10/24/2010)[/b][hr]For those edge cases where absolute performance is critical (perhaps you have a few billion rows to process) the SQLCLR solution really shines. As far as I know, it is the only 'running total' solution that can make (effective) use of parallelism - and therein lies the whole of its advantage.[/quote]You're a fast-jet kinda guy Paul :-D best I can hope to do is a lumbering transport - slow and steady, lots of work.Mon, 25 Oct 2010 14:18:49 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]Jeff Moden (10/24/2010)[/b][hr]Heh... it's actually a darned clever method that you've come up with. And, it has the advantage of being documented! :-P[/quote]Sorry it's taken so long to reply to this, had to leave work early with man-flu :/Thanks Jeff. Not sure I can take credit for coming up with this, but I'll take a little for plugging it from time to time :)FWIW I use both methods in anger at work, whichever seems appropriate.Mon, 25 Oct 2010 14:15:43 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]Jeff Moden (10/24/2010)[/b][hr]That's what I thought. My recommendation would be to avoid global temp tables because (as you know) they're visible and usable by anyone. The big problem with their use for stored procedures is that if two people try to run the same thing at the same time (admittedly, kind of tough to do at 700 ms), BOOM!!! It doesn't even have to be the same thing running... just two sprocs using the same names for the global temp tables.[/quote]Yes, the global temporary tables are just to make the demonstration easier.In the real world, I would use a real table, perhaps with a GUID name.Sun, 24 Oct 2010 22:50:53 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]Paul White NZ (10/23/2010)[/b][hr][quote][b]Jeff Moden (10/23/2010)[/b][hr]Do the source (##temp) and destination (##Results) tables need to be global tables in order for the solution that you posted to work?[/quote]Hi Jeff,The most accurate answer is to say that #tables and table variables [i]won't[/i] 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[/quote]That's what I thought. My recommendation would be to avoid global temp tables because (as you know) they're visible and usable by anyone. The big problem with their use for stored procedures is that if two people try to run the same thing at the same time (admittedly, kind of tough to do at 700 ms), BOOM!!! It doesn't even have to be the same thing running... just two sprocs using the same names for the global temp tables.Of course, if this were the real world and I needed to solve a bazillion row problem and needed your good CLR proc to do it, I'd modify the code to "reserve" the global temp names in a "token" table to be able to run. If it couldn't save the name in the table because it was already present, it would be because someone already had the code running from somewhere else. There's always a workaround.Sun, 24 Oct 2010 22:24:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]ChrisM@home (10/24/2010)[/b][hr][quote][b]Jeff Moden (10/22/2010)[/b][hr][quote][b]... we'll have a drag race. :-)[/quote]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 [i]requires[/i].700ms! Paul you do make me laugh! 24hrs --> 5mins --> 16secs --> (Quirky Update, about 2secs) --> 700ms. Not bad at all. [/quote]Heh... it's actually a darned clever method that you've come up with. And, it has the advantage of being documented! :-PSun, 24 Oct 2010 22:19:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]ChrisM@home (10/24/2010)[/b][hr]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 [i]requires[/i][/quote].Hey Chris,I really do like your recursive CTE method - I remember being amazed by how fast it was when I first saw you use it ([url]http://www.sqlservercentral.com/Forums/FindPost873955.aspx[/url]).It does have a big advantage in simplicity (especially over the SQLCLR solution, which took me over an hour to put together) and for very many problems I'd say it was the method of choice - easily fast enough, fully standard techniques, and quick and easy to modify.[quote]700ms! Paul you do make me laugh! 24hrs --> 5mins --> 16secs --> (Quirky Update, about 2secs) --> 700ms. Not bad at all.[/quote]Cheers :-)For those edge cases where absolute performance is critical (perhaps you have a few billion rows to process) the SQLCLR solution really shines. As far as I know, it is the only 'running total' solution that can make (effective) use of parallelism - and therein lies the whole of its advantage.Sun, 24 Oct 2010 17:02:30 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]Jeff Moden (10/22/2010)[/b][hr][quote][b]... we'll have a drag race. :-)[/quote]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 [i]requires[/i].700ms! Paul you do make me laugh! 24hrs --> 5mins --> 16secs --> (Quirky Update, about 2secs) --> 700ms. Not bad at all. Sun, 24 Oct 2010 09:44:33 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]gmrose (10/23/2010)[/b][hr][quote]Out of curiosity, how many records are you processing? And did you try out the script that I posted? Wayne[/quote]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[/quote]Thanks for the feedback.In the script I posted [url=http://www.sqlservercentral.com/Forums/FindPost1008146.aspx][u]here[/u][/url], 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.)Sat, 23 Oct 2010 17:51:54 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote]Out of curiosity, how many records are you processing? And did you try out the script that I posted? Wayne[/quote]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.gmroseSat, 23 Oct 2010 15:47:47 GMTgmrosehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]Paul White NZ (10/23/2010)[/b][hr].......The multiple reading and writing threads use different sessions, so a #table or variable would not be visible.Paul[/quote]That is the ultimate, one and only valid reason to use global temporary tables. Great feedback !T-14 and counting for SQLPassSat, 23 Oct 2010 14:59:53 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]Jeff Moden (10/23/2010)[/b][hr]Do the source (##temp) and destination (##Results) tables need to be global tables in order for the solution that you posted to work?[/quote]Hi Jeff,The most accurate answer is to say that #tables and table variables [i]won't[/i] 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.PaulSat, 23 Oct 2010 09:52:06 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspxPaul,Do the source (##temp) and destination (##Results) tables need to be global tables in order for the solution that you posted to work?Sat, 23 Oct 2010 08:41:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]Paul White NZ (10/23/2010)[/b][hr]If the results need to be persisted, simply change the SELECT into a SELECT INTO.[/quote]Heh... Of course... I've really got to stop posting when I'm tired. Thanks, Paul.Sat, 23 Oct 2010 08:13:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]Paul White NZ (10/23/2010)[/b][hr]I'm not sure I see where the requirement to update the original table comes from?[/quote]True enough. I suppose the output could be consumed by other code.Sat, 23 Oct 2010 08:02:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspxForgot the attach the C# source code (the meat of the thing is in the parallelWorker class, at the bottom):[code="other"]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 /// amachanic@gmail.com /// </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; } }}[/code]Sat, 23 Oct 2010 06:36:52 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]Chris Morris-439714 (10/22/2010)[/b][hr]400,000 rows in 15 seconds...[/quote]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 [url=http://sqlblog.com/files/folders/beta/entry29021.aspx]Query Parallelizer[/url] engine, and runs on both SQL Server 2005 and 2008.Test Data Creation[code="sql"]-- ========================================-- Test data creation-- (thanks to Chris Morris)-- ========================================USE [tempdb];GOIF OBJECT_ID (N'tempdb..#tempstore', N'U') IS NOT NULL DROP TABLE #tempstore;GOCREATE TABLE #tempstore ( acct_nbr INTEGER NOT NULL, ckid INTEGER NOT NULL, stat VARCHAR(10) NOT NULL, );GOINSERT INTO #tempstore (acct_nbr, ckid, stat)SELECT 1, 101 ,'Open' UNION ALLSELECT 1, 102 ,'Open' UNION ALLSELECT 1, 103 ,'Open' UNION ALLSELECT 1, 104 ,'Void' UNION ALLSELECT 1, 105 ,'Void' UNION ALLSELECT 1, 106 ,'Open' UNION ALLSELECT 1, 107 ,'Open' UNION ALLSELECT 1, 108 ,'Open' UNION ALLSELECT 1, 109 ,'Void' UNION ALLSELECT 1, 110 ,'Open' UNION ALLSELECT 2, 101 ,'Open' UNION ALLSELECT 2, 102 ,'Open' UNION ALLSELECT 2, 103 ,'Open' UNION ALLSELECT 2, 104 ,'Void' UNION ALLSELECT 3, 105 ,'Void' UNION ALLSELECT 3, 106 ,'Open' UNION ALLSELECT 3, 107 ,'Open' UNION ALLSELECT 4, 108 ,'Open' UNION ALLSELECT 4, 109 ,'Void' UNION ALLSELECT 4, 110 ,'Open';GOIF OBJECT_ID(N'tempdb..##temp') IS NOT NULL DROP TABLE ##temp;GOCREATE TABLE ##temp ( acct_nbr INTEGER NOT NULL, ckid INTEGER NOT NULL, stat VARCHAR(10) NOT NULL, );GOCREATE UNIQUE CLUSTERED INDEX cuq ON ##temp (acct_nbr, ckid);GO-- 400,000 rows of dataINSERT 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.statFROM #tempstore TCROSS JOIN ( SELECT TOP (20000) n = ROW_NUMBER() OVER ( ORDER BY a.[name] ) - 1 FROM master.dbo.syscolumns a, master.dbo.syscolumns b ) Numbers;GO[/code]SQLCLR security setup[code="sql"]-- ========================================-- SQLCLR security : master database-- ========================================USE [master];GOIF 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;GOIF CERT_ID('SQLCLR_Unsafe_Permission_Cert') IS NOT NULL DROP CERTIFICATE SQLCLR_Unsafe_Permission_Cert;GOCREATE 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';GOCREATE LOGIN SQLCLR_Unsafe_Permission_LoginFROM CERTIFICATE SQLCLR_Unsafe_Permission_Cert;GOGRANT UNSAFE ASSEMBLYTO SQLCLR_Unsafe_Permission_Login;GO-- These two files must not already existBACKUP CERTIFICATE SQLCLR_Unsafe_Permission_CertTO 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];GOIF OBJECT_ID(N'dbo.Sequence', N'FT') IS NOT NULL DROP FUNCTION dbo.Sequence;GOIF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'GMRose') DROP ASSEMBLY GMRose;GOIF CERT_ID('SQLCLR_Unsafe_Permission_Cert') IS NOT NULL DROP CERTIFICATE SQLCLR_Unsafe_Permission_Cert;GOCREATE CERTIFICATE SQLCLR_Unsafe_Permission_CertFROM FILE = 'C:\temp\SQLCLR_Unsafe.cer'WITH PRIVATE KEY ( FILE = 'C:\temp\SQLCLR_Unsafe.pvk', DECRYPTION BY PASSWORD = 'SQLCLRUnsafeKey', ENCRYPTION BY PASSWORD = 'SQLCLRUnsafeCertPassword' );[/code]SQLCLR solution objects[code="sql"]-- ========================================-- SQLCLR object creation-- ========================================---- Uses Adam Machanic's Query Parallelizer engine-- See http://sqlblog.com/files/folders/beta/entry29021.aspxCREATE ASSEMBLY [QueryParallelizer] AUTHORIZATION [dbo]FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103008D7FC24C0000000000000000E00002210B0108000052000000060000000000006E71000000200000008000000000400000200000000200000400000000000000040000000000000000C0000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000001C7100004F00000000800000900300000000000000000000000000000000000000A000000C000000607000001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000074510000002000000052000000020000000000000000000000000000200000602E7273726300000090030000008000000004000000540000000000000000000000000000400000402E72656C6F6300000C00000000A000000002000000580000000000000000000000000000400000420000000000000000000000000000000050710000000000004800000002000500303900003037000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B300400E900000001000011281300000A39D80000007201000070731400000A0A066F1500000A731600000A0B07066F1700000A0772350000706F1800000A076F1900000A72A70000701F1620800000006F1A00000A186F1B00000A076F1900000A72C10000701F1620800000006F1A00000A186F1B00000A076F1C00000A26731D00000A0C08076F1900000A166F1E00000A6F1F00000A74380000016F2000000A08076F1900000A176F1E00000A6F1F00000A74380000016F2100000A08176F2200000A08166F2300000A0820C80000006F2400000A086F2500000A8001000004DE10062C06066F2600000ADC1480010000042A0000000110000002001500C3D8000A000000001A7E010000042A1E027B2700000A2A6E027B2800000A2C0B72D3000070732900000A7A02037D2700000A2A1E027B2A00000A2AAA027B2800000A2C0B723F010070732900000A7A0316300B72A7010070732900000A7A02037D2A00000A2A1E027B2B00000A2AAA027B2800000A2C0B720D020070732900000A7A0316300B726F020070732900000A7A02037D2B00000A2A1E027B2C00000A2A6E027B2800000A2C0B72CF020070732900000A7A02037D2C00000A2A1E027B2D00000A2A6E027B2800000A2C0B722D030070732900000A7A02037D2D00000A2A1E027B2E00000A2A6E027B2800000A2C0B7297030070732900000A7A02037D2E00000A2A000000133002009300000002000011021F647D2A00000A021F647D2B00000A02282F00000A0E061732060E061F20310B7205040070733000000A7A0E056A0E046A59176A580A0E066A063104066910060E040E05310B7269040070733000000A7A02037D3100000A02047D3200000A02057D3300000A020E047D3400000A020E057D3500000A020E067D3600000A020E077D3700000A0228020000067D2700000A2A0003300E006E00000000000000027B2800000A2C0B72CB040070732900000A7A02177D2800000A027B3100000A027B3200000A027B3300000A027B3400000A027B3500000A027B3600000A027B3700000A027B2700000A027B2A00000A027B2B00000A027B2C00000A027B2D00000A027B2E00000A733800000A2A0000033004004B0000000000000002282F00000A02037D3900000A02047D3A00000A02057D3B00000A020E047D3C00000A020E057D3D00000A020E067D3E00000A0202FE063F00000A734000000A734100000A7D4200000A2A42027C4300000A284400000A166AFE022A1B3002003A00000003000011027C4300000A284500000A26027B3900000A6F4600000ADE0326DE0017734700000A0A066F4800000A027B4200000A6F4900000ADE0326DE002A0000011C000000000C000D19000301000001000029000D360003010000011B3003009801000004000011140A17734A00000A0B076F4800000A027B3900000A6F4B00000A0A027B3C00000A2C11066F1500000A027B3900000A6F4C00000A02284D00000A3A0E010000027B4E00000A6F4F00000A0C0839FC000000027B3900000A6F1900000A166F1E00000A087B5000000A8C410000016F5100000A027B3900000A6F1900000A176F1E00000A087B5200000A8C410000016F5100000A027B3C00000A2D06066F1500000A027B3900000A6F5300000A0D096F5400000A2C59027B3B00000A096F5500000A6F5600000A13072B1A11076F5700000A130402284D00000A2D1202110417285800000A11076F5900000A2DDDDE0C11072C0711076F2600000ADC021208FE150900001B110816285800000A2B18096F5A00000A262B07096F5A00000A26096F5B00000A2DF1027B3C00000A2C08096F5C00000A2B06066F5D00000A027B4E00000A176F5E00000A38E7FEFFFFDE311305723F0500701105735F00000A1306027B4E00000A11066F6000000A027B4E00000A176F5E00000A02286100000ADE00DE15027B3900000A6F6200000A062C06066F6200000ADC2A414C000002000000C800000027000000EF0000000C0000000000000000000000020000004D0100004F0100003100000016000001020000000200000080010000820100001500000000000000A202037D4E00000A027B4200000A6F6300000A2D1302284D00000A2D0B027B4200000A6F6400000A2A00000013300400E000000005000011027B6500000A2D1802027B3D00000A8D0900001B7D6500000A02157D6600000A042C1D027B6500000A02257B6600000A1758250D7D6600000A0903A40900001B027B6600000A027B3D00000A17592E06043A89000000027B6500000A027B6600000A736700000A0A027B3A00000A2C34027B3A00000A6F6800000A0B2B1E027B3A00000A07066F6900000A2C0902147D6500000A2B0E17286A00000A02284D00000A2CDA027B3E00000A2C33027B3E00000A6F6800000A0C2B1D027B3E00000A08066F6900000A2C0802147D6500000A2A17286A00000A02284D00000A2CDB2A133004005A0000000200001102282F00000A02037D6B00000A0203D48D0A00001B7D6C00000A02168D0900001B16736700000A7D6D00000A166A0A2B14027B6C00000A06D4027B6D00000AA206176A580A060332E802156A7D6E00000A02156A7D6F00000A2A62027C6E00000A284500000A027B6B00000A5D287000000A2A62027C6F00000A284500000A027B6B00000A5D287000000A2A8A027B6D00000A027B6C00000A03D48F0A00001B04027B6D00000A280100002BFE012A00133004002900000006000011027B6D00000A04027B6C00000A03D48F0A00001B027B6D00000A280200002B250A5106FE0116FE012A000000033007005C0000000000000002282F00000A02037D7300000A02047D7400000A02057D7500000A020E047D7600000A020E057D7700000A0202FE067800000A734000000A734100000A7D7900000A020E060E070E080E09027B7900000A737A00000A7D7B00000A2AB602037D7C00000A027B7900000A6F6300000A2D18027B7B00000A6F7D00000A2D0B027B7900000A6F6400000A2A32027B7B00000A6F7E00000A2A001B300300CA0000000700001117734A00000A0A066F4800000A027B7300000A027B7500000A737F00000A0B07027B7400000A6F8000000A07166F8100000A07027B7600000A6F8200000A027B7700000A2C49027B7700000A6F8300000A13052B1A11056F0200000A741A0000010C076F8400000A086F8500000A2611056F5900000A2DDDDE1511057503000001130611062C0711066F2600000ADC07027B7B00000A6F8600000ADE1D0D723F05007009735F00000A1304027B7C00000A11046F6000000ADE00DE0D027B7C00000A176F5E00000ADC2A00000128000002005300277A001500000000000000009D9D001D1600000102000000BCBC000D0000000042027C8800000A284400000A166AFE022A0000001B3002003700000003000011027C8800000A284500000A26027B8900000A6F6300000A2C1D17734700000A0A066F4800000A027B8900000A6F4900000ADE0326DE002A0001100000000026000D33000301000001CE02157D8A00000A02282F00000A02037D8B00000A02047D8C00000A02057D8D00000A020E047D8E00000A020E057D8900000A2A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A00133003008C00000008000011027B9000000A2D58027B8D00000A6F9100000A0A027B8D00000A06027C9000000A6F9200000A2C0902157D8A00000A2B2F027B9300000A2C02162A027B8E00000A176F9400000A2C0702177D9300000A02287D00000A2CBC02287E00000A2BB402257B8A00000A1758250B7D8A00000A07027B9000000A7B9500000A310C02147D9000000A3876FFFFFF172A1A738F00000A7A1A738F00000A7A1E027B8B00000A2A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A8E027B8C00000A027B9000000A7B9600000A027B8A00000AA30900001B036F9700000A2A1A738F00000A7A1A738F00000A7A1A738F00000A7A1A738F00000A7A5602282F00000A02037D5000000A02047D5200000A2A0000000330030072000000000000000216739800000A7D9900000A02156A7D9A00000A02282F00000A02037D9B00000A02047D9C00000A02057D9D00000A020E047D9E00000A020E057D9F00000A020E067DA000000A020E077DA100000A020E076FA200000A7DA300000A020E076FA200000A0E066FA200000A587DA400000A2A00001B3002002100000009000011027B9D00000A250B28A500000A02FE137BA600000A0ADE070728A700000ADC062A0000000110000002000D000B180007000000001B3002002A0000000A000011027B9D00000A250A28A500000A02FE137BA600000A2D090203FE137DA600000ADE070628A700000ADC2A00000110000002000D00152200070000000072032C0C027CA800000A284500000A26027B9900000A6FA900000A262A000000133002002100000002000011027C9A00000A284500000A0A06027BA300000A2F0A027BA100000A06D49A2A142A0000001B3002004B0000000B000011160A2B16027B9F00000A069A6F6100000ADE0326DE000617580A06027B9F00000A8E6932DF160B2B16027BA000000A079A6FAA00000ADE0326DE000717580B07027BA000000A8E6932DF2A00011C0000000004000F13000301000001000029000F380003010000011B300300F40000000C000011027B9F00000A13051613062B13110511069A0A06026FAB00000A110617581306110611058E6932E5027BA000000A13071613082B13110711089A0B07026FAC00000A110817581308110811078E6932E5027B9C00000A6FA900000A26027CA800000A284400000A0C08027BA300000A330C027B9E00000A6FA900000A2608027BA400000A2E32027B9B00000A6F6300000A2C0A02FE137BA600000A2C080228AD00000A2B13027B9900000A20EE0200006F9400000A262BA4DE1E0D727905007009735F00000A1304021104286000000A0228AD00000ADE00DE19027B9C00000A6FA900000A26027B9D00000A6FA900000A26DC2A011C000000000000BABA001E1600000102000000DADA00190000000003300300550000000000000002282F00000A0E051732060E051F20310B72C3050070733000000A7A02037DAE00000A02047DAF00000A02057DB000000A020E047DB100000A020E057DB200000A020E062C040E062B0528020000067DB300000A2A000000133007005D0000000D000011020304050E040E050E0628B400000A020E077DB500000A020E087DB600000A722D06007073B700000A0A02066F8400000A7DB800000A0E092C220E090C160D2B1508099A0B027BB800000A076F8500000A260917580D09088E6932E52A5602282F00000A02037D9600000A02047D9500000A2A0013300B00D80200000E00001102157DB900000A02282F00000A020E096A73BA00000A7DBB00000A021673BC00000A7DBD00000A140A1673BC00000A0B170C73BE00000A0D0E0D2C650E0D7BB100000A0C0E096A73BA00000A0A1713042B44090E0D7BB300000A0E0D7BAE00000A0E0D7BB500000A0E0D7BB600000A0E0D7BB800000A0E0D7BAF00000A0E0D7BB000000A060773BF00000A6FC000000A11041758130411040E0D7BB200000A31B173C100000A130573C200000A13060E056A0E046A59176A5813090E0B163DA600000011090E066A2F270E04130A2B161106110A110A73C300000A6FC400000A110A1758130A110A0E0531E438C800000016130B2B6B0E046A11090E066A5B110B6A5A58691307110B17580E062E160E046A176A5911090E066A5B110B17586A5A58692B020E051308110820FFFFFF7F2F1911072000000080311011081107175933080E06175910062B1011061107110873C300000A6FC400000A110B1758130B110B0E06328F2B500E04130711070E0B175958130811080E0531040E05130811061107110873C300000A6FC400000A11080E052E231108130C11081758130711070E0B17595813081108110C2F040E05130811070E0531BD16130D2B720E082C040E082B052802000006731400000A130E110E6FC500000A130F110F036F1800000A110F166FC600000A110F6F1900000A041E6FC700000A26110F6F1900000A051E6FC700000A261105110F082D03142B06027BBB00000A0E070E0C0E0A0673C800000A6FC900000A110D1758130D110D11066FCA00000A0E0632040E062B0711066FCA00000A3F71FFFFFF1673BC00000A13100228CB00000A1110027BBD00000A0711056FCC00000A096FCD00000A11066FCE00000A73CF00000A7DD000000A027BD000000AFE06D100000A734000000A734100000A131111116F6400000A111020881300006F9400000A1312027BD000000A6FD200000A2C0C027BD000000A6FD200000A7A11122D0B722F060070732900000A7A2A0A022A0A022A5E027BD300000A7B9600000A027BB900000AA30900001B2A72027BD300000A7B9600000A027BB900000AA30900001B8C0900001B2A00133003009500000008000011027BD300000A2D61027BBB00000A6F9100000A0A027BBB00000A06027CD300000A6F9200000A2C0902157DB900000A2B38027BD400000A2C02162A027BBD00000A176F9400000A2C0702177DD400000A027BD000000A6FD200000A2CB7027BD000000A6FD200000A7A02257BB900000A1758250B7DB900000A07027BD300000A7B9500000A310C02147DD300000A386DFFFFFF172A1A738F00000A7A36027BD000000A166F5E00000A2A000003300300540200000000000073D500000A80510000047E5100000416D04E00000128D600000A6FD700000A7E5100000417D01400001B28D600000A6FD700000A7E5100000418D05000000128D600000A6FD700000A7E5100000419D03800000128D600000A6FD700000A7E510000041F1FD01000000128D600000A6FD700000A7E510000041AD01000000128D600000A6FD700000A7E510000041F21D01000000128D600000A6FD700000A7E510000041F22D01000000128D600000A6FD700000A7E510000041BD01100000128D600000A6FD700000A7E510000041CD05100000128D600000A6FD700000A7E510000041DD01400001B28D600000A6FD700000A7E510000041ED04100000128D600000A6FD700000A7E510000041F09D01100000128D600000A6FD700000A7E510000041F0AD03800000128D600000A6FD700000A7E510000041F0BD03800000128D600000A6FD700000A7E510000041F0CD03800000128D600000A6FD700000A7E510000041F0DD05100000128D600000A6FD700000A7E510000041F0FD01000000128D600000A6FD700000A7E510000041F10D05200000128D600000A6FD700000A7E510000041F11D01100000128D600000A6FD700000A7E510000041F12D03800000128D600000A6FD700000A7E510000041F20D01000000128D600000A6FD700000A7E510000041F13D01400001B28D600000A6FD700000A7E510000041F14D05300000128D600000A6FD700000A7E510000041F0ED01300000128D600000A6FD700000A7E510000041F15D01400001B28D600000A6FD700000A7E510000041F16D03800000128D600000A6FD700000A2A327E51000004026FD800000A2A1E02282F00000A2A2A0203041428630000062A1B300700AC0200000F00001117734A00000A0A066F4800000A140B052C03052B052802000006731400000A0C086FC500000A0D0972990600706F1800000A086F1500000A096F1C00000A2609026F1800000A096F1900000A031E6FC700000A20000000808C410000016F5100000A096F1900000A041E6FC700000A20FFFFFF7F8C410000016F5100000A096F5300000A130411046FD900000A0B072D0B72B9060070732900000A7ADE0C11042C0711046F2600000ADCDE0A092C06096F2600000ADCDE0A082C06086F2600000ADC076FDA00000A6FDB00000A8D1D000001130516130638BB010000076FDA00000A11066FDC00000A72270700706FDD00000AA51C0000011307110717594505000000AE00000038010000AE000000380100002D00000011071F0A59450300000098000000220100009800000011071F155945020000008600000086000000380B01000011051106076FDA00000A11066FDC00000A72410700706FDD00000A7438000001076FDA00000A11066FDC00000A72270700706FDD00000AA51C000001076FDA00000A11066FDC00000A72570700706FDD00000AA552000001D2076FDA00000A11066FDC00000A72790700706FDD00000AA552000001D273DE00000AA238CC00000011051106076FDA00000A11066FDC00000A72410700706FDD00000A7438000001076FDA00000A11066FDC00000A72270700706FDD00000AA51C000001076FDA00000A11066FDC00000A72930700706FDD00000AA5410000016A20FFFFFF7F6A2E1F076FDA00000A11066FDC00000A72930700706FDD00000AA5410000016A2B02156A73DF00000AA22B4211051106076FDA00000A11066FDC00000A72410700706FDD00000A7438000001076FDA00000A11066FDC00000A72270700706FDD00000AA51C00000173E000000AA21106175813061106076FDA00000A6FDB00000A3F33FEFFFF11052A0128000002008600189E000C000000000200270085AC000A000000000200200098B8000A0000000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000F0150000237E00005C160000C812000023537472696E67730000000024290000AC07000023555300D0300000100000002347554944000000E03000005006000023426C6F6200000000000000020000015717A20B090E000000FA25330016000001000000560000001000000051000000630000008B00000008000000E00000000E0000000F000000060000001200000019000000020000001400000001000000030000000B0000000C0000000200000000000A0001000000000006003D0136010A005201460106005E0136010A006A0146010600760136010600A30188010600C401B1010600D00188010600DE01B1010A0043042D0406006F045E040A0054052D040A0067052D0406008A055E040A00290646010600CA0636010600DF0636010600FC06360106001707360106002A085E0406009208720806009D0836010A0051092D040600660936010600730936010A00010A2D040600D70A88010A00E40A46010A00200B050B06006F0B500B06006C0C5A0C0600830C5A0C0600A00C5A0C0600BF0C5A0C0600D80C5A0C0600F10C5A0C06000C0D5A0C0600270D5A0C0600400D500B0600540D500B0600620D5A0C06007B0D5A0C0600AB0D980DAF00BF0D00000600CE0D72080600EE0D72080A001E0E050B0A00390E2D040A005A0E470E0A007B0E470E0A00950E2D040A00BB0E2D040A00CC0E470E0A00D80E46010A00090F2D0406002E0F36010A00890F470E0600B80F36010600CA0F5E040600D60F5E0406000810EC0F06001B10EC0F06003B102B100A005D102D040600881036010A00A610470E0E00D510BF100600F11036010A0013112D0406004C11B10106007C115A0C0600931136010600AB115E040600BE1136010600D3115E040600E6115E040600FA11880106004612360106004C12360106007012360106007812360106007F1236010600851236010A008A1246010A00A51246010A00C012460100000000010000000000010001008101000020003200050001000100010010004A003200050002000300030010005E000000050010001100030010006B00000005001B001700030010007C000000050020001C000300100087000000050028002000030010009A00000005003100440003001000A600000005003300450002010000B4000000150040004C0002010000C500000015004000500002001000DD00000005004000540003001000EE00000005004900560003001000FA00000005004B005700010100000D013200050051005F00810110001B0132000500520062003100EA01180021003802180021003E02180021004A02180021005602270021005F022700210068022700210079022A0001008202320001008F0218000100A60227000100B40227000100C00227000100CA0232000100DA02350021004E04B30021005304B700210079022A0021007604BF002100CA0232002100B402270021007D04B70001008604C30001008E04CB000100970427000100A404D0002100F404D0002100FF04E80021000705F10001000D05D00001001A05D0002100EA01180021009B0518002100AC0533012100C00227002100B80537012100C2053B0121007604BF0001008604C3002100D10527002100DC05430121007D04B7002100E4054B0121007604BF000100F805F1000100FF05270001000D0632000100A404D0002600B00727002600BD0727002100CA07BF002100D5074B012100E9074B012100E4054B0121006802D0002100F907D000210009080E0221001108170221001D08200201003908290201004E08D00001005F08D0000100A7082D022600970918002600A00727002600A80943012600B00932002600CF0927002600DA0918002600EB0933012600060427002600F709370126008E04CB0026001A0A270021005304B7002100E9074B012100240AC3000100F805F1000100FF05270001000D0632003100EE0AF1025020000000009118FB011B000100582100000000960802021F0001005F21000000008608E7023D000100672100000000860802034100010083210000000086081D03460002008B210000000086082F034A000200B621000000008608410346000300BE2100000000860851034A000300E921000000008608610346000400F1210000000086086F034A0004000D220000000086087D034F00050015220000000086089103530005003122000000008608A503580006003922000000008608B603610006005822000000008618C7036B000700F822000000008600CD037B000E007423000000008618C70399000E00CB23000000008608B6044F001400DC23000000008600C904D30014004024000000008600D004D30014003026000000008600D704D70014005C26000000008100DD04E10015004827000000008618C703F9001700AE270000000086002805FE001800C7270000000086003805FE001800E02700000000860049050201180004280000000086004F050D011A003C28000000008618C70319011C00A428000000008600D704D7002500D228000000008600C904D3002600E028000000008600D004D3002600E029000000008608B6044F002600F429000000008600C904D3002600482A000000008618C7034F0126007C2A00000000E6011906D3002B00832A00000000E6091F0646002B008A2A00000000E601330664012B00912A00000000E60942064F002B00982A00000000E6014F064F002B00A02A00000000E6014F054F002B00382B00000000E6095A0646002B003F2B00000000E6016E06D3002B00462B00000000E609760646002B004E2B00000000E601850669012B00552B00000000E60190066E012C005C2B00000000E601980673012D00632B00000000E601A1067D0132006A2B00000000E601A90682013300712B00000000E601B2068C013800782B00000000E601BA06920139007F2B00000000E601D30697013A00862B00000000E601E7069D013B008D2B00000000E601F206A3013C00942B00000000E6010107A8013D009B2B00000000E6010E07AE013E00A22B00000000E6011C07B3013F00A92B00000000E6012407B9014000B02B00000000E6012D07BE014100B72B00000000E6013607C3014200BE2B00000000E6013F0792014300C52B00000000E6014707C8014400CC2B00000000E601520792014500D32B00000000E6015C07CD014600F72B00000000E6016507D2014700FE2B00000000E6016F0769014800052C00000000E6097807D80149000C2C00000000E6097807CD014A00132C000000008618C703E7014B002C2C000000008618C703ED014D00AC2C000000008608BD0833025400EC2C000000008608D70838025400342D000000008600F10853005500542D00000000860004093E025600842D0000000081001709D3005600F82D0000000086002809D30056000000000003008618C7034C025600000000000300C6015F0952025800000000000300C60181095D025900000000000300C6018D0967025C000000000003008618C7034C025D00000000000300C6015F0972025F00000000000300C601810979026100000000000300C6018D0984026500142F000000008618C7038A026600782F000000008618C70399026C00E12F000000008618C703AE027500F82F000000008618C703B6027700DC3200000000E601270AD1028400DF3200000000E101350ADA028400E23200000000E609620ADF028400FA3200000000E1096E0AE4028400183300000000E601990A4F008400B93300000000E601A20AD3008400C03300000000E6016E06D3008400D033000000009118FB011B0084003036000000009600F40AFB0284003D36000000008618C703D300850045360000000096002C0B0203850050360000000096002C0B0B038800000001003D0B000001003D0B000001003D0B000001003D0B000001003D0B000001003D0B000001003802000002003E02000003004A02000004005602000005005F02000006006802000007007902000001004E0400000200530400000300790200000400CA0200000500B402000006007D0400000100860400000100430B00000200DD0400000100F40400000100470B000002003D0B00000100470B020002003D0B00000100EA01000002009B0500000300AC0500000400C00200000500B80500000600D10500000700DC05000008007D0400000900E40500000100860400000100D10500000200DC05000003007D0400000400E405000005007604000001007C0B000001007C0B000001007C0B000002007E0B000003008A0B00000400910B000005009E0B000001007C0B000001007C0B00000200A50B000003008A0B00000400910B000005009E0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B000001007C0B00000100B10B000001007C0B000001007C0B00000100B60B000001007C0B00000100B10B000001007C0B00000100B00700000200BD0700000100CA0700000200D50700000300E90700000400BD0B000005000908000006001108000007001D08000001003D0B00000100D30B00000100EE0B00000200F50B00000100FC0B00000100FC0B00000200060C00000300EE0B00000100F80500000100EE0B00000200F50B00000100430B000002000F0C00000100430B000002000F0C00000300060C00000400EE0B00000100F805000001009B0500000200D10500000300DC05000004001D0C000005003C0C00000600EA01000001009B0500000200D10500000300DC05000004001D0C000005003C0C00000600EA0100000700AC0500000800C00200000900B805000001008E04000002001A0A000001003802000002003E02000003004A02000004005602000005005F02000006006802000007007902000008008F0200000900A60200000A00B40200000B00C00200000C00CA0200000D00DA0200000100470C000001003802000002004C0C00000300530C000001003802000002004C0C00000300530C000004008F020700090007000D00070011000E0006000E001D000E000A000E000D000E0025003900270ADA024900620AE402F100C703D300F900C70341000101C70341000901C70341001101C70341001901C70341002101C70341002901C70341003101C70341003901C70353004101C70341004901C70341005101C70341005901C70315036901C7034A007101C703D3007901290E1C038101C70341008901670ED3005100C703D30051006C0E20039101850E41005100AC0E27039901C80E2D03A901EB0E37039101F90E4600B901C703D300990178073E03A901240FE402B901350F4100B901440F4100B901570F5300B9016E0F5300B901790F4A00C901A30F3D0019006E06D3001C008F0218001C0082023200B100C70341001C00A60227001C00B40227001C00C00227001C00CA0232001C00DA0235000900C703D300D101C70341001C00380218001C003E0218001C004A0218001C00560227001C005F0227001C00680227001C0079022A002400C703B6022C004E04B3002C005304B7002C0079022A002C00CA0232002C00B40227002C007D04B7002C00D004D300D901C7034C025900C70369032C007604BF002C00A404D000E1014F057003E101E20F70039101C904D300E901C7037603F9015010D30059005710D3000102C703760351007110830391018010D3002C00B6044F002C008604C300340004093E023C00B0072700A9018E1097033C00BD072700510098109C031102B3104F0044005F0952020C00270AD1021400620ADF022C00DD04E1004900990A4F0011024F054F0011024F064F0011026E06D30089011906D3003400F1085300B100C703AB033400D70838022C00C904D30019026E06D3005900DF104F005900D704D3002C008E04CB002C00970427005400C703AE025C003805FE005C00490502015900EB10DF035C00F404D0005C00FF04E8005C000705F1005C000D05D0005C001A05D0002102F610F003E101FA10F503E1010A110B046400EA01180064009B0518006400AC0533016400C00227006400B80537016400D004D30064007604BF006C00C7034F016400C2053B0164008604C3006C00B6044F006C00C904D3002902C7032D0429021F114100290238114A0029026F034A003102270ADA0229025B1134046900C80E390429026E1140043902C70341006C00A404D0006C007604BF006C00FF0527006C00D10527006C00DC0543016C007D04B7006C00E4054B014102C703D3006C00F805F1005C002805FE005C004F050D016C000D0632004902B611690154001A0A270054008E04CB0074005F097202A100C703530034003908290234004E08D0003400CA07BF003400D5074B013400E9074B013400E4054B01340009080E0234001108170234001D0820025102C411FE0034006802D0003400F907D0005902DB116F043400A7082D025902E1116F0434005F08D0006102F6114F006400C904D3002C00D704D7006400D704D70034001709D3007C00970918007C00A00727007C00A80943017C00B00932007C00CF0927007C00DA0918007C00C7038A027C00EB0933017C00060427002902C70341007C00F70937012400FF0527005C00C703F90024005304B7007100C70353002400E9074B018400C703D3006400C70319018400C80EC8048C00C703D3009400C703D3003C00C703E7019400C80EC80481010112E60491010F124A009901C80EEB042C00C70399008C00C80EC80494002212460059002C12F4048C003E12F90484003E12F90494003E12F9043400C703ED012400240AC30034002809D3003400BD0833022400F805F10024000D0632009C00C703D30091005E1247059C00C80E4F059C0078075A0511023306640179009C126105A90222124600A10278076705B1027807D801E900C7036E05E900C7037705E900C7037F052E004B00C7052E0023009B052E002B00B2052E003300B2052E003B00B8052E0043009B052E006B00E8052E005300B2052E006300B2052E0083001F062E00930031062E007B0012062E008B002806E3003B045904450357037D03B203E40316044604630474047B0480048504B004FF04860502000100030002000400080007000900090010000E00110000001F0223000000D50384000000EC0388000000FA038800000006048800000010048C000000200490000000E5048C000000E5048C00000081078800000087078C000000900788000000A00788000000AB07DD010000AB07E20100003B0947020000A80AE8020000B00AED0202000200030001000400050002000300050002000500070001000600070002000700090001000800090001000A000B00020009000B0002000B000D0001000C000D0001000E000F0002000D000F0002001200110002002000130002002400150002002600170002002900190002002B001B00020042001D00020043001F0002004600210001004700210002005A00230002005B0025000E00B20003000E00B60005000A00110050035B03620389039003A103A803D103D8031F0426046804A904BC04CE04DA043E0557050480000001000000000000000000000000000C0E000002000000000000000000000001002D0100000000020000000000000000000000010046010000000002000000000000000000000001003601000000000400030005000300060003000700060008000300090003000A0003000B0003000C0003000D0003000E00030000000000060044010000000008004401000000000A004401000000000C004401000000000E00440100000000100044010000000012004401000000001400440100000000160044010000000018004401000000001A004401000000001C004401E3000204E50002040000003C4D6F64756C653E005175657279506172616C6C656C697A65722E646C6C00436F6E6E656374696F6E4275696C646572004164616D4D616368616E69632E5175657279546F6F6C73005175657279506172616C6C656C697A6572603100776F726B657254687265616400726F756E64526F62696E4275666665720062756C6B54687265616400696E7465726E616C4461746152656164657200776F726B5061636B616765006D6F6E69746F7254687265616400526F774C6F67696344656C65676174650042756C6B436F70794D617070696E6744656C65676174650042756C6B436F707953657474696E6773006974656D5061636B61676500696E7465726E616C456E756D657261746F720054797065436F6E766572746572004D6574616461746147656E657261746F72006D73636F726C69620053797374656D004F626A65637400540053797374656D2E446174610049446174615265616465720049446973706F7361626C650049446174615265636F7264004D756C74696361737444656C65676174650053797374656D2E436F6C6C656374696F6E732E47656E657269630049456E756D657261626C6560310053797374656D2E436F6C6C656374696F6E730049456E756D657261626C650049456E756D657261746F7260310049456E756D657261746F7200636F6E6E656374696F6E537472696E67002E6363746F72006765745F4C6F6F706261636B436F6E6E656374696F6E537472696E67004C6F6F706261636B436F6E6E656374696F6E537472696E67007175657279006D696E5661726961626C65006D61785661726961626C65006D696E56616C7565006D617856616C7565006E756D576F726B65725468726561647300726F774C6F67696300697350726F63657373696E6700746172676574436F6E6E656374696F6E537472696E6700726F7742756666657253697A65007061636B61676553697A6500626174636853697A65007265757365436F6E6E656374696F6E0062756C6B53657474696E6773006765745F546172676574436F6E6E656374696F6E537472696E67007365745F546172676574436F6E6E656374696F6E537472696E67006765745F526F7742756666657253697A65007365745F526F7742756666657253697A65006765745F5061636B61676553697A65007365745F5061636B61676553697A65006765745F426174636853697A65007365745F426174636853697A65006765745F5265757365436F6E6E656374696F6E007365745F5265757365436F6E6E656374696F6E006765745F42756C6B53657474696E6773007365745F42756C6B53657474696E6773002E63746F720050726F6365737300546172676574436F6E6E656374696F6E537472696E6700526F7742756666657253697A65005061636B61676553697A6500426174636853697A65005265757365436F6E6E656374696F6E0042756C6B53657474696E67730053797374656D2E446174612E53716C436C69656E740053716C436F6D6D616E6400636F6D6D006F7574707574526F77730053797374656D2E546872656164696E670054687265616400776F726B65720062756C6B526F7773006D6F6E69746F72006974656D4C697374006C697374506F736974696F6E0074687265616443616E63656C436F756E74006765745F54687265616443616E63656C65640043616E63656C00646F576F726B00537461727400656E71756575650054687265616443616E63656C65640062756666657253697A65006275666665727300626C616E6B00726561644C6F636174696F6E0077726974654C6F636174696F6E00476574526561644C6F636174696F6E0047657457726974654C6F636174696F6E00577269746500526561640053716C42756C6B436F70794F7074696F6E730053716C42756C6B436F7079436F6C756D6E4D617070696E67436F6C6C656374696F6E004D616E75616C52657365744576656E740064657374696E6174696F6E5461626C6500636F70794F7074696F6E7300636F6C756D6E4D61700062756C6B44617461526561646572006669656C64436F756E7400646174614D61700062756C6B436F6D706C6574696F6E4576656E7400726573756C740063757272656E74526573756C74006D6F6E69746F724578697400436C6F7365006765745F446570746800446174615461626C6500476574536368656D615461626C65006765745F4973436C6F736564004E657874526573756C74006765745F5265636F726473416666656374656400446973706F7365006765745F4669656C64436F756E7400476574426F6F6C65616E0047657442797465004765744279746573004765744368617200476574436861727300476574446174610047657444617461547970654E616D65004461746554696D65004765744461746554696D6500446563696D616C00476574446563696D616C00476574446F75626C650054797065004765744669656C645479706500476574466C6F61740047756964004765744775696400476574496E74313600476574496E74333200476574496E743634004765744E616D65004765744F7264696E616C00476574537472696E670047657456616C75650047657456616C75657300497344424E756C6C006765745F4974656D004465707468004973436C6F736564005265636F7264734166666563746564004669656C64436F756E74004974656D0072616E67654D696E696D756D0072616E67654D6178696D756D006D61696E54687265616400696E697469616C697A6174696F6E4576656E7400636F6D706C6574696F6E4576656E74006E756D546F74616C5468726561647300776F726B6572730062756C6B576F726B65727300776F726B5061636B61676573004175746F52657365744576656E7400776F726B6572436F6D706C657465644576656E74006E657874576F726B6572546872656164006E756D46696E6973686564546872656164730053797374656D2E52756E74696D652E436F6D70696C65725365727669636573004973566F6C6174696C6500457863657074696F6E00776F726B6572546872656164457863657074696F6E006765745F576F726B6572546872656164457863657074696F6E007365745F576F726B6572546872656164457863657074696F6E00536574576F726B6572436F6D706C65746564004765744E657874576F726B5061636B6167650063616E63656C416C6C546872656164730053657475704D6F6E69746F7254687265616400576F726B6572546872656164457863657074696F6E0053716C4461746152656164657200496E766F6B6500494173796E63526573756C74004173796E6343616C6C6261636B00426567696E496E766F6B6500456E64496E766F6B650044657374696E6174696F6E5461626C6500446174614D6170004F7574707574526F7773546F45787465726E616C456E756D657261746F72004E756D5468726561647300436F6E6E656374696F6E537472696E6700436F70794F7074696F6E7300436F6C756D6E4D61700053716C42756C6B436F7079436F6C756D6E4D617070696E67006974656D436F756E74006D7000476574456E756D657261746F720053797374656D2E436F6C6C656374696F6E732E49456E756D657261626C652E476574456E756D657261746F72006765745F43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E6765745F43757272656E74004D6F76654E6578740052657365740043757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E43757272656E740044696374696F6E61727960320053716C44625479706500747970657300436F6E76657274546F4E657454797065004D6963726F736F66742E53716C5365727665722E5365727665720053716C4D657461446174610047657451756572794D657461646174610076616C756500726F77006C6F636174696F6E0053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650069006669656C644F666673657400627566666572006275666665726F6666736574006C656E677468006669656C646F6666736574006E616D650076616C7565730062756C6B436F6D706C65746974696F6E4576656E7400696E6372656D656E74436F6D706C6574696F6E436F756E746572006F626A656374006D6574686F6400696E707574446174610063616C6C6261636B00636F6C756D6E4F7264696E616C006F7574707574526F7773546F45787465726E616C456E756D657261746F72006E756D546872656164730074797065006D696E566172006D61785661720053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F64657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005175657279506172616C6C656C697A65720053716C436F6E74657874006765745F4973417661696C61626C650053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E007365745F436F6E6E656374696F6E004462436F6D6D616E64007365745F436F6D6D616E64546578740053716C506172616D65746572436F6C6C656374696F6E006765745F506172616D65746572730053716C506172616D6574657200416464004462506172616D6574657200506172616D65746572446972656374696F6E007365745F446972656374696F6E00457865637574654E6F6E51756572790053716C436F6E6E656374696F6E537472696E674275696C646572006765745F56616C756500537472696E67007365745F44617461536F75726365007365745F496E697469616C436174616C6F67007365745F496E74656772617465645365637572697479007365745F456E6C697374007365745F4D6178506F6F6C53697A65004462436F6E6E656374696F6E537472696E674275696C646572006765745F436F6E6E656374696F6E537472696E6700417267756D656E74457863657074696F6E00546872656164537461727400496E7465726C6F636B656400496E6372656D656E740053797374656D2E53656375726974792E5065726D697373696F6E730053656375726974795065726D697373696F6E005065726D697373696F6E53746174650053797374656D2E536563757269747900436F64654163636573735065726D697373696F6E004173736572740041626F72740053716C436C69656E745065726D697373696F6E006765745F436F6E6E656374696F6E005072657061726500496E743332007365745F56616C7565004578656375746552656164657200446244617461526561646572006765745F486173526F77730053797374656D2E436F6D706F6E656E744D6F64656C00436F6D706F6E656E74006765745F4973416C69766500536C656570004D6174680041627300436F6D7061726545786368616E67650045786368616E67650053716C42756C6B436F7079007365745F44657374696E6174696F6E5461626C654E616D65007365745F42756C6B436F707954696D656F757400436F6C6C656374696F6E42617365006765745F436F6C756D6E4D617070696E6773005772697465546F5365727665720044656661756C744D656D626572417474726962757465004E6F74496D706C656D656E746564457863657074696F6E005761697448616E646C6500576169744F6E65004172726179006765745F4C6F6E674C656E677468004D6F6E69746F7200456E7465720045786974004576656E745761697448616E646C6500536574004C697374603100437265617465436F6D6D616E64007365745F436F6D6D616E6454696D656F7574006765745F436F756E74006765745F43757272656E7454687265616400546F417272617900496E7436340052756E74696D655479706548616E646C65004765745479706546726F6D48616E646C6500426F6F6C65616E00446F75626C6500496E74313600427974650044617461526F77436F6C6C656374696F6E006765745F526F777300496E7465726E616C44617461436F6C6C656374696F6E426173650044617461526F7700003363006F006E007400650078007400200063006F006E006E0065006300740069006F006E0020003D00200074007200750065000071530045004C00450043005400200040007300650072007600650072005F006E0061006D00650020003D002000400040005300450052005600450052004E0041004D0045002C0020004000640062005F006E0061006D00650020003D002000440042005F004E0041004D00450028002900001940007300650072007600650072005F006E0061006D00650000114000640062005F006E0061006D006500006B43006F006E006E0065006300740069006F006E00200073007400720069006E0067002000630061006E0020006F006E006C007900200062006500200073006500740020007000720069006F007200200074006F002000700072006F00630065007300730069006E006700006752006F00770020006200750066006600650072002000730069007A0065002000630061006E0020006F006E006C007900200062006500200073006500740020007000720069006F007200200074006F002000700072006F00630065007300730069006E006700006552006F00770020006200750066006600650072002000730069007A00650020006D007500730074002000620065002000670072006500610074006500720020007400680061006E0020006F007200200065007100750061006C00200074006F002000310000615000610063006B006100670065002000730069007A0065002000630061006E0020006F006E006C007900200062006500200073006500740020007000720069006F007200200074006F002000700072006F00630065007300730069006E006700005F5000610063006B006100670065002000730069007A00650020006D007500730074002000620065002000670072006500610074006500720020007400680061006E0020006F007200200065007100750061006C00200074006F0020003100005D420061007400630068002000730069007A0065002000630061006E0020006F006E006C007900200062006500200073006500740020007000720069006F007200200074006F002000700072006F00630065007300730069006E006700006943006F006E006E0065006300740069006F006E002000720065007500730065002000630061006E0020006F006E006C007900200062006500200073006500740020007000720069006F007200200074006F002000700072006F00630065007300730069006E006700006D420075006C006B00200063006F00700079002000730065007400740069006E00670073002000630061006E0020006F006E006C007900200062006500200073006500740020007000720069006F007200200074006F002000700072006F00630065007300730069006E00670000634E0075006D0062006500720020006F006600200077006F0072006B00650072002000740068007200650061006400730020006D0075007300740020006200650020006200650074007700650065006E0020003100200061006E00640020003300320000614D0069006E00560061006C007500650020006D0075007300740020006200650020006C0065007300730020007400680061006E0020006F007200200065007100750061006C00200074006F0020004D0061007800560061006C00750065002E000073500072006F00630065007300730069006E0067002000630061006E0020006F006E006C00790020006200650020007300740061007200740065006400200061002000730069006E0067006C0065002000740069006D0065002000700065007200200069006E007300740061006E0063006500003945007800630065007000740069006F006E0020006F006300630075007200720065006400200069006E00200077006F0072006B0065007200004945007800630065007000740069006F006E0020006F006300630075007200720065006400200069006E0020006D006F006E00690074006F007200200074006800720065006100640000694E0075006D0062006500720020006F0066002000620075006C006B00200063006F00700079002000740068007200650061006400730020006D0075007300740020006200650020006200650074007700650065006E0020003100200061006E006400200033003200000100694D006F006E00690074006F007200200074006800720065006100640020006600610069006C0065006400200074006F00200069006E0069007400690061006C0069007A0065002000770069007400680069006E002000350020007300650063006F006E0064007300001F530045005400200046004D0054004F004E004C00590020004F004E003B00006D4D0065007400610064006100740061002000470065006E0065007200610074006F007200200066006F0075006E00640020006E006F00200063006F006C0075006D006E007300200069006E002000740068006500200069006E007000750074002000710075006500720079000019500072006F00760069006400650072005400790070006500001543006F006C0075006D006E004E0061006D00650000214E0075006D00650072006900630050007200650063006900730069006F006E0000194E0075006D0065007200690063005300630061006C006500001543006F006C0075006D006E00530069007A00650000000000AE9DE95E7E303F428427037A29423B560008B77A5C561934E089061512190113000615122101130002060E030000010300000E0308000E020608070615122801130002060207061512300113000320000E042001010E032000080420010108032000020420010102082000151230011300092001011512300113000F2007010E0E0E0808081512280113000820001512190113000328000E032800080328000208280015123001130019200601122915121401130015122801130002081512140113000306122907061512140113000306122D070615122401130004061D130002060A03200001092001011512240113000620020113000208061D1512340113000706151234011300042001010A0320000A0A2002020A1512340113000B2002020A10151234011300192009010E0E11310812350815122C01130015121401130012390306113103061235070615121C011300070615122C01130003061239142005010815122C0113001512140113001239122D042000123D042001020804200105080920050A080A1D05080804200103080920050A080A1D0308080520011209080420010E080520011141080520011145080420010D080520011249080420010C08052001114D08042001060804200108080420010A08042001080E0420011C08052001081D1C0420011C0E0428011C0E0428011C0805200201080820200701122D1239123912391D1512100113001D1512180113001D15122001130008061D15121001130008061D15121801130008061D1512200113000306125105061F55125904200012590520010112590820001512200113000428001259052002011C180A2001151219011300125D0920031261125D12651C0A200115121901130012610620021C1300080A2004126113000812651C0520011C12610E2006010E0815122C01130002080E142009010E0815122C01130002080E1131081D1269072002011D1300081A200D010E0E0E0808081512280113000E08080802151230011300082000151221011300042000122504200013000320001C04280013000328001C090615126D0211711249060001124911710800031D12750E0E0E0900041D12750E0E0E0E062001011180B103000002062001011280C10520001280CD0920031280D10E117108062001011180D90620011280D1080A07031280C112291280DD0615120C0113000307010A0615123801130006151210011300062001011280ED0500010A100A062001011180F90507011280F50520001280C10615122401130006151220011300042001011C042000125D06151228011300021300062002010E12591E07091280C1128101151220011300125D1300125912591512210113001300061512340113000615121401130004000101080B07041512340113000A0A080400010A0A0C1001031E00101E001E001E00080A011512340113000A1001021E00101E001E00080701151234011300061512180113000615121C011300062002010E11310420001235062001126912690520010112091207071281011281151269125912591225120D090100044974656D00000407020A080615122C011300040001011C06070212591239040701123904070208082307091512100113001512180113000A125912591D151210011300081D15121801130008061512300113000B070412811512691D1269080B15128135011512180113000520010113000B15128135011512100113000B151281350115122001130004200012290820021280D10E1171040000122D0520001D13003E07131512140113001239021512813501151218011300081512813501151210011300151281350115122001130008080A080808081280C112291239122D020815126D0211711249070001124911813D0720020113001301021D050620011301130005200012815106200112815908082004010E11710505072003010E11710A062002010E1171140708128101123D1280C11229125D1D1275081171160100115175657279506172616C6C656C697A657200000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313000002901002465343635336131332D386538362D343932372D626463312D65613337353665613435363300000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000008D7FC24C00000000020000009E0000007C7000007C52000052534453CB9B358FF267194295914B7BDE3E336301000000433A5C55736572735C5061756C2057686974655C446F63756D656E74735C56697375616C2053747564696F20323031305C50726F6A656374735C5175657279506172616C6C656C697A65725F62657461315C5175657279506172616C6C656C697A65725C6F626A5C52656C656173655C5175657279506172616C6C656C697A65722E7064620000004471000000000000000000005E71000000200000000000000000000000000000000000000000000050710000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058800000380300000000000000000000380334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00498020000010053007400720069006E006700460069006C00650049006E0066006F00000074020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F006600740000004C0012000100460069006C0065004400650073006300720069007000740069006F006E00000000005100750065007200790050006100720061006C006C0065006C0069007A00650072000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000004C001600010049006E007400650072006E0061006C004E0061006D00650000005100750065007200790050006100720061006C006C0065006C0069007A00650072002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F006600740020003200300031003000000000005400160001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005100750065007200790050006100720061006C006C0065006C0069007A00650072002E0064006C006C000000440012000100500072006F0064007500630074004E0061006D006500000000005100750065007200790050006100720061006C006C0065006C0069007A00650072000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007000000C000000703100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000WITH PERMISSION_SET = SAFE;GOCREATE ASSEMBLY [GMRose] AUTHORIZATION [dbo]FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300B7C2C24C0000000000000000E00002210B010800001600000006000000000000AE3500000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000005835000053000000004000004003000000000000000000000000000000000000006000000C000000C43400001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B4150000002000000016000000020000000000000000000000000000200000602E7273726300000040030000004000000004000000180000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001C00000000000000000000000000004000004200000000000000000000000000000000903500000000000048000000020005009423000030110000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300B00750000000100001173070000060A0F00281200000A0F01281200000A0F02281200000A0F03281300000A0F04281300000A0F05281300000A06FE0608000006731400000A731500000A0B070F06281200000A1914FE0602000006731600000A1618141A1614731700000A6F1800000A07176F1900000A076F1A00000A2A000000133002004500000002000011030A064503000000020000000E0000001A0000002B24027B010000048C1F0000012A027B020000048C1F0000012A027B030000048C1F0000012A7201000070731B00000A7A000000133002003B000000030000110274030000020A03067B01000004281C00000A810400000104067B02000004281C00000A810400000105067B03000004281C00000A81040000012A1E02281D00000A2A1E02281D00000A2A7202281D00000A02037D0100000402047D0200000402057D030000042A1E02281D00000A2A133002004800000004000011282600000A6F2700000A027B060000043315027B050000041FFE330B02167D05000004020A2B131673100000060A06027B100000047D1000000406027B080000047D07000004062A1E0228090000062A133005002B01000002000011027B050000040A06450200000005000000E5000000380F01000002157D0500000402177D0900000402177D0A00000402167D0B00000402147D0C00000402167D0D00000402167D0E00000402147D0F00000438C200000002027B07000004166F2800000A7D0D00000402027B07000004176F2800000A7D0E00000402027B07000004186F2900000A7D0F000004027B090000042C0902167D090000042B36027B0D000004027B0B0000042E0702177D0A000004027B0F000004027B0C000004282A00000A2C0E02257B0A00000417587D0A00000402027B0D000004027B0E000004027B0A00000473060000067D0400000402177D05000004172A02157D0500000402027B0D0000047D0B00000402027B0F0000047D0C000004027B070000046F2B00000A3A2EFFFFFF162A1E027B040000042A1A732C00000A7A062A1E027B040000042A7A02281D00000A02037D0500000402282600000A6F2700000A7D060000042A001330020018000000040000111FFE73100000060A06027D1000000406037D08000004062A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000A4050000237E0000100600004808000023537472696E677300000000580E00001800000023555300700E0000100000002347554944000000800E0000B002000023426C6F6200000000000000020000015717A20B0902000000FA253300160000010000002900000005000000100000001000000012000000050000002D0000001500000004000000010000000200000002000000070000000600000001000000030000000100000000000A00010000000000060056004F00060070005D000A009D0088000A00A700880006001601FB000A003A0124010600CF01B0010600F301E10106000A02E10106002702E10106004602E10106005F02E10106007802E10106009302E1010600AE02E1010600C702B0010600DB02B0010600E902E10106000203E101060032031F035300460300000600750355030600950355030A00D503BA030E001E04060467003204000067004304000067005B0400000A006C0424010A007F0424010600C5044F000600CB044F000600EE04FB000600FC045D00060008054F00060073071F0306009C078B070A00DC07C9070600FC074F00060016084F0006002C08550300000000010000000000010001000100100015000000050001000100010010002A000000050001000500000010003700000005000400070003011000E10400000500040009000600DA003D000600E8003D000600F0003D0001009B05BB01010043063D0001004E063D000600DC01CD0106008E06CD0106009806D1010600AA063D000600B9063D000600CB06D4010600DC063D000600EA063D000600F806D40106000507D7015020000000009600B0000A000100D420000000009600B9001D0008002821000000009600C70024000A006F21000000008618D40032000E007721000000008618D40032000E007F21000000008618D40036000E009C21000000008618D400320011007023000000008600480140001100A42100000000E10114059D011200F82100000000E1016505B1011200002200000000E1019205B7011200372300000000E109A805BF0112003F2300000000E101F50532001200462300000000E101200632001200482300000000E1096306C90112005023000000008618D4005B001200000001004F01000002005501000003006101000004006D01000005007601000006007F01000007008D0100000100A20100000200A60100000100AE0102000200DA0002000300E80002000400F00000000100DA0000000200E80000000300F00000000100DC01000001004306050016000500090005001A000500890005008D003900D40032004100D4004B004900D4004B005100D4004B005900D4004B006100D4004B006900D4004B007100D4004B007900D4004B008100D40050008900D4004B009100D4004B009900D4004B00A100D4005500B100D4005B00B900D4003200C100D40032001900EA0312012100EA0316010C00D40021011400D4002E011C00D40021012400D4004C011400980461011400A90450001400BD046B010101D4004B002100D50483010900D40032002C005705A70111005705B10111019205B7013400E905C40111011A06320019013B0632001101E905C9012101D40032002901A307E9012901B50716013101E907F4013101F207F90139010308FE0131011108B7014101D40032004901D400320020008B0060002E00230010022E002B0016022E0083008F022E003B0025022E00130004022E001B0010022E00530010022E007B0086022E00330004022E00430010022E005B0046022E006B0070022E0073007D02A3006B01E40120012B01E40140012B01E40180012B01E401A0012B01E401E0012B01E40100022B01E40174017F018901EF010500010000000F07DB0100004C07E00102000C00030002000F000500050012003D00050014003F0005001600410005001800430005001A00450005001C00470005001E0049001A0127013E0145018E019501048000000100000000000000000000000000B3030000020000000000000000000000010046000000000002000000000000000000000001007C00000000000100000000000000000000000000F40300000000050004000000003C4D6F64756C653E00474D526F73652E646C6C0055736572446566696E656446756E6374696F6E73004F75747075745265636F726400706172616C6C656C576F726B6572006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261626C650053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670053716C496E7433320053657175656E6365004465636F64654F7264696E616C0053657175656E636546696C6C002E63746F72004163636F756E744E756D62657200436865636B49640053657175656E636549640053797374656D2E436F6C6C656374696F6E732E47656E657269630049456E756D657261626C6560310053797374656D2E446174612E53716C436C69656E740053716C4461746152656164657200646F576F726B007175657279006D696E5661726961626C65006D61785661726961626C65006D696E56616C7565006D617856616C756500776F726B6572546872656164730064657374696E6174696F6E5461626C654E616D6500726F77006F7264696E616C006F0053797374656D2E52756E74696D652E496E7465726F705365727669636573004F757441747472696275746500726F77730053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500474D526F7365004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F56616C7565005175657279506172616C6C656C697A6572004164616D4D616368616E69632E5175657279546F6F6C73005175657279506172616C6C656C697A6572603100526F774C6F67696344656C65676174650042756C6B436F70794D617070696E6744656C65676174650042756C6B436F707953657474696E67730053716C42756C6B436F70794F7074696F6E730053716C42756C6B436F7079436F6C756D6E4D617070696E67007365745F42756C6B53657474696E6773007365745F5265757365436F6E6E656374696F6E0050726F6365737300496E74333200457863657074696F6E006F705F496D706C69636974003C646F576F726B3E645F5F300049456E756D657261746F7260310049456E756D657261746F720049446973706F7361626C650053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261626C653C4F75747075745265636F72643E2E476574456E756D657261746F7200476574456E756D657261746F720053797374656D2E436F6C6C656374696F6E732E49456E756D657261626C652E476574456E756D657261746F72004D6F76654E657874003C3E325F5F63757272656E740053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261746F723C4F75747075745265636F72643E2E6765745F43757272656E74006765745F43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E52657365740052657365740053797374656D2E49446973706F7361626C652E446973706F736500446973706F7365003C3E315F5F7374617465003C3E6C5F5F696E697469616C54687265616449640053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E6765745F43757272656E74003C3E335F5F726F7773003C66697273745265636F72643E355F5F31003C73657175656E63653E355F5F32003C6C6173744163636F756E743E355F5F33003C6C6173745374617475733E355F5F34003C6163636F756E743E355F5F35003C636865636B49643E355F5F36003C7374617475733E355F5F37003C3E345F5F746869730053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261746F723C4F75747075745265636F72643E2E43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E43757272656E7400446562756767657248696464656E4174747269627574650053797374656D2E546872656164696E6700546872656164006765745F43757272656E74546872656164006765745F4D616E6167656454687265616449640053797374656D2E446174612E436F6D6D6F6E0044624461746152656164657200476574496E74333200476574537472696E6700537472696E67006F705F496E657175616C6974790052656164004E6F74537570706F72746564457863657074696F6E00436F6D70696C657247656E657261746564417474726962757465000000134E006F0020007300750063006800200069000000000071BF6CF9B7A9454E8C533A1E7D037F3D0008B77A5C561934E0891200071209110D110D110D111111111111110D0600021C120C080D0004011C10111110111110111103200001062003010808080206080A200115121501120C1219042001010E0420010102052001011155042001010880B0010002005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A4461746141636365737301000000540E1146696C6C526F774D6574686F644E616D650C53657175656E636546696C6C0320000E032000080615126901120C052002011C180615126501120C0F2007010E0E0E0808081512690113000615126D01120C0615127101120C142009010E0815126D01130002080E1175081D1279092001011512710113000820001512150113000A0702121015126501120C03070108050001111108040701120C0615121501120C071512808501120C0920001512808501120C09200015128085011300052000128089032000020306120C042000120C04200013000320001C0306121902060202060E03061210042800120C0328001C0401000000050000128095040701121404200108080420010E08050002020E0E0B010006474D526F736500000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313000002901002438663639323232382D393939362D346332622D616464332D37363635656138336139396300000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000B7C2C24C000000000200000077000000E0340000E016000052534453AC3390009EAF614ABC3D6E95120DFA0208000000433A5C55736572735C5061756C2057686974655C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C474D526F73655C474D526F73655C6F626A5C52656C656173655C474D526F73652E70646200008035000000000000000000009E3500000020000000000000000000000000000000000000000000009035000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000E80200000000000000000000E80234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00448020000010053007400720069006E006700460069006C00650049006E0066006F00000024020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F00660074000000380007000100460069006C0065004400650073006300720069007000740069006F006E000000000047004D0052006F007300650000000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000038000B00010049006E007400650072006E0061006C004E0061006D006500000047004D0052006F00730065002E0064006C006C00000000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310030000000000040000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000047004D0052006F00730065002E0064006C006C0000000000300007000100500072006F0064007500630074004E0061006D0065000000000047004D0052006F007300650000000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000B03500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000WITH PERMISSION_SET = SAFE;GO-- Sign the QP engineADD SIGNATURETO ASSEMBLY::QueryParallelizerBY CERTIFICATE SQLCLR_Unsafe_Permission_CertWITH PASSWORD = 'SQLCLRUnsafeCertPassword';GO-- Give QP engine UNSAFE permissions (for threading)ALTER ASSEMBLY QueryParallelizerWITH PERMISSION_SET = UNSAFE, VISIBILITY = OFF;GO-- The SQLCLR functionCREATE 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;[/code]Test run[code="sql"]-- ========================================-- Test run-- ========================================IF OBJECT_ID(N'tempdb..##Results', N'U') IS NOT NULL DROP TABLE ##Results;GO-- Results will be bulk-copied to this tableCREATE TABLE ##Results ( AccountNumber INTEGER NOT NULL, CheckId INTEGER NOT NULL, SequenceId INTEGER NOT NULL, );GODECLARE @StartTime DATETIME, @Min INTEGER, @Max INTEGER;-- Set the test start time, and find the range-- of account numbers in the input data setSELECT @StartTime = GETUTCDATE(), @Min = MIN(acct_nbr), @Max = 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', @Min, @Max, 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 codeSELECT 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;[/code]Clean-up code[code="sql"]-- ========================================-- Clean-up code-- ========================================USE [tempdb];GODROP TABLE #tempstore;GODROP TABLE ##Results;GODROP FUNCTION dbo.Sequence;GODROP ASSEMBLY GMRose;GODROP CERTIFICATE SQLCLR_Unsafe_Permission_Cert;GOUSE [master];GODROP LOGIN SQLCLR_Unsafe_Permission_Login;GODROP CERTIFICATE SQLCLR_Unsafe_Permission_Cert;GO-- ========================================-- Delete the certificate backup files-- from C:\temp manually-- ========================================[/code]Sat, 23 Oct 2010 06:24:32 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]Jeff Moden (10/22/2010)[/b][hr][quote][b]Chris Morris-439714 (10/22/2010)[/b][hr]Heh Jeff's gonna hate me for this but I couldn't resist it.400,000 rows in 15 seconds:[/quote]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. :-)[/quote]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.Sat, 23 Oct 2010 00:11:29 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]Chris Morris-439714 (10/22/2010)[/b][hr]Heh Jeff's gonna hate me for this but I couldn't resist it.400,000 rows in 15 seconds:[/quote]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. :-)Fri, 22 Oct 2010 22:29:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]gmrose (10/21/2010)[/b][hr]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[/quote]Heh Jeff's gonna hate me for this but I couldn't resist it.400,000 rows in 15 seconds:[code="sql"]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 ALLSELECT 1, 102 ,'Open' UNION ALLSELECT 1, 103 ,'Open' UNION ALLSELECT 1, 104 ,'Void' UNION ALLSELECT 1, 105 ,'Void' UNION ALLSELECT 1, 106 ,'Open' UNION ALLSELECT 1, 107 ,'Open' UNION ALLSELECT 1, 108 ,'Open' UNION ALLSELECT 1, 109 ,'Void' UNION ALLSELECT 1, 110 ,'Open' UNION ALLSELECT 2, 101 ,'Open' UNION ALLSELECT 2, 102 ,'Open' UNION ALLSELECT 2, 103 ,'Open' UNION ALLSELECT 2, 104 ,'Void' UNION ALLSELECT 3, 105 ,'Void' UNION ALLSELECT 3, 106 ,'Open' UNION ALLSELECT 3, 107 ,'Open' UNION ALLSELECT 4, 108 ,'Open' UNION ALLSELECT 4, 109 ,'Void' UNION ALLSELECT 4, 110 ,'Open';-- 400,000 rows of dataIF 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 #WorkingsFROM #tempORDER BY acct_nbr, ckidCREATE 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 CalculatorOPTION( MAXRECURSION 0)-- 400,000 rows: 15 seconds[/code]Fri, 22 Oct 2010 09:32:45 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]gmrose (10/21/2010)[/b][hr]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[/quote]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...Fri, 22 Oct 2010 08:45:54 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]gmrose (10/21/2010)[/b][hr]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[/quote]You are processing information for a bank. You might want to post your final code so we can double check for you. :-)Thu, 21 Oct 2010 23:29:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]gmrose (10/21/2010)[/b][hr]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[/quote]Yes, this is indeed a real improvement. Out of curiosity, how many records are you processing? And did you try out the script that I posted?Thu, 21 Oct 2010 18:49:47 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspxThank 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.gmroseThu, 21 Oct 2010 17:26:30 GMTgmrosehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]ColdCoffee (10/20/2010)[/b][hr]Wayne, in ur solution, you are basing your order over ckid column (Sequence = ROW_NUMBER() OVER (ORDER BY ckid) )What if ckid is not in a proper order ? what the OP wants is to maintain the open-void in tact right.. so if you row_number it over ckid, then would that open-void combo be maintained ?P.S: i am still learning the QU, so please pardon my ignorance..[/quote]Not a problem - it would only be a problem when you stop learning - and even then, it's YOUR problem! :-D:-P;-):w00t:In the original post, the OP shows the expected results in ckid order, and the grp number changes whenever the stat changes. That is why there is a clustered index on the ckid column. The ROW_NUMBER() ... ORDER BY clause MUST identical to the clustered index columns (including the sort direction).The ROW_NUMBER() ORDER BY is a neat trick that Paul White came up with, and it was enhanced by Tom Thompson. It essentially guarantees that the update occurs in the proper order, or not at all. Tom's enhancement covers the (theoretical) possibility that the sequence number is properly generated in a linear fashion, while the variable assignment (that's being carried row-to-row) isn't.In the solution I posted, I alluded to the possibility of needing an additional field to do this properly. I'm assuming that there would be an account number. This would require:1. changing the clustered index (and the ROW_NUMBER()) to include this column.2. changing the logic of the variable assignment to handle when the account number changes.I've found that the best way of learning is by practicing. You don't get rid of c.u.r.s.o.r.s. by writing more of them; you learn the QU (and other set-based practices) by doing things with them. So, let's make a couple of minor modifications to the specification. Let's include the account number, and change the specs so that whenever the account number changes, the grp restarts at one. All other specs remain the same. The following code has the sample data and expected results.[code="sql"]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 1, 101 ,'Open' UNION ALLSELECT 1, 102 ,'Open' UNION ALLSELECT 1, 103 ,'Open' UNION ALLSELECT 1, 104 ,'Void' UNION ALLSELECT 1, 105 ,'Void' UNION ALLSELECT 1, 106 ,'Open' UNION ALLSELECT 1, 107 ,'Open' UNION ALLSELECT 1, 108 ,'Open' UNION ALLSELECT 1, 109 ,'Void' UNION ALLSELECT 1, 110 ,'Open' UNION ALLSELECT 2, 101 ,'Open' UNION ALLSELECT 2, 102 ,'Open' UNION ALLSELECT 2, 103 ,'Open' UNION ALLSELECT 2, 104 ,'Void' UNION ALLSELECT 3, 105 ,'Void' UNION ALLSELECT 3, 106 ,'Open' UNION ALLSELECT 3, 107 ,'Open' UNION ALLSELECT 4, 108 ,'Open' UNION ALLSELECT 4, 109 ,'Void' UNION ALLSELECT 4, 110 ,'Open';-- expected output:SELECT acct_nbr = 1, ckid = 101 , stat = 'Open', grp = 1 UNION ALLSELECT 1, 102 ,'Open',1 UNION ALLSELECT 1, 103 ,'Open',1 UNION ALLSELECT 1, 104 ,'Void',2 UNION ALLSELECT 1, 105 ,'Void',2 UNION ALLSELECT 1, 106 ,'Open',3 UNION ALLSELECT 1, 107 ,'Open',3 UNION ALLSELECT 1, 108 ,'Open',3 UNION ALLSELECT 1, 109 ,'Void',4 UNION ALLSELECT 1, 110 ,'Open',5 UNION ALLSELECT 2, 101 ,'Open',1 UNION ALLSELECT 2, 102 ,'Open',1 UNION ALLSELECT 2, 103 ,'Open',1 UNION ALLSELECT 2, 104 ,'Void',2 UNION ALLSELECT 3, 105 ,'Void',1 UNION ALLSELECT 3, 106 ,'Open',2 UNION ALLSELECT 3, 107 ,'Open',2 UNION ALLSELECT 4, 108 ,'Open',1 UNION ALLSELECT 4, 109 ,'Void',2 UNION ALLSELECT 4, 110 ,'Open',3;[/code]Thu, 21 Oct 2010 13:24:47 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspxThat explains the bad performance pretty much...You're using a table variable just like I did for my demo version. :blush: My fault. I'm sorry.A table variable is a bad option to use since it's usually populated without proper indexing causing a table scan instead of a index seek. Furthermore, there are no statistics available for the query optimizer to come up with the best query plan. Instead QO will assume that the table varialbe will have only one row.I thought you would replace the table variable with your original table, but I didn't mention it.Now that I see you're using a computed column as a replacement of ckid, I'd recommend you make the following changes (+ change the table name in the CTE).[code="sql"]CREATE TABLE #tmpChecks(OrgCheck char(15) PRIMARY KEY CLUSTERED,OrgId smallint,CheckNum int,Status char(1),AmtCheck money,CheckDate char(10),RegisterId char(12))INSERT INTO #tmpChecksSELECT CAST(OrgId as char(3))+PrintedCheckNum as OrgCheck,OrgId, PrintedCheckNum as CheckNum, Status,AmtCheck = CASE WHEN Status = 'V' THEN 0 ELSE AmtCheck END,CONVERT(char(10), DateCheck,101) as CheckDate, RegisterIdfrom MyTable WITH (NOLOCK)WHEREISNUMERIC(PrintedCheckNum) = 1order by OrgCheck[/code]I'd be curious to know the timing of this code compared to the previous solution... Again, I'm sorry...Thu, 21 Oct 2010 12:56:04 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]LutzM (10/21/2010)[/b]How many rows does your table have? And what indexes?Please post complete DDL for the table in question.Edit: the actual execution plan would be great, too...[/quote]This table currently has almost 414,000 rows. Here is the DDL for the table:USE [MyDatabase]GO/****** Object: Table [dbo].[MyTable] Script Date: 10/21/2010 08:48:43 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[MyTable]( [AcctFundCode] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AcctFundId] DEFAULT (''), [ACHTrans] [bit] NOT NULL CONSTRAINT [DF_MyTable_ACHTrans] DEFAULT (0), [AddrCity] [varchar](22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AddrCity] DEFAULT (''), [AddrForeignStateCode] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AddrCountry] DEFAULT (''), [AddrName] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorName] DEFAULT (''), [AddrStateCode] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorState] DEFAULT (''), [AddrStreet] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorStreet] DEFAULT (''), [AddrZip] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorZip] DEFAULT (''), [AmtCheck] [money] NOT NULL CONSTRAINT [DF_MyTable_AmtTotal] DEFAULT (0), [BankAcctId] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_BankAcctId] DEFAULT (''), [BankCheckId] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_CheckId] DEFAULT (''), [BankCheckSort] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AddrSortValue] DEFAULT (''), [DateCancelled] [datetime] NULL, [DateCheck] [datetime] NULL, [DateTimeCreated] [datetime] NOT NULL CONSTRAINT [DF_MyTable_DateTimeCreated] DEFAULT (getdate()), [DateTimeEdited] [datetime] NULL, [DateTimePrinted] [datetime] NULL, [EmpId] [int] NOT NULL CONSTRAINT [DF_MyTable_PersonId] DEFAULT (0), [OrgId] [smallint] NOT NULL CONSTRAINT [DF_MyTable_OrgId] DEFAULT (0), [PayeeId] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_StudentId] DEFAULT (''), [RegisterId] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_RegisterId] DEFAULT (''), [RegisterIdCancel] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_RegisterIdCancel] DEFAULT (''), [Status] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_ReqStatusCode] DEFAULT (''), [UserIdCreated] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_UserIdCreated] DEFAULT (''), [UserIdEdited] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_UserIdEdited] DEFAULT (''), [UserIdPrinted] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_UserIdPrinted] DEFAULT (''), [VendorAddrId] [tinyint] NOT NULL CONSTRAINT [DF_MyTable_VendorAddrId] DEFAULT (0), [VendorId] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorId_1] DEFAULT (''), [VendorPayrollCheckType] [tinyint] NOT NULL CONSTRAINT [DF_MyTable_VendorPayrollCheckType] DEFAULT (0), [PrintedCheckNum] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [BatchId] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_BatchId] DEFAULT (''), [DateCleared] [datetime] NULL, [AcctResourceCode] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AcctResourceCode] DEFAULT (''), [AcctProjectYearCode] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AcctProjectYearCode] DEFAULT (''), [BatchIdCancel] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_BatchIdCancel] DEFAULT (''), CONSTRAINT [PK_MyTable_CheckId] PRIMARY KEY NONCLUSTERED ( [BankCheckId] ASC, [OrgId] ASC, [BankAcctId] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [MyDatabaseData4]) ON [MyDatabaseData4]GOSET ANSI_PADDING OFFGOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Account Fund Id when only single funded checks are allowed' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AcctFundCode'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'True if this was part of an ACH Bank Transaction' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'ACHTrans'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee City Name' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrCity'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Country Name' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrForeignStateCode'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Name' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrName'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee State Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrStateCode'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Street Address' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrStreet'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Zip Code' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrZip'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Total Check Amount' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AmtCheck'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Bank Account Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BankAcctId'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BankCheckId'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Sort value used when printing checks' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BankCheckSort'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date this check was cancelled' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateCancelled'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Date' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateCheck'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creation Date and Time' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateTimeCreated'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last changed Date and Time' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateTimeEdited'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Print date and time' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateTimePrinted'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee Id value when this check is associated with an Employee' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'EmpId'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Organization''s Id' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'OrgId'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Id value when this check is associated with a one time vendor' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'PayeeId'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Register this appeared on' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'RegisterId'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Cancel Register Id this check appeared on' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'RegisterIdCancel'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Status Code value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'Status'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creation User Id' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'UserIdCreated'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last changed by User Id' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'UserIdEdited'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User Id of the user who printed the check' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'UserIdPrinted'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Vendor Address Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'VendorAddrId'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Vendor Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'VendorId'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Vendor Payroll Check Type (1 - Deduction, 2 - Contribution, 3 - Both Deduction & Contribution, 4 - ACH Check to cover direct deposits)' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'VendorPayrollCheckType'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is a check # that some foreign system printed for our check record' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'PrintedCheckNum'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Batch Id value associated with the printing of checks' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BatchId'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Datetime the check cleared the bank' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateCleared'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Account Resource Code that paid the most for this check' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AcctResourceCode'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Account Project Year component value that paid the most in combination with the AcctFundCode, AcctResourceCode' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AcctProjectYearCode'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'BatchId that this check was cancelled on' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BatchIdCancel'[hr]Here are the indexes:USE [MyDatabase]GOCREATE CLUSTERED INDEX [IX_MyTable_BankAcctId] ON [dbo].[MyTable] ( [BankAcctId] ASC, [OrgId] ASC, [DateCheck] DESC, [BankCheckId] DESC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]CREATE NONCLUSTERED INDEX [IX_MyTable_BatchId] ON [dbo].[MyTable] ( [BatchId] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]CREATE NONCLUSTERED INDEX [IX_MyTable_BatchIdCancel] ON [dbo].[MyTable] ( [BatchIdCancel] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]CREATE NONCLUSTERED INDEX [IX_MyTable_CheckNum] ON [dbo].[MyTable] ( [PrintedCheckNum] ASC, [DateTimeCreated] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]CREATE NONCLUSTERED INDEX [IX_MyTable_Status] ON [dbo].[MyTable] ( [BankAcctId] ASC, [Status] ASC, [OrgId] ASC, [DateCancelled] ASC, [DateCheck] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]CREATE NONCLUSTERED INDEX [IX_MyTable_VendorId] ON [dbo].[MyTable] ( [OrgId] ASC, [VendorId] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable_CheckId] PRIMARY KEY NONCLUSTERED ( [BankCheckId] ASC, [OrgId] ASC, [BankAcctId] ASC)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4][hr]Here is the current execution plan:DECLARE @tmpChecks TABLE( OrgCheck char(15), OrgId smallint, CheckNum int, Status char(1), AmtCheck money, CheckDate char(10), RegisterId char(12))INSERT INTO @tmpChecksSELECT CAST(OrgId as char(3))+PrintedCheckNum as OrgCheck,OrgId, PrintedCheckNum as CheckNum, Status, AmtCheck = CASE WHEN Status = 'V' THEN 0 ELSE AmtCheck END,CONVERT(char(10), DateCheck,101) as CheckDate, RegisterId from MyTable WITH (NOLOCK) WHERE ISNUMERIC(PrintedCheckNum) = 1 order by 1;WITH cte1 AS -- row number per group( SELECT *, ROW_NUMBER() OVER(ORDER BY OrgCheck) AS CkRow, ROW_NUMBER() OVER(PARTITION BY Status ORDER BY OrgCheck ) AS StatRow, ROW_NUMBER() OVER(ORDER BY OrgCheck)- ROW_NUMBER() OVER(PARTITION BY Status ORDER BY OrgCheck ) AS DiffRow FROM @TmpChecks),cte2 AS -- row number per group range, ordered by OrgCheck( SELECT MIN(CkRow) as MinRow,MAX(CkRow) as MaxRow, Status, DiffRow, ROW_NUMBER() OVER(ORDER BY MIN(CkRow)) AS CheckGroup FROM cte1 GROUP BY Status, DiffRow)SELECT cte1.OrgCheck, cte1.OrgId, cte1.CheckNum, cte1.Status, cte1.AmtCheck, cte1.CheckDate, cte1.RegisterId, cte2.CheckGroupFROM cte1INNER JOIN cte2 ON cte1.CkRow>=cte2.MinRow AND cte1.CkRow<=cte2.MaxRowORDER BY cte1.OrgCheck[hr]The general idea is that the accounting application that uses this table currently allows users to run reports that were written using Crystal Reports. I have written one custom report for internal control of the checks which lists series of open checks and series of void checks. The report relies on a stored procedure which uses values provided by the user to select the checks for the report. I have written this stored procedure which is similar to the execution plan shown above but which also includes fairly complex WHERE clauses to handle the user-provided values. When I connect the stored procedure to the Crystal Report, I need it to run through all rows in MyTable once so that when it is run later by the users, none of their user-provided values will have been excluded by the initial connection of the stored procedure to the Crystal Report.I don't mean for this to turn into a large task for anyone on the forum. Thank you again for any suggestions.gmroseThu, 21 Oct 2010 10:22:16 GMTgmrosehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]gmrose (10/20/2010)[/b][hr]After I finish reading the article, I will need to build a new statement using its suggestions. The one that I built based on Lutz's solution has been running for over 30 minutes so far and hasn't finished yet.Thanks anyways.gmrose[/quote]How many rows does your table have? And what indexes?Please post complete DDL for the table in question.Edit: the actual execution plan would be great, too...Thu, 21 Oct 2010 08:55:13 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspxWayne, in ur solution, you are basing your order over ckid column (Sequence = ROW_NUMBER() OVER (ORDER BY ckid) )What if ckid is not in a proper order ? what the OP wants is to maintain the open-void in tact right.. so if you row_number it over ckid, then would that open-void combo be maintained ?P.S: i am still learning the QU, so please pardon my ignorance..Wed, 20 Oct 2010 22:50:29 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspxLooks like it's time for a quirky update solution:[code="sql"]IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;-- This primary key is crucial. If your table doesn't have a clustered index-- on this, then you will have to dump the data to a temp table (along with -- the PK columns of the table), use this PK, then update the real table-- by joining this temp table back to the real table by the PK columns.CREATE TABLE #temp (ckid INT PRIMARY KEY CLUSTERED, stat varchar(10), Grp int);INSERT INTO #temp (ckid, stat)SELECT 101 ,'Open' UNION ALLSELECT 102 ,'Open' UNION ALLSELECT 103 ,'Open' UNION ALLSELECT 104 ,'Void' UNION ALLSELECT 105 ,'Void' UNION ALLSELECT 106 ,'Open' UNION ALLSELECT 107 ,'Open' UNION ALLSELECT 108 ,'Open' UNION ALLSELECT 109 ,'Void' UNION ALLSELECT 110 ,'Open';-- declare and initialize variables needed in the update statement.DECLARE @Sequence int, -- for safety check @stat varchar(10), -- to hold stat column from last row @grp int, -- current grp number @ckid int; -- for anchor columnSET @Sequence = 1;SET @grp = 1;/*This form of the UPDATE statement has some rules for proper usage.See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/for a complete discussion of how this works, and all of the rules for utilizing it.If you don't follow ALL the rules, you WILL mess up your data.*/WITH SafeTable AS(-- generate table with a sequence column in clustered index order -- in order to verify that update is happening in the correct orderSELECT ckid, stat, grp, Sequence = ROW_NUMBER() OVER (ORDER BY ckid) FROM #temp)UPDATE t -- verify in proper sequence order; if not, throw an error so nothing is updated SET @grp = grp = CASE WHEN Sequence = @Sequence THEN CASE WHEN stat <> @Stat THEN @grp + 1-- if you have to separate this by account numbers also, then you will need-- a when clause to handle it here also. -- different stat --> increment grp number ELSE @grp END -- same stat --> same grp number ELSE 1/0 END, -- not in proper sequence order, so throw an error @Sequence = @Sequence + 1, @stat = stat, -- get the current value to compare to in next row @ckid = ckid -- anchor column FROM SafeTable t WITH (TABLOCKX) -- lock table OPTION (MAXDOP 1); -- prevent parallelism!select * from #temp;[/code]Wed, 20 Oct 2010 21:12:23 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspxAfter I finish reading the article, I will need to build a new statement using its suggestions. The one that I built based on Lutz's solution has been running for over 30 minutes so far and hasn't finished yet.Thanks anyways.gmroseWed, 20 Oct 2010 18:24:24 GMTgmrosehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspx[quote][b]Craig Farrell (10/20/2010)[/b][hr]Lutz, that is wild. I like it. Thanks. :w00t:Have you done that on huge recordsets? Does it perform well?[/quote]No, I haven't. And I probably won't either.Such requirements and some rather large tables actually call for the quirky update from my point of view.I don't expect the CTE to perform anywhere near the quirky update. Mainly because of the triple sort operation, the aggregation and the join on a range. But it should outperform any loop.So, why did I posted this solution anyway?#1: You already posted the link to Jeffs article.#2: I did pretty much what you've already described. Just using T-SQL over English ;-):-D #3: gmrose specifically asked for a cte solution and #4: it was some kind of an exercise for me to do it using a non-quirky-update method.Wed, 20 Oct 2010 18:12:04 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspxLutz, that is wild. I like it. Thanks. :w00t:Have you done that on huge recordsets? Does it perform well?Wed, 20 Oct 2010 17:42:06 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspxThank you both for your help, Craig for the interesting article which I have started reading, and Lutz for the solution I needed.gmroseWed, 20 Oct 2010 16:52:32 GMTgmrosehttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspxIt's slightly more complicated than just using a CTE...[code="sql"]DECLARE @tbl TABLE(ckid INT,stat VARCHAR(10) )INSERT INTO @tblSELECT 101 ,'Open' UNION ALLSELECT 102 ,'Open' UNION ALLSELECT 103 ,'Open' UNION ALLSELECT 104 ,'Void' UNION ALLSELECT 105 ,'Void' UNION ALLSELECT 106 ,'Open' UNION ALLSELECT 107 ,'Open' UNION ALLSELECT 108 ,'Open' UNION ALLSELECT 109 ,'Void' UNION ALLSELECT 110 ,'Open'SELECT *FROM @tbl;WITH cte AS -- row number per group( SELECT *, ROW_NUMBER() OVER(ORDER BY ckid) r1, ROW_NUMBER() OVER(ORDER BY ckid)- ROW_NUMBER() OVER(PARTITION BY stat ORDER BY ckid ) AS ROW FROM @tbl),cte2 AS -- row number per group range, ordered by ckid( SELECT MIN(r1) mi,MAX(r1) ma,stat,ROW, ROW_NUMBER() OVER(ORDER BY MIN(r1) ) r2 FROM cte GROUP BY stat,ROW)SELECT ckid ,cte.stat,cte2.r2FROM cteINNER JOIN cte2 ON r1>=mi AND r1<=maORDER BY r1[/code]Wed, 20 Oct 2010 16:42:29 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspxA serial update might work for this, but the implementation would require me to see the underlying DDL for the table and indexing, specifically the clustered index.For the proper rules to doing something like this, do a search for 'Quirky Update' here on SSC and you'll find a very detailed article on how to do something like that.EDIT: found it: [url]http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]Other than that, you'll have to make data-islands of the data and then perhaps setup a row_number() over on the subset. I'll have to chew on that one a while.Wed, 20 Oct 2010 16:22:08 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1008083-149-1.aspxI have a table of checks, each of which has a Status of 'Open' or 'Void'. Below are some sample data.CKID Stat101 Open102 Open103 Open104 Void105 Void106 Open107 Open108 Open109 Void110 OpenI would like to create a CTE that would include a column for each group of checks, as shown below.CKID Stat Grp101 Open 1102 Open 1103 Open 1104 Void 2105 Void 2106 Open 3107 Open 3108 Open 3109 Void 4110 Open 5Thank you for any help.Wed, 20 Oct 2010 15:55:51 GMTgmrose