question

JAhlen avatar image
JAhlen asked

Phil Factor Speed Phreak Challenge #6 - The Stock Exchange Order Book State problem

**[Sep 6th NOTE] The competition is now over. Congratulations to Matt Whitfield who won this prestigious challenge! Congratulations also to Daniel Ross for a well-deserved second place. A special mention goes to Phil Factor for fastest cursor solution although it was not part of the competition. See the final results below. Be welcome to post any new solutions, although they will not be part of the competition. /JAhlen**

The Stock Exchange Order Book State problem

As winner of the previous round, I’ve been given the honor to write the problem for Phil Factor Speed Phreak Challenge #6. This problem is from the real world and the sample data is real. Stock exchanges receive high volumes of buy and sell orders. These are either executed immediately *or* placed in order books. When in the order books they can be executed or cancelled partially or in whole. The stock exchanges are an auctioning system, so that the highest buy orders and lowest sell orders are the most likely to get executed. We capture events from the stock exchange that affect the order books. The events are of four types: * **Add Order (A)** – Adds a new buy or sell order at a certain price and quantity into the order book. The order has an OrderReference number which is referred by later events (Cancel, Execute or Delete) affecting the same order. * **Cancel Order (C)** – Partially cancels a previous Add Order (referenced through the OrderReferenceNumber). The result is that the quantity decreases in the order book. * **Execute Order (E)** – Executes whole or part of a previous Add Order (referenced through the OrderReferenceNumber). This is the result of a matching process against a new order that is not in the order book (and not in the table). The only result in the order book is that the quantity decreases. * **Delete Order (D)** – Completely cancels a previous Add Order (referenced through the OrderReferenceNumber). Below is an example of some events and the resulting order book state. EventIDEventTypeOrderRefNumberBuy/Sell Unit PriceQuantity1A101Sell8.6520002 A102Sell8.605003 A103Buy8.4012004 A104Buy8.5010005 A105Sell8.705006 A106Sell8.7010007C1065008A107Buy8.453009C101100010A108Buy8.4550011A109Sell8.75200012D10913E1048.50600 First six orders are added to the order book. Then half of the 6th order gets cancelled and half the quantity is removed from the order book. Another order is added and then half of the first order is cancelled. Then two orders are added and the last of them is completely removed from the order book. Finally an earlier order gets partially executed. The result of all these events are: ![Order book image](http://www.ahlen.name/PhilFactor/OrderBook.gif) **Any new order that arrives is either executed against an existing order or placed in the order book.** If you want your buy order to execute immediately you would have to pay at least 8.60. If you want to buy more than 500 shares you would have to pay at least 8.65 since there are only 500 shares available at the 8.60 level. If you want a sell order to execute immediately you would get at most 8.50 per share. Any sell order priced above 8.50 would go into the order book instead of being executed.

The challenge

I’ve provided a file that contains real events from a stock exchange for a few order books during a trading day that you can easily import into an SQL Server table. The challenge is to produce another table that contains information about the two highest buy levels and two lowest sell levels. Every time there is any change to and of these levels a new row should be produced. The row contains the EventID of the latest event that has been processed. Our earlier example events would give us the following result. Note that event 5-7 does not affect any of the fields and therefore no row is created. EventIDBest Buy PriceBest Buy QuantityBest Sell PriceBest Sell QuantitySecond Best Buy PriceSecond Best Buy QuantitySecond Best Sell PriceSecond Best Sell Quantity18.65200028.605008.6520003 8.4012008.605008.6520004 8.5010008.605008.4012008.6520008 8.5010008.605008.453008.6520009 8.5010008.605008.453008.65 1000108.5010008.605008.45 8008.651000138.504008.605008.45 8008.651000

Important notes

* The events must be processed in ascending EventID order. Otherwise you might for example get a cancellation before the order is added. * The price of an order that is in the order book never changes. Only quantity can change. * Execution price of an order should always be ignored. You should use the price from the referenced Add Order. The result of an Execute Order is the same as a Cancel Order. * An order cancel event results in a downward modification of available quantity. * If the available quantity of an order reaches zero, it is dead and can be dropped. * An order delete event results in that the whole order is dropped. * BestBuyPrice, BestSellPrice, etc are calculated after the event has been processed. * No rows should be created in the OrderBookState table when there is no change to any of the fields. * We don't allow nulls in the OrderBookState table, so put zeroes if there is no value.

Getting started

Download the necessary stuff from this link: [http://www.ahlen.name/PhilFactor/Challenge6.zip](http://www.ahlen.name/PhilFactor/Challenge6.zip) 1. Run Setup.sql to create the tables **OrderBookEvents**, **OrderBookState** and **CorrectOrderBookState** (that you can use to verify your solution). 2. Open **BulkInsertEventsAndResults.sql** and modify the paths. Then run it to read the events into OrderBookEvents and results into CorrectOrderBookState. 3. For a starter - check out SlowSolution.sql if you want to see an example of a solution (cursor-based)

Rules

All entries have to be submitted before **9pm Sunday 5th of September 2010 (UTC)**. Entries will be evaluated as they are submitted and time allows. Final winner will be announced shortly after that. Any SQL CLR submissions must include full source code as well as compiled version. The judge reserves the right to test again with a larger data set in the event of a tie. First Prize for SQL Speed Phreak Challenge #6: $100 Amazon voucher and a license for SQL Data Generator! There will also be three categorized runner-up prizes: * Best Cursor-based solution * Best CLR solution * Best SSIS or unusual/experimental attempt The winners of these prizes will get a licensed copy of either SQL Prompt (Pro version) or SQL Refactor, whichever they prefer. Good luck! //Johan

Final results

+---------------------------------+-----------------------+----------------------+ | Name | Category | Execution Time (s) | +---------------------------------+-----------------------+----------------------+ | Matt v2b | SQL CLR (Unsafe) | 7.8 | | Daniel v2b | SSIS | 9.4 | | Matt v2a | SQL CLR (Unsafe) | 10.4 | | Matt v2 | SQL CLR (Unsafe) | 12.1 | | Matt v1 | SQL CLR (External) | 23.9 | | Daniel v1 | SQL CLR (External) | 28.5 | | Phil Factor cursor | T-SQL CURSOR | 256.7 | | Original cursor solution | T-SQL CURSOR | 2977.6 | +---------------------------------+-----------------------+----------------------+ Note: Phil's solution was not part of the competition but is included for reference. Overall winner: **Matt Whitfield** Best Cursor-based solution: **none** Best SQL CLR solution: **Matt Whitfield** Best SSIS / experimental solution: **Daniel Ross**
t-sqlchallengepuzzlespeed-phreakcompetition
10 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Daniel Ross avatar image Daniel Ross commented ·
@JAhlen what do we do when the eventType is E and unitPrice =0.00? as in eventID 222666. It looks as if the cursor solution, when the unitprice =0.00, puts the execution order through at the best buy price or best sell price accordingly, is that right?
0 Likes 0 ·
JAhlen avatar image JAhlen commented ·
@Daniel Ross When EventType is E you should always use the price from the referenced Add Order event (EventType A). As you can see in the example cursor solution you can handle the E event in exactly the same way as a C event.
0 Likes 0 ·
Daniel Ross avatar image Daniel Ross commented ·
@JAhlen, thanks mate for clarifying that. The part about the Execution price in the important notes gave me the impression that we had to use the unit price of the E event
0 Likes 0 ·
JAhlen avatar image JAhlen commented ·
@Daniel Ross Thanks for the comment. I have clarified the explanation under Important notes.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Just a question - I've replicated the functionality as is, but why is the best buy price the highest, and the best sell price the lowest? I would have thought it would be best to buy at the lowest price and sell at the highest price? I must be missing something!
0 Likes 0 ·
Show more comments
Matt Whitfield avatar image
Matt Whitfield answered
v2b (a couple more slight tweaks) Setup: CREATE ASSEMBLY [Challenge6] AUTHORIZATION [dbo] FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103005DFE834C0000000000000000E00002210B0108000026000000060000000000005E44000000200000006000000000400000200000000200000400000000000000040000000000000000A0000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000000C4400004F000000006000002803000000000000000000000000000000000000008000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000064240000002000000026000000020000000000000000000000000000200000602E7273726300000028030000006000000004000000280000000000000000000000000000400000402E72656C6F6300000C0000000080000000020000002C0000000000000000000000000000400000420000000000000000000000000000000040440000000000004800000002000500C02B00004C1800000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000033002004E0000000000000002037D0100000402047D0200000402057D03000004020E047D04000004020E057D05000004020E067D06000004020E077D07000004020E087D08000004020E097D09000004020E0A7D0A0000042A0000133003001601000001000011036F0F00000A0A0616027B010000048C1A0000016F1000000A0617027B020000048C1A0000016F1000000A027B03000004281100000A0B071F64731200000A281300000A0B0618078C1B0000016F1000000A0619027B040000048C1A0000016F1000000A027B05000004281100000A0C081F64731200000A281300000A0C061A088C1B0000016F1000000A061B027B060000048C1A0000016F1000000A027B07000004281100000A0D091F64731200000A281300000A0D061C098C1B0000016F1000000A061D027B080000048C1A0000016F1000000A027B09000004281100000A130411041F64731200000A281300000A1304061E11048C1B0000016F1000000A061F09027B0A0000048C1A0000016F1000000A062A000013300300AF00000002000011160A06027B0C0000042F23027B0B000004069A0B03077B110000045504077B120000045405166A550E0416542B4003166A5504165405166A550E0416542A027B0B000004069A0B077B110000040C08034C330D04254A077B1200000458542B0E0508550E04077B12000004542B33061758250A027B0C00000432C32B24027B0B000004069A0B077B110000040D09054C330E0E04254A077B1200000458542B012A061758250A027B0C00000432CF2A00133003004400000003000011160A027B0C00000417590B2B30060706591763580C027B0D000004027B0B000004089A036F040000060D092D02082A09162F060817580A2B040817590B060731CC06662A6203027B0B0000048E692E0C027C0B00000403280100002B2A7A02281500000A0220001000008D050000027D0B00000402037D0D0000042A133002001000000004000011020328080000060A06163202062A152A133006006D00000004000011020328080000060A06162F0306660A027B0C000004027B0B0000048E69331002027B0B0000048E69185A280900000606027B0C0000042F1D027B0B00000406027B0B000004061758027B0C0000040659281600000A027B0B0000040603A202257B0C00000417587D0C0000042A0000001330060041000000040000110203280B0000060A06152E3402257B0C00000417597D0C00000406027B0C0000042F1D027B0B000004061758027B0B00000406027B0C0000040659281600000A2AB202281500000A02037D0E00000402047D0F00000402057D10000004020E047D11000004020E057D120000042AA602731700000A7D1900000402731500000A7D1A0000040216731800000A7D1C00000402281500000A2A1B30030034020000050000110274070000020A067B18000004731900000A0B076F1A00000A067B1A0000040C067B190000040D07731B00000A13047201000070731C00000A130511056F1D00000A721F000070D01A000001281E00000A6F1F00000A2611056F1D00000A722F000070D01A000001281E00000A6F1F00000A2611056F1D00000A7247000070D01B000001281E00000A6F1F00000A2611056F1D00000A7261000070D01A000001281E00000A6F1F00000A2611056F1D00000A7281000070D01B000001281E00000A6F1F00000A2611056F1D00000A729D000070D01A000001281E00000A6F1F00000A2611056F1D00000A72BF000070D01B000001281E00000A6F1F00000A2611056F1D00000A72E5000070D01A000001281E00000A6F1F00000A2611056F1D00000A7211010070D01B000001281E00000A6F1F00000A2611056F1D00000A7239010070D01A000001281E00000A6F1F00000A26110472670100706F2000000A110420D00700006F2100000A11056F2200000A1306067B1C00000413071613081107156F2300000A260825130B282400000A11076F2500000A26096F2600000A1309096F2700000A067B1B0000041308DE08110B282800000ADC1109130C16130D2B26110C110D8F020000027102000002130A1106120A110528020000066F2900000A110D1758130D110D110C8E6932D211082D0E11066F2A00000A20D00700003210110411056F2B00000A11056F2C00000A11083963FFFFFFDE0C11042C0711046F2D00000ADCDE0A072C06076F2D00000ADC2A414C0000020000009101000020000000B10100000800000000000000020000002F000000EC0100001B0200000C00000000000000020000001300000016020000290200000A00000000000000133002002F00000006000011027B110000040B1201037B11000004282E00000A0A062C02062A027B0F0000040C1202037B0F000004282F00000A2A00133002003100000006000011027B110000040B1201037B11000004282E00000A0A062C0406155A2A027B0F0000040C1202037B0F000004282F00000A2A0000001B300B00A703000007000011733100000A0A140B140C7295010070731900000A0D096F1A00000A72C701007009733200000A130611066F3300000A130711076F3400000A261107166F3500000A13041107176F3500000A1305DE0C11072C0711076F2D00000ADCDE0C11062C0711066F2D00000ADC1B8D30000001132B112B167254020070A2112B171104A2112B187264020070A2112B191105A2112B1A7288020070A2112B283600000A1308730F0000061309110911087D1800000414FE0610000006733700000A733800000A130A110A196F3900000A110A11096F3A00000A11097B19000004130B166A131A16131B166A131C16131D166A131E16131F166A132016132115132216132311097B1A000004132472BE02007009733200000A132511256F3300000A1326381E0200001126176F3B00000A130C1126186F3B00000A130D1126196F3B00000A130E110E11222E2414FE06120000067303000006730A0000060B14FE06110000067303000006730A0000060C110E1322110C132C112C1F4159450500000005000000E800000063000000BA0000006300000038E300000011261A6F3B00000A1F42FE0116FE01130F11261B6F3C00000A131011261C6F3B00000A1311110E110D110F11101111730E000006132706110D11276F3D00000A110F2C0D0811276F0C00000638920000000711276F0C000006388500000011261C6F3B00000A131106110D6F3E00000A132811287B120000041111332611287B100000042C0A0811286F0D0000062B080711286F0D00000606110D6F3F00000A262B401128257B120000041111597D120000042B2E06110D6F3E00000A132911297B100000042C0A0811296F0D0000062B080711296F0D00000606110D6F3F00000A260712121213121612176F070000060812141215121812196F07000006111A1112332A111B11133324111C1114331E111D11153318111E11163312111F1117330C112011183306112111192E7C122A1126166F3B00000A110E111211131114111511161117111811192801000006112425132D282400000A110B112A6F4000000A1123175825132320E8030000321011097B1C0000046F4100000A26161323DE08112D282800000ADC1112131A1113131B1114131C1115131D1116131E1117131F111813201119132111266F3400000A3AD6FDFFFFDE0C11262C0711266F2D00000ADCDE0C11252C0711256F2D00000ADC112425132E282400000A1109177D1B00000411097B1C0000046F4100000A26DE08112E282800000ADC110A6F4200000ADE0A092C06096F2D00000ADC2A0041AC000002000000310000001E0000004F0000000C000000000000000200000028000000350000005D0000000C0000000000000002000000F1020000290000001A0300000800000000000000020000001F01000031020000500300000C000000000000000200000016010000480200005E0300000C000000000000000200000074030000170000008B03000008000000000000000200000015000000870300009C0300000A000000000000001E02281500000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000014070000237E0000800700006009000023537472696E677300000000E0100000B00400002355530090150000100000002347554944000000A0150000AC02000023426C6F620000000000000002000001571D02080908000000FA0133001600000100000033000000080000001E000000140000002800000042000000060000000B000000070000000200000001000000030000000100000000000A00010000000000060095008E0006009F008E000600B1008E000600B8008E000A00730167010A007B016701060097018E000600A4018E000E00A5028A020600DC02CB0206006404450406000105EF0406001805EF0406003505EF0406005405EF0406006D05EF0406008605EF040600A105EF040600BC05EF040600D50545040600E905EF0406002206020606004206020606006B064504060081064504060093068E000600A2068E000600C2068E000A00EA06D4060A000B07F8060A001D07D4060A002907670106004A078E0006004F078E000A00730767010A00A50767010600C007CB020600D307CB020600E107CB020A000A08670106003D088E00060051088E000A007C086108060092088A020A009F08D4060A00AA08D4060A00C608F8060600E2088E000600F008CB0206000909CB0206001009CB0200000000010000000000010001000801100019000000050001000100000100002300000009000B00030000011000390000000D000B000700000110004F0000000D000E000E00000100005B000000110013000F0000011000660000000D0018000F0001001000740000000D001D0010000600BD000A000600C5000A000600D1000D000600DE000A000600EE000D000600FC000A0006000D010D00060020010A00060036010D0006004A010A000100EB0157000100F2010A000100F8015C002600C5000A00260025020A0026003A027100260048020D00060052020A0006065B020A0056801A027D00568063027D0056806B027D00568072027D000600790295000600AD0298000600B702A0000600C20271000600ED02A3005180FB020A00518006030A005020000000008618610110000100AC2000000000830085011E000B000000000003008618610125000C00000000000300C60190012B000E00000000000300C601B20133001000000000000300C601BE013F001400D021000000008600C801450015008C22000000008100DD0151001900DC22000000008100040260001A00F522000000008618610165001B001423000000008100110251001C0030230000000086001A026B001D00AC230000000086001E026B001E00F923000000008618610174001F0026240000000086186101A700240050240000000091001703B5002400DC260000000091002303BA00250018270000000091003803BA00270058270000000096004E03C2002900B82B0000000086186101A7002900000001006503000002006D0300000300790300000400860300000500960300000600A40300000700B50300000800C80300000900DE0300000A00F203000001000904000001001904000002002004000001002704000002002904000001002704000002002904000003002B04000004001904000001003404020001003B04020002007104020003007E04020004008E0400000100A10400000100A10400000100A70400000100B20400000100B20400000100B204000001006D0300000200B70400000300CC0400000400DA0400000500E40400000100ED0400000100270400000200290400000100270400000200290459006101A70061006101C60069006101C60071006101C60079006101C60081006101C60089006101C60091006101C60099006101C600A1006101CB00A9006101C600B10061016000B9006101A700C1006101D00031008C06D60029009906DB00D900AA06E100D90061016000D900B606E700E100C8060C0119006101A700E100CF061F010C006101A70051006101CB00E9006101C600F1001807A700F9006101310131006101C60031003E073701090161073D0101011A024601F9007E07C600F900970760003100B70750012901CB0756013101DB07B5003901F1075B010C00F7075F010C00FF07A70031010508B50021011A026501410125086B01F9002F086F013100FF07A70049014908A700510157089701D10057089C0159016101A70014006101A70069016101B5016901B808BC017901D3085B017901D808C2018101E908C70189016101250091016101CD0191011F09D40191012C09DB01790132099C0179013B09E00114001A02E50114004409ED0114001E02F4010C004D09FA01390155095B0191015909A700080050008100080054008600080058008B0008005C00900008007400AB0008007800B0002E00330053022E006B008A022E001B0063022E00230063022E002B0063022E00130053022E003B0069022E00430063022E00530063022E006300810260028301A701F000FD0005011B017501A10100022A01AC01048000000100000043000000000000000000600600000200000000000000000000000100850000000000020000000000000000000000010067010000000002000000000000000000000001008E0000000000290016010000003C4D6F64756C653E004368616C6C656E6765362E646C6C00726F7744657461696C004163746976654F72646572436F6D70617269736F6E00536F727465644163746976654F726465724C697374004163746976654F72646572004576656E7454797065730054687265616444657461696C730053746F72656450726F63656475726573006D73636F726C69620053797374656D0056616C756554797065004D756C74696361737444656C6567617465004F626A65637400456E756D004576656E744944004F72646572426F6F6B49440042657374427579507269636500426573744275795175616E74697479004265737453656C6C5072696365004265737453656C6C5175616E74697479005365636F6E64426573744275795072696365005365636F6E64426573744275795175616E74697479005365636F6E644265737453656C6C5072696365005365636F6E644265737453656C6C5175616E74697479002E63746F720053797374656D2E446174610044617461526F7700446174615461626C6500416464546F5461626C6500496E766F6B6500494173796E63526573756C74004173796E6343616C6C6261636B00426567696E496E766F6B6500456E64496E766F6B650047657442657374416E645365636F6E6442657374005F62696E617279536561726368005F6974656D73005F73697A65005F636F6D70617269736F6E005F736574436170616369747900696E6465784F665F004164640052656D6F7665004F726465725265666572656E63654E756D6265720053656C6C496E64696361746F7200556E69745072696365005175616E746974790076616C75655F5F00457865637574650043616E63656C0044656C65746500436F6E6E656374696F6E537472696E670053797374656D2E436F6C6C656374696F6E732E47656E657269630051756575656031006C6F61645175657565006C6F636B4F626A6563740066696E69736865640053797374656D2E546872656164696E67004D616E75616C52657365744576656E7400726F7773417661696C61626C65005F626174636853697A65005F7369676E616C426174636853697A65005F74687265616450726F63005F617363656E64696E67436F6D70617269736F6E005F64657363656E64696E67436F6D70617269736F6E0047656E65726174654F72646572426F6F6B5374617465006576656E744944006F72646572426F6F6B49440062657374427579507269636500626573744275795175616E74697479006265737453656C6C5072696365006265737453656C6C5175616E74697479007365636F6E64426573744275795072696365007365636F6E64426573744275795175616E74697479007365636F6E644265737453656C6C5072696365007365636F6E644265737453656C6C5175616E74697479006F7574707574446174615461626C65006F626A656374006D6574686F64007800790063616C6C6261636B00726573756C74004265737450726963650053797374656D2E52756E74696D652E496E7465726F705365727669636573004F757441747472696275746500426573745175616E74697479005365636F6E64426573745072696365005365636F6E64426573745175616E746974790076616C756500636F6D70617269736F6E006974656D006F726465725265666572656E63654E756D6265720073656C6C496E64696361746F7200756E69745072696365007175616E74697479006F0053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004368616C6C656E676536005374727563744C61796F7574417474726962757465004C61796F75744B696E64004E6577526F7700496E743332007365745F4974656D00446563696D616C006F705F496D706C69636974006F705F4469766973696F6E00417272617900526573697A6500436F70790053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C42756C6B436F70790044617461436F6C756D6E436F6C6C656374696F6E006765745F436F6C756D6E7300547970650052756E74696D655479706548616E646C65004765745479706546726F6D48616E646C650044617461436F6C756D6E007365745F44657374696E6174696F6E5461626C654E616D65007365745F426174636853697A650044617461526F77436F6C6C656374696F6E006765745F526F7773005761697448616E646C6500576169744F6E65004D6F6E69746F7200456E746572004576656E745761697448616E646C6500526573657400546F417272617900436C656172004578697400496E7465726E616C44617461436F6C6C656374696F6E42617365006765745F436F756E74005772697465546F5365727665720049446973706F7361626C6500446973706F736500496E74363400436F6D70617265546F004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650044696374696F6E61727960320053716C436F6D6D616E640053716C44617461526561646572004578656375746552656164657200446244617461526561646572005265616400476574537472696E6700537472696E6700436F6E63617400506172616D65746572697A6564546872656164537461727400546872656164005468726561645072696F72697479007365745F5072696F7269747900537461727400476574496E74333200476574496E743634006765745F4974656D00456E717565756500536574004A6F696E000000001D4F00720064006500720042006F006F006B0053007400610074006500000F4500760065006E0074004900440000174F00720064006500720042006F006F006B00490044000019420065007300740042007500790050007200690063006500001F42006500730074004200750079005100750061006E007400690074007900001B4200650073007400530065006C006C005000720069006300650000214200650073007400530065006C006C005100750061006E00740069007400790000255300650063006F006E006400420065007300740042007500790050007200690063006500002B5300650063006F006E00640042006500730074004200750079005100750061006E00740069007400790000275300650063006F006E0064004200650073007400530065006C006C0050007200690063006500002D5300650063006F006E0064004200650073007400530065006C006C005100750061006E007400690074007900002D5B00640062006F005D002E005B004F00720064006500720042006F006F006B00530074006100740065005D00003163006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065003B0000808B5400520055004E00430041005400450020005400410042004C00450020005B00640062006F005D002E005B004F00720064006500720042006F006F006B00530074006100740065005D003B002000530045004C004500430054002000400040005300450052005600450052004E0041004D0045002C002000440042005F004E0041004D00450028002900000F5300650072007600650072003D0000233B0049006E0069007400690061006C00200043006100740061006C006F0067003D0000353B0049006E00740065006700720061007400650064002000530065006300750072006900740079003D0074007200750065003B000081ED530045004C0045004300540020005B004500760065006E007400490044005D002C0020004100530043004900490028005B004500760065006E00740054007900700065005D00290020004100530020005B004500760065006E00740054007900700065005D002C0020005B004F0072006400650072005200650066006500720065006E00630065004E0075006D006200650072005D002C0020005B004F00720064006500720042006F006F006B00490044005D002C0020004100530043004900490028005B00420075007900530065006C006C0049006E00640069006300610074006F0072005D00290020004100530020005B00420075007900530065006C006C0049006E00640069006300610074006F0072005D002C00200043004F004E00560045005200540028005B0062006900670069006E0074005D002C0020005B0055006E0069007400500072006900630065005D0020002A00200031003000300029002C0020005B005100750061006E0074006900740079005D002000460052004F004D0020005B004F00720064006500720042006F006F006B004500760065006E00740073005D0020004F00520044004500520020004200590020005B004F00720064006500720042006F006F006B00490044005D002C0020005B004500760065006E007400490044005D0000000000005EC95AC6CF5345920BF5C90A33CA3B0008B77A5C561934E08902060802060A0D200A0108080A080A080A080A0806200112151219052002011C1807200208121412140B2004121D1214121412211C05200108121D0B200401100A1008100A100805200108121404061D12140306120C042001010805200101120C052001011214020602082005010808020A0803061118044100000004450000000443000000044400000002060E070615122501110802061C030612290320000104D007000004E8030000040001011C070002081214121403000001042001010E0420010102052001011165042000121505200201081C050001116D0A080002116D116D116D0C07051215116D116D116D116D0707040812140A0A060704080808080910010201101D1E0008040A011214030701080A000501127108127108080615122501110805200101127505200012808108000112808511808909200212808D0E1280850520001280910420010208032000020520001D13000520010112150320000805200101121921070E121C12751C151225011108127D12191280911229021D110811081C1D110808042001080A0420010808050703080A08040100000008151280B102081214062002010E12750520001280B90420010E080500010E1D0E062001011280C5062001011180CD042001011C0420010A0807200201130013010620011301130005200102130005200101130052072F151280B1020812141210121012750E0E1280B51280B90E121C1280C915122501110811180808020A080A080A080A080A080A080A080A080A0808081C1280B51280B912141214121411081D0E11181C1C0F01000A4368616C6C656E676536000005010000000017010012436F7079726967687420C2A920203230313000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000003444000000000000000000004E44000000200000000000000000000000000000000000000000000040440000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058600000CC0200000000000000000000CC0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000430000000100000043003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0042C020000010053007400720069006E006700460069006C00650049006E0066006F00000008020000010030003000300030003000340062003000000040000B000100460069006C0065004400650073006300720069007000740069006F006E00000000004300680061006C006C0065006E0067006500360000000000340009000100460069006C006500560065007200730069006F006E000000000031002E0030002E00360037002E0030000000000040000F00010049006E007400650072006E0061006C004E0061006D00650000004300680061006C006C0065006E006700650036002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003000000048000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004300680061006C006C0065006E006700650036002E0064006C006C000000000038000B000100500072006F0064007500630074004E0061006D006500000000004300680061006C006C0065006E0067006500360000000000380009000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E00360037002E003000000000003C000900010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E00360037002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004000000C000000603400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WITH PERMISSION_SET = UNSAFE GO ALTER ASSEMBLY [Challenge6] WITH VISIBILITY = ON GO CREATE PROCEDURE [dbo].[GenerateOrderBookState] AS EXTERNAL NAME [Challenge6].[StoredProcedures].[GenerateOrderBookState] GO Execute & Teardown as before. Code: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Collections.Generic; using System.Threading; using System.IO; struct rowDetail { public int EventID; public int OrderBookID; public long BestBuyPrice; public int BestBuyQuantity; public long BestSellPrice; public int BestSellQuantity; public long SecondBestBuyPrice; public int SecondBestBuyQuantity; public long SecondBestSellPrice; public int SecondBestSellQuantity; /// /// Creates a new class instance. /// public rowDetail(int eventID, int orderBookID, long bestBuyPrice, int bestBuyQuantity, long bestSellPrice, int bestSellQuantity, long secondBestBuyPrice, int secondBestBuyQuantity, long secondBestSellPrice, int secondBestSellQuantity) { this.EventID = eventID; this.OrderBookID = orderBookID; this.BestBuyPrice = bestBuyPrice; this.BestBuyQuantity = bestBuyQuantity; this.BestSellPrice = bestSellPrice; this.BestSellQuantity = bestSellQuantity; this.SecondBestBuyPrice = secondBestBuyPrice; this.SecondBestBuyQuantity = secondBestBuyQuantity; this.SecondBestSellPrice = secondBestSellPrice; this.SecondBestSellQuantity = secondBestSellQuantity; } internal DataRow AddToTable(DataTable outputDataTable) { DataRow outputRow = outputDataTable.NewRow(); outputRow[0] = EventID; outputRow[1] = OrderBookID; Decimal bestBuyPrice = BestBuyPrice; bestBuyPrice /= 100; outputRow[2] = bestBuyPrice; outputRow[3] = BestBuyQuantity; Decimal bestSellPrice = BestSellPrice; bestSellPrice /= 100; outputRow[4] = bestSellPrice; outputRow[5] = BestSellQuantity; Decimal secondBestBuyPrice = SecondBestBuyPrice; secondBestBuyPrice /= 100; outputRow[6] = secondBestBuyPrice; outputRow[7] = SecondBestBuyQuantity; Decimal secondBestSellPrice = SecondBestSellPrice; secondBestSellPrice /= 100; outputRow[8] = secondBestSellPrice; outputRow[9] = SecondBestSellQuantity; return outputRow; } } delegate int ActiveOrderComparison(ActiveOrder x, ActiveOrder y); sealed class SortedActiveOrderList { public void GetBestAndSecondBest(out long BestPrice, out int BestQuantity, out long SecondBestPrice, out int SecondBestQuantity) { int i = 0; ActiveOrder ao; if (i < _size) { ao = _items[i]; BestPrice = ao.UnitPrice; BestQuantity = ao.Quantity; SecondBestPrice = 0; SecondBestQuantity = 0; } else { BestPrice = 0; BestQuantity = 0; SecondBestPrice = 0; SecondBestQuantity = 0; return; } while (++i < _size) { ao = _items[i]; long price = ao.UnitPrice; if (price == BestPrice) { BestQuantity += ao.Quantity; } else { SecondBestPrice = price; SecondBestQuantity = ao.Quantity; break; } } while (++i < _size) { ao = _items[i]; long price = ao.UnitPrice; if (price == SecondBestPrice) { SecondBestQuantity += ao.Quantity; } else { return; } } } int _binarySearch(ActiveOrder value) { int lo = 0; int hi = _size - 1; while (lo > 1); int order = _comparison(_items[i], value); if (order == 0) return i; if (order < 0) { lo = i + 1; } else { hi = i - 1; } } return ~lo; } private ActiveOrder[] _items; private int _size; ActiveOrderComparison _comparison; void _setCapacity(int value) { if (value != _items.Length) { Array.Resize(ref _items, value); } } /// /// Constructs a object specifying an IComparer for type T /// /// The IComparer for type T that compares objects public SortedActiveOrderList(ActiveOrderComparison comparison) { _items = new ActiveOrder[4096]; _comparison = comparison; } /// /// Gets the index of the item using a BinarySearch /// /// The item to search for /// The index of the item, or it's complement if not found private int indexOf_(ActiveOrder item) { int potentialIndex = _binarySearch(item); return (potentialIndex < 0) ? -1 : potentialIndex; } /// /// Adds an item to the collection. /// /// The object to add to the collection. public void Add(ActiveOrder item) { int index = _binarySearch(item); if (index < 0) { // If the item isn't present BinarySearch returns the insertion point negated. index = ~index; } if (_size == _items.Length) { _setCapacity(_items.Length * 2); } if (index < _size) { Array.Copy(_items, index, _items, index + 1, _size - index); } _items[index] = item; _size++; } /// /// Removes the first occurrence of item from the collection. /// /// The object to remove /// true if item was successfully removed from the collection, otherwise false. This method also returns false if item is not found. public void Remove(ActiveOrder item) { int index = indexOf_(item); if (index != -1) { _size--; if (index < _size) { Array.Copy(_items, index + 1, _items, index, _size - index); } } } } sealed class ActiveOrder { public readonly int OrderBookID; public readonly int OrderReferenceNumber; public readonly bool SellIndicator; public readonly long UnitPrice; public int Quantity; /// /// Creates a new class instance. /// public ActiveOrder(int orderBookID, int orderReferenceNumber, bool sellIndicator, long unitPrice, int quantity) { OrderBookID = orderBookID; OrderReferenceNumber = orderReferenceNumber; SellIndicator = sellIndicator; UnitPrice = unitPrice; Quantity = quantity; } } enum EventTypes { Add = 65, Execute = 69, Cancel = 67, Delete = 68 } sealed class ThreadDetails { public string ConnectionString; public Queue loadQueue = new Queue(); public object lockObject = new object(); public bool finished = false; public ManualResetEvent rowsAvailable = new ManualResetEvent(false); } public partial class StoredProcedures { private static void _threadProc(object o) { ThreadDetails td = (ThreadDetails)o; using (SqlConnection externalConn = new SqlConnection(td.ConnectionString)) { externalConn.Open(); object lockObject = td.lockObject; Queue rowQueue = td.loadQueue; using (SqlBulkCopy bulkCopy = new SqlBulkCopy(externalConn)) { DataTable outputDataTable = new DataTable("OrderBookState"); outputDataTable.Columns.Add("EventID", typeof(Int32)); outputDataTable.Columns.Add("OrderBookID", typeof(Int32)); outputDataTable.Columns.Add("BestBuyPrice", typeof(Decimal)); outputDataTable.Columns.Add("BestBuyQuantity", typeof(Int32)); outputDataTable.Columns.Add("BestSellPrice", typeof(Decimal)); outputDataTable.Columns.Add("BestSellQuantity", typeof(Int32)); outputDataTable.Columns.Add("SecondBestBuyPrice", typeof(Decimal)); outputDataTable.Columns.Add("SecondBestBuyQuantity", typeof(Int32)); outputDataTable.Columns.Add("SecondBestSellPrice", typeof(Decimal)); outputDataTable.Columns.Add("SecondBestSellQuantity", typeof(Int32)); bulkCopy.DestinationTableName = "[dbo].[OrderBookState]"; bulkCopy.BatchSize = _batchSize; DataRowCollection rows = outputDataTable.Rows; ManualResetEvent sync = td.rowsAvailable; while (true) { bool finish = false; sync.WaitOne(System.Threading.Timeout.Infinite); rowDetail[] rowDetailArray; lock (lockObject) { sync.Reset(); rowDetailArray = rowQueue.ToArray(); rowQueue.Clear(); finish = td.finished; } foreach (rowDetail row in rowDetailArray) { rows.Add(row.AddToTable(outputDataTable)); } if (finish || rows.Count >= _batchSize) { bulkCopy.WriteToServer(outputDataTable); outputDataTable.Clear(); } if (finish) { break; } } } } } const int _batchSize = 2000; const int _signalBatchSize = 1000; private static int _ascendingComparison(ActiveOrder x, ActiveOrder y) { int i = x.UnitPrice.CompareTo(y.UnitPrice); if (i != 0) { return i; } else { return x.OrderReferenceNumber.CompareTo(y.OrderReferenceNumber); } } private static int _descendingComparison(ActiveOrder x, ActiveOrder y) { int i = x.UnitPrice.CompareTo(y.UnitPrice); if (i != 0) { return i * -1; } else { return x.OrderReferenceNumber.CompareTo(y.OrderReferenceNumber); } } [Microsoft.SqlServer.Server.SqlProcedure] public static void GenerateOrderBookState() { Dictionary ordersByReferenceNumber = new Dictionary(); SortedActiveOrderList buyOrderList = null; SortedActiveOrderList sellOrderList = null; using (SqlConnection sqlConn = new SqlConnection("context connection=true;")) { sqlConn.Open(); string serverName, databaseName; using (SqlCommand truncateCommand = new SqlCommand("TRUNCATE TABLE [dbo].[OrderBookState]; SELECT @@SERVERNAME, DB_NAME()", sqlConn)) { using (SqlDataReader reader = truncateCommand.ExecuteReader()) { reader.Read(); serverName = reader.GetString(0); databaseName = reader.GetString(1); } } string externalConnectionString = "Server=" + serverName + ";Initial Catalog=" + databaseName + ";Integrated Security=true;"; ThreadDetails td = new ThreadDetails(); td.ConnectionString = externalConnectionString; Thread loadThread = new Thread(new ParameterizedThreadStart(_threadProc)); loadThread.Priority = ThreadPriority.AboveNormal; loadThread.Start(td); Queue rowQueue = td.loadQueue; EventTypes EventType; int OrderReferenceNumber; int OrderBookID; bool SellIndicator; long UnitPrice; int Quantity; long BestBuyPrice; int BestBuyQuantity; long BestSellPrice; int BestSellQuantity; long SecondBestBuyPrice; int SecondBestBuyQuantity; long SecondBestSellPrice; int SecondBestSellQuantity; long PrevBestBuyPrice = 0; int PrevBestBuyQuantity = 0; long PrevBestSellPrice = 0; int PrevBestSellQuantity = 0; long PrevSecondBestBuyPrice = 0; int PrevSecondBestBuyQuantity = 0; long PrevSecondBestSellPrice = 0; int PrevSecondBestSellQuantity = 0; int PrevOrderBookID = -1; int outputRowCount = 0; object lockObject = td.lockObject; using (SqlCommand readCommand = new SqlCommand("SELECT [EventID], ASCII([EventType]) AS [EventType], [OrderReferenceNumber], [OrderBookID], ASCII([BuySellIndicator]) AS [BuySellIndicator], CONVERT([bigint], [UnitPrice] * 100), [Quantity] FROM [OrderBookEvents] ORDER BY [OrderBookID], [EventID]", sqlConn)) { using (SqlDataReader reader = readCommand.ExecuteReader()) { while (reader.Read()) { EventType = (EventTypes)reader.GetInt32(1); OrderReferenceNumber = reader.GetInt32(2); OrderBookID = reader.GetInt32(3); if (OrderBookID != PrevOrderBookID) { buyOrderList = new SortedActiveOrderList(_descendingComparison); sellOrderList = new SortedActiveOrderList(_ascendingComparison); } PrevOrderBookID = OrderBookID; switch (EventType) { case EventTypes.Add: { SellIndicator = reader.GetInt32(4) != 66; UnitPrice = reader.GetInt64(5); Quantity = reader.GetInt32(6); ActiveOrder ao = new ActiveOrder(OrderBookID, OrderReferenceNumber, SellIndicator, UnitPrice, Quantity); ordersByReferenceNumber.Add(OrderReferenceNumber, ao); if (SellIndicator) { sellOrderList.Add(ao); } else { buyOrderList.Add(ao); } } break; case EventTypes.Execute: case EventTypes.Cancel: { Quantity = reader.GetInt32(6); ActiveOrder ao = ordersByReferenceNumber[OrderReferenceNumber]; if (ao.Quantity == Quantity) { if (ao.SellIndicator) { sellOrderList.Remove(ao); } else { buyOrderList.Remove(ao); } ordersByReferenceNumber.Remove(OrderReferenceNumber); } else { ao.Quantity -= Quantity; } } break; case EventTypes.Delete: { ActiveOrder ao = ordersByReferenceNumber[OrderReferenceNumber]; if (ao.SellIndicator) { sellOrderList.Remove(ao); } else { buyOrderList.Remove(ao); } ordersByReferenceNumber.Remove(OrderReferenceNumber); } break; } buyOrderList.GetBestAndSecondBest(out BestBuyPrice, out BestBuyQuantity, out SecondBestBuyPrice, out SecondBestBuyQuantity); sellOrderList.GetBestAndSecondBest(out BestSellPrice, out BestSellQuantity, out SecondBestSellPrice, out SecondBestSellQuantity); if (PrevBestBuyPrice != BestBuyPrice || PrevBestBuyQuantity != BestBuyQuantity || PrevBestSellPrice != BestSellPrice || PrevBestSellQuantity != BestSellQuantity || PrevSecondBestBuyPrice != SecondBestBuyPrice || PrevSecondBestBuyQuantity != SecondBestBuyQuantity || PrevSecondBestSellPrice != SecondBestSellPrice || PrevSecondBestSellQuantity != SecondBestSellQuantity) { rowDetail rd = new rowDetail(reader.GetInt32(0), OrderBookID, BestBuyPrice, BestBuyQuantity, BestSellPrice, BestSellQuantity, SecondBestBuyPrice, SecondBestBuyQuantity, SecondBestSellPrice, SecondBestSellQuantity); lock (lockObject) { rowQueue.Enqueue(rd); if (++outputRowCount >= _signalBatchSize) { td.rowsAvailable.Set(); outputRowCount = 0; } } PrevBestBuyPrice = BestBuyPrice; PrevBestBuyQuantity = BestBuyQuantity; PrevBestSellPrice = BestSellPrice; PrevBestSellQuantity = BestSellQuantity; PrevSecondBestBuyPrice = SecondBestBuyPrice; PrevSecondBestBuyQuantity = SecondBestBuyQuantity; PrevSecondBestSellPrice = SecondBestSellPrice; PrevSecondBestSellQuantity = SecondBestSellQuantity; } } } } lock (lockObject) { td.finished = true; td.rowsAvailable.Set(); } loadThread.Join(); } } };
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Daniel Ross avatar image
Daniel Ross answered
Daniel Ross V1 Wow, good work Jahlen you have really put up a nice challenge. I am going to call this "My one and only version cause my head hurts". CREATE ASSEMBLY [SqlClassLibrary] AUTHORIZATION [dbo] FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010400B108724C0000000000000000E00002210B01080000320000000E0000000000004E51000000200000006000000000400000200000000200000400000000000000040000000000000000C000000004000000000000020040850000100000100000000010000010000000000000100000000000000000000000F45000005700000000800000C00800000000000000000000000000000000000000A000000C000000006000001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000054310000002000000032000000040000000000000000000000000000200000602E736461746100007C000000006000000002000000360000000000000000000000000000400000C02E72737263000000C008000000800000000A000000380000000000000000000000000000400000402E72656C6F6300000C00000000A00000000200000042000000000000000000000000000040000042000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000030510000000000004800000002000500FC3B0000F814000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002602280100000A00002A00001B300F00DA0400000100001100730200000A1305730200000A1307730200000A1304730200000A1306730300000A0D730400000A130D730400000A130C0F00FE16020000016F0500000A13130F01FE16020000016F0500000A1311096F0600000A7201000070730700000A6F0800000A00096F0600000A7211000070730700000A6F0800000A00096F0600000A7229000070730700000A6F0800000A00096F0600000A7243000070730700000A6F0800000A00096F0600000A7263000070730700000A6F0800000A00096F0600000A727F000070730700000A6F0800000A00096F0600000A72A1000070730700000A6F0800000A00096F0600000A72C7000070730700000A6F0800000A00096F0600000A72F3000070730700000A6F0800000A00096F0600000A721B010070730700000A6F0800000A00730900000A130E00730A00000A13140011141B8D0D00000113161116167249010070A2001116171113A2001116187259010070A2001116191111A20011161A726F010070A2001116280B00000A6F0C00000A0011146F0D00000A00110E11146F0E00000A00110E72A30100706F0F00000A00110E6F1000000A6F0500000A1313110E72CB0100706F0F00000A00110E6F1100000A6F1200000A1311110E72ED0100706F0F00000A00110E6F1300000A26110E72310200706F0F00000A00110E6F1400000A130F38D0010000110F166F1500000A6F0500000A281600000A1308110F176F1500000A6F0500000A1312110F186F1500000A6F0500000A281600000A130A110F196F1500000A6F0500000A281600000A1309110F1A6F1500000A6F0500000A1310110F1B6F1500000A6F0500000A281700000A0C110F1C6F1500000A6F0500000A281600000A130B0011121317001117725003007016281800000A1640970000000011081112110A1109111008110B730C0000060A1110725403007016281800000A163337120506120D1206110A110B0811092809000006001200071205120712061204120D120C1109110B110A1110081108092806000006002B3600120506120C1204110A110B0811092808000006001200071205120712061204120D120C1109110B110A11100811080928050000060000389D000000001117725803007016281800000A163324001200071205120712061204120D120C1109110B110A1110081108092804000006002B68001117725C03007016281800000A163324001200071205120712061204120D120C1109110B110A1110081108092803000006002B33001117726003007016281800000A163322001200071205120712061204120D120C1109110B110A1110081108092803000006000000110F6F1900000A3A24FEFFFF110F6F1A00000A00001114731B00000A13150011156F1C00000A16166F1D00000A2611156F1C00000A17176F1D00000A2611156F1C00000A18186F1D00000A2611156F1C00000A19196F1D00000A2611156F1C00000A1A1A6F1D00000A2611156F1C00000A1B1B6F1D00000A2611156F1C00000A1C1C6F1D00000A2611156F1C00000A1D1D6F1D00000A2611156F1C00000A1E1E6F1D00000A2611156F1C00000A1F091F096F1D00000A26111572640300706F1E00000A001115096F1F00000A6F2000000A6F2100000A001115096F2200000A0011146F2300000A0000DE0E11152C0811156F2400000A0000DC11146F2500000A0000DE0E11142C0811146F2400000A0000DC002A00004130000002000000E5030000CC000000B10400000E00000000000001020000003B0100008F030000CA0400000E00000000000001133004006D07000002000011000204500E0A8C150000016F2600000A7404000002730D0000065105500E088C150000016F2600000A7403000002730B00000610010E0B725403007016281800000A16408603000002507B0F000004037B01000004282700000A1640450100000E06730400000A510E060E04500E088C150000016F2600000A7404000001510E09037B05000004408A00000003037B020000047D0100000403037B060000047D05000004030E0650186F2800000A282900000A7D02000004030E0650186F2A00000A282B00000A7D06000004030F0E0E0D0E0828070000060005500E088C15000001036F2C00000A000E065002507B0F0000048C050000016F2D00000A000E04500E088C150000010E06506F2C00000A0038890000000002500A06067B0C000004160E09DAD67D0C00000404500E0A8C1500000102506F2C00000A00030B07077B05000004160E09DAD67D0500000405500E088C15000001036F2C00000A00030F0E0E0D0E082807000006000E065002507B0F0000048C05000001037B050000048C150000016F2E00000A000E04500E088C150000010E06506F2C00000A0000382302000002507B0F000004037B02000004282700000A16402F0100000E06730400000A510E060E04500E088C150000016F2600000A7404000001510E09037B060000043372030E0650186F2800000A282900000A7D02000004030E0650186F2A00000A282B00000A7D06000004030F0E0E0D0E0828070000060005500E088C15000001036F2C00000A000E065002507B0F0000048C050000016F2D00000A000E04500E088C150000010E06506F2C00000A00388E00000000030B07077B06000004160E09DAD67D0600000402500A06067B0C000004160E09DAD67D0C000004045002507B0A0000048C1500000102506F2C00000A00030F0E0E0D0E0828070000060005500E088C15000001036F2C00000A000E065002507B0F0000048C05000001037B060000048C150000016F2E00000A000E04500E088C150000010E06506F2C00000A000038DC000000000E06730400000A510E060E04500E088C150000016F2600000A7404000001510E090E065002507B0F0000048C050000016F2F00000A282B00000A332A0E065002507B0F0000048C050000016F2D00000A000E04500E088C150000010E06506F2C00000A002B740002500A06067B0C000004160E09DAD67D0C0000040E065002507B0F0000048C050000010E065002507B0F0000048C050000016F2F00000A282B00000A0E09DA8C150000016F2E00000A00045002507B0A0000048C1500000102506F2C00000A000E04500E088C150000010E06506F2C00000A000000389C0300000002507B0F000004037B03000004282700000A1640570100000E07730400000A510E070E05500E088C150000016F2600000A7404000001510E09037B07000004409C00000003037B040000047D0300000403037B080000047D07000004030E07500E07506F3000000A19DA6F2800000A282900000A7D04000004030E07500E07506F3000000A19DA6F2A00000A282B00000A7D08000004030F0E0E0D0E0828070000060005500E088C15000001036F2C00000A000E075002507B0F0000048C050000016F2D00000A000E05500E088C150000010E07506F2C00000A00388900000000030B07077B07000004160E09DAD67D0700000402500A06067B0C000004160E09DAD67D0C00000404500E0A8C1500000102506F2C00000A00030F0E0E0D0E0828070000060005500E088C15000001036F2C00000A000E075002507B0F0000048C05000001037B070000048C150000016F2E00000A000E05500E088C150000010E07506F2C00000A0000382B02000002507B0F000004037B04000004282700000A16403F0100000E07730400000A510E070E05500E088C150000016F2600000A7404000001510E09037B080000044084000000030E07500E07506F3000000A19DA6F2800000A282900000A7D04000004030E07500E07506F3000000A19DA6F2A00000A282B00000A7D08000004030F0E0E0D0E0828070000060005500E088C15000001036F2C00000A000E075002507B0F0000048C050000016F2D00000A000E05500E088C150000010E07506F2C00000A00388900000000030B07077B08000004160E09DAD67D0800000402500A06067B0C000004160E09DAD67D0C00000404500E0A8C1500000102506F2C00000A00030F0E0E0D0E0828070000060005500E088C15000001036F2C00000A000E075002507B0F0000048C05000001037B080000048C150000016F2E00000A000E05500E088C150000010E07506F2C00000A000038D4000000000E07730400000A510E070E05500E088C150000016F2600000A7404000001510E090E075002507B0F0000048C050000016F2F00000A282B00000A332A0E075002507B0F0000048C050000016F2D00000A000E05500E088C150000010E07506F2C00000A002B6C00025002507B0C0000040E09DA7D0C0000040E075002507B0F0000048C050000010E075002507B0F0000048C050000016F2F00000A282B00000A0E09DA8C150000016F2E00000A0004500E0A8C1500000102506F2C00000A000E05500E088C150000010E07506F2C00000A00000000002A00000013300400C706000003000011000204500E0A8C150000016F2600000A7404000002730D0000065105500E088C150000016F2600000A7403000002730B00000610010E0B725403007016281800000A16402503000002507B0F000004037B01000004282700000A1640270100000E06730400000A510E060E04500E088C150000016F2600000A74040000015102507B0C000004037B05000004408700000003037B020000047D0100000403037B060000047D05000004030E0650186F2800000A282900000A7D02000004030E0650186F2A00000A282B00000A7D06000004030F0E0E0D0E0828070000060005500E088C15000001036F2C00000A000E065002507B0F0000048C050000016F2D00000A000E04500E088C150000010E06506F2C00000A002B6900030A06067B050000041602507B0C000004DAD67D05000004030F0E0E0D0E0828070000060005500E088C15000001036F2C00000A000E065002507B0F0000048C05000001037B050000048C150000016F2E00000A000E04500E088C150000010E06506F2C00000A000038E001000002507B0F000004037B02000004282700000A16400C0100000E06730400000A510E060E04500E088C150000016F2600000A74040000015102507B0C000004037B06000004336F030E0650186F2800000A282900000A7D02000004030E0650186F2A00000A282B00000A7D06000004030F0E0E0D0E0828070000060005500E088C15000001036F2C00000A000E065002507B0F0000048C050000016F2D00000A000E04500E088C150000010E06506F2C00000A002B6900030A06067B060000041602507B0C000004DAD67D06000004030F0E0E0D0E0828070000060005500E088C15000001036F2C00000A000E065002507B0F0000048C05000001037B060000048C150000016F2E00000A000E04500E088C150000010E06506F2C00000A000038BC000000000E06730400000A510E060E04500E088C150000016F2600000A74040000015102507B0C0000040E065002507B0F0000048C050000016F2F00000A282B00000A332A0E065002507B0F0000048C050000016F2D00000A000E04500E088C150000010E06506F2C00000A002B4F000E065002507B0F0000048C050000010E065002507B0F0000048C050000016F2F00000A282B00000A02507B0C000004DA8C150000016F2E00000A000E04500E088C150000010E06506F2C00000A00000038480300000002507B0F000004037B03000004282700000A1640390100000E07730400000A510E070E05500E088C150000016F2600000A74040000015102507B0C000004037B07000004409900000003037B040000047D0300000403037B080000047D07000004030E07500E07506F3000000A19DA6F2800000A282900000A7D04000004030E07500E07506F3000000A19DA6F2A00000A282B00000A7D08000004030F0E0E0D0E0828070000060005500E088C15000001036F2C00000A000E075002507B0F0000048C050000016F2D00000A000E05500E088C150000010E07506F2C00000A002B6900030A06067B070000041602507B0C000004DAD67D07000004030F0E0E0D0E0828070000060005500E088C15000001036F2C00000A000E075002507B0F0000048C05000001037B070000048C150000016F2E00000A000E05500E088C150000010E07506F2C00000A000038F501000002507B0F000004037B04000004282700000A1640210100000E07730400000A510E070E05500E088C150000016F2600000A74040000015102507B0C000004037B080000044081000000030E07500E07506F3000000A19DA6F2800000A282900000A7D04000004030E07500E07506F3000000A19DA6F2A00000A282B00000A7D08000004030F0E0E0D0E0828070000060005500E088C15000001036F2C00000A000E075002507B0F0000048C050000016F2D00000A000E05500E088C150000010E07506F2C00000A002B6900030A06067B080000041602507B0C000004DAD67D08000004030F0E0E0D0E0828070000060005500E088C15000001036F2C00000A000E075002507B0F0000048C05000001037B080000048C150000016F2E00000A000E05500E088C150000010E07506F2C00000A000038BC000000000E07730400000A510E070E05500E088C150000016F2600000A74040000015102507B0C0000040E075002507B0F0000048C050000016F2F00000A282B00000A332A0E075002507B0F0000048C050000016F2D00000A000E05500E088C150000010E07506F2C00000A002B4F000E075002507B0F0000048C050000010E075002507B0F0000048C050000016F2F00000A282B00000A02507B0C000004DA8C150000016F2E00000A000E05500E088C150000010E07506F2C00000A0000000004500E0A8C150000016F3100000A00002A0013300800BD010000040000110005500E088C150000016F3200000A2D2F7E3300000A7E3300000A7E3300000A7E3300000A16161616730A000006100105500E088C15000001036F3400000A000005500E088C150000016F2600000A7403000002730B0000061001037B010000040C037B020000040D037B030000040A037B040000040B037B050000041306037B060000041307037B070000041304037B080000041305000E0C130800110806282700000A163142060B110413050E0C0A0E091304080906071106110711041105730A000006100105500E088C15000001036F2C00000A00030F0E0E0D0E0828070000060038D000000000110806282700000A16333C11040E09D61304080906071106110711041105730A000006100105500E088C15000001036F2C00000A00030F0E0E0D0E08280700000600388800000000110807282700000A1631390E0C0B0E091305080906071106110711041105730A000006100105500E088C15000001036F2C00000A00030F0E0E0D0E082807000006002B4300110807282700000A16333711050E09D61305080906071106110711041105730A000006100105500E088C15000001036F2C00000A00030F0E0E0D0E0828070000060000002A000000133008000D020000050000110005500E088C150000016F3200000A2D2F7E3300000A7E3300000A7E3300000A7E3300000A16161616730A000006100105500E088C15000001036F3400000A000005500E088C150000016F2600000A7403000002730B0000061001037B010000040C037B020000040D037B030000040A037B040000040B037B050000041306037B060000041307037B070000041304037B080000041305087E3300000A282700000A1633170E0C7E3500000A283600000A0C1203FE15050000012B1B097E3300000A282700000A16330D0E0C7E3500000A283600000A0D00000E0C130800110808282700000A162F51097E3300000A282700000A162E02080D00110613070E0C0C0E091306080906071106110711041105730A000006100105500E088C15000001036F2C00000A00030F0E0E0D0E0828070000060038D000000000110808282700000A16333C11060E09D61306080906071106110711041105730A000006100105500E088C15000001036F2C00000A00030F0E0E0D0E08280700000600388800000000110809282700000A162F390E0C0D0E091307080906071106110711041105730A000006100105500E088C15000001036F2C00000A00030F0E0E0D0E082807000006002B4300110809282700000A16333711070E09D61307080906071106110711041105730A000006100105500E088C15000001036F2C00000A00030F0E0E0D0E0828070000060000002A00000013300300CE000000060000110003506F3700000A0A0616048C150000016F3800000A000617058C150000016F3800000A000618027B030000048C050000016F3800000A000619027B070000048C150000016F3800000A00061A027B010000048C050000016F3800000A00061B027B050000048C150000016F3800000A00061C027B040000048C050000016F3800000A00061D027B080000048C150000016F3800000A00061E027B020000048C050000016F3800000A00061F09027B060000048C150000016F3800000A0003506F1F00000A066F3900000A00002A000013300400ED000000070000110002500E048C15000001036F3400000A0005500E078C150000016F3200000A2D3304730400000A5104500E068C050000010E058C150000016F3A00000A0005500E078C1500000104506F3400000A0038960000000004730400000A510405500E078C150000016F2600000A74040000015104500E068C050000016F3B00000A2C4004500E068C050000016F3C00000A0A0450060450066F2A00000A282B00000A0E05D68C150000016F3D00000A0005500E078C1500000104506F2C00000A002B280004500E068C050000010E058C150000016F3A00000A0005500E078C1500000104506F2C00000A000000002A00000013300400ED000000080000110002500E048C15000001036F3400000A0005500E078C150000016F3200000A2D3304730400000A5104500E068C050000010E058C150000016F3A00000A0005500E078C1500000104506F3400000A0038960000000004730400000A510405500E078C150000016F2600000A74040000015104500E068C050000016F3B00000A2C4004500E068C050000016F3C00000A0A0450060450066F2A00000A282B00000A0E05D68C150000016F3D00000A0005500E078C1500000104506F2C00000A002B280004500E068C050000010E058C150000016F3A00000A0005500E078C1500000104506F2C00000A000000002A0000001330020047000000000000000002280100000A0002037D0100000402047D0200000402057D03000004020E047D04000004020E057D05000004020E067D06000004020E077D07000004020E087D08000004002A00133002006A000000000000000002280100000A0002037B010000047D0100000402037B020000047D0200000402037B030000047D0300000402037B040000047D0400000402037B050000047D0500000402037B060000047D0600000402037B070000047D0700000402037B080000047D08000004002A0000FE0002280100000A0002037D0900000402047D0D00000402057D0A000004020E047D0B000004020E057D0E000004020E077D0C000004020E067D0F000004002A133002005E000000000000000002280100000A0002037B090000047D0900000402037B0D0000047D0D00000402037B0A0000047D0A00000402037B0B0000047D0B00000402037B0E0000047D0E00000402037B0C0000047D0C00000402037B0F0000047D0F000004002A000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000054070000237E0000C00700004C07000023537472696E6773000000000C0F0000840300002355530090120000100000002347554944000000A01200005802000023426C6F620000000000000002000001571502000900000000FA0133001600000100000025000000040000000F0000000D000000630000004B0000000E00000008000000010000000400000000003507010000000000060062005B000E0090007B000600C900B6000600D300B6000600DE005B000E00E6006F000E0095027F020E00A0027F020E00AE027F020E00BC027F020E00D1026F000E00F2026F00060001035B000A00B4038D030A00D4038D030E00F7037F020E002D047F020E005F046F000600A0045B001200CA04B4040600D4045B000E0019056F000E0059053E05060082056F0506009F056F056700B30500000600E205C20506000206C20506003F06200606004D062006060061065B000600890677060600A40677060600BF0677060600D80677060600F106770606000E07770600000000010000000000010001000100000029003A00050001000100010000004A003A00050001000A000100000055003A00050009000C000600B40162000600BC0162000600C50162000600CC0162000600D40166000600DD0166000600E70166000600EF01660006006701660006004D01660006003401660006004101660006002A027F00060037027F0006005F01620050200000000006186900130001005C200000000016009A00170001007825000000001600F0001F000300F42C00000000160075011F001200C8330000000016007C011F002100943500000000160085011F003000B0370000000016008E0143003F008C380000000016009D014E0043008839000000001600AC014E004B00843A000000000618690069005300D83A000000000618690079005B00503B000000000618690082005C00903B00000000061869008E00630000000100A40000000200AC0000000100FC00000002000301000003000A01000004001201000005001A01000006002101000007002701000008002E0100000900340100000A00410100000B004D0100000C00590100000D005F0100000E00670100000F00720100000100FC00000002000301000003000A01000004001201000005001A01000006002101000007002701000008002E0100000900340100000A00410100000B004D0100000C00590100000D005F0100000E00670100000F00720100000100FC00000002000301000003000A01000004001201000005001A01000006002101000007002701000008002E0100000900340100000A00410100000B004D0100000C00590100000D005F0100000E00670100000F00720100000100FC00000002000301000003000A01000004001201000005001A01000006002101000007002701000008002E0100000900340100000A00410100000B004D0100000C00590100000D005F0100000E00670100000F00720100000100960100000200720100000300670100000400340100000100A40100000200FC00000003002E01000004002101000005004D01000006004101000007005F0100000800340100000100A40100000200FC00000003002701000004001A01000005004D01000006004101000007005F0100000800340100000100F80100000200FD01000003000302000004000702000005000C02000006001202000007001902000008001E02000001002402000001003D02000002004502000003004F02000004005802000005006202000006006502000007006F020000010078020900690013001900690013003100690013002100690013000900C80294003100E6029800610069009D005900FD02A20039006900130049006900130069000803A80049000F039D0049002403130039002903AE00390038039D0039004803B4003900560394006900C802940039006603B80039007603BC0041008403C1007100C003C6007100CA03CB007900DE03D1004100EC03D8004100F103130051006900AE0051001A04DC008100FD02E100510046049D0031007104E80091007A04B80051008404ED0051009204F2004900F10313009900AC041300A100AC0413001900840322012900DA0427012100E204C1007100CA032F012100E904C1007100C00335011900F4043A012100FD0440012100F4043A0121008403220121007A04B8001900FD0440011900040551012900100562001900FD023A012900150562002900FD026701310021057001B100F40475019100FD027B012100FD023A01210004055101210028058601210033057501B90069001300C10069001300C90069009401D9006900ED00E10069001300E90069009D00F10069009A01F90069009A01010169009D00090169009D00110169009D00190169009D00210169009D00290169009D002000FB018F012E002302FA012E0003029F012E000B02A8012E001302B1012E001B02D0012E003B0206022E002B0200022E003302FA012E004B0242022E005B022D022E0043022D022E005302FA014000F3018F01F80045014C015601560181018B018B0104800000010000002F0FA86D0000000000002507000002000000000000000000000001000A00000000000800000000000000000000000A0013000000000002000000000000000000000001006F000000000002000000000000000000000001005B00000000000000003C4D6F64756C653E006D73636F726C6962004D6963726F736F66742E56697375616C42617369630053746F72656450726F636564757265730073746F636B4D61726B65745F434C52006F726465725374617465006F726465720053797374656D004F626A656374002E63746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E67006F72646572426F6F6B0061536572766572006144617461626173650053797374656D2E436F6C6C656374696F6E7300486173687461626C6500536F727465644C69737400446563696D616C00446174615461626C650063616E63656C4F7264657200616F72646572006153746174650068746F72646572006874737461746500687453656C6C00687442757900736C53656C6C00736C42757900696E744F72646572426F6F6B00696E745175616E7469747900696E744F7264657252656600737472627300646563556E697400696E744576656E7449440064740064656C6574650075706461746541420075706461746541530073656E64726F7700617374617465006164644275790068744F726465720061646473656C6C0064656353656C6C0064656353656C6C3200646563427579006465634275793200696E7453656C6C5100696E7453656C6C513200696E744275795100696E7442757951320053656C6C0053656C6C320042757900427579320053656C6C510053656C6C51320042757951004275795132007374617465007374724576656E7454797065007374724253004576656E744964006576656E7454797065004F72646572526566004F72646572426F6F6B00425300556E69745072696365005175616E7469747900614F726465720053797374656D2E446174612E53716C436C69656E740053716C436F6D6D616E640053716C446174615265616465720053716C436F6E6E656374696F6E0053716C42756C6B436F707900546F537472696E670044617461436F6C756D6E436F6C6C656374696F6E006765745F436F6C756D6E730044617461436F6C756D6E0041646400537472696E6700436F6E636174007365745F436F6E6E656374696F6E537472696E67004F70656E007365745F436F6E6E656374696F6E007365745F436F6D6D616E645465787400457865637574655363616C6172006765745F436F6D6D616E645465787400457865637574654E6F6E51756572790045786563757465526561646572006765745F4974656D004D6963726F736F66742E56697375616C42617369632E436F6D70696C6572536572766963657300436F6E76657273696F6E7300546F496E746567657200546F446563696D616C004F70657261746F727300436F6D70617265537472696E67005265616400436C6F73650053716C42756C6B436F7079436F6C756D6E4D617070696E67436F6C6C656374696F6E006765745F436F6C756D6E4D617070696E67730053716C42756C6B436F7079436F6C756D6E4D617070696E67007365745F44657374696E6174696F6E5461626C654E616D650044617461526F77436F6C6C656374696F6E006765745F526F7773006765745F436F756E74007365745F426174636853697A65005772697465546F5365727665720049446973706F7361626C6500446973706F73650053797374656D2E436F6D706F6E656E744D6F64656C00436F6D706F6E656E7400496E74333200436F6D70617265004765744B6579004765744279496E646578007365745F4974656D0052656D6F766500436F6E7461696E734B6579005A65726F004F6E650044617461526F77004E6577526F7700496E6465784F664B6579005365744279496E646578004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053797374656D2E446961676E6F73746963730044656275676765724E6F6E55736572436F64654174747269627574650044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053797374656D2E52756E74696D652E496E7465726F705365727669636573004775696441747472696275746500436F6D56697369626C6541747472696275746500434C53436F6D706C69616E744174747269627574650053797374656D2E5265666C656374696F6E00417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053716C436C6173734C6962726172790053716C436C6173734C6962726172792E646C6C00000000000F4500760065006E0074004900440000174F00720064006500720042006F006F006B00490044000019420065007300740042007500790050007200690063006500001F42006500730074004200750079005100750061006E007400690074007900001B4200650073007400530065006C006C005000720069006300650000214200650073007400530065006C006C005100750061006E00740069007400790000255300650063006F006E006400420065007300740042007500790050007200690063006500002B5300650063006F006E00640042006500730074004200750079005100750061006E00740069007400790000275300650063006F006E0064004200650073007400530065006C006C0050007200690063006500002D5300650063006F006E0064004200650073007400530065006C006C005100750061006E007400690074007900000F5300650072007600650072003D0000153B00440061007400610062006100730065003D0000333B0049006E00740065006700720061007400650064002000530065006300750072006900740079003D0074007200750065000027730065006C006500630074002000400040007300650072007600650072006E0061006D0065000021730065006C006500630074002000640062005F006E0061006D0065002800290000437400720075006E00630061007400650020007400610062006C0065002000640062006F002E004F00720064006500720042006F006F006B005300740061007400650000811D530045004C0045004300540020004500760065006E0074004900440020002C004500760065006E00740054007900700065002C004F0072006400650072005200650066006500720065006E00630065004E0075006D006200650072002C004F00720064006500720042006F006F006B00490044002C00420075007900530065006C006C0049006E00640069006300610074006F00720020002C0055006E00690074005000720069006300650020002C005100750061006E0074006900740079002000460052004F004D002000640062006F002E004F00720064006500720042006F006F006B004500760065006E007400730020006F006200650020006F00720064006500720020006200790020006500760065006E007400490044000003410000035300000344000003450000034300001D4F00720064006500720042006F006F006B0053007400610074006500000000CBA825416AD1E54A817D1625D0FFCAD30008B77A5C561934E08908B03F5F7F11D50A3A03200001070002011109110923000F01101210120C10120D10120D10120D10120D1012111012110808080E11150812190A000401120C10121908081300080110120D121010121110120D0808111508030611150206080F20080111151115111511150808080805200101120C02060E0B200701080E08080E1115080520010112100320000E042000122D042001010E0520010112310500010E1D0E0520010112250320001C0320000804200012210420011C08040001080E05000111150E060003080E0E0203200002042000124106200212450808042000124904200101080520010112192907181210120C11151219120D120D120D120D0808080812111211121D12210E0E0E0E122512291D0E0E0420011C1C070002081115111505000111151C040001081C052002011C1C042001011C0607021210120C040701120C042001021C1007091115111511151115080808081115080002111511151115042000125905200201081C0520010112590407011259042001081C03070108040100000005200101116904200101020801000301000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773012901002431653830626164392D383062392D346537362D383432312D643632633534303737613763000005010000000005010001000026010021436F7079726967687420C2A9205441464520517565656E736C616E64203230313000001401000F73746F636B4D61726B657420434C5200001401000F5441464520517565656E736C616E640000001C51000000000000000000003E510000002000000000000000000000000000000000000000000000305100000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000B108724C0000000002000000600000001C6000001C3600005253445374923EFB6119CA4084490C3E6182E1F701000000433A5C64616E5C76625C73746F636B4D61726B657420434C525C73746F636B4D61726B657420434C525C6F626A5C44656275675C53716C436C6173734C6962726172792E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000030003000000280000800E000000480000801000000060000080000000000000000000000000000002000200000078000080030000009000008000000000000000000000000000000100007F0000A80000800000000000000000000000000000010001000000C00000800000000000000000000000000000010000000000D80000000000000000000000000000000000010000000000E80000000000000000000000000000000000010000000000F800000000000000000000000000000000000100000000000801000088840000E80200000000000000000000708700002801000000000000000000009888000022000000000000000000000018810000700300000000000000000000700334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100A86D2F0F00000100A86D2F0F3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004D0020000010053007400720069006E006700460069006C00650049006E0066006F000000AC020000010030003000300030003000340062003000000040001000010043006F006D00700061006E0079004E0061006D006500000000005400410046004500200051007500650065006E0073006C0061006E0064000000480010000100460069006C0065004400650073006300720069007000740069006F006E0000000000730074006F0063006B004D00610072006B0065007400200043004C005200000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003800380037002E00320038003000370032000000000048001400010049006E007400650072006E0061006C004E0061006D0065000000530071006C0043006C006100730073004C006900620072006100720079002E0064006C006C0000006800210001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020005400410046004500200051007500650065006E0073006C0061006E00640020003200300031003000000000005000140001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0043006C006100730073004C006900620072006100720079002E0064006C006C000000400010000100500072006F0064007500630074004E0061006D00650000000000730074006F0063006B004D00610072006B0065007400200043004C005200000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003800380037002E00320038003000370032000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003800380037002E003200380030003700320000000000280000002000000040000000010004000000000080020000000000000000000000000000000000000000000000008000008000000080800080000000800080008080000080808000C0C0C0000000FF0000FF000000FFFF00FF000000FF00FF00FFFF0000FFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007777777777777777777777777777700444444444444444444444444444447004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF4700488888888888888888888888888847004444444444444444444444444444470044C4C4C4C4C4C4C4C4C4ECECE49747004CCCCCCCCCCCCCCCCCCCCCCCCCCC40000444444444444444444444444444000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFC00000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000003C0000007FFFFFFFFFFFFFFFFFFFFFFFF2800000010000000200000000100040000000000C0000000000000000000000000000000000000000000000000008000008000000080800080000000800080008080000080808000C0C0C0000000FF0000FF000000FFFF00FF000000FF00FF00FFFF0000FFFFFF000000000000000000077777777777777744444444444444474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF84748888888888888474CCCCCCCCCCCCC47C4444444444444C000000000000000000000000000000000FFFF000080000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000FFFF0000FFFF00000000010002002020100001000400E8020000020010101000010004002801000003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005000000C000000503100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WITH PERMISSION_SET = EXTERNAL_ACCESS GO CREATE PROCEDURE [dbo].[orderBook] @aServer [nvarchar](4000), @aDatabase [nvarchar](4000) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SqlClassLibrary].[stockMarket_CLR.StoredProcedures].[orderBook] GO then use this DECLARE @dbName AS NVARCHAR(128) DECLARE @serverName AS NVARCHAR(128) set @serverName= @@SERVERNAME set @dbName= DB_NAME() EXEC dbo.orderbook @servername,@dbName and here is my code Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class StoredProcedures _ Public Shared Sub orderBook(ByVal aServer As SqlString, ByVal aDatabase As SqlString) Dim htOrder As New Hashtable Dim htState As New Hashtable Dim htBuy As New Hashtable Dim htSell As New Hashtable Dim dt As New DataTable Dim slSell As New SortedList Dim slBuy As New SortedList Dim intEventID, intOrderRef, intOrderBook, intQuantity As Integer Dim strEventType, strBS As String Dim decUnit As Decimal Dim aOrder As order Dim aState As orderState Dim strServer, strDatabase As String strServer = aServer.ToString strDatabase = aDatabase.ToString 'initiliase the datatable. this dt holds the data for the orderBookState table dt.Columns.Add(New DataColumn("EventID")) dt.Columns.Add(New DataColumn("OrderBookID")) dt.Columns.Add(New DataColumn("BestBuyPrice")) dt.Columns.Add(New DataColumn("BestBuyQuantity")) dt.Columns.Add(New DataColumn("BestSellPrice")) dt.Columns.Add(New DataColumn("BestSellQuantity")) dt.Columns.Add(New DataColumn("SecondBestBuyPrice")) dt.Columns.Add(New DataColumn("SecondBestBuyQuantity")) dt.Columns.Add(New DataColumn("SecondBestSellPrice")) dt.Columns.Add(New DataColumn("SecondBestSellQuantity")) Dim sqlComm As New SqlCommand Dim sqlDR As SqlDataReader Using sqlconn As New SqlConnection sqlconn.ConnectionString = "Server=" & strServer & ";Database=" & strDatabase & ";Integrated Security=true" sqlconn.Open() sqlComm.Connection = sqlconn sqlComm.CommandText = "truncate table dbo.OrderBookState" sqlComm.ExecuteNonQuery() sqlComm.CommandText = "SELECT EventID ,EventType,OrderReferenceNumber,OrderBookID,BuySellIndicator ," & _ "UnitPrice ,Quantity FROM dbo.OrderBookEvents obe order by eventID" sqlDR = sqlComm.ExecuteReader While sqlDR.Read intEventID = CInt(sqlDR(0).ToString) strEventType = sqlDR(1).ToString intOrderRef = CInt(sqlDR(2).ToString) intOrderBook = CInt(sqlDR(3).ToString) strBS = sqlDR(4).ToString decUnit = CDec(sqlDR(5).ToString) intQuantity = CInt(sqlDR(6).ToString) Select Case strEventType Case "A" aOrder = New order(intEventID, strEventType, intOrderRef, intOrderBook, strBS, decUnit, intQuantity) If strBS = "S" Then addsell(htOrder, aOrder, slSell, htSell, intOrderRef, intQuantity, decUnit, intOrderBook) updateAS(aOrder, aState, htOrder, htState, htSell, htBuy, slSell, slBuy, intOrderBook, intQuantity, intOrderRef, strBS, decUnit, intEventID, dt) Else addBuy(htOrder, aOrder, slBuy, htBuy, intOrderRef, intQuantity, decUnit, intOrderBook) updateAB(aOrder, aState, htOrder, htState, htSell, htBuy, slSell, slBuy, intOrderBook, intQuantity, intOrderRef, strBS, decUnit, intEventID, dt) End If Case "D" delete(aOrder, aState, htOrder, htState, htSell, htBuy, slSell, slBuy, intOrderBook, intQuantity, intOrderRef, strBS, decUnit, intEventID, dt) Case "E" cancelOrder(aOrder, aState, htOrder, htState, htSell, htBuy, slSell, slBuy, intOrderBook, intQuantity, intOrderRef, strBS, decUnit, intEventID, dt) Case "C" cancelOrder(aOrder, aState, htOrder, htState, htSell, htBuy, slSell, slBuy, intOrderBook, intQuantity, intOrderRef, strBS, decUnit, intEventID, dt) End Select End While sqlDR.Close() Using copy As New SqlBulkCopy(sqlconn) copy.ColumnMappings.Add(0, 0) copy.ColumnMappings.Add(1, 1) copy.ColumnMappings.Add(2, 2) copy.ColumnMappings.Add(3, 3) copy.ColumnMappings.Add(4, 4) copy.ColumnMappings.Add(5, 5) copy.ColumnMappings.Add(6, 6) copy.ColumnMappings.Add(7, 7) copy.ColumnMappings.Add(8, 8) copy.ColumnMappings.Add(9, 9) copy.DestinationTableName = "OrderBookState" copy.BatchSize = dt.Rows.Count copy.WriteToServer(dt) sqlconn.Close() End Using sqlconn.Dispose() End Using End Sub Shared Sub cancelOrder(ByRef aorder As order, ByVal aState As orderState, ByRef htorder As Hashtable, ByRef htstate As Hashtable, ByRef htSell As Hashtable, ByRef htBuy As Hashtable, ByRef slSell As SortedList, ByRef slBuy As SortedList, ByVal intOrderBook As Integer, ByVal intQuantity As Integer, ByVal intOrderRef As Integer, ByVal strbs As String, ByVal decUnit As Decimal, ByVal intEventID As Integer, ByVal dt As DataTable) aorder = New order(CType(htorder.Item(intOrderRef), order)) aState = New orderState(CType(htstate.Item(intOrderBook), orderState)) If strbs = "S" Then If aorder.decUnit = aState.decSell Then slSell = New SortedList slSell = CType(htSell.Item(intOrderBook), SortedList) If intQuantity = aState.intSellQ Then aState.decSell = aState.decSell2 aState.intSellQ = aState.intSellQ2 aState.decSell2 = CDec(slSell.GetKey(2)) aState.intSellQ2 = CInt(slSell.GetByIndex(2)) sendrow(aState, dt, intEventID, intOrderBook) htstate.Item(intOrderBook) = aState slSell.Remove(aorder.decUnit) htSell.Item(intOrderBook) = slSell Else aorder.intQuantity += -intQuantity htorder.Item(intOrderRef) = aorder aState.intSellQ += -intQuantity htstate.Item(intOrderBook) = aState sendrow(aState, dt, intEventID, intOrderBook) slSell.Item(aorder.decUnit) = aState.intSellQ htSell.Item(intOrderBook) = slSell End If ElseIf aorder.decUnit = aState.decSell2 Then slSell = New SortedList slSell = CType(htSell.Item(intOrderBook), SortedList) If intQuantity = aState.intSellQ2 Then aState.decSell2 = CDec(slSell.GetKey(2)) aState.intSellQ2 = CInt(slSell.GetByIndex(2)) sendrow(aState, dt, intEventID, intOrderBook) htstate.Item(intOrderBook) = aState slSell.Remove(aorder.decUnit) htSell.Item(intOrderBook) = slSell Else aState.intSellQ2 += -intQuantity aorder.intQuantity += -intQuantity htorder.Item(aorder.intOrderRef) = aorder sendrow(aState, dt, intEventID, intOrderBook) htstate.Item(intOrderBook) = aState slSell.Item(aorder.decUnit) = aState.intSellQ2 htSell.Item(intOrderBook) = slSell End If Else slSell = New SortedList slSell = CType(htSell.Item(intOrderBook), SortedList) If intQuantity = CInt(slSell.Item(aorder.decUnit)) Then slSell.Remove(aorder.decUnit) htSell.Item(intOrderBook) = slSell Else aorder.intQuantity += -intQuantity slSell.Item(aorder.decUnit) = CInt(slSell.Item(aorder.decUnit)) - intQuantity htorder.Item(aorder.intOrderRef) = aorder htSell.Item(intOrderBook) = slSell End If End If Else If aorder.decUnit = aState.decBuy Then slBuy = New SortedList slBuy = CType(htBuy.Item(intOrderBook), SortedList) If intQuantity = aState.intBuyQ Then aState.decBuy = aState.decBuy2 aState.intBuyQ = aState.intBuyQ2 aState.decBuy2 = CDec(slBuy.GetKey(slBuy.Count - 3)) aState.intBuyQ2 = CInt(slBuy.GetByIndex(slBuy.Count - 3)) sendrow(aState, dt, intEventID, intOrderBook) htstate.Item(intOrderBook) = aState slBuy.Remove(aorder.decUnit) htBuy.Item(intOrderBook) = slBuy Else aState.intBuyQ += -intQuantity aorder.intQuantity += -intQuantity htorder.Item(intOrderRef) = aorder sendrow(aState, dt, intEventID, intOrderBook) htstate.Item(intOrderBook) = aState slBuy.Item(aorder.decUnit) = aState.intBuyQ htBuy.Item(intOrderBook) = slBuy End If ElseIf aorder.decUnit = aState.decBuy2 Then slBuy = New SortedList slBuy = CType(htBuy.Item(intOrderBook), SortedList) If intQuantity = aState.intBuyQ2 Then aState.decBuy2 = CDec(slBuy.GetKey(slBuy.Count - 3)) aState.intBuyQ2 = CInt(slBuy.GetByIndex(slBuy.Count - 3)) sendrow(aState, dt, intEventID, intOrderBook) htstate.Item(intOrderBook) = aState slBuy.Remove(aorder.decUnit) htBuy.Item(intOrderBook) = slBuy Else aState.intBuyQ2 += -intQuantity aorder.intQuantity += -intQuantity htorder.Item(intOrderRef) = aorder sendrow(aState, dt, intEventID, intOrderBook) htstate.Item(intOrderBook) = aState slBuy.Item(aorder.decUnit) = aState.intBuyQ2 htBuy.Item(intOrderBook) = slBuy End If Else slBuy = New SortedList slBuy = CType(htBuy.Item(intOrderBook), SortedList) If intQuantity = CInt(slBuy.Item(aorder.decUnit)) Then slBuy.Remove(aorder.decUnit) htBuy.Item(intOrderBook) = slBuy Else aorder.intQuantity = aorder.intQuantity - intQuantity slBuy.Item(aorder.decUnit) = CInt(slBuy.Item(aorder.decUnit)) - intQuantity htorder.Item(intOrderRef) = aorder htBuy.Item(intOrderBook) = slBuy End If End If End If End Sub Shared Sub delete(ByRef aorder As order, ByVal aState As orderState, ByRef htorder As Hashtable, ByRef htstate As Hashtable, ByRef htSell As Hashtable, ByRef htBuy As Hashtable, ByRef slSell As SortedList, ByRef slBuy As SortedList, ByVal intOrderBook As Integer, ByVal intQuantity As Integer, ByVal intOrderRef As Integer, ByVal strbs As String, ByVal decUnit As Decimal, ByVal intEventID As Integer, ByVal dt As DataTable) aorder = New order(CType(htorder.Item(intOrderRef), order)) aState = New orderState(CType(htstate.Item(intOrderBook), orderState)) If strbs = "S" Then If aorder.decUnit = aState.decSell Then slSell = New SortedList slSell = CType(htSell.Item(intOrderBook), SortedList) If aorder.intQuantity = aState.intSellQ Then aState.decSell = aState.decSell2 aState.intSellQ = aState.intSellQ2 aState.decSell2 = CDec(slSell.GetKey(2)) aState.intSellQ2 = CInt(slSell.GetByIndex(2)) sendrow(aState, dt, intEventID, intOrderBook) htstate.Item(intOrderBook) = aState slSell.Remove(aorder.decUnit) htSell.Item(intOrderBook) = slSell Else aState.intSellQ += -aorder.intQuantity sendrow(aState, dt, intEventID, intOrderBook) htstate.Item(intOrderBook) = aState slSell.Item(aorder.decUnit) = aState.intSellQ htSell.Item(intOrderBook) = slSell End If ElseIf aorder.decUnit = aState.decSell2 Then slSell = New SortedList slSell = CType(htSell.Item(intOrderBook), SortedList) If aorder.intQuantity = aState.intSellQ2 Then aState.decSell2 = CDec(slSell.GetKey(2)) aState.intSellQ2 = CInt(slSell.GetByIndex(2)) sendrow(aState, dt, intEventID, intOrderBook) htstate.Item(intOrderBook) = aState slSell.Remove(aorder.decUnit) htSell.Item(intOrderBook) = slSell Else aState.intSellQ2 += -aorder.intQuantity sendrow(aState, dt, intEventID, intOrderBook) htstate.Item(intOrderBook) = aState slSell.Item(aorder.decUnit) = aState.intSellQ2 htSell.Item(intOrderBook) = slSell End If Else slSell = New SortedList slSell = CType(htSell.Item(intOrderBook), SortedList) If aorder.intQuantity = CInt(slSell.Item(aorder.decUnit)) Then slSell.Remove(aorder.decUnit) htSell.Item(intOrderBook) = slSell Else slSell.Item(aorder.decUnit) = CInt(slSell.Item(aorder.decUnit)) - aorder.intQuantity htSell.Item(intOrderBook) = slSell End If End If Else If aorder.decUnit = aState.decBuy Then slBuy = New SortedList slBuy = CType(htBuy.Item(intOrderBook), SortedList) If aorder.intQuantity = aState.intBuyQ Then aState.decBuy = aState.decBuy2 aState.intBuyQ = aState.intBuyQ2 aState.decBuy2 = CDec(slBuy.GetKey(slBuy.Count - 3)) aState.intBuyQ2 = CInt(slBuy.GetByIndex(slBuy.Count - 3)) sendrow(aState, dt, intEventID, intOrderBook) htstate.Item(intOrderBook) = aState slBuy.Remove(aorder.decUnit) htBuy.Item(intOrderBook) = slBuy Else aState.intBuyQ += -aorder.intQuantity sendrow(aState, dt, intEventID, intOrderBook) htstate.Item(intOrderBook) = aState slBuy.Item(aorder.decUnit) = aState.intBuyQ htBuy.Item(intOrderBook) = slBuy End If ElseIf aorder.decUnit = aState.decBuy2 Then slBuy = New SortedList slBuy = CType(htBuy.Item(intOrderBook), SortedList) If aorder.intQuantity = aState.intBuyQ2 Then aState.decBuy2 = CDec(slBuy.GetKey(slBuy.Count - 3)) aState.intBuyQ2 = CInt(slBuy.GetByIndex(slBuy.Count - 3)) sendrow(aState, dt, intEventID, intOrderBook) htstate.Item(intOrderBook) = aState slBuy.Remove(aorder.decUnit) htBuy.Item(intOrderBook) = slBuy Else aState.intBuyQ2 += -aorder.intQuantity sendrow(aState, dt, intEventID, intOrderBook) htstate.Item(intOrderBook) = aState slBuy.Item(aorder.decUnit) = aState.intBuyQ2 htBuy.Item(intOrderBook) = slBuy End If Else slBuy = New SortedList slBuy = CType(htBuy.Item(intOrderBook), SortedList) If aorder.intQuantity = CInt(slBuy.Item(aorder.decUnit)) Then slBuy.Remove(aorder.decUnit) htBuy.Item(intOrderBook) = slBuy Else slBuy.Item(aorder.decUnit) = CInt(slBuy.Item(aorder.decUnit)) - aorder.intQuantity htBuy.Item(intOrderBook) = slBuy End If End If End If htorder.Remove(intOrderRef) End Sub Shared Sub updateAB(ByRef aorder As order, ByVal aState As orderState, ByRef htorder As Hashtable, ByRef htstate As Hashtable, ByRef htSell As Hashtable, ByRef htBuy As Hashtable, ByRef slSell As SortedList, ByRef slBuy As SortedList, ByVal intOrderBook As Integer, ByVal intQuantity As Integer, ByVal intOrderRef As Integer, ByVal strbs As String, ByVal decUnit As Decimal, ByVal intEventID As Integer, ByVal dt As DataTable) If Not htstate.ContainsKey(intOrderBook) Then aState = New orderState(0, 0, 0, 0, 0, 0, 0, 0) htstate.Add(intOrderBook, aState) End If aState = New orderState(CType(htstate.Item(intOrderBook), orderState)) Dim decSell, decSell2, decBuy, decBuy2 As Decimal Dim intSellQ, intSellQ2, intBuyQ, intBuyQ2 As Integer decSell = aState.decSell decSell2 = aState.decSell2 decBuy = aState.decBuy decBuy2 = aState.decBuy2 intSellQ = aState.intSellQ intSellQ2 = aState.intSellQ2 intBuyQ = aState.intBuyQ intBuyQ2 = aState.intBuyQ2 Select Case decUnit Case Is > decBuy decBuy2 = decBuy intBuyQ2 = intBuyQ decBuy = decUnit intBuyQ = intQuantity aState = New orderState(decSell, decSell2, decBuy, decBuy2, intSellQ, intSellQ2, intBuyQ, intBuyQ2) htstate.Item(intOrderBook) = aState sendrow(aState, dt, intEventID, intOrderBook) Case Is = decBuy intBuyQ += intQuantity aState = New orderState(decSell, decSell2, decBuy, decBuy2, intSellQ, intSellQ2, intBuyQ, intBuyQ2) htstate.Item(intOrderBook) = aState sendrow(aState, dt, intEventID, intOrderBook) Case Is > decBuy2 decBuy2 = decUnit intBuyQ2 = intQuantity aState = New orderState(decSell, decSell2, decBuy, decBuy2, intSellQ, intSellQ2, intBuyQ, intBuyQ2) htstate.Item(intOrderBook) = aState sendrow(aState, dt, intEventID, intOrderBook) Case Is = decBuy2 intBuyQ2 += intQuantity aState = New orderState(decSell, decSell2, decBuy, decBuy2, intSellQ, intSellQ2, intBuyQ, intBuyQ2) htstate.Item(intOrderBook) = aState sendrow(aState, dt, intEventID, intOrderBook) End Select End Sub Shared Sub updateAS(ByRef aorder As order, ByVal aState As orderState, ByRef htorder As Hashtable, ByRef htstate As Hashtable, ByRef htSell As Hashtable, ByRef htBuy As Hashtable, ByRef slSell As SortedList, ByRef slBuy As SortedList, ByVal intOrderBook As Integer, ByVal intQuantity As Integer, ByVal intOrderRef As Integer, ByVal strbs As String, ByVal decUnit As Decimal, ByVal intEventID As Integer, ByVal dt As DataTable) If Not htstate.ContainsKey(intOrderBook) Then aState = New orderState(0, 0, 0, 0, 0, 0, 0, 0) htstate.Add(intOrderBook, aState) End If aState = New orderState(CType(htstate.Item(intOrderBook), orderState)) Dim decSell, decSell2, decBuy, decBuy2 As Decimal Dim intSellQ, intSellQ2, intBuyQ, intBuyQ2 As Integer decSell = aState.decSell decSell2 = aState.decSell2 decBuy = aState.decBuy decBuy2 = aState.decBuy2 intSellQ = aState.intSellQ intSellQ2 = aState.intSellQ2 intBuyQ = aState.intBuyQ intBuyQ2 = aState.intBuyQ2 If decSell = 0 Then decSell = decUnit + 1 decSell2 = 0 ElseIf decSell2 = 0 Then decSell2 = decUnit + 1 End If Select Case decUnit Case Is < decSell If decSell2 0 Then decSell2 = decSell End If intSellQ2 = intSellQ decSell = decUnit intSellQ = intQuantity aState = New orderState(decSell, decSell2, decBuy, decBuy2, intSellQ, intSellQ2, intBuyQ, intBuyQ2) htstate.Item(intOrderBook) = aState sendrow(aState, dt, intEventID, intOrderBook) Case Is = decSell intSellQ += intQuantity aState = New orderState(decSell, decSell2, decBuy, decBuy2, intSellQ, intSellQ2, intBuyQ, intBuyQ2) htstate.Item(intOrderBook) = aState sendrow(aState, dt, intEventID, intOrderBook) Case Is < decSell2 decSell2 = decUnit intSellQ2 = intQuantity aState = New orderState(decSell, decSell2, decBuy, decBuy2, intSellQ, intSellQ2, intBuyQ, intBuyQ2) htstate.Item(intOrderBook) = aState sendrow(aState, dt, intEventID, intOrderBook) Case Is = decSell2 intSellQ2 += intQuantity aState = New orderState(decSell, decSell2, decBuy, decBuy2, intSellQ, intSellQ2, intBuyQ, intBuyQ2) htstate.Item(intOrderBook) = aState sendrow(aState, dt, intEventID, intOrderBook) End Select End Sub Shared Sub sendrow(ByVal astate As orderState, ByRef dt As DataTable, ByVal intEventID As Integer, ByVal intOrderBook As Integer) Dim row As DataRow row = dt.NewRow row.Item(0) = intEventID row.Item(1) = intOrderBook row.Item(2) = astate.decBuy row.Item(3) = astate.intBuyQ row.Item(4) = astate.decSell row.Item(5) = astate.intSellQ row.Item(6) = astate.decBuy2 row.Item(7) = astate.intBuyQ2 row.Item(8) = astate.decSell2 row.Item(9) = astate.intSellQ2 dt.Rows.Add(row) End Sub Shared Sub addBuy(ByRef htOrder As Hashtable, ByVal aorder As order, ByRef slBuy As SortedList, ByRef htBuy As Hashtable, ByVal intOrderRef As Integer, ByVal intQuantity As Integer, ByVal decUnit As Decimal, ByVal intOrderBook As Integer) Dim intIndex As Integer htOrder.Add(intOrderRef, aorder) If Not htBuy.ContainsKey(intOrderBook) Then slBuy = New SortedList slBuy.Add(decUnit, intQuantity) htBuy.Add(intOrderBook, slBuy) Else slBuy = New SortedList slBuy = CType(htBuy.Item(intOrderBook), SortedList) If slBuy.ContainsKey(decUnit) Then intIndex = slBuy.IndexOfKey(decUnit) slBuy.SetByIndex(intIndex, CInt(slBuy.GetByIndex(intIndex)) + intQuantity) htBuy.Item(intOrderBook) = slBuy Else slBuy.Add(decUnit, intQuantity) htBuy.Item(intOrderBook) = slBuy End If End If End Sub Shared Sub addsell(ByRef htOrder As Hashtable, ByVal aorder As order, ByRef slSell As SortedList, ByRef htSell As Hashtable, ByVal intOrderRef As Integer, ByVal intQuantity As Integer, ByVal decUnit As Decimal, ByVal intOrderBook As Integer) Dim intIndex As Integer htOrder.Add(intOrderRef, aorder) If Not htSell.ContainsKey(intOrderBook) Then slSell = New SortedList slSell.Add(decUnit, intQuantity) htSell.Add(intOrderBook, slSell) Else slSell = New SortedList slSell = CType(htSell.Item(intOrderBook), SortedList) If slSell.ContainsKey(decUnit) Then intIndex = slSell.IndexOfKey(decUnit) slSell.SetByIndex(intIndex, CInt(slSell.GetByIndex(intIndex)) + intQuantity) htSell.Item(intOrderBook) = slSell Else slSell.Add(decUnit, intQuantity) htSell.Item(intOrderBook) = slSell End If End If End Sub End Class Public Class orderState Public decSell, decSell2, decBuy, decBuy2 As Decimal Public intSellQ, intSellQ2, intBuyQ, intBuyQ2 As Integer Sub New(ByVal Sell As Decimal, ByVal Sell2 As Decimal, ByVal Buy As Decimal, ByVal Buy2 As Decimal, ByVal SellQ As Integer, ByVal SellQ2 As Integer, ByVal BuyQ As Integer, ByVal BuyQ2 As Integer) decSell = Sell decSell2 = Sell2 decBuy = Buy decBuy2 = Buy2 intSellQ = SellQ intSellQ2 = SellQ2 intBuyQ = BuyQ intBuyQ2 = BuyQ2 End Sub Sub New(ByVal state As orderState) decSell = state.decSell decSell2 = state.decSell2 decBuy = state.decBuy decBuy2 = state.decBuy2 intSellQ = state.intSellQ intSellQ2 = state.intSellQ2 intBuyQ = state.intBuyQ intBuyQ2 = state.intBuyQ2 End Sub End Class Public Class order Public intEventID, intOrderRef, intOrderBook, intQuantity As Integer Public strEventType, strBS As String Public decUnit As Decimal Sub New(ByVal EventId As Integer, ByVal eventType As String, ByVal OrderRef As Integer, ByVal OrderBook As Integer, ByVal BS As String, ByVal UnitPrice As Decimal, ByVal Quantity As Integer) intEventID = EventId strEventType = eventType intOrderRef = OrderRef intOrderBook = OrderBook strBS = BS intQuantity = Quantity decUnit = UnitPrice End Sub Sub New(ByVal aOrder As order) intEventID = aOrder.intEventID strEventType = aOrder.strEventType intOrderRef = aOrder.intOrderRef intOrderBook = aOrder.intOrderBook strBS = aOrder.strBS intQuantity = aOrder.intQuantity decUnit = aOrder.decUnit End Sub End Class
12 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@Daniel - I think it went something like this... > Hey, Dave, shit. I think I forgot to add the facility for Bulk Copy to the context connection protocol interface. > Hmm, shit. Let's go to the canteen and ask 5 people if they can think of a reason why anyone would need that, if not, then we're good to go... > Yeah, and we can get waffles... > Yeah, let's not worry about asking people and just get the waffles.
2 Likes 2 ·
JAhlen avatar image JAhlen commented ·
@Daniel Ross Nice! I've asked Phil to test the performance of your solution.
0 Likes 0 ·
Phil Factor avatar image Phil Factor commented ·
Soon, soon!
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
@Phil - by way of benchmarking, can you also provide the run duration of the "SlowSolution.sql"?
0 Likes 0 ·
Phil Factor avatar image Phil Factor commented ·
Sure. This takes twice as long on Peso's machine than mine, so it is important to benchmark this to get a feel for the performance. I'd like another entry to time Daniel's entry against. Anyone feel confident enough with their entry yet? Both my TSQL attempts so far have ended in ignominy. Trying to do a new one!
0 Likes 0 ·
Show more comments
Matt Whitfield avatar image
Matt Whitfield answered
Matt v1 Setup: CREATE INDEX IX_OrderBookEvents_1 ON [dbo].[OrderBookEvents] ([OrderBookID], [EventID]) INCLUDE ([EventType], [OrderReferenceNumber], [BuySellIndicator], [UnitPrice], [Quantity]) GO CREATE ASSEMBLY [Challenge6] AUTHORIZATION [dbo] FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300ED54784C0000000000000000E00002210B0108000020000000060000000000001E3F0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000D03E00004B000000004000002803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000241F0000002000000020000000020000000000000000000000000000200000602E7273726300000028030000004000000004000000220000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000002600000000000000000000000000004000004200000000000000000000000000000000003F0000000000004800000002000500B82900001815000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006602730F00000A7D1000000A02281100000A02037D1200000A2A0000133003002900000001000011027B1000000A03027B1200000A6F1300000A0A06162F0306660A027B1000000A06036F1400000A062A00000013300300B100000002000011027B1000000A03027B1200000A6F1300000A0A06162F02160A060B2B3E027B1200000A027B1000000A076F1500000A036F1600000A2D32027B1000000A076F1500000A8C0600001B038C0600001B281700000A2C02072A0717580B07027B1000000A6F1800000A32B4060C2B3E027B1200000A027B1000000A086F1500000A036F1600000A2D28027B1000000A086F1500000A8C0600001B038C0600001B281700000A2C02082A0817590C08162FBE152A46027B1000000A6F1900000A8C0700001B2A46027B1000000A6F1900000A8C0700001B2A260203281A00000A262A001330020019000000010000110203281B00000A0A06152E0C027B1000000A066F1C00000A2AB202281100000A02037D0300000402047D0400000402057D05000004020E047D06000004020E057D070000042A0000133002002F00000003000011037B060000040B1201047B06000004281D00000A0A062C02062A037B040000040C1202047B04000004281E00000A2A1E02281100000A2A00133002003100000003000011037B060000040B1201047B06000004281D00000A0A062C0406155A2A037B040000040C1202047B04000004281E00000A2A1E02281100000A2A0000001B3006009905000004000011732000000A0A732100000A0B732100000A0C7201000070732200000A0D096F2300000A723300007009732400000A130611066F2500000A130711076F2600000A261107166F2700000A13041107176F2700000A1305DE0C11072C0711076F2800000ADCDE0C11062C0711066F2800000ADC1B8D21000001132C112C1672C0000070A2112C171104A2112C1872D0000070A2112C191105A2112C1A72F4000070A2112C282900000A13081108732200000A130911096F2300000A16732A00000A131916131A16732A00000A131B16131C16732A00000A131D16131E16732A00000A131F161320161321722A010070732B00000A132211226F2C00000A7248010070D018000001282D00000A6F2E00000A2611226F2C00000A7258010070D018000001282D00000A6F2E00000A2611226F2C00000A7270010070D009000001282D00000A6F2E00000A2611226F2C00000A728A010070D018000001282D00000A6F2E00000A2611226F2C00000A72AA010070D009000001282D00000A6F2E00000A2611226F2C00000A72C6010070D018000001282D00000A6F2E00000A2611226F2C00000A72E8010070D009000001282D00000A6F2E00000A2611226F2C00000A720E020070D018000001282D00000A6F2E00000A2611226F2C00000A723A020070D009000001282D00000A6F2E00000A2611226F2C00000A7262020070D018000001282D00000A6F2E00000A261109732F00000A1323112372900200706F3000000A112320E80300006F3100000A72BE02007009732400000A132411246F2500000A132538F20200001125176F3200000A130B1125186F3300000A130C1125196F3300000A130D141326141327110B132D112D1759450400000005000000A2000000A20000000F010000384E01000011251A6F3400000A130E11251B6F3500000A130F11251C6F3300000A1310110D110C110E110F11107308000006132806110C11286F3600000A08110D12266F3700000A2D16730A000006733800000A132608110D11266F3900000A07110D12276F3700000A2D16730C000006733800000A132707110D11276F3900000A110E2C0E112611286F3A00000A38BF000000112711286F3A00000A38B100000011251C6F3300000A131007110D6F3B00000A132708110D6F3B00000A132606110C6F3C00000A132911297B070000041110332811297B050000042C0B112611296F3D00000A2B09112711296F3D00000A06110C6F3E00000A262B561129257B070000041110597D070000042B4407110D6F3B00000A132708110D6F3B00000A132606110C6F3C00000A132A112A7B050000042C0B1126112A6F3D00000A2B091127112A6F3D00000A06110C6F3E00000A2611271211121212151216280E00000611261213121412171218280E00000611191111283F00000A2D3C111A11123336111B1113283F00000A2D2B111C11143325111D1115283F00000A2D1A111E11163314111F1117283F00000A2D09112011183BF90000001125166F3300000A130A11226F4000000A132B112B16110A8C180000016F4100000A112B17110D8C180000016F4100000A112B1811118C090000016F4100000A112B1911128C180000016F4100000A112B1A11138C090000016F4100000A112B1B11148C180000016F4100000A112B1C11158C090000016F4100000A112B1D11168C180000016F4100000A112B1E11178C090000016F4100000A112B1F0911188C180000016F4100000A11226F4200000A112B6F4300000A1121175825132120E80300003213112311226F4400000A11226F4500000A161321111113191112131A1113131B1114131C1115131D1116131E1117131F1118132011256F2600000A3A02FDFFFF1121163109112311226F4400000ADE0C11252C0711256F2800000ADCDE0C11242C0711246F2800000ADCDE0C11232C0711236F2800000ADCDE0C11092C0711096F2800000ADCDE0A092C06096F2800000ADC2A00000041AC000002000000390000001E000000570000000C00000000000000020000003000000035000000650000000C00000000000000020000004302000013030000560500000C00000000000000020000003A0200002A030000640500000C0000000000000002000000150200005D030000720500000C0000000000000002000000B2000000CE040000800500000C00000000000000020000001D000000710500008E0500000A0000000000000013300300DD00000005000011026F4600000A0A066F4700000A066F4800000A2C2D066F4900000A0B03077B06000004810900000104077B07000004540516732A00000A81090000010E0416542B5B0316732A00000A81090000010416540516732A00000A81090000010E0416542A066F4900000A0B077B060000040D09037109000001284A00000A2C0D04254A077B0700000458542B12050981090000010E04077B07000004542B0A066F4800000A250C2DBB082D2A2A066F4900000A0B077B06000004057109000001284A00000A2C0E0E04254A077B0700000458542B012A066F4800000A2DCF2A1E02281100000A2A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000E8050000237E0000540600000807000023537472696E6773000000005C0D0000F8040000235553005412000010000000234755494400000064120000B402000023426C6F620000000000000002000001571F020A0904000000FA0133001600000100000029000000070000000D0000000F00000013000000040000004A000000050000000B00000005000000010000000C00000001000000020000000100000000000A0001000000000006008F0088000600B10096000600D200BF000600DE0096000600EA00880006000301960006001F01960006003B01BF000600AE0188000600BA029B0206000903F70206002003F70206003D03F70206005C03F70206007503F70206008E03F7020600A903F7020600C403F7020600DD039B020600F103F70206002A040A0406004A040A041B00AA0400000600C80488000A00F504DA0406000B0596000A002E0518050A004F053C050A00610518050A006C0518050A0088053C050600A40588000600B80588000A00C605CE040A00D005CE040600F10588000600F60588000A001A06CE040A00250618050A009906CE040A00B106CE0400000000010000000000010001000001100019000000050001000100000110002600000005000300080000011000320000000500080009000001100049000000050008000B000001000061000000150008000D00010010006C00000005000D000D0001000A01280001001601300026007F014C0026008B014C002600A0014F002600B60152000600C0014C000606D1014C00568074016C005680D9016C005680E1016C005680E8016C005180EF014C005020000000008618EF00180001006C20000000008100F50022000200A420000000008100FA0022000300612100000000E6012D0138000400732100000000E10147014100040085210000000086007401460004009021000000008600780146000500B521000000008618EF0056000600E42100000000E601C90160000B001F22000000008618EF0068000D00282200000000E601C90160000D006522000000008618EF0068000F007022000000009600FA0189000F00C42800000000910011028D000F00AD29000000008618EF0068001400000001002702000001003002000001003802000001003802000001003802000001003D02000002004902000003005E02000004006C02000005007602000001007F02000002008102000001007F0200000200810200000100830202000200910202000300C70202000400D40202000500E4020200060002000D0004000A0005000A0019002D0141005100EF0068005900EF00A1006100EF00A1006900EF00A1007100EF00A1007900EF00A1008100EF00A1008900EF00A1009100EF00A1009900EF00A600A100EF00A100A900EF00AB00B100EF0068001C00EF00680024000A0128000900EF0068002400160130001C007304BE001C008004CA001C008704D5002C00C901E20009009004ED001C00A004F3001C002D01FD002400F50022002400FA0022001C00B504AB004900BE040D01C100BE041301C900EF0068004400EF0068004C00EF006800D900EF00A100E1005C056800E900EF003301E9007A053A01F90095053F01F9009A0543010101B00568000901BF0548014900EF00AB001101EF00A1001101E5054E01210108065401190174015D013901EF00670139013106A10039014A06AB00F90058066D01F90060061301F90069067201F90074067701440074017D014C007F0685015400EF0018004C0074017D015400740146004C0087049501440087049501540078014600440078019C0149008B06A2011101A106AA014101A806B0011101C306B60149017401BC013901CC06C3011101DA0668005C002D0138004100E00668004100E6063F016400EF0648024900FB06A20108002400700008002800750008002C007A00080030007F000800340084002E003B005B022E00730092022E0023006B022E002B006B022E0033006B022E001B005B022E00430071022E004B006B022E005B006B022E006B008902A001FB007000D100F7001801CA014D0202000A0003000A001100B000B700DB00EA0006011F0127018E013A02410204800000010000001B0000000000000000006804000002000000000000000000000001007F00000000000200000000000000000000000100CE04000000000000000004007D000000003C4D6F64756C653E004368616C6C656E6765362E646C6C00536F727465644C6973746031004163746976654F7264657200417363656E64696E675072696365436F6D70617265720044657363656E64696E675072696365436F6D7061726572004576656E7454797065730053746F72656450726F636564757265730054006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E732E47656E657269630049456E756D657261626C6560310053797374656D2E436F6C6C656374696F6E730049456E756D657261626C650049436F6D7061726572603100456E756D002E63746F72006164645F00696E6465784F665F004C6973746031006F7264657265644C69737400636F6D70617265720049456E756D657261746F72603100476574456E756D657261746F720049456E756D657261746F720053797374656D2E436F6C6C656374696F6E732E49456E756D657261626C652E476574456E756D657261746F72004164640052656D6F7665004F72646572426F6F6B4944004F726465725265666572656E63654E756D6265720053656C6C496E64696361746F7200446563696D616C00556E69745072696365005175616E7469747900436F6D706172650076616C75655F5F00457865637574650043616E63656C0044656C657465005F626174636853697A650047656E65726174654F72646572426F6F6B5374617465005F67657442657374416E645365636F6E644265737400436F6D7061726572006E65774974656D006974656D006F72646572426F6F6B4944006F726465725265666572656E63654E756D6265720073656C6C496E64696361746F7200756E69745072696365007175616E7469747900780079006F7264657265644F7264657273004265737450726963650053797374656D2E52756E74696D652E496E7465726F705365727669636573004F757441747472696275746500426573745175616E74697479005365636F6E64426573745072696365005365636F6E64426573745175616E746974790053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004368616C6C656E6765360042696E61727953656172636800496E73657274006765745F4974656D005265666572656E6365457175616C73006765745F436F756E7400456E756D657261746F720052656D6F7665417400436F6D70617265546F00496E7433320053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650044696374696F6E61727960320053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E640053716C44617461526561646572004578656375746552656164657200446244617461526561646572005265616400476574537472696E670049446973706F7361626C6500446973706F736500537472696E6700436F6E63617400446174615461626C650044617461436F6C756D6E436F6C6C656374696F6E006765745F436F6C756D6E7300547970650052756E74696D655479706548616E646C65004765745479706546726F6D48616E646C650044617461436F6C756D6E0053716C42756C6B436F7079007365745F44657374696E6174696F6E5461626C654E616D65007365745F426174636853697A65004765744279746500476574496E74333200476574426F6F6C65616E00476574446563696D616C0054727947657456616C7565006F705F496E657175616C6974790044617461526F77004E6577526F77007365745F4974656D0044617461526F77436F6C6C656374696F6E006765745F526F7773005772697465546F53657276657200436C656172005265736574004D6F76654E657874006765745F43757272656E74006F705F457175616C6974790000003163006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065003B0000808B5400520055004E00430041005400450020005400410042004C00450020005B00640062006F005D002E005B004F00720064006500720042006F006F006B00530074006100740065005D003B002000530045004C004500430054002000400040005300450052005600450052004E0041004D0045002C002000440042005F004E0041004D00450028002900000F5300650072007600650072003D0000233B0049006E0069007400690061006C00200043006100740061006C006F0067003D0000353B0049006E00740065006700720061007400650064002000530065006300750072006900740079003D0074007200750065003B00001D4F00720064006500720042006F006F006B0053007400610074006500000F4500760065006E0074004900440000174F00720064006500720042006F006F006B00490044000019420065007300740042007500790050007200690063006500001F42006500730074004200750079005100750061006E007400690074007900001B4200650073007400530065006C006C005000720069006300650000214200650073007400530065006C006C005100750061006E00740069007400790000255300650063006F006E006400420065007300740042007500790050007200690063006500002B5300650063006F006E00640042006500730074004200750079005100750061006E00740069007400790000275300650063006F006E0064004200650073007400530065006C006C0050007200690063006500002D5300650063006F006E0064004200650073007400530065006C006C005100750061006E007400690074007900002D5B00640062006F005D002E005B004F00720064006500720042006F006F006B00530074006100740065005D00008237530045004C0045004300540020005B004500760065006E007400490044005D002C00200043004F004E00560045005200540028005B00740069006E00790069006E0074005D002C002000430048004100520049004E0044004500580028005B004500760065006E00740054007900700065005D002C0020002700410045004300440027002900290020004100530020005B004500760065006E00740054007900700065005D002C0020005B004F0072006400650072005200650066006500720065006E00630065004E0075006D006200650072005D002C0020005B004F00720064006500720042006F006F006B00490044005D002C00200043004F004E00560045005200540028005B006200690074005D002C002000430048004100520049004E0044004500580028005B00420075007900530065006C006C0049006E00640069006300610074006F0072005D002C0020002700420053002700290020002D0020003100290020004100530020005B00420075007900530065006C006C0049006E00640069006300610074006F0072005D002C0020005B0055006E0069007400500072006900630065005D002C0020005B005100750061006E0074006900740079005D002000460052004F004D0020005B004F00720064006500720042006F006F006B004500760065006E00740073005D0020004F00520044004500520020004200590020005B004F00720064006500720042006F006F006B00490044005D002C0020005B004500760065006E007400490044005D00010007EAD50A9805944889BBD661A7310EA40008B77A5C561934E089061512090113000615121101120C092001011512110113000520010813000706151219011300070615121101130008200015121D0113000420001221052001011300020608020602030611250920050108080211250807200208120C120C0320000103061118040100000004020000000403000000040400000004E8030000030000011300050115120901120C10112510081011251008042001010E0420010102042001010806151219011300061512080113000B20020813001512110113000620020108130003070108052001130008061512110113000720020813001300021300050002021C1C0320000805070308080808200015115D0113000615115D011300052001081125042001080806070308112508071512690208120C0B151269020815120801120C062002010E126D0420001279032000020420010E080500010E1D0E05200012808D0800011280911180950920021280990E12809105200101126D0420010508042001020805200111250807200201130013010820020213001013010615120801120C0620011301130005200102130007000202112511250520001280A105200201081C0520001280A5062001011280A1062001011280896F072E1512690208120C151269020815120801120C151269020815120801120C126D0E0E127512790E126D0811180808021125081125081125081125081125081125081125081125081125080812808912809D1275127915120801120C15120801120C120C120C120C1280A11D0E11180615120901120C0615121D01120C04200013000D070415121D01120C120C0211250F01000A4368616C6C656E676536000005010000000017010012436F7079726967687420C2A920203230313000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000F83E000000000000000000000E3F0000002000000000000000000000000000000000000000000000003F00000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000CC0200000000000000000000CC0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE000001000000010000001B000000010000001B003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0042C020000010053007400720069006E006700460069006C00650049006E0066006F00000008020000010030003000300030003000340062003000000040000B000100460069006C0065004400650073006300720069007000740069006F006E00000000004300680061006C006C0065006E0067006500360000000000340009000100460069006C006500560065007200730069006F006E000000000031002E0030002E00320037002E0030000000000040000F00010049006E007400650072006E0061006C004E0061006D00650000004300680061006C006C0065006E006700650036002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003000000048000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004300680061006C006C0065006E006700650036002E0064006C006C000000000038000B000100500072006F0064007500630074004E0061006D006500000000004300680061006C006C0065006E0067006500360000000000380009000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E00320037002E003000000000003C000900010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E00320037002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000203F00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WITH PERMISSION_SET = EXTERNAL_ACCESS GO ALTER ASSEMBLY [Challenge6] WITH VISIBILITY = ON GO CREATE PROCEDURE [dbo].[GenerateOrderBookState] AS EXTERNAL NAME [Challenge6].[StoredProcedures].[GenerateOrderBookState] GO Execute: EXEC [dbo].[GenerateOrderBookState] Teardown: DROP INDEX IX_OrderBookEvents_1 ON [dbo].[OrderBookEvents] GO DROP PROCEDURE [dbo].[GenerateOrderBookState] GO DROP ASSEMBLY [Challenge6] Code: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Collections.Generic; sealed class SortedList : IEnumerable { /// /// Constructs a object specifying an IComparer for type T /// /// The IComparer for type T that compares objects public SortedList(IComparer Comparer) { comparer = Comparer; } /// /// Adds an item to the list /// /// The item to add /// The index of the added item int add_(T newItem) { int index = orderedList.BinarySearch(newItem, comparer); if (index < 0) { // If the item isn't present BinarySearch returns the insertion point negated. index = ~index; } orderedList.Insert(index, newItem); return index; } /// /// Gets the index of the item using a BinarySearch /// /// The item to search for /// The index of the item, or it's complement if not found private int indexOf_(T item) { // As this collection can contain one *or more* of the same item, potentialIndex // is only guaranteed to point to an arbitrary item which matches "item" (based on the // comparer). int potentialIndex = orderedList.BinarySearch(item, comparer); // No hint for the search if potentialIndex is negative. if (potentialIndex < 0) potentialIndex = 0; // To find the actual item's index we must search forwards and backwards around // potentialIndex until we find an item with the same reference. // Search forwards for (int i = potentialIndex; i < orderedList.Count; ++i) { // if we have come to an item that the provided IComparer says is different, // then we have searched fare enough already if (comparer.Compare(orderedList[i], item) != 0) { break; } if (object.ReferenceEquals(orderedList[i], item)) { return i; } } // Search backwards for (int i = potentialIndex; i >= 0; --i) { // if we have come to an item that the provided IComparer says is different, // then we have searched fare enough already if (comparer.Compare(orderedList[i], item) != 0) { break; } if (object.ReferenceEquals(orderedList[i], item)) { return i; } } // Item not found return -1; } /// /// The internal list object /// private List orderedList = new List(); /// /// The comparer /// private IComparer comparer; #region IEnumerable Members /// /// Returns an enumerator that iterates through a collection. /// /// An System.Collections.Generic.IEnumerator<T> object that can be used to iterate through the collection. public IEnumerator GetEnumerator() { return orderedList.GetEnumerator(); } #endregion #region IEnumerable Members /// /// Returns an enumerator that iterates through a collection. /// /// An System.Collections.IEnumerator object that can be used to iterate through the collection. System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() { return orderedList.GetEnumerator(); } #endregion /// /// Adds an item to the collection. /// /// The object to add to the collection. public void Add(T item) { add_(item); } /// /// Removes the first occurrence of item from the collection. /// /// The object to remove /// true if item was successfully removed from the collection, otherwise false. This method also returns false if item is not found. public void Remove(T item) { int index = indexOf_(item); if (index != -1) { orderedList.RemoveAt(index); } } } sealed class ActiveOrder { public readonly int OrderBookID; public readonly int OrderReferenceNumber; public readonly bool SellIndicator; public readonly decimal UnitPrice; public int Quantity; /// /// Creates a new class instance. /// public ActiveOrder(int orderBookID, int orderReferenceNumber, bool sellIndicator, decimal unitPrice, int quantity) { OrderBookID = orderBookID; OrderReferenceNumber = orderReferenceNumber; SellIndicator = sellIndicator; UnitPrice = unitPrice; Quantity = quantity; } } sealed class AscendingPriceComparer : IComparer { #region IComparer Members public int Compare(ActiveOrder x, ActiveOrder y) { int i = x.UnitPrice.CompareTo(y.UnitPrice); if (i != 0) { return i; } else { return x.OrderReferenceNumber.CompareTo(y.OrderReferenceNumber); } } #endregion } sealed class DescendingPriceComparer : IComparer { #region IComparer Members public int Compare(ActiveOrder x, ActiveOrder y) { int i = x.UnitPrice.CompareTo(y.UnitPrice); if (i != 0) { return i * -1; } else { return x.OrderReferenceNumber.CompareTo(y.OrderReferenceNumber); } } #endregion } enum EventTypes { Add = 1, Execute = 2, Cancel = 3, Delete = 4 } public partial class StoredProcedures { const int _batchSize = 1000; [Microsoft.SqlServer.Server.SqlProcedure] public static void GenerateOrderBookState() { Dictionary ordersByReferenceNumber = new Dictionary(); Dictionary> buyOrdersByPriceByOrderBookID = new Dictionary>(); Dictionary> sellOrdersByPriceByOrderBookID = new Dictionary>(); using (SqlConnection sqlConn = new SqlConnection("context connection=true;")) { sqlConn.Open(); string serverName, databaseName; using (SqlCommand truncateCommand = new SqlCommand("TRUNCATE TABLE [dbo].[OrderBookState]; SELECT @@SERVERNAME, DB_NAME()", sqlConn)) { using (SqlDataReader reader = truncateCommand.ExecuteReader()) { reader.Read(); serverName = reader.GetString(0); databaseName = reader.GetString(1); } } string externalConnectionString = "Server=" + serverName + ";Initial Catalog=" + databaseName + ";Integrated Security=true;"; using (SqlConnection externalConn = new SqlConnection(externalConnectionString)) { externalConn.Open(); int EventID; EventTypes EventType; int OrderReferenceNumber; int OrderBookID; bool SellIndicator; decimal UnitPrice; int Quantity; decimal BestBuyPrice; int BestBuyQuantity; decimal BestSellPrice; int BestSellQuantity; decimal SecondBestBuyPrice; int SecondBestBuyQuantity; decimal SecondBestSellPrice; int SecondBestSellQuantity; decimal PrevBestBuyPrice = 0; int PrevBestBuyQuantity = 0; decimal PrevBestSellPrice = 0; int PrevBestSellQuantity = 0; decimal PrevSecondBestBuyPrice = 0; int PrevSecondBestBuyQuantity = 0; decimal PrevSecondBestSellPrice = 0; int PrevSecondBestSellQuantity = 0; int outputRowCount = 0; DataTable outputDataTable = new DataTable("OrderBookState"); outputDataTable.Columns.Add("EventID", typeof(Int32)); outputDataTable.Columns.Add("OrderBookID", typeof(Int32)); outputDataTable.Columns.Add("BestBuyPrice", typeof(Decimal)); outputDataTable.Columns.Add("BestBuyQuantity", typeof(Int32)); outputDataTable.Columns.Add("BestSellPrice", typeof(Decimal)); outputDataTable.Columns.Add("BestSellQuantity", typeof(Int32)); outputDataTable.Columns.Add("SecondBestBuyPrice", typeof(Decimal)); outputDataTable.Columns.Add("SecondBestBuyQuantity", typeof(Int32)); outputDataTable.Columns.Add("SecondBestSellPrice", typeof(Decimal)); outputDataTable.Columns.Add("SecondBestSellQuantity", typeof(Int32)); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(externalConn)) { bulkCopy.DestinationTableName = "[dbo].[OrderBookState]"; bulkCopy.BatchSize = _batchSize; using (SqlCommand readCommand = new SqlCommand("SELECT [EventID], CONVERT([tinyint], CHARINDEX([EventType], 'AECD')) AS [EventType], [OrderReferenceNumber], [OrderBookID], CONVERT([bit], CHARINDEX([BuySellIndicator], 'BS') - 1) AS [BuySellIndicator], [UnitPrice], [Quantity] FROM [OrderBookEvents] ORDER BY [OrderBookID], [EventID]", sqlConn)) { using (SqlDataReader reader = readCommand.ExecuteReader()) { while (reader.Read()) { EventType = (EventTypes)reader.GetByte(1); OrderReferenceNumber = reader.GetInt32(2); OrderBookID = reader.GetInt32(3); SortedList sellOrderList = null; SortedList buyOrderList = null; switch (EventType) { case EventTypes.Add: { SellIndicator = reader.GetBoolean(4); UnitPrice = reader.GetDecimal(5); Quantity = reader.GetInt32(6); ActiveOrder ao = new ActiveOrder(OrderBookID, OrderReferenceNumber, SellIndicator, UnitPrice, Quantity); ordersByReferenceNumber.Add(OrderReferenceNumber, ao); if (!sellOrdersByPriceByOrderBookID.TryGetValue(OrderBookID, out sellOrderList)) { sellOrderList = new SortedList(new AscendingPriceComparer()); sellOrdersByPriceByOrderBookID.Add(OrderBookID, sellOrderList); } if (!buyOrdersByPriceByOrderBookID.TryGetValue(OrderBookID, out buyOrderList)) { buyOrderList = new SortedList(new DescendingPriceComparer()); buyOrdersByPriceByOrderBookID.Add(OrderBookID, buyOrderList); } if (SellIndicator) { sellOrderList.Add(ao); } else { buyOrderList.Add(ao); } } break; case EventTypes.Execute: case EventTypes.Cancel: { Quantity = reader.GetInt32(6); buyOrderList = buyOrdersByPriceByOrderBookID[OrderBookID]; sellOrderList = sellOrdersByPriceByOrderBookID[OrderBookID]; ActiveOrder ao = ordersByReferenceNumber[OrderReferenceNumber]; if (ao.Quantity == Quantity) { if (ao.SellIndicator) { sellOrderList.Remove(ao); } else { buyOrderList.Remove(ao); } ordersByReferenceNumber.Remove(OrderReferenceNumber); } else { ao.Quantity -= Quantity; } } break; case EventTypes.Delete: { buyOrderList = buyOrdersByPriceByOrderBookID[OrderBookID]; sellOrderList = sellOrdersByPriceByOrderBookID[OrderBookID]; ActiveOrder ao = ordersByReferenceNumber[OrderReferenceNumber]; if (ao.SellIndicator) { sellOrderList.Remove(ao); } else { buyOrderList.Remove(ao); } ordersByReferenceNumber.Remove(OrderReferenceNumber); } break; } _getBestAndSecondBest(buyOrderList, out BestBuyPrice, out BestBuyQuantity, out SecondBestBuyPrice, out SecondBestBuyQuantity); _getBestAndSecondBest(sellOrderList, out BestSellPrice, out BestSellQuantity, out SecondBestSellPrice, out SecondBestSellQuantity); if (PrevBestBuyPrice != BestBuyPrice || PrevBestBuyQuantity != BestBuyQuantity || PrevBestSellPrice != BestSellPrice || PrevBestSellQuantity != BestSellQuantity || PrevSecondBestBuyPrice != SecondBestBuyPrice || PrevSecondBestBuyQuantity != SecondBestBuyQuantity || PrevSecondBestSellPrice != SecondBestSellPrice || PrevSecondBestSellQuantity != SecondBestSellQuantity) { EventID = reader.GetInt32(0); DataRow outputRow = outputDataTable.NewRow(); outputRow[0] = EventID; outputRow[1] = OrderBookID; outputRow[2] = BestBuyPrice; outputRow[3] = BestBuyQuantity; outputRow[4] = BestSellPrice; outputRow[5] = BestSellQuantity; outputRow[6] = SecondBestBuyPrice; outputRow[7] = SecondBestBuyQuantity; outputRow[8] = SecondBestSellPrice; outputRow[9] = SecondBestSellQuantity; outputDataTable.Rows.Add(outputRow); if (++outputRowCount >= _batchSize) { bulkCopy.WriteToServer(outputDataTable); outputDataTable.Clear(); outputRowCount = 0; } PrevBestBuyPrice = BestBuyPrice; PrevBestBuyQuantity = BestBuyQuantity; PrevBestSellPrice = BestSellPrice; PrevBestSellQuantity = BestSellQuantity; PrevSecondBestBuyPrice = SecondBestBuyPrice; PrevSecondBestBuyQuantity = SecondBestBuyQuantity; PrevSecondBestSellPrice = SecondBestSellPrice; PrevSecondBestSellQuantity = SecondBestSellQuantity; } } if (outputRowCount > 0) { bulkCopy.WriteToServer(outputDataTable); } } } } } } } private static void _getBestAndSecondBest(IEnumerable orderedOrders, out decimal BestPrice, out int BestQuantity, out decimal SecondBestPrice, out int SecondBestQuantity) { IEnumerator enumerator = orderedOrders.GetEnumerator(); enumerator.Reset(); ActiveOrder ao; if (enumerator.MoveNext()) { ao = enumerator.Current; BestPrice = ao.UnitPrice; BestQuantity = ao.Quantity; SecondBestPrice = 0; SecondBestQuantity = 0; } else { BestPrice = 0; BestQuantity = 0; SecondBestPrice = 0; SecondBestQuantity = 0; return; } bool b; while ((b = enumerator.MoveNext())) { ao = enumerator.Current; decimal price = ao.UnitPrice; if (price == BestPrice) { BestQuantity += ao.Quantity; } else { SecondBestPrice = price; SecondBestQuantity = ao.Quantity; break; } } if (!b) { return; } while (enumerator.MoveNext()) { ao = enumerator.Current; if (ao.UnitPrice == SecondBestPrice) { SecondBestQuantity += ao.Quantity; } else { return; } } } };
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image Phil Factor commented ·
I've done a SQL one without loops but it runs slower than my cursor solution!
1 Like 1 ·
JAhlen avatar image JAhlen commented ·
Nice! I hope we will also see some non-SQL CLR solutions soon.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
I hope so, but I know I won't have time to do a good SQL one - this sort of problem requires taking apart the problem domain carefully and re-working it as a set based problem. Given the requirement for in-order processing, and state management, I think it would take quite a bit of time. Well, for someone with a small brain like me, anyway.
0 Likes 0 ·
Daniel Ross avatar image Daniel Ross commented ·
@matt, good work. As for a SQL, there is no way i would be able to do it using SQL without loops but I would like to see one.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@Daniel - I'm pretty sure I would be able to do one without loops given enough time - but that's something I don't have a lot of at the moment! :)
0 Likes 0 ·
Phil Factor avatar image
Phil Factor answered
**Phil Factor Cursor 1C** I think I'm going for the 'Best Cursor Solution' prize. This modification makes the slow cursor solution run in 21 minutes on my machine. (runs twice as fast). It is really just a test-bed for a 'quirky' solution that unfortunately doesn't run error-free because the Quirky Update seems to allow only one correlated subquery (the next one returns null at the slightest provocation). Al I'm doing is to eliminate all the events that shouldn't require a complete recalculation of the first and second places. If, after all, the event involves a BUY, then how can that affect the best SELL prices?. (and so on) This eliminates most of the events that require a recalculation of the best and second best ... DECLARE @EventID INT , @EventType CHAR(1) , @OrderReferenceNumber INT , @OrderBookID INT , @BuySellIndicator CHAR(1) , @UnitPrice DECIMAL(18, 2) , @BookPrice DECIMAL(18, 2) , @Quantity INT , @BestBuyPrice DECIMAL(18, 2) , @BestBuyQuantity INT , @BestSellPrice DECIMAL(18, 2) , @BestSellQuantity INT , @SecondBestBuyPrice DECIMAL(18, 2) , @SecondBestBuyQuantity INT , @SecondBestSellPrice DECIMAL(18, 2) , @SecondBestSellQuantity INT , @PrevBestBuyPrice DECIMAL(18, 2) , @PrevBestBuyQuantity INT , @PrevBestSellPrice DECIMAL(18, 2) , @PrevBestSellQuantity INT , @PrevSecondBestBuyPrice DECIMAL(18, 2) , @PrevSecondBestBuyQuantity INT , @PrevSecondBestSellPrice DECIMAL(18, 2) , @PrevSecondBestSellQuantity INT , @PrevOrderBookID INT , @Impact INT TRUNCATE TABLE OrderBookState DROP TABLE #OrderBook CREATE TABLE [dbo].#OrderBook ( [EventID] [int] NOT NULL , [EventType] [char](1) NOT NULL , [OrderReferenceNumber] [int] NOT NULL , [OrderBookID] [int] NOT NULL , [BuySellIndicator] [char](1) NOT NULL , [UnitPrice] [decimal](18, 2) NOT NULL , [Quantity] [int] NOT NULL , currentQuantity INT NULL , BookPrice [decimal](18, 2) NULL , ValidToEventID INT NULL, ) CREATE CLUSTERED INDEX upwardsIndex ON #OrderBook(OrderReferenceNumber ASC,eventID ASC) INSERT INTO #OrderBook ( EventID , EventType , OrderReferenceNumber , OrderBookID , BuySellIndicator , UnitPrice , Quantity ) SELECT EventID , EventType , OrderReferenceNumber , OrderBookID , BuySellIndicator , UnitPrice , Quantity FROM dbo.OrderBookEvents ORDER BY eventid DECLARE @OldOrn INT , @currentQuantity INT UPDATE #OrderBook SET @currentQuantity = CurrentQuantity = CASE WHEN OrderReferenceNumber = @OldORN THEN @currentQuantity ELSE 0 END + CASE EventType WHEN 'a' THEN +quantity WHEN 'C' THEN -quantity WHEN 'e' THEN -quantity WHEN 'd' THEN -@currentQuantity ELSE 0 END , @bookPrice = bookprice = CASE EventType WHEN 'a' THEN Unitprice ELSE @bookprice END , @OldOrn = OrderReferenceNumber DROP INDEX #OrderBook.upwardsIndex --CREATE CLUSTERED INDEX EventidIndex ON #OrderBook(orderbookid,eventID asc) SET NOCOUNT ON DECLARE @ActiveOrders TABLE ( OrderBookID INT NOT NULL , OrderReferenceNumber INT NOT NULL PRIMARY KEY , BuySellIndicator CHAR(1) NOT NULL , UnitPrice DECIMAL(18, 2) NOT NULL , Quantity INT NOT NULL ) DECLARE SlowSolution CURSOR fast_forward FOR SELECT [EventID] ,[EventType] ,[OrderReferenceNumber] ,[OrderBookID] ,[BuySellIndicator] ,[UnitPrice] ,[BookPrice] ,[Quantity] FROM [#OrderBook] ORDER BY [OrderBookID],[EventID] FOR READ ONLY SET @PrevBestBuyPrice = 0 SET @PrevBestBuyQuantity = 0 SET @PrevBestSellPrice = 0 SET @PrevBestSellQuantity = 0 SET @PrevSecondBestBuyPrice = 0 SET @PrevSecondBestBuyQuantity = 0 SET @PrevSecondBestSellPrice = 0 SET @PrevSecondBestSellQuantity = 0 SET @PrevOrderBookID = 0 OPEN SlowSolution FETCH NEXT FROM SlowSolution INTO @EventID, @EventType, @OrderReferenceNumber, @OrderBookID, @BuySellIndicator, @UnitPrice, @bookPrice, @Quantity WHILE @@FETCH_STATUS = 0 BEGIN IF @EventType = 'A' BEGIN INSERT INTO @ActiveOrders ( OrderBookID , OrderReferenceNumber , BuySellIndicator , UnitPrice , Quantity ) VALUES ( @OrderBookID , @OrderReferenceNumber , @BuySellIndicator , @UnitPrice , @Quantity ) END IF @EventType IN ( 'E', 'C' ) BEGIN UPDATE @ActiveOrders SET Quantity = Quantity - @Quantity WHERE OrderReferenceNumber = @OrderReferenceNumber DELETE FROM @ActiveOrders WHERE OrderReferenceNumber = @OrderReferenceNumber AND Quantity = 0 END IF @EventType = 'D' BEGIN DELETE FROM @ActiveOrders WHERE OrderReferenceNumber = @OrderReferenceNumber END SET @BestBuyPrice = 0 SET @BestBuyQuantity = 0 SET @BestSellPrice = 0 SET @BestSellQuantity = 0 SET @SecondBestBuyPrice = 0 SET @SecondBestBuyQuantity = 0 SET @SecondBestSellPrice = 0 SET @SecondBestSellQuantity = 0 IF @BuySellIndicator = 'B' AND @PrevOrderBookID = @OrderBookID BEGIN --if it is a buy on the same book, then nothing will affect the sell prices or quantities SELECT @BestSellPrice = @PrevBestSellPrice , @BestSellQuantity = @PrevBestSellQuantity , @SecondBestSellPrice = @PrevSecondBestSellPrice , @SecondBestSellQuantity = @PrevSecondBestSellQuantity IF @BookPrice < @PrevSecondBestBuyPrice--then the price and quantity aren't affected SELECT @BestBuyPrice = @PrevBestBuyPrice , @BestBuyQuantity = @PrevBestBuyQuantity , @SecondBestBuyPrice = @PrevSecondBestBuyPrice , @SecondBestBuyQuantity = @PrevSecondBestBuyQuantity ELSE IF @eventtype = 'A' AND @BookPrice < @PrevBestBuyPrice AND @BookPrice > @PrevSecondBestBuyPrice --new second-best price SELECT @BestBuyPrice = @PrevBestBuyPrice , @BestBuyQuantity = @PrevBestBuyQuantity , @SecondBestBuyPrice = @BookPrice , @SecondBestBuyQuantity = @Quantity ELSE IF @BookPrice = @PrevBestBuyPrice --SOME more quantity FOR the best price SELECT @BestBuyPrice = @PrevBestBuyPrice , @BestBuyQuantity = @PrevBestBuyQuantity + CASE @eventtype WHEN 'A' THEN @quantity WHEN 'D' THEN -@PrevBestBuyQuantity ELSE -@Quantity END , @SecondBestBuyPrice = CASE WHEN @BestBuyQuantity = 0 THEN 0 ELSE @PrevSecondBestBuyPrice END ,--invalidate the second best @SecondBestBuyQuantity = @PrevSecondBestBuyQuantity ELSE IF @BookPrice = @PrevSecondBestBuyPrice --SOME more quantity FOR the second-best price SELECT @BestBuyPrice = @PrevBestBuyPrice , @BestBuyQuantity = @PrevBestBuyQuantity , @SecondBestBuyPrice = @BookPrice , @SecondBestBuyQuantity = @PrevSecondBestBuyQuantity + CASE @eventtype WHEN 'A' THEN @quantity WHEN 'D' THEN -@PrevSecondBestBuyQuantity ELSE -@Quantity END ELSE IF @eventtype = 'A' AND @BookPrice > @PrevBestBuyPrice --new best price SELECT @BestBuyPrice = @BookPrice , @BestBuyQuantity = @Quantity , @SecondBestBuyPrice = @PrevBestBuyPrice , @SecondBestBuyQuantity = @PrevBestBuyQuantity END ELSE IF @BuySellIndicator = 'S' AND @PrevOrderBookID = @OrderBookID BEGIN --if it is a sell on the same book, then nothing will affect the buy prices or quantities SELECT @BestBuyPrice = @PrevBestBuyPrice , @BestBuyQuantity = @PrevBestBuyQuantity , @SecondBestBuyPrice = @PrevSecondBestBuyPrice , @SecondBestBuyQuantity = @PrevSecondBestBuyQuantity IF @BookPrice > @PrevSecondBestSellPrice AND @PrevSecondBestSellPrice>0--then the price and quantity aren't affected SELECT @BestSellPrice = @PrevBestSellPrice , @BestSellQuantity = @PrevBestSellQuantity , @SecondBestSellPrice = @PrevSecondBestSellPrice , @SecondBestSellQuantity = @PrevSecondBestSellQuantity ELSE IF @eventtype = 'A' AND @BookPrice < @PrevBestSellPrice AND @BookPrice > @PrevSecondBestSellPrice AND @PrevSecondBestSellPrice>0 --new second-best price SELECT @BestSellPrice = @PrevBestSellPrice , @BestSellQuantity = @PrevBestSellQuantity , @SecondBestSellPrice = @BookPrice , @SecondBestSellQuantity = @Quantity ELSE IF @BookPrice = @PrevBestSellPrice --SOME more quantity FOR the best price SELECT @BestSellPrice = @PrevBestSellPrice , @BestSellQuantity = @PrevBestSellQuantity + CASE @eventtype WHEN 'A' THEN @quantity WHEN 'D' THEN -@PrevBestSellQuantity ELSE -@Quantity END , @SecondBestSellPrice = CASE WHEN @BestSellQuantity = 0 THEN 0 ELSE @PrevSecondBestSellPrice END ,--invalidate the second best @SecondBestSellQuantity = @PrevSecondBestSellQuantity ELSE IF @BookPrice = @PrevSecondBestSellPrice --SOME more quantity FOR the second-best price SELECT @BestSellPrice = @PrevBestSellPrice , @BestSellQuantity = @PrevBestSellQuantity , @SecondBestSellPrice = @BookPrice , @SecondBestSellQuantity = @PrevSecondBestSellQuantity + CASE @eventtype WHEN 'A' THEN @quantity WHEN 'D' THEN -@PrevSecondBestSellQuantity ELSE -@Quantity END ELSE IF @eventtype = 'A' AND @BookPrice < @PrevBestSellPrice --new best price SELECT @BestSellPrice = @BookPrice , @BestSellQuantity = @Quantity , @SecondBestSellPrice = @PrevBestSellPrice , @SecondBestSellQuantity = @PrevBestSellQuantity END IF @BestBuyPrice = 0 OR @BestBuyQuantity = 0 --flag that we need to recalculate SELECT TOP 1 @BestBuyPrice = UnitPrice--there is a change , @BestBuyQuantity = SUM(Quantity) FROM @ActiveOrders WHERE BuySellIndicator = 'B' AND OrderBookID = @OrderBookID GROUP BY UnitPrice ORDER BY UnitPrice DESC IF @BestSellPrice = 0 OR @BestSellQuantity = 0--flag that we need to recalculate SELECT TOP 1 @BestSellPrice = UnitPrice , @BestSellQuantity = SUM(Quantity) FROM @ActiveOrders WHERE BuySellIndicator = 'S' AND OrderBookID = @OrderBookID GROUP BY UnitPrice ORDER BY UnitPrice ASC IF @SecondBestBuyPrice = 0 OR @SecondBestBuyQuantity = 0--flag that we need to recalculate SELECT TOP 1 @SecondBestBuyPrice = UnitPrice , @SecondBestBuyQuantity = SUM(Quantity) FROM @ActiveOrders WHERE BuySellIndicator = 'B' AND OrderBookID = @OrderBookID AND UnitPrice < @BestBuyPrice GROUP BY UnitPrice ORDER BY UnitPrice DESC IF @SecondBestSellPrice = 0 OR @SecondBestSellQuantity = 0--flag that we need to recalculate SELECT TOP 1 @SecondBestSellPrice = UnitPrice , @SecondBestSellQuantity = SUM(Quantity) FROM @ActiveOrders WHERE BuySellIndicator = 'S' AND OrderBookID = @OrderBookID AND UnitPrice > @BestSellPrice GROUP BY UnitPrice ORDER BY UnitPrice ASC IF ( @PrevBestBuyPrice @BestBuyPrice OR @PrevBestBuyQuantity @BestBuyQuantity OR @PrevBestSellPrice @BestSellPrice OR @PrevBestSellQuantity @BestSellQuantity OR @PrevSecondBestBuyPrice @SecondBestBuyPrice OR @PrevSecondBestBuyQuantity @SecondBestBuyQuantity OR @PrevSecondBestSellPrice @SecondBestSellPrice OR @PrevSecondBestSellQuantity @SecondBestSellQuantity ) BEGIN INSERT INTO [OrderBookState] ( [EventID] , [OrderBookID] , [BestBuyPrice] , [BestBuyQuantity] , [BestSellPrice] , [BestSellQuantity] , [SecondBestBuyPrice] , [SecondBestBuyQuantity] , [SecondBestSellPrice] , [SecondBestSellQuantity] ) VALUES ( @EventID , @OrderBookID , @BestBuyPrice , @BestBuyQuantity , @BestSellPrice , @BestSellQuantity , @SecondBestBuyPrice , @SecondBestBuyQuantity , @SecondBestSellPrice , @SecondBestSellQuantity ) SET @PrevBestBuyPrice = @BestBuyPrice SET @PrevBestBuyQuantity = @BestBuyQuantity SET @PrevBestSellPrice = @BestSellPrice SET @PrevBestSellQuantity = @BestSellQuantity SET @PrevSecondBestBuyPrice = @SecondBestBuyPrice SET @PrevSecondBestBuyQuantity = @SecondBestBuyQuantity SET @PrevSecondBestSellPrice = @SecondBestSellPrice SET @PrevSecondBestSellQuantity = @SecondBestSellQuantity SET @PrevOrderBookID = @OrderBookID END --PRINT @EventID FETCH NEXT FROM SlowSolution INTO @EventID, @EventType, @OrderReferenceNumber, @OrderBookID, @BuySellIndicator, @UnitPrice, @bookPrice, @Quantity END CLOSE SlowSolution DEALLOCATE SlowSolution
12 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image Phil Factor commented ·
Now fixed. It was a little unkind to entirely eliminate it from the competition just for two wrong rows in 196967.
1 Like 1 ·
JAhlen avatar image JAhlen commented ·
@Phil Good improvement! Could you do a test run of all the solutions so far and publish?
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@JAhlen - if you want the test harness, drop me an email...
0 Likes 0 ·
JAhlen avatar image JAhlen commented ·
Would be interesting to see how a SSIS solution would perform. My guess is that SSIS could win this challenge.
0 Likes 0 ·
Daniel Ross avatar image Daniel Ross commented ·
@jahlen - funny you should say that, I've got a SSIS solution, it is quicker than my clr on a single core, a better server would be much faster.
0 Likes 0 ·
Show more comments
Daniel Ross avatar image
Daniel Ross answered
EDIT: I had the wrong output on the script task, i hadnt set the precision to 2. I checked the results before I uploaded this time as well (should have before) lol, I guess that we can't post xml here:) especially if it is over 5,00 lines long. Who would have thought? @Matt my CLR takes about 13 seconds, and the package takes only 6 on my PC. The logic of the script is exactly the same as the CLR version, except for the data flows. I tried doing a multi parrallel solution, but it was the same performance as the CLR, i think the bottleneck was too much. The package can be downloaded from the link below, just set the connection and you should be OK. http://www.mediafire.com/file/o5xqcdupa0mqm31/Challenge6.zip
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@Daniel - Ok cool - should be in for a battle then :) I'm doing a multi-threaded CLR one at the moment, which is probably about as fast as I'm ever going to get it to go...
0 Likes 0 ·
JAhlen avatar image JAhlen commented ·
@Matt - I'm looking forward to see it!
0 Likes 0 ·
Phil Factor avatar image Phil Factor commented ·
I'm having a struggle to come up with a fast TSQL solution that isn't cursor-based but I'm not quite beaten yet. I've come up with a very pretty slow one but it is so slow that I'm too ashamed to post it.
0 Likes 0 ·
Daniel Ross avatar image Daniel Ross commented ·
@Matt - awesome, lol but can you break this site with it?
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Hahaha - Phil - I think we need a new prize category for the next challenge :)
0 Likes 0 ·
Show more comments
Matt Whitfield avatar image
Matt Whitfield answered
Matt v2 - The 'don't do this at home kinds' version. I wouldn't put this in production, because it's an UNSAFE assembly, due to threading. But it does run a bit quicker than my single thread version. Setup: CREATE INDEX IX_OrderBookEvents_1 ON [dbo].[OrderBookEvents] ([OrderBookID], [EventID]) INCLUDE ([EventType], [OrderReferenceNumber], [BuySellIndicator], [UnitPrice], [Quantity]) GO CREATE ASSEMBLY [Challenge6] AUTHORIZATION [dbo] FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300631E7D4C0000000000000000E00002210B010800002800000006000000000000EE47000000200000006000000000400000200000000200000400000000000000040000000000000000A0000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000009C4700004F000000006000002803000000000000000000000000000000000000008000000C0000001C4700001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000F4270000002000000028000000020000000000000000000000000000200000602E72737263000000280300000060000000040000002A0000000000000000000000000000400000402E72656C6F6300000C0000000080000000020000002E00000000000000000000000000004000004200000000000000000000000000000000D0470000000000004800000002000500282D0000F41900000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000033002004F000000000000000002037D0100000402047D0200000402057D03000004020E047D04000004020E057D05000004020E067D06000004020E077D07000004020E087D08000004020E097D09000004020E0A7D0A0000042A0013300300CD0000000100001100036F1100000A0A0616027B010000048C220000016F1200000A000617027B020000048C220000016F1200000A000618027B030000048C070000016F1200000A000619027B040000048C220000016F1200000A00061A027B050000048C070000016F1200000A00061B027B060000048C220000016F1200000A00061C027B070000048C070000016F1200000A00061D027B080000048C220000016F1200000A00061E027B090000048C070000016F1200000A00061F09027B0A0000048C220000016F1200000A00060B2B00072A7202731300000A7D0C00000402281400000A000002037D0B000004002A000013300300790000000200001100160A027B0C0000046F1500000A17590B2B5000060706591763580C027B0B000004027B0C000004086F1600000A036F040000060D0916FE0116FE01130511052D050813042B2F0916FE0416FE01130511052D08000817580A002B06000817590B00000607FE0216FE01130511052DA3066613042B0011042A00000013300200160000000300001100020328080000060A06163203062B01150C2B00082A000013300100160000000400001100027B0C0000046F1700000A8C0300001B0A2B00062A000013300100160000000500001100027B0C0000046F1700000A8C0300001B0A2B00062A000013300300280000000600001100020328080000060A0616FE0416FE010B072D050006660A00027B0C00000406036F1800000A002A13300200210000000600001100020328090000060A0615FE010B072D0F00027B0C000004066F1900000A00002ABE02281400000A000002037D0D00000402047D0E00000402057D0F000004020E047D10000004020E057D11000004002AC602731A00000A7D1800000402731400000A7D1900000402167D1A0000040216731B00000A7D1B00000402281400000A002A001B3003008102000007000011000274070000020A067B17000004731C00000A0B00076F1D00000A00067B190000040C067B180000040D07731E00000A1304007201000070731F00000A130511056F2000000A721F000070D022000001282100000A6F2200000A2611056F2000000A722F000070D022000001282100000A6F2200000A2611056F2000000A7247000070D007000001282100000A6F2200000A2611056F2000000A7261000070D022000001282100000A6F2200000A2611056F2000000A7281000070D007000001282100000A6F2200000A2611056F2000000A729D000070D022000001282100000A6F2200000A2611056F2000000A72BF000070D007000001282100000A6F2200000A2611056F2000000A72E5000070D022000001282100000A6F2200000A2611056F2000000A7211010070D007000001282100000A6F2200000A2611056F2000000A7239010070D022000001282100000A6F2200000A26110472670100706F2300000A00110420D00700006F2400000A0011056F2500000A1306067B1B000004130738C3000000001613081107156F2600000A260825130B282700000A000011076F2800000A26096F2900000A1309096F2A00000A00067B1A000004130800DE09110B282B00000A00DC00001109130C16130D2B29110C110D8F020000027102000002130A001106120A110528020000066F2C00000A0000110D1758130D110D110C8E69FE04130E110E2DC911082D1011066F2D00000A20D0070000FE042B0116130E110E2D1400110411056F2E00000A0011056F2F00000A0000110816FE01130E110E2D03002B090017130E3835FFFFFF00DE14110414FE01130E110E2D0811046F3000000A00DC0000DE120714FE01130E110E2D07076F3000000A00DC002A000000414C0000020000009E01000023000000C101000009000000000000000200000032000000230200005502000014000000000000000200000014000000590200006D0200001200000000000000133002003E0000000800001100027B100000040C1202037B10000004283100000A0A0616FE010D092D0500060B2B1900027B0E00000413041204037B0E000004283200000A0B2B00072A000013300200400000000800001100027B100000040C1202037B10000004283100000A0A0616FE010D092D070006155A0B2B1900027B0E00000413041204037B0E000004283200000A0B2B00072A1B300B00730400000900001100733400000A0A140B140C7295010070731C00000A0D00096F1D00000A0072C701007009733500000A13060011066F3600000A13070011076F3700000A261107166F3800000A13041107176F3800000A130500DE14110714FE01132811282D0811076F3000000A00DC0000DE14110614FE01132811282D0811066F3000000A00DC001B8D3600000113291129167254020070A21129171104A21129187264020070A21129191105A211291A7288020070A21129283900000A1308730F0000061309110911087D1700000414FE0610000006733A00000A733B00000A130A110A196F3C00000A00110A11096F3D00000A0011097B18000004130B16733E00000A131A16131B16733E00000A131C16131D16733E00000A131E16131F16733E00000A132016132115132216132311097B19000004132472BE02007009733500000A13250011256F3600000A1307003895020000001107176F3F00000A130C1107186F3F00000A130D1107196F3F00000A130E110E1122FE01132811282D260014FE0612000006730300000673070000060B14FE0611000006730300000673070000060C00110E1322110C132A112A1F41594505000000050000002201000070000000E300000070000000381D0100000011071A6F3F00000A1F42FE0116FE01130F11071B6F4000000A131011071C6F3F00000A1311110E110D110F11101111730E000006132606110D11266F4100000A00110F16FE01132811282D0D000811266F0C00000600002B0B000711266F0C00000600000038B20000000011071C6F3F00000A131106110D6F4200000A132611267B110000041111FE0116FE01132811282D350011267B0F00000416FE01132811282D0D000811266F0D00000600002B0B000711266F0D000006000006110D6F4300000A26002B12001126257B110000041111597D1100000400002B3F0006110D6F4200000A132611267B0F00000416FE01132811282D0D000811266F0D00000600002B0B000711266F0D000006000006110D6F4300000A26002B00071212121312161217281400000600081214121512181219281400000600111A1112284400000A2D3B111B11133335111C1114284400000A2D2A111D11153324111E1116284400000A2D19111F1117331311201118284400000A2D0811211119FE012B0116132811283A8D0000000012271107166F3F00000A110E11121113111411151116111711181119280100000600112425132B282700000A0000110B11276F4500000A001123175825132320E8030000FE04132811282D120011097B1B0000046F4600000A261613230000DE09112B282B00000A00DC001112131A1113131B1114131C1115131D1116131E1117131F1118132011191321000011076F3700000A132811283A5BFDFFFF00DE14110714FE01132811282D0811076F3000000A00DC0000DE14112514FE01132811282D0811256F3000000A00DC00112425132B282700000A00001109177D1A00000411097B1B0000046F4600000A2600DE09112B282B00000A00DC00110A6F4700000A0000DE120914FE01132811282D07096F3000000A00DC002A0041AC0000020000003500000020000000550000001400000000000000020000002B000000420000006D0000001400000000000000020000008603000034000000BA0300000900000000000000020000004B010000AE020000F903000014000000000000000200000041010000D00200001104000014000000000000000200000031040000190000004A04000009000000000000000200000016000000490400005F040000120000000000000013300300180100000A00001100026F4800000A0A066F4900000A00066F4A00000A16FE01130411042D2F00066F4B00000A0B03077B10000004810700000104077B11000004540516733E00000A81070000010E041654002B25000316733E00000A81070000010416540516733E00000A81070000010E04165438A50000002B4700066F4B00000A0B077B100000040D09037107000001284C00000A16FE01130411042D0F0004254A077B110000045854002B1300050981070000010E04077B11000004542B0F00066F4A00000A250C130411042DAB08130411042D03002B442B3600066F4B00000A0B077B10000004057107000001284C00000A16FE01130411042D10000E04254A077B110000045854002B03002B0D00066F4A00000A130411042DBE2A1E02281400000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000A0070000237E00000C0800003C0A000023537472696E677300000000481200007C04000023555300C4160000100000002347554944000000D41600002003000023426C6F620000000000000002000001571F020A0900000000FA0133001600000100000039000000080000001D0000001500000028000000020000004C000000060000000C0000000A0000000100000006000000010000000300000000000A00010000000000060095008E0006009F008E000600B1008E000600D300B8000600F400E100060000018E00060019018E000A00C301B7010A00CB01B7010600E7018E000600F4018E0006003B02B80006004E02B80006006A02E1000E001303B80006004A0339030600440525050600930581050600AA0581050600C70581050600E60581050600FF05810506001806810506003306810506004E06810506006706250506007B0681050600A70694067300BB0600000600EA06CA0606000A07CA0606003307250506004907250506005B078E0033007D0700000A00AE0798070A00CF07BC070A00E10798070A00ED07B70106000E088E00060013088E000A003708B7010A006908B7010600840839030600970839030600A50839030A00CE08B7010600F7088E000A003009150906004609B8000A00530998070A005E0998070A007A09BC07060096098E000600A40939030600BD0939030600C409390300000000010000000000010001000801100019000000050001000100000100002300000009000B00030000011000390000000D000B000700000110004F0000000D000D000E00000100005B000000190012000F0000011000660000000D0017000F0001001000740000000D001C00100006000501110006000D01110006002101140006002E01110006003E01140006004C01110006005D01140006007001110006008601140006009A01110001001802510001004202610026000D0111002600AE0211002600C3027D002600D10214000600DB0211000606E40211005680A3028A005680EC028A005680F4028A005680FB028A0006000203A20006001B03A50006002503AD00060030037D0006005B03B0005180690311005180740311005020000000008618B10118000100AC20000000008300D5012A000B000000000003008618B10131000C00000000000300C601E00137000E00000000000300C60102023F001000000000000300C6010E024B0014008521000000008618B10155001500A42100000000810024025B0016002C2200000000810032025B001700502200000000E6015C0269001800742200000000E1017602720018009822000000008600A30277001800CC22000000008600A70277001900F922000000008618B10180001A002923000000008618B101B4001F005C230000000091008503C2001F0038260000000091009103C70020008426000000009100A603C7002200D026000000009600BC03CF002400FC2B000000009100D303D3002400202D000000008618B101B400290000000100E90300000200F10300000300FD03000004000A04000005001A04000006002804000007003904000008004C0400000900620400000A007604000001008D04000001009D0400000200A40400000100AB0400000200AD0400000100AB0400000200AD0400000300AF04000004009D0400000100B80400000100BF0400000100CA0400000100D00400000100D00400000100D00400000100F10300000200D50400000300EA0400000400F804000005000205000001000B0500000100AB0400000200AD0400000100AB0400000200AD04000001000D05020002001B05020003005105020004005E05020005006E05040006000400150029005C0272008900B101B4009100B101E7009900B101E700A100B101E700A900B101E700B100B101E700B900B101E700C100B101E700C900B101E700D100B101EC00D900B101E700E100B101F100F100B101F700F900B101B4000101B101FC004900540703014100610708011400B101B4001900B101B40014006A071C0114007407200114005C02350114008807550114008F07F7002400B101B4008100B101EC002101B101E7002901DC07B4003101B10168014900B101E700490002086F014101250875013901A3027E0131014208E70031015B08F70049007B08880161018F088E0169019F08C2007101B50893012400BB0897012400C308B4006901C908C2005901A3029D0179016A071C013101E908A3014900C308B40081010309B40039000B09CE0111010B09D4018901B101B4002C00B101B4009901B101F00199016C09F801A90187099301A9018C09FE01B1019D090302B901B1013100C101B1010902C101D3091002C101E00917023900B101F700A901E609D401A901EF091C022C00A30222022C0074072A022C00A70231023900FA0937022400080A3F027101100A9301C101140AB4000C005C029B027100B508B4007100190A93013400220AAB0239002E0A370208004C008E00080050009300080054009800080058009D0008007000B80008007400BD002E003B00BF022E001B00BF022E002300CF022E002B00CF022E003300CF022E005B00CF022E007B00FF022E004B00CF022E004300D5022E006B00ED022E007300F60260029B01E2010E0126012F01470150015C01A901D9014502B0020400160003000A0015013F016101E701A40204800000010000003D0000000000000000002807000002000000000000000000000001008500000000000200000000000000000000000100B7010000000002000000000000000000000001008E000000000000000000003C4D6F64756C653E004368616C6C656E6765362E646C6C00726F7744657461696C004163746976654F72646572436F6D70617269736F6E00536F727465644163746976654F726465724C697374004163746976654F72646572004576656E7454797065730054687265616444657461696C730053746F72656450726F63656475726573006D73636F726C69620053797374656D0056616C756554797065004D756C74696361737444656C6567617465004F626A6563740053797374656D2E436F6C6C656374696F6E732E47656E657269630049456E756D657261626C6560310053797374656D2E436F6C6C656374696F6E730049456E756D657261626C6500456E756D004576656E744944004F72646572426F6F6B494400446563696D616C0042657374427579507269636500426573744275795175616E74697479004265737453656C6C5072696365004265737453656C6C5175616E74697479005365636F6E64426573744275795072696365005365636F6E64426573744275795175616E74697479005365636F6E644265737453656C6C5072696365005365636F6E644265737453656C6C5175616E74697479002E63746F720053797374656D2E446174610044617461526F7700446174615461626C6500416464546F5461626C6500496E766F6B6500494173796E63526573756C74004173796E6343616C6C6261636B00426567696E496E766F6B6500456E64496E766F6B65005F636F6D70617269736F6E005F62696E61727953656172636800696E6465784F665F004C6973746031006F7264657265644C6973740049456E756D657261746F72603100476574456E756D657261746F720049456E756D657261746F720053797374656D2E436F6C6C656374696F6E732E49456E756D657261626C652E476574456E756D657261746F72004164640052656D6F7665004F726465725265666572656E63654E756D6265720053656C6C496E64696361746F7200556E69745072696365005175616E746974790076616C75655F5F00457865637574650043616E63656C0044656C65746500436F6E6E656374696F6E537472696E670051756575656031006C6F61645175657565006C6F636B4F626A6563740066696E69736865640053797374656D2E546872656164696E67004D616E75616C52657365744576656E7400726F7773417661696C61626C65005F626174636853697A65005F7369676E616C426174636853697A65005F74687265616450726F63005F617363656E64696E67436F6D70617269736F6E005F64657363656E64696E67436F6D70617269736F6E0047656E65726174654F72646572426F6F6B5374617465005F67657442657374416E645365636F6E6442657374006576656E744944006F72646572426F6F6B49440062657374427579507269636500626573744275795175616E74697479006265737453656C6C5072696365006265737453656C6C5175616E74697479007365636F6E64426573744275795072696365007365636F6E64426573744275795175616E74697479007365636F6E644265737453656C6C5072696365007365636F6E644265737453656C6C5175616E74697479006F7574707574446174615461626C65006F626A656374006D6574686F64007800790063616C6C6261636B00726573756C7400636F6D70617269736F6E0076616C7565006974656D006F726465725265666572656E63654E756D6265720073656C6C496E64696361746F7200756E69745072696365007175616E74697479006F006F7264657265644F7264657273004265737450726963650053797374656D2E52756E74696D652E496E7465726F705365727669636573004F757441747472696275746500426573745175616E74697479005365636F6E64426573745072696365005365636F6E64426573745175616E746974790053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004368616C6C656E676536005374727563744C61796F7574417474726962757465004C61796F75744B696E64004E6577526F7700496E743332007365745F4974656D006765745F436F756E74006765745F4974656D00456E756D657261746F7200496E736572740052656D6F766541740053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C42756C6B436F70790044617461436F6C756D6E436F6C6C656374696F6E006765745F436F6C756D6E7300547970650052756E74696D655479706548616E646C65004765745479706546726F6D48616E646C650044617461436F6C756D6E007365745F44657374696E6174696F6E5461626C654E616D65007365745F426174636853697A650044617461526F77436F6C6C656374696F6E006765745F526F7773005761697448616E646C6500576169744F6E65004D6F6E69746F7200456E746572004576656E745761697448616E646C6500526573657400546F417272617900436C656172004578697400496E7465726E616C44617461436F6C6C656374696F6E42617365005772697465546F5365727665720049446973706F7361626C6500446973706F736500436F6D70617265546F004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650044696374696F6E61727960320053716C436F6D6D616E640053716C44617461526561646572004578656375746552656164657200446244617461526561646572005265616400476574537472696E6700537472696E6700436F6E63617400506172616D65746572697A6564546872656164537461727400546872656164005468726561645072696F72697479007365745F5072696F7269747900537461727400476574496E74333200476574446563696D616C006F705F496E657175616C69747900456E717565756500536574004A6F696E004D6F76654E657874006765745F43757272656E74006F705F457175616C697479000000001D4F00720064006500720042006F006F006B0053007400610074006500000F4500760065006E0074004900440000174F00720064006500720042006F006F006B00490044000019420065007300740042007500790050007200690063006500001F42006500730074004200750079005100750061006E007400690074007900001B4200650073007400530065006C006C005000720069006300650000214200650073007400530065006C006C005100750061006E00740069007400790000255300650063006F006E006400420065007300740042007500790050007200690063006500002B5300650063006F006E00640042006500730074004200750079005100750061006E00740069007400790000275300650063006F006E0064004200650073007400530065006C006C0050007200690063006500002D5300650063006F006E0064004200650073007400530065006C006C005100750061006E007400690074007900002D5B00640062006F005D002E005B004F00720064006500720042006F006F006B00530074006100740065005D00003163006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065003B0000808B5400520055004E00430041005400450020005400410042004C00450020005B00640062006F005D002E005B004F00720064006500720042006F006F006B00530074006100740065005D003B002000530045004C004500430054002000400040005300450052005600450052004E0041004D0045002C002000440042005F004E0041004D00450028002900000F5300650072007600650072003D0000233B0049006E0069007400690061006C00200043006100740061006C006F0067003D0000353B0049006E00740065006700720061007400650064002000530065006300750072006900740079003D0074007200750065003B000081BB530045004C0045004300540020005B004500760065006E007400490044005D002C0020004100530043004900490028005B004500760065006E00740054007900700065005D00290020004100530020005B004500760065006E00740054007900700065005D002C0020005B004F0072006400650072005200650066006500720065006E00630065004E0075006D006200650072005D002C0020005B004F00720064006500720042006F006F006B00490044005D002C0020004100530043004900490028005B00420075007900530065006C006C0049006E00640069006300610074006F0072005D00290020004100530020005B00420075007900530065006C006C0049006E00640069006300610074006F0072005D002C0020005B0055006E0069007400500072006900630065005D002C0020005B005100750061006E0074006900740079005D002000460052004F004D0020005B004F00720064006500720042006F006F006B004500760065006E00740073005D0020004F00520044004500520020004200590020005B004F00720064006500720042006F006F006B00490044005D002C0020005B004500760065006E007400490044005D0000000C212FE2FEFF4542B6878C58FD1247190008B77A5C561934E089061512110112140206080306111D11200A010808111D08111D08111D08111D0806200112211225052002011C1807200208121412140B2004122912141214122D1C0520010812290306120C05200101120C0520010812140706151231011214082000151235011214042000123905200101121402060209200501080802111D0803061118044100000004450000000443000000044400000002060E070615123D01110802061C030612410320000104D007000004E8030000040001011C0700020812141214030000011300050115121101121410111D100810111D1008042001010E0420010102052001011175042001010806200101118085042000122105200201081C0607021221122106151231011214032000080520011300080807060808080808020507030808080920001511808D011300071511808D01121408070115123501121404070112390620020108130004070208020615123D0111080620010112809105200012809D0800011280A11180A50920021280A90E1280A10520001280AD0420010208032000020520001D130005200101122105200101122524070F121C1280911C15123D01110812809912251280AD1241021D110811081C1D1108080205200108111D04200108080807050808111D0208040100000008151280C902081214072002010E1280910520001280D10420010E080500010E1D0E062001011280DD062001011180E5042001011C052001111D0807200201130013010620011301130005200102130007000202111D111D05200101130055072C151280C902081214121012101280910E0E1280CD1280D10E121C1280E115123D0111081118080802111D08111D08111D08111D08111D08111D08111D08111D08111D0808081C1280CD12141108021D0E11181C0820001512350113000615123501121404200013000E0705151235011214121402111D020F01000A4368616C6C656E676536000005010000000017010012436F7079726967687420C2A920203230313000000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000631E7D4C000000000200000062000000384700003829000052534453BA7FA7CE3CDC484ABC398E6F71B12C3B13000000433A5C436F64655C41746C616E7469735C5468726F77617761795C4368616C6C656E6765365C4368616C6C656E6765365C6F626A5C44656275675C4368616C6C656E6765362E706462000000C44700000000000000000000DE470000002000000000000000000000000000000000000000000000D0470000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058600000CC0200000000000000000000CC0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE000001000000010000003D000000010000003D003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0042C020000010053007400720069006E006700460069006C00650049006E0066006F00000008020000010030003000300030003000340062003000000040000B000100460069006C0065004400650073006300720069007000740069006F006E00000000004300680061006C006C0065006E0067006500360000000000340009000100460069006C006500560065007200730069006F006E000000000031002E0030002E00360031002E0030000000000040000F00010049006E007400650072006E0061006C004E0061006D00650000004300680061006C006C0065006E006700650036002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003000000048000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004300680061006C006C0065006E006700650036002E0064006C006C000000000038000B000100500072006F0064007500630074004E0061006D006500000000004300680061006C006C0065006E0067006500360000000000380009000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E00360031002E003000000000003C000900010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E00360031002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004000000C000000F03700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WITH PERMISSION_SET = UNSAFE GO ALTER ASSEMBLY [Challenge6] WITH VISIBILITY = ON GO CREATE PROCEDURE [dbo].[GenerateOrderBookState] AS EXTERNAL NAME [Challenge6].[StoredProcedures].[GenerateOrderBookState] GO Execute: EXEC [dbo].[GenerateOrderBookState] Teardown: DROP INDEX IX_OrderBookEvents_1 ON [dbo].[OrderBookEvents] GO DROP PROCEDURE [dbo].[GenerateOrderBookState] GO DROP ASSEMBLY [Challenge6] Code: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Collections.Generic; using System.Collections; using System.Threading; using System.IO; struct rowDetail { public int EventID; public int OrderBookID; public decimal BestBuyPrice; public int BestBuyQuantity; public decimal BestSellPrice; public int BestSellQuantity; public decimal SecondBestBuyPrice; public int SecondBestBuyQuantity; public decimal SecondBestSellPrice; public int SecondBestSellQuantity; /// /// Creates a new class instance. /// public rowDetail(int eventID, int orderBookID, decimal bestBuyPrice, int bestBuyQuantity, decimal bestSellPrice, int bestSellQuantity, decimal secondBestBuyPrice, int secondBestBuyQuantity, decimal secondBestSellPrice, int secondBestSellQuantity) { this.EventID = eventID; this.OrderBookID = orderBookID; this.BestBuyPrice = bestBuyPrice; this.BestBuyQuantity = bestBuyQuantity; this.BestSellPrice = bestSellPrice; this.BestSellQuantity = bestSellQuantity; this.SecondBestBuyPrice = secondBestBuyPrice; this.SecondBestBuyQuantity = secondBestBuyQuantity; this.SecondBestSellPrice = secondBestSellPrice; this.SecondBestSellQuantity = secondBestSellQuantity; } internal DataRow AddToTable(DataTable outputDataTable) { DataRow outputRow = outputDataTable.NewRow(); outputRow[0] = EventID; outputRow[1] = OrderBookID; outputRow[2] = BestBuyPrice; outputRow[3] = BestBuyQuantity; outputRow[4] = BestSellPrice; outputRow[5] = BestSellQuantity; outputRow[6] = SecondBestBuyPrice; outputRow[7] = SecondBestBuyQuantity; outputRow[8] = SecondBestSellPrice; outputRow[9] = SecondBestSellQuantity; return outputRow; } } delegate int ActiveOrderComparison(ActiveOrder x, ActiveOrder y); sealed class SortedActiveOrderList : IEnumerable { ActiveOrderComparison _comparison; /// /// Constructs a object specifying an IComparer for type T /// /// The IComparer for type T that compares objects public SortedActiveOrderList(ActiveOrderComparison comparison) { _comparison = comparison; } int _binarySearch(ActiveOrder value) { int lo = 0; int hi = orderedList.Count - 1; while (lo > 1); int order = _comparison(orderedList[i], value); if (order == 0) return i; if (order < 0) { lo = i + 1; } else { hi = i - 1; } } return ~lo; } /// /// Gets the index of the item using a BinarySearch /// /// The item to search for /// The index of the item, or it's complement if not found private int indexOf_(ActiveOrder item) { // As this collection can contain one *or more* of the same item, potentialIndex // is only guaranteed to point to an arbitrary item which matches "item" (based on the // comparer). int potentialIndex = _binarySearch(item); return (potentialIndex < 0) ? -1 : potentialIndex; // No hint for the search if potentialIndex is negative. if (potentialIndex < 0) potentialIndex = 0; // To find the actual item's index we must search forwards and backwards around // potentialIndex until we find an item with the same reference. // Search forwards for (int i = potentialIndex; i < orderedList.Count; ++i) { // if we have come to an item that the provided IComparer says is different, // then we have searched fare enough already if (_comparison(orderedList[i], item) != 0) { break; } if (object.ReferenceEquals(orderedList[i], item)) { return i; } } // Search backwards for (int i = potentialIndex; i >= 0; --i) { // if we have come to an item that the provided IComparer says is different, // then we have searched fare enough already if (_comparison(orderedList[i], item) != 0) { break; } if (object.ReferenceEquals(orderedList[i], item)) { return i; } } // Item not found return -1; } /// /// The internal list object /// private List orderedList = new List(); #region IEnumerable Members /// /// Returns an enumerator that iterates through a collection. /// /// An System.Collections.Generic.IEnumerator<T> object that can be used to iterate through the collection. public IEnumerator GetEnumerator() { return orderedList.GetEnumerator(); } #endregion #region IEnumerable Members /// /// Returns an enumerator that iterates through a collection. /// /// An System.Collections.IEnumerator object that can be used to iterate through the collection. System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() { return orderedList.GetEnumerator(); } #endregion /// /// Adds an item to the collection. /// /// The object to add to the collection. public void Add(ActiveOrder item) { int index = _binarySearch(item); if (index < 0) { // If the item isn't present BinarySearch returns the insertion point negated. index = ~index; } orderedList.Insert(index, item); } /// /// Removes the first occurrence of item from the collection. /// /// The object to remove /// true if item was successfully removed from the collection, otherwise false. This method also returns false if item is not found. public void Remove(ActiveOrder item) { int index = indexOf_(item); if (index != -1) { orderedList.RemoveAt(index); } } } sealed class ActiveOrder { public readonly int OrderBookID; public readonly int OrderReferenceNumber; public readonly bool SellIndicator; public readonly decimal UnitPrice; public int Quantity; /// /// Creates a new class instance. /// public ActiveOrder(int orderBookID, int orderReferenceNumber, bool sellIndicator, decimal unitPrice, int quantity) { OrderBookID = orderBookID; OrderReferenceNumber = orderReferenceNumber; SellIndicator = sellIndicator; UnitPrice = unitPrice; Quantity = quantity; } } enum EventTypes { Add = 65, Execute = 69, Cancel = 67, Delete = 68 } sealed class ThreadDetails { public string ConnectionString; public Queue loadQueue = new Queue(); public object lockObject = new object(); public bool finished = false; public ManualResetEvent rowsAvailable = new ManualResetEvent(false); } public partial class StoredProcedures { private static void _threadProc(object o) { ThreadDetails td = (ThreadDetails)o; using (SqlConnection externalConn = new SqlConnection(td.ConnectionString)) { externalConn.Open(); object lockObject = td.lockObject; Queue rowQueue = td.loadQueue; using (SqlBulkCopy bulkCopy = new SqlBulkCopy(externalConn)) { DataTable outputDataTable = new DataTable("OrderBookState"); outputDataTable.Columns.Add("EventID", typeof(Int32)); outputDataTable.Columns.Add("OrderBookID", typeof(Int32)); outputDataTable.Columns.Add("BestBuyPrice", typeof(Decimal)); outputDataTable.Columns.Add("BestBuyQuantity", typeof(Int32)); outputDataTable.Columns.Add("BestSellPrice", typeof(Decimal)); outputDataTable.Columns.Add("BestSellQuantity", typeof(Int32)); outputDataTable.Columns.Add("SecondBestBuyPrice", typeof(Decimal)); outputDataTable.Columns.Add("SecondBestBuyQuantity", typeof(Int32)); outputDataTable.Columns.Add("SecondBestSellPrice", typeof(Decimal)); outputDataTable.Columns.Add("SecondBestSellQuantity", typeof(Int32)); bulkCopy.DestinationTableName = "[dbo].[OrderBookState]"; bulkCopy.BatchSize = _batchSize; DataRowCollection rows = outputDataTable.Rows; ManualResetEvent sync = td.rowsAvailable; while (true) { bool finish = false; sync.WaitOne(System.Threading.Timeout.Infinite); rowDetail[] rowDetailArray; lock (lockObject) { sync.Reset(); rowDetailArray = rowQueue.ToArray(); rowQueue.Clear(); finish = td.finished; } foreach (rowDetail row in rowDetailArray) { rows.Add(row.AddToTable(outputDataTable)); } if (finish || rows.Count >= _batchSize) { bulkCopy.WriteToServer(outputDataTable); outputDataTable.Clear(); } if (finish) { break; } } } } } const int _batchSize = 2000; const int _signalBatchSize = 1000; private static int _ascendingComparison(ActiveOrder x, ActiveOrder y) { int i = x.UnitPrice.CompareTo(y.UnitPrice); if (i != 0) { return i; } else { return x.OrderReferenceNumber.CompareTo(y.OrderReferenceNumber); } } private static int _descendingComparison(ActiveOrder x, ActiveOrder y) { int i = x.UnitPrice.CompareTo(y.UnitPrice); if (i != 0) { return i * -1; } else { return x.OrderReferenceNumber.CompareTo(y.OrderReferenceNumber); } } [Microsoft.SqlServer.Server.SqlProcedure] public static void GenerateOrderBookState() { Dictionary ordersByReferenceNumber = new Dictionary(); SortedActiveOrderList buyOrderList = null; SortedActiveOrderList sellOrderList = null; using (SqlConnection sqlConn = new SqlConnection("context connection=true;")) { sqlConn.Open(); string serverName, databaseName; using (SqlCommand truncateCommand = new SqlCommand("TRUNCATE TABLE [dbo].[OrderBookState]; SELECT @@SERVERNAME, DB_NAME()", sqlConn)) { using (SqlDataReader reader = truncateCommand.ExecuteReader()) { reader.Read(); serverName = reader.GetString(0); databaseName = reader.GetString(1); } } string externalConnectionString = "Server=" + serverName + ";Initial Catalog=" + databaseName + ";Integrated Security=true;"; ThreadDetails td = new ThreadDetails(); td.ConnectionString = externalConnectionString; Thread loadThread = new Thread(new ParameterizedThreadStart(_threadProc)); loadThread.Priority = ThreadPriority.AboveNormal; loadThread.Start(td); Queue rowQueue = td.loadQueue; EventTypes EventType; int OrderReferenceNumber; int OrderBookID; bool SellIndicator; decimal UnitPrice; int Quantity; decimal BestBuyPrice; int BestBuyQuantity; decimal BestSellPrice; int BestSellQuantity; decimal SecondBestBuyPrice; int SecondBestBuyQuantity; decimal SecondBestSellPrice; int SecondBestSellQuantity; decimal PrevBestBuyPrice = 0; int PrevBestBuyQuantity = 0; decimal PrevBestSellPrice = 0; int PrevBestSellQuantity = 0; decimal PrevSecondBestBuyPrice = 0; int PrevSecondBestBuyQuantity = 0; decimal PrevSecondBestSellPrice = 0; int PrevSecondBestSellQuantity = 0; int PrevOrderBookID = -1; int outputRowCount = 0; object lockObject = td.lockObject; using (SqlCommand readCommand = new SqlCommand("SELECT [EventID], ASCII([EventType]) AS [EventType], [OrderReferenceNumber], [OrderBookID], ASCII([BuySellIndicator]) AS [BuySellIndicator], [UnitPrice], [Quantity] FROM [OrderBookEvents] ORDER BY [OrderBookID], [EventID]", sqlConn)) { using (SqlDataReader reader = readCommand.ExecuteReader()) { while (reader.Read()) { EventType = (EventTypes)reader.GetInt32(1); OrderReferenceNumber = reader.GetInt32(2); OrderBookID = reader.GetInt32(3); if (OrderBookID != PrevOrderBookID) { buyOrderList = new SortedActiveOrderList(_descendingComparison); sellOrderList = new SortedActiveOrderList(_ascendingComparison); } PrevOrderBookID = OrderBookID; switch (EventType) { case EventTypes.Add: { SellIndicator = reader.GetInt32(4) != 66; UnitPrice = reader.GetDecimal(5); Quantity = reader.GetInt32(6); ActiveOrder ao = new ActiveOrder(OrderBookID, OrderReferenceNumber, SellIndicator, UnitPrice, Quantity); ordersByReferenceNumber.Add(OrderReferenceNumber, ao); if (SellIndicator) { sellOrderList.Add(ao); } else { buyOrderList.Add(ao); } } break; case EventTypes.Execute: case EventTypes.Cancel: { Quantity = reader.GetInt32(6); ActiveOrder ao = ordersByReferenceNumber[OrderReferenceNumber]; if (ao.Quantity == Quantity) { if (ao.SellIndicator) { sellOrderList.Remove(ao); } else { buyOrderList.Remove(ao); } ordersByReferenceNumber.Remove(OrderReferenceNumber); } else { ao.Quantity -= Quantity; } } break; case EventTypes.Delete: { ActiveOrder ao = ordersByReferenceNumber[OrderReferenceNumber]; if (ao.SellIndicator) { sellOrderList.Remove(ao); } else { buyOrderList.Remove(ao); } ordersByReferenceNumber.Remove(OrderReferenceNumber); } break; } _getBestAndSecondBest(buyOrderList, out BestBuyPrice, out BestBuyQuantity, out SecondBestBuyPrice, out SecondBestBuyQuantity); _getBestAndSecondBest(sellOrderList, out BestSellPrice, out BestSellQuantity, out SecondBestSellPrice, out SecondBestSellQuantity); if (PrevBestBuyPrice != BestBuyPrice || PrevBestBuyQuantity != BestBuyQuantity || PrevBestSellPrice != BestSellPrice || PrevBestSellQuantity != BestSellQuantity || PrevSecondBestBuyPrice != SecondBestBuyPrice || PrevSecondBestBuyQuantity != SecondBestBuyQuantity || PrevSecondBestSellPrice != SecondBestSellPrice || PrevSecondBestSellQuantity != SecondBestSellQuantity) { rowDetail rd = new rowDetail(reader.GetInt32(0), OrderBookID, BestBuyPrice, BestBuyQuantity, BestSellPrice, BestSellQuantity, SecondBestBuyPrice, SecondBestBuyQuantity, SecondBestSellPrice, SecondBestSellQuantity); lock (lockObject) { rowQueue.Enqueue(rd); if (++outputRowCount >= _signalBatchSize) { td.rowsAvailable.Set(); outputRowCount = 0; } } PrevBestBuyPrice = BestBuyPrice; PrevBestBuyQuantity = BestBuyQuantity; PrevBestSellPrice = BestSellPrice; PrevBestSellQuantity = BestSellQuantity; PrevSecondBestBuyPrice = SecondBestBuyPrice; PrevSecondBestBuyQuantity = SecondBestBuyQuantity; PrevSecondBestSellPrice = SecondBestSellPrice; PrevSecondBestSellQuantity = SecondBestSellQuantity; } } } } lock (lockObject) { td.finished = true; td.rowsAvailable.Set(); } loadThread.Join(); } } private static void _getBestAndSecondBest(IEnumerable orderedOrders, out decimal BestPrice, out int BestQuantity, out decimal SecondBestPrice, out int SecondBestQuantity) { IEnumerator enumerator = orderedOrders.GetEnumerator(); enumerator.Reset(); ActiveOrder ao; if (enumerator.MoveNext()) { ao = enumerator.Current; BestPrice = ao.UnitPrice; BestQuantity = ao.Quantity; SecondBestPrice = 0; SecondBestQuantity = 0; } else { BestPrice = 0; BestQuantity = 0; SecondBestPrice = 0; SecondBestQuantity = 0; return; } bool b; while ((b = enumerator.MoveNext())) { ao = enumerator.Current; decimal price = ao.UnitPrice; if (price == BestPrice) { BestQuantity += ao.Quantity; } else { SecondBestPrice = price; SecondBestQuantity = ao.Quantity; break; } } if (!b) { return; } while (enumerator.MoveNext()) { ao = enumerator.Current; if (ao.UnitPrice == SecondBestPrice) { SecondBestQuantity += ao.Quantity; } else { return; } } } };
13 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JAhlen avatar image JAhlen commented ·
Splendid! Here are some preliminary test results: Daniel v2 (SSIS): 9,4 seconds (INCORRECT RESULTS) Matt v2 (UNSAFE SQLCLR): 12,1 seconds Matt v1 (EXTERNAL_ACCESS SQLCLR): 23,9 seconds Daniel v1 (EXTERNAL_ACCESS SQLCLR): 28,5 seconds
0 Likes 0 ·
Daniel Ross avatar image Daniel Ross commented ·
oh no! better have a look at that
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@JAhlen - brilliant. Out of interest, what type of machine are you running on?
0 Likes 0 ·
JAhlen avatar image JAhlen commented ·
0 Likes 0 ·
Peso avatar image Peso commented ·
Finally some timings. My T-SQL solutions runs in about 55-60 seconds (still has one bug left - doesn't work for one odd condition), so I'm way behind. This competition, and the other before including some kind of a running total, I think now is proofed that SQLCLR is here to stay.
0 Likes 0 ·
Show more comments
Matt Whitfield avatar image
Matt Whitfield answered
Ok then, just a few tweaks :) Matt 2a - slight variation. Setup: CREATE ASSEMBLY [Challenge6] AUTHORIZATION [dbo] FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030063EA834C0000000000000000E00002210B0108000026000000060000000000001E45000000200000006000000000400000200000000200000400000000000000040000000000000000A000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000C844000053000000006000002803000000000000000000000000000000000000008000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000024250000002000000026000000020000000000000000000000000000200000602E7273726300000028030000006000000004000000280000000000000000000000000000400000402E72656C6F6300000C0000000080000000020000002C0000000000000000000000000000400000420000000000000000000000000000000000450000000000004800000002000500C82B0000001900000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000033002004E0000000000000002037D0100000402047D0200000402057D03000004020E047D04000004020E057D05000004020E067D06000004020E077D07000004020E087D08000004020E097D09000004020E0A7D0A0000042A0000133003001601000001000011036F0F00000A0A0616027B010000048C1A0000016F1000000A0617027B020000048C1A0000016F1000000A027B03000004281100000A0B071F64731200000A281300000A0B0618078C1B0000016F1000000A0619027B040000048C1A0000016F1000000A027B05000004281100000A0C081F64731200000A281300000A0C061A088C1B0000016F1000000A061B027B060000048C1A0000016F1000000A027B07000004281100000A0D091F64731200000A281300000A0D061C098C1B0000016F1000000A061D027B080000048C1A0000016F1000000A027B09000004281100000A130411041F64731200000A281300000A1304061E11048C1B0000016F1000000A061F09027B0A0000048C1A0000016F1000000A062A1E027B0C0000042A26027B0B000004039A2A6203027B0B0000048E692E0C027C0B00000403280100002B2A7A02281600000A0220001000008D050000027D0B00000402037D0D0000042A133003004400000002000011160A027B0C00000417590B2B30060706591763580C027B0D000004027B0B000004089A036F040000060D092D02082A09162F060817580A2B040817590B060731CC06662A1330020010000000030000110203280B0000060A06163202062A152A133006006D000000030000110203280B0000060A06162F0306660A027B0C000004027B0B0000048E69331002027B0B0000048E69185A280900000606027B0C0000042F1D027B0B00000406027B0B000004061758027B0C0000040659281700000A027B0B0000040603A202257B0C00000417587D0C0000042A0000001330060041000000030000110203280C0000060A06152E3402257B0C00000417597D0C00000406027B0C0000042F1D027B0B000004061758027B0B00000406027B0C0000040659281700000A2AB202281600000A02037D0E00000402047D0F00000402057D10000004020E047D11000004020E057D120000042AA602731800000A7D1900000402731600000A7D1A0000040216731900000A7D1C00000402281600000A2A1B30030034020000040000110274070000020A067B18000004731A00000A0B076F1B00000A067B1A0000040C067B190000040D07731C00000A13047201000070731D00000A130511056F1E00000A721F000070D01A000001281F00000A6F2000000A2611056F1E00000A722F000070D01A000001281F00000A6F2000000A2611056F1E00000A7247000070D01B000001281F00000A6F2000000A2611056F1E00000A7261000070D01A000001281F00000A6F2000000A2611056F1E00000A7281000070D01B000001281F00000A6F2000000A2611056F1E00000A729D000070D01A000001281F00000A6F2000000A2611056F1E00000A72BF000070D01B000001281F00000A6F2000000A2611056F1E00000A72E5000070D01A000001281F00000A6F2000000A2611056F1E00000A7211010070D01B000001281F00000A6F2000000A2611056F1E00000A7239010070D01A000001281F00000A6F2000000A26110472670100706F2100000A110420D00700006F2200000A11056F2300000A1306067B1C00000413071613081107156F2400000A260825130B282500000A11076F2600000A26096F2700000A1309096F2800000A067B1B0000041308DE08110B282900000ADC1109130C16130D2B26110C110D8F020000027102000002130A1106120A110528020000066F2A00000A110D1758130D110D110C8E6932D211082D0E11066F2B00000A20D00700003210110411056F2C00000A11056F2D00000A11083963FFFFFFDE0C11042C0711046F2E00000ADCDE0A072C06076F2E00000ADC2A414C0000020000009101000020000000B10100000800000000000000020000002F000000EC0100001B0200000C00000000000000020000001300000016020000290200000A00000000000000133002002F00000005000011027B110000040B1201037B11000004282F00000A0A062C02062A027B0F0000040C1202037B0F000004283000000A2A00133002003100000005000011027B110000040B1201037B11000004282F00000A0A062C0406155A2A027B0F0000040C1202037B0F000004283000000A2A0000001B300B00A703000006000011733200000A0A140B140C7295010070731A00000A0D096F1B00000A72C701007009733300000A130611066F3400000A130711076F3500000A261107166F3600000A13041107176F3600000A1305DE0C11072C0711076F2E00000ADCDE0C11062C0711066F2E00000ADC1B8D31000001132B112B167254020070A2112B171104A2112B187264020070A2112B191105A2112B1A7288020070A2112B283700000A130873100000061309110911087D1800000414FE0611000006733800000A733900000A130A110A196F3A00000A110A11096F3B00000A11097B19000004130B166A131A16131B166A131C16131D166A131E16131F166A132016132115132216132311097B1A000004132472BE02007009733300000A132511256F3400000A1326381E0200001126176F3C00000A130C1126186F3C00000A130D1126196F3C00000A130E110E11222E2414FE06130000067303000006730A0000060B14FE06120000067303000006730A0000060C110E1322110C132C112C1F4159450500000005000000E800000063000000BA0000006300000038E300000011261A6F3C00000A1F42FE0116FE01130F11261B6F3D00000A131011261C6F3C00000A1311110E110D110F11101111730F000006132706110D11276F3E00000A110F2C0D0811276F0D00000638920000000711276F0D000006388500000011261C6F3C00000A131106110D6F3F00000A132811287B120000041111332611287B100000042C0A0811286F0E0000062B080711286F0E00000606110D6F4000000A262B401128257B120000041111597D120000042B2E06110D6F3F00000A132911297B100000042C0A0811296F0E0000062B080711296F0E00000606110D6F4000000A2607121212131216121728150000060812141215121812192815000006111A1112332A111B11133324111C1114331E111D11153318111E11163312111F1117330C112011183306112111192E7C122A1126166F3C00000A110E111211131114111511161117111811192801000006112425132D282500000A110B112A6F4100000A1123175825132320E8030000321011097B1C0000046F4200000A26161323DE08112D282900000ADC1112131A1113131B1114131C1115131D1116131E1117131F111813201119132111266F3500000A3AD6FDFFFFDE0C11262C0711266F2E00000ADCDE0C11252C0711256F2E00000ADC112425132E282500000A1109177D1B00000411097B1C0000046F4200000A26DE08112E282900000ADC110A6F4300000ADE0A092C06096F2E00000ADC2A0041AC000002000000310000001E0000004F0000000C000000000000000200000028000000350000005D0000000C0000000000000002000000F1020000290000001A0300000800000000000000020000001F01000031020000500300000C000000000000000200000016010000480200005E0300000C000000000000000200000074030000170000008B03000008000000000000000200000015000000870300009C0300000A0000000000000013300300A600000007000011160A026F070000060B06072F2202066F080000060C03087B110000045504087B120000045405166A550E0416542B3F03166A5504165405166A550E0416542A02066F080000060C087B110000040D09034C330D04254A087B1200000458542B0E0509550E04087B12000004542B2F061758250A0732C92B2502066F080000060C087B1100000413041104054C330E0E04254A087B1200000458542B012A061758250A0732D32A1E02281600000A2A000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000078070000237E0000E40700009409000023537472696E67730000000078110000B0040000235553002816000010000000234755494400000038160000C802000023426C6F620000000000000002000001571DA2090908000000FA0133001600000100000034000000080000001E000000160000002A00000043000000060000000C000000070000000100000002000000020000000200000001000000030000000100000000000A00010000000000060095008E0006009F008E000600B1008E000600B8008E000A00730167010A007B016701060097018E000600A4018E000E00AE0293020600E502D4020600E104C204060030051E05060047051E05060064051E05060083051E0506009C051E050600B5051E050600D0051E050600EB051E0506000406C204060018061E0506005106310606007106310606009A06C2040600B006C2040600C2068E000600D1068E000600F1061E05060008078E000A0030071A070A0051073E070A0063071A070A006F076701060090078E00060095078E000A00B90767010A00EB07670106000608D40206001908D40206002708D4020A0050086701060079088E0006008D088E000A00B8089D080600CE0893020A00DB081A070A00E6081A070A0002093E0706001E098E0006002C09D40206004509D40206004C09D40200000000010000000000010001000801100019000000050001000100000100002300000009000B00030000011000390000000D000B000700000110004F0000000D000E000F00000100005B00000011001300100000011000660000000D001800100001001000740000000D001D0011000600BD000A000600C5000A000600D1000D000600DE000A000600EE000D000600FC000A0006000D010D00060020010A00060036010D0006004A010A000100C80145000100CF010A000100D5014A002600C5000A0026002E020A00260043027900260051020D0006005B020A00060664020A0056801802850056806C02850056807402850056807B028500060082029D000600B602A0000600C002A8000600CB0279000600F602AB00518004030A0051800F030A005020000000008618610110000100AC2000000000830085011E000B000000000003008618610125000C00000000000300C60190012B000E00000000000300C601B20133001000000000000300C601BE013F001400CE21000000008608E1014E001500D621000000008608EB0152001500E021000000008100F40158001600F92100000000861861015D001700182200000000810001026300180068220000000081000F02630019008422000000008600180269001A0000230000000086001C0269001B004D2300000000861861017C001C007A230000000086186101AF002100A4230000000091002003BD00210030260000000091002C03C20022006C260000000091004103C2002400AC260000000096005703CA0026000C2B0000000091006E03CE002600BE2B0000000086186101AF002B00000001008403000002008C0300000300980300000400A50300000500B50300000600C30300000700D40300000800E70300000900FD0300000A001104000001002804000001003804000002003F04000001004604000002004804000001004604000002004804000003004A04000004003804000001005304000001005A04000001005C04000001006204000001005C04000001006D04000001006D04000001006D04000001008C03000002007204000003008704000004009504000005009F0400000100A80400000100460400000200480400000100460400000200480400000100AA0402000200B80402000300EE0402000400FB04020005000B0559006101AF0061006101DC0069006101DC0071006101DC0079006101DC0081006101DC0089006101DC0091006101DC0099006101DC00A1006101E100A9006101DC00B10061015800B9006101AF00C1006101E6003100BB06EC002900C806F100D900D906F700D90061015800D900E506FD00E1006101DC00E9000E071D0119006101AF00E900150737010C006101AF0051006101E100F1006101DC00F9005E07AF0001016101490131006101DC00310084074F011101A7075501090118025E010101C407DC000101DD0758003100FD076801310111086E0139012108BD004101370873010C003D0877010C004508AF0039014B08BD00290118027D014901E1014E0001016B08830131004508AF0051018508AF0059019308AC01D1009308B10161016101AF0014006101AF0071016101CA017101F408D10181010F09730181011409D70189012509DC0191016101250099016101E20199015B09E90199016809F00181016E09B10181017709F50114001802FA011400EB01020214001C0209020C0080090F0241018809730199018C09AF00080050008900080054008E0008005800930008005C00980008007400B30008007800B8002E002B0081022E00330071022E006B00A8022E001B0081022E00230081022E003B0087022E00130071022E00430081022E00530081022E0063009F028300A300130180028B01BC0106012C0133018901B6011502680204000100000023026F000000290273000200070003000200080005004201C1010480000001000000410000000000000000008F0600000200000000000000000000000100850000000000020000000000000000000000010067010000000002000000000000000000000001008E00000000002B00270100000000003C4D6F64756C653E004368616C6C656E6765362E646C6C00726F7744657461696C004163746976654F72646572436F6D70617269736F6E00536F727465644163746976654F726465724C697374004163746976654F72646572004576656E7454797065730054687265616444657461696C730053746F72656450726F63656475726573006D73636F726C69620053797374656D0056616C756554797065004D756C74696361737444656C6567617465004F626A65637400456E756D004576656E744944004F72646572426F6F6B49440042657374427579507269636500426573744275795175616E74697479004265737453656C6C5072696365004265737453656C6C5175616E74697479005365636F6E64426573744275795072696365005365636F6E64426573744275795175616E74697479005365636F6E644265737453656C6C5072696365005365636F6E644265737453656C6C5175616E74697479002E63746F720053797374656D2E446174610044617461526F7700446174615461626C6500416464546F5461626C6500496E766F6B6500494173796E63526573756C74004173796E6343616C6C6261636B00426567696E496E766F6B6500456E64496E766F6B65005F6974656D73005F73697A65005F636F6D70617269736F6E006765745F436F756E74006765745F4974656D005F7365744361706163697479005F62696E61727953656172636800696E6465784F665F004164640052656D6F766500436F756E74004974656D004F726465725265666572656E63654E756D6265720053656C6C496E64696361746F7200556E69745072696365005175616E746974790076616C75655F5F00457865637574650043616E63656C0044656C65746500436F6E6E656374696F6E537472696E670053797374656D2E436F6C6C656374696F6E732E47656E657269630051756575656031006C6F61645175657565006C6F636B4F626A6563740066696E69736865640053797374656D2E546872656164696E67004D616E75616C52657365744576656E7400726F7773417661696C61626C65005F626174636853697A65005F7369676E616C426174636853697A65005F74687265616450726F63005F617363656E64696E67436F6D70617269736F6E005F64657363656E64696E67436F6D70617269736F6E0047656E65726174654F72646572426F6F6B5374617465005F67657442657374416E645365636F6E6442657374006576656E744944006F72646572426F6F6B49440062657374427579507269636500626573744275795175616E74697479006265737453656C6C5072696365006265737453656C6C5175616E74697479007365636F6E64426573744275795072696365007365636F6E64426573744275795175616E74697479007365636F6E644265737453656C6C5072696365007365636F6E644265737453656C6C5175616E74697479006F7574707574446174615461626C65006F626A656374006D6574686F64007800790063616C6C6261636B00726573756C7400690076616C756500636F6D70617269736F6E006974656D006F726465725265666572656E63654E756D6265720073656C6C496E64696361746F7200756E69745072696365007175616E74697479006F006F7264657265644F7264657273004265737450726963650053797374656D2E52756E74696D652E496E7465726F705365727669636573004F757441747472696275746500426573745175616E74697479005365636F6E64426573745072696365005365636F6E64426573745175616E746974790053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004368616C6C656E676536005374727563744C61796F7574417474726962757465004C61796F75744B696E64004E6577526F7700496E743332007365745F4974656D00446563696D616C006F705F496D706C69636974006F705F4469766973696F6E0044656661756C744D656D62657241747472696275746500417272617900526573697A6500436F70790053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C42756C6B436F70790044617461436F6C756D6E436F6C6C656374696F6E006765745F436F6C756D6E7300547970650052756E74696D655479706548616E646C65004765745479706546726F6D48616E646C650044617461436F6C756D6E007365745F44657374696E6174696F6E5461626C654E616D65007365745F426174636853697A650044617461526F77436F6C6C656374696F6E006765745F526F7773005761697448616E646C6500576169744F6E65004D6F6E69746F7200456E746572004576656E745761697448616E646C6500526573657400546F417272617900436C656172004578697400496E7465726E616C44617461436F6C6C656374696F6E42617365005772697465546F5365727665720049446973706F7361626C6500446973706F736500496E74363400436F6D70617265546F004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650044696374696F6E61727960320053716C436F6D6D616E640053716C44617461526561646572004578656375746552656164657200446244617461526561646572005265616400476574537472696E6700537472696E6700436F6E63617400506172616D65746572697A6564546872656164537461727400546872656164005468726561645072696F72697479007365745F5072696F7269747900537461727400476574496E74333200476574496E74363400456E717565756500536574004A6F696E00000000001D4F00720064006500720042006F006F006B0053007400610074006500000F4500760065006E0074004900440000174F00720064006500720042006F006F006B00490044000019420065007300740042007500790050007200690063006500001F42006500730074004200750079005100750061006E007400690074007900001B4200650073007400530065006C006C005000720069006300650000214200650073007400530065006C006C005100750061006E00740069007400790000255300650063006F006E006400420065007300740042007500790050007200690063006500002B5300650063006F006E00640042006500730074004200750079005100750061006E00740069007400790000275300650063006F006E0064004200650073007400530065006C006C0050007200690063006500002D5300650063006F006E0064004200650073007400530065006C006C005100750061006E007400690074007900002D5B00640062006F005D002E005B004F00720064006500720042006F006F006B00530074006100740065005D00003163006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065003B0000808B5400520055004E00430041005400450020005400410042004C00450020005B00640062006F005D002E005B004F00720064006500720042006F006F006B00530074006100740065005D003B002000530045004C004500430054002000400040005300450052005600450052004E0041004D0045002C002000440042005F004E0041004D00450028002900000F5300650072007600650072003D0000233B0049006E0069007400690061006C00200043006100740061006C006F0067003D0000353B0049006E00740065006700720061007400650064002000530065006300750072006900740079003D0074007200750065003B000081ED530045004C0045004300540020005B004500760065006E007400490044005D002C0020004100530043004900490028005B004500760065006E00740054007900700065005D00290020004100530020005B004500760065006E00740054007900700065005D002C0020005B004F0072006400650072005200650066006500720065006E00630065004E0075006D006200650072005D002C0020005B004F00720064006500720042006F006F006B00490044005D002C0020004100530043004900490028005B00420075007900530065006C006C0049006E00640069006300610074006F0072005D00290020004100530020005B00420075007900530065006C006C0049006E00640069006300610074006F0072005D002C00200043004F004E00560045005200540028005B0062006900670069006E0074005D002C0020005B0055006E0069007400500072006900630065005D0020002A00200031003000300029002C0020005B005100750061006E0074006900740079005D002000460052004F004D0020005B004F00720064006500720042006F006F006B004500760065006E00740073005D0020004F00520044004500520020004200590020005B004F00720064006500720042006F006F006B00490044005D002C0020005B004500760065006E007400490044005D0000000000053C709513ADEE42B6A38B267D882A3B0008B77A5C561934E08902060802060A0D200A0108080A080A080A080A0806200112151219052002011C1807200208121412140B2004121D1214121412211C05200108121D04061D12140306120C03200008052001121408042001010805200101120C05200108121405200101121403280008052801121408020602082005010808020A0803061118044100000004450000000443000000044400000002060E070615122501110802061C030612290320000104D007000004E8030000040001011C0700020812141214030000010D0005011210100A1008100A1008042001010E0420010102052001011165042000121505200201081C050001116D0A080002116D116D116D0C07051215116D116D116D116D090100044974656D00000910010201101D1E0008040A01121406070408080808030701080A000501127508127508080615122501110805200101127905200012808508000112808911808D0920021280910E1280890520001280950420010208032000020520001D130005200101121505200101121922070E121C12791C15122501110812808112191280951229021D110811081C1D110808042001080A0420010808050703080A08040100000008151280B502081214062002010E12790520001280BD0420010E080500010E1D0E062001011280C9062001011180D1042001011C0420010A0807200201130013010620011301130005200102130005200101130052072F151280B5020812141210121012790E0E1280B91280BD0E121C1280CD15122501110811180808020A080A080A080A080A080A080A080A080A0808081C1280B91280BD12141214121411081D0E11181C1C080705080812140A0A0F01000A4368616C6C656E676536000005010000000017010012436F7079726967687420C2A920203230313000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100F044000000000000000000000E4500000020000000000000000000000000000000000000000000000045000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058600000CC0200000000000000000000CC0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000410000000100000041003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0042C020000010053007400720069006E006700460069006C00650049006E0066006F00000008020000010030003000300030003000340062003000000040000B000100460069006C0065004400650073006300720069007000740069006F006E00000000004300680061006C006C0065006E0067006500360000000000340009000100460069006C006500560065007200730069006F006E000000000031002E0030002E00360035002E0030000000000040000F00010049006E007400650072006E0061006C004E0061006D00650000004300680061006C006C0065006E006700650036002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003000000048000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004300680061006C006C0065006E006700650036002E0064006C006C000000000038000B000100500072006F0064007500630074004E0061006D006500000000004300680061006C006C0065006E0067006500360000000000380009000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E00360035002E003000000000003C000900010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E00360035002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004000000C000000203500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WITH PERMISSION_SET = UNSAFE GO ALTER ASSEMBLY [Challenge6] WITH VISIBILITY = ON GO CREATE PROCEDURE [dbo].[GenerateOrderBookState] AS EXTERNAL NAME [Challenge6].[StoredProcedures].[GenerateOrderBookState] GO Execute and teardown as before. Code: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Collections.Generic; using System.Threading; using System.IO; struct rowDetail { public int EventID; public int OrderBookID; public long BestBuyPrice; public int BestBuyQuantity; public long BestSellPrice; public int BestSellQuantity; public long SecondBestBuyPrice; public int SecondBestBuyQuantity; public long SecondBestSellPrice; public int SecondBestSellQuantity; /// /// Creates a new class instance. /// public rowDetail(int eventID, int orderBookID, long bestBuyPrice, int bestBuyQuantity, long bestSellPrice, int bestSellQuantity, long secondBestBuyPrice, int secondBestBuyQuantity, long secondBestSellPrice, int secondBestSellQuantity) { this.EventID = eventID; this.OrderBookID = orderBookID; this.BestBuyPrice = bestBuyPrice; this.BestBuyQuantity = bestBuyQuantity; this.BestSellPrice = bestSellPrice; this.BestSellQuantity = bestSellQuantity; this.SecondBestBuyPrice = secondBestBuyPrice; this.SecondBestBuyQuantity = secondBestBuyQuantity; this.SecondBestSellPrice = secondBestSellPrice; this.SecondBestSellQuantity = secondBestSellQuantity; } internal DataRow AddToTable(DataTable outputDataTable) { DataRow outputRow = outputDataTable.NewRow(); outputRow[0] = EventID; outputRow[1] = OrderBookID; Decimal bestBuyPrice = BestBuyPrice; bestBuyPrice /= 100; outputRow[2] = bestBuyPrice; outputRow[3] = BestBuyQuantity; Decimal bestSellPrice = BestSellPrice; bestSellPrice /= 100; outputRow[4] = bestSellPrice; outputRow[5] = BestSellQuantity; Decimal secondBestBuyPrice = SecondBestBuyPrice; secondBestBuyPrice /= 100; outputRow[6] = secondBestBuyPrice; outputRow[7] = SecondBestBuyQuantity; Decimal secondBestSellPrice = SecondBestSellPrice; secondBestSellPrice /= 100; outputRow[8] = secondBestSellPrice; outputRow[9] = SecondBestSellQuantity; return outputRow; } } delegate int ActiveOrderComparison(ActiveOrder x, ActiveOrder y); sealed class SortedActiveOrderList { private ActiveOrder[] _items; private int _size; ActiveOrderComparison _comparison; public int Count { get { return _size; } } public ActiveOrder this[int i] { get { return _items[i]; } } void _setCapacity(int value) { if (value != _items.Length) { Array.Resize(ref _items, value); } } /// /// Constructs a object specifying an IComparer for type T /// /// The IComparer for type T that compares objects public SortedActiveOrderList(ActiveOrderComparison comparison) { _items = new ActiveOrder[4096]; _comparison = comparison; } int _binarySearch(ActiveOrder value) { int lo = 0; int hi = _size - 1; while (lo > 1); int order = _comparison(_items[i], value); if (order == 0) return i; if (order < 0) { lo = i + 1; } else { hi = i - 1; } } return ~lo; } /// /// Gets the index of the item using a BinarySearch /// /// The item to search for /// The index of the item, or it's complement if not found private int indexOf_(ActiveOrder item) { int potentialIndex = _binarySearch(item); return (potentialIndex < 0) ? -1 : potentialIndex; } /// /// Adds an item to the collection. /// /// The object to add to the collection. public void Add(ActiveOrder item) { int index = _binarySearch(item); if (index < 0) { // If the item isn't present BinarySearch returns the insertion point negated. index = ~index; } if (_size == _items.Length) { _setCapacity(_items.Length * 2); } if (index < _size) { Array.Copy(_items, index, _items, index + 1, _size - index); } _items[index] = item; _size++; } /// /// Removes the first occurrence of item from the collection. /// /// The object to remove /// true if item was successfully removed from the collection, otherwise false. This method also returns false if item is not found. public void Remove(ActiveOrder item) { int index = indexOf_(item); if (index != -1) { _size--; if (index < _size) { Array.Copy(_items, index + 1, _items, index, _size - index); } } } } sealed class ActiveOrder { public readonly int OrderBookID; public readonly int OrderReferenceNumber; public readonly bool SellIndicator; public readonly long UnitPrice; public int Quantity; /// /// Creates a new class instance. /// public ActiveOrder(int orderBookID, int orderReferenceNumber, bool sellIndicator, long unitPrice, int quantity) { OrderBookID = orderBookID; OrderReferenceNumber = orderReferenceNumber; SellIndicator = sellIndicator; UnitPrice = unitPrice; Quantity = quantity; } } enum EventTypes { Add = 65, Execute = 69, Cancel = 67, Delete = 68 } sealed class ThreadDetails { public string ConnectionString; public Queue loadQueue = new Queue(); public object lockObject = new object(); public bool finished = false; public ManualResetEvent rowsAvailable = new ManualResetEvent(false); } public partial class StoredProcedures { private static void _threadProc(object o) { ThreadDetails td = (ThreadDetails)o; using (SqlConnection externalConn = new SqlConnection(td.ConnectionString)) { externalConn.Open(); object lockObject = td.lockObject; Queue rowQueue = td.loadQueue; using (SqlBulkCopy bulkCopy = new SqlBulkCopy(externalConn)) { DataTable outputDataTable = new DataTable("OrderBookState"); outputDataTable.Columns.Add("EventID", typeof(Int32)); outputDataTable.Columns.Add("OrderBookID", typeof(Int32)); outputDataTable.Columns.Add("BestBuyPrice", typeof(Decimal)); outputDataTable.Columns.Add("BestBuyQuantity", typeof(Int32)); outputDataTable.Columns.Add("BestSellPrice", typeof(Decimal)); outputDataTable.Columns.Add("BestSellQuantity", typeof(Int32)); outputDataTable.Columns.Add("SecondBestBuyPrice", typeof(Decimal)); outputDataTable.Columns.Add("SecondBestBuyQuantity", typeof(Int32)); outputDataTable.Columns.Add("SecondBestSellPrice", typeof(Decimal)); outputDataTable.Columns.Add("SecondBestSellQuantity", typeof(Int32)); bulkCopy.DestinationTableName = "[dbo].[OrderBookState]"; bulkCopy.BatchSize = _batchSize; DataRowCollection rows = outputDataTable.Rows; ManualResetEvent sync = td.rowsAvailable; while (true) { bool finish = false; sync.WaitOne(System.Threading.Timeout.Infinite); rowDetail[] rowDetailArray; lock (lockObject) { sync.Reset(); rowDetailArray = rowQueue.ToArray(); rowQueue.Clear(); finish = td.finished; } foreach (rowDetail row in rowDetailArray) { rows.Add(row.AddToTable(outputDataTable)); } if (finish || rows.Count >= _batchSize) { bulkCopy.WriteToServer(outputDataTable); outputDataTable.Clear(); } if (finish) { break; } } } } } const int _batchSize = 2000; const int _signalBatchSize = 1000; private static int _ascendingComparison(ActiveOrder x, ActiveOrder y) { int i = x.UnitPrice.CompareTo(y.UnitPrice); if (i != 0) { return i; } else { return x.OrderReferenceNumber.CompareTo(y.OrderReferenceNumber); } } private static int _descendingComparison(ActiveOrder x, ActiveOrder y) { int i = x.UnitPrice.CompareTo(y.UnitPrice); if (i != 0) { return i * -1; } else { return x.OrderReferenceNumber.CompareTo(y.OrderReferenceNumber); } } [Microsoft.SqlServer.Server.SqlProcedure] public static void GenerateOrderBookState() { Dictionary ordersByReferenceNumber = new Dictionary(); SortedActiveOrderList buyOrderList = null; SortedActiveOrderList sellOrderList = null; using (SqlConnection sqlConn = new SqlConnection("context connection=true;")) { sqlConn.Open(); string serverName, databaseName; using (SqlCommand truncateCommand = new SqlCommand("TRUNCATE TABLE [dbo].[OrderBookState]; SELECT @@SERVERNAME, DB_NAME()", sqlConn)) { using (SqlDataReader reader = truncateCommand.ExecuteReader()) { reader.Read(); serverName = reader.GetString(0); databaseName = reader.GetString(1); } } string externalConnectionString = "Server=" + serverName + ";Initial Catalog=" + databaseName + ";Integrated Security=true;"; ThreadDetails td = new ThreadDetails(); td.ConnectionString = externalConnectionString; Thread loadThread = new Thread(new ParameterizedThreadStart(_threadProc)); loadThread.Priority = ThreadPriority.AboveNormal; loadThread.Start(td); Queue rowQueue = td.loadQueue; EventTypes EventType; int OrderReferenceNumber; int OrderBookID; bool SellIndicator; long UnitPrice; int Quantity; long BestBuyPrice; int BestBuyQuantity; long BestSellPrice; int BestSellQuantity; long SecondBestBuyPrice; int SecondBestBuyQuantity; long SecondBestSellPrice; int SecondBestSellQuantity; long PrevBestBuyPrice = 0; int PrevBestBuyQuantity = 0; long PrevBestSellPrice = 0; int PrevBestSellQuantity = 0; long PrevSecondBestBuyPrice = 0; int PrevSecondBestBuyQuantity = 0; long PrevSecondBestSellPrice = 0; int PrevSecondBestSellQuantity = 0; int PrevOrderBookID = -1; int outputRowCount = 0; object lockObject = td.lockObject; using (SqlCommand readCommand = new SqlCommand("SELECT [EventID], ASCII([EventType]) AS [EventType], [OrderReferenceNumber], [OrderBookID], ASCII([BuySellIndicator]) AS [BuySellIndicator], CONVERT([bigint], [UnitPrice] * 100), [Quantity] FROM [OrderBookEvents] ORDER BY [OrderBookID], [EventID]", sqlConn)) { using (SqlDataReader reader = readCommand.ExecuteReader()) { while (reader.Read()) { EventType = (EventTypes)reader.GetInt32(1); OrderReferenceNumber = reader.GetInt32(2); OrderBookID = reader.GetInt32(3); if (OrderBookID != PrevOrderBookID) { buyOrderList = new SortedActiveOrderList(_descendingComparison); sellOrderList = new SortedActiveOrderList(_ascendingComparison); } PrevOrderBookID = OrderBookID; switch (EventType) { case EventTypes.Add: { SellIndicator = reader.GetInt32(4) != 66; UnitPrice = reader.GetInt64(5); Quantity = reader.GetInt32(6); ActiveOrder ao = new ActiveOrder(OrderBookID, OrderReferenceNumber, SellIndicator, UnitPrice, Quantity); ordersByReferenceNumber.Add(OrderReferenceNumber, ao); if (SellIndicator) { sellOrderList.Add(ao); } else { buyOrderList.Add(ao); } } break; case EventTypes.Execute: case EventTypes.Cancel: { Quantity = reader.GetInt32(6); ActiveOrder ao = ordersByReferenceNumber[OrderReferenceNumber]; if (ao.Quantity == Quantity) { if (ao.SellIndicator) { sellOrderList.Remove(ao); } else { buyOrderList.Remove(ao); } ordersByReferenceNumber.Remove(OrderReferenceNumber); } else { ao.Quantity -= Quantity; } } break; case EventTypes.Delete: { ActiveOrder ao = ordersByReferenceNumber[OrderReferenceNumber]; if (ao.SellIndicator) { sellOrderList.Remove(ao); } else { buyOrderList.Remove(ao); } ordersByReferenceNumber.Remove(OrderReferenceNumber); } break; } _getBestAndSecondBest(buyOrderList, out BestBuyPrice, out BestBuyQuantity, out SecondBestBuyPrice, out SecondBestBuyQuantity); _getBestAndSecondBest(sellOrderList, out BestSellPrice, out BestSellQuantity, out SecondBestSellPrice, out SecondBestSellQuantity); if (PrevBestBuyPrice != BestBuyPrice || PrevBestBuyQuantity != BestBuyQuantity || PrevBestSellPrice != BestSellPrice || PrevBestSellQuantity != BestSellQuantity || PrevSecondBestBuyPrice != SecondBestBuyPrice || PrevSecondBestBuyQuantity != SecondBestBuyQuantity || PrevSecondBestSellPrice != SecondBestSellPrice || PrevSecondBestSellQuantity != SecondBestSellQuantity) { rowDetail rd = new rowDetail(reader.GetInt32(0), OrderBookID, BestBuyPrice, BestBuyQuantity, BestSellPrice, BestSellQuantity, SecondBestBuyPrice, SecondBestBuyQuantity, SecondBestSellPrice, SecondBestSellQuantity); lock (lockObject) { rowQueue.Enqueue(rd); if (++outputRowCount >= _signalBatchSize) { td.rowsAvailable.Set(); outputRowCount = 0; } } PrevBestBuyPrice = BestBuyPrice; PrevBestBuyQuantity = BestBuyQuantity; PrevBestSellPrice = BestSellPrice; PrevBestSellQuantity = BestSellQuantity; PrevSecondBestBuyPrice = SecondBestBuyPrice; PrevSecondBestBuyQuantity = SecondBestBuyQuantity; PrevSecondBestSellPrice = SecondBestSellPrice; PrevSecondBestSellQuantity = SecondBestSellQuantity; } } } } lock (lockObject) { td.finished = true; td.rowsAvailable.Set(); } loadThread.Join(); } } private static void _getBestAndSecondBest(SortedActiveOrderList orderedOrders, out long BestPrice, out int BestQuantity, out long SecondBestPrice, out int SecondBestQuantity) { int i = 0; int count = orderedOrders.Count; ActiveOrder ao; if (i < count) { ao = orderedOrders[i]; BestPrice = ao.UnitPrice; BestQuantity = ao.Quantity; SecondBestPrice = 0; SecondBestQuantity = 0; } else { BestPrice = 0; BestQuantity = 0; SecondBestPrice = 0; SecondBestQuantity = 0; return; } while (++i < count) { ao = orderedOrders[i]; long price = ao.UnitPrice; if (price == BestPrice) { BestQuantity += ao.Quantity; } else { SecondBestPrice = price; SecondBestQuantity = ao.Quantity; break; } } while (++i < count) { ao = orderedOrders[i]; long price = ao.UnitPrice; if (price == SecondBestPrice) { SecondBestQuantity += ao.Quantity; } else { return; } } } };
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JAhlen avatar image JAhlen commented ·
Great! Things can always be improved. Current standings: Daniel v2b (SSIS): 9,4 seconds Matt v2a (UNSAFE SQLCLR): 10,4 seconds Matt v2 (UNSAFE SQLCLR): 12,1 seconds Matt v1 (EXTERNAL_ACCESS SQLCLR): 23,9 seconds Daniel v1 (EXTERNAL_ACCESS SQLCLR): 28,5 seconds
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.