x

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.

EventID EventType OrderRefNumber Buy/Sell Unit Price Quantity 1 A 101 Sell 8.65 2000 2 A 102 Sell 8.60 500 3 A 103 Buy 8.40 1200 4 A 104 Buy 8.50 1000 5 A 105 Sell 8.70 500 6 A 106 Sell 8.70 1000 7 C 106 500 8 A 107 Buy 8.45 300 9 C 101 1000 10 A 108 Buy 8.45 500 11 A 109 Sell 8.75 2000 12 D 109 13 E 104 8.50 600

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

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.

EventID Best Buy Price Best Buy Quantity Best Sell Price Best Sell Quantity Second Best Buy Price Second Best Buy Quantity Second Best Sell Price Second Best Sell Quantity 1 8.65 2000 2 8.60 500 8.65 2000 3 8.40 1200 8.60 500 8.65 2000 4 8.50 1000 8.60 500 8.40 1200 8.65 2000 8 8.50 1000 8.60 500 8.45 300 8.65 2000 9 8.50 1000 8.60 500 8.45 300 8.65 1000 10 8.50 1000 8.60 500 8.45 800 8.65 1000 13 8.50 400 8.60 500 8.45 800 8.65 1000

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

  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

more ▼

asked Aug 16, 2010 at 04:02 AM in Default

avatar image

JAhlen
41 2 2 6

@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?

Aug 17, 2010 at 10:30 PM Daniel Ross

@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.

Aug 18, 2010 at 12:48 AM JAhlen

@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

Aug 18, 2010 at 05:27 PM Daniel Ross

@Daniel Ross Thanks for the comment. I have clarified the explanation under Important notes.

Aug 19, 2010 at 01:18 AM JAhlen

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!

Aug 27, 2010 at 05:23 PM Matt Whitfield ♦♦
show all comments (comments are locked)
10|1200 characters needed characters left

7 answers: sort voted first

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;

     /// <summary>
     /// Creates a new <see cref="rowDetail" /> class instance.
     /// </summary>
     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 <= hi)
         {
             int i = lo + ((hi - 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<ActiveOrder>(ref _items, value);
         }
     }

     /// <summary>
     /// Constructs a <see cref="SortedList&lt;T&gt;"/> object specifying an IComparer for type T
     /// </summary>
     /// <param name="Comparer">The IComparer for type T that compares objects</param>
     public SortedActiveOrderList(ActiveOrderComparison comparison)
     {
         _items = new ActiveOrder[4096];
         _comparison = comparison;
     }

     /// <summary>
     /// Gets the index of the item using a BinarySearch
     /// </summary>
     /// <param name="item">The item to search for</param>
     /// <returns>The index of the item, or it's complement if not found</returns>
     private int indexOf_(ActiveOrder item)
     {
         int potentialIndex = _binarySearch(item);
         return (potentialIndex < 0) ? -1 : potentialIndex;
     }

     /// <summary>
     /// Adds an item to the collection.
     /// </summary>
     /// <param name="item">The object to add to the collection.</param>
     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++;
     }

     /// <summary>
     /// Removes the first occurrence of item from the collection.
     /// </summary>
     /// <param name="item">The object to remove</param>
     /// <returns>true if item was successfully removed from the collection, otherwise false. This method also returns false if item is not found.</returns>
     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;

     /// <summary>
     /// Creates a new <see cref="ActiveOrder" /> class instance.
     /// </summary>
     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<rowDetail> loadQueue = new Queue<rowDetail>();
     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<rowDetail> 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<int, ActiveOrder> ordersByReferenceNumber = new Dictionary<int, ActiveOrder>();
         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<rowDetail> 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();
         }
     }
 };

more ▼

answered Sep 05, 2010 at 01:38 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left

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


 <Microsoft.SqlServer.Server.SqlProcedure()> _
 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
more ▼

answered Aug 22, 2010 at 11:03 PM

avatar image

Daniel Ross
2.9k 11 15 18

@Daniel Ross Nice! I've asked Phil to test the performance of your solution.

Aug 23, 2010 at 12:54 AM JAhlen

Soon, soon!

Aug 24, 2010 at 08:41 AM Phil Factor

@Phil - by way of benchmarking, can you also provide the run duration of the "SlowSolution.sql"?

Aug 25, 2010 at 12:25 AM ThomasRushton ♦♦

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!

Aug 26, 2010 at 02:01 AM Phil Factor

@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.

Aug 31, 2010 at 10:33 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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<T> : IEnumerable<T>
 {
     /// <summary>
     /// Constructs a <see cref="SortedList&lt;T&gt;"/> object specifying an IComparer for type T
     /// </summary>
     /// <param name="Comparer">The IComparer for type T that compares objects</param>
     public SortedList(IComparer<T> Comparer)
     {
         comparer = Comparer;
     }
 
     /// <summary>
     /// Adds an item to the list
     /// </summary>
     /// <param name="newItem">The item to add</param>
     /// <returns>The index of the added item</returns>
     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;
     }
 
     /// <summary>
     /// Gets the index of the item using a BinarySearch
     /// </summary>
     /// <param name="item">The item to search for</param>
     /// <returns>The index of the item, or it's complement if not found</returns>
     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;
     }
 
     /// <summary>
     /// The internal list object
     /// </summary>
     private List<T> orderedList = new List<T>();
 
     /// <summary>
     /// The comparer
     /// </summary>
     private IComparer<T> comparer;
 
     #region IEnumerable<T> Members
 
     /// <summary>
     /// Returns an enumerator that iterates through a collection.
     /// </summary>
     /// <returns>An System.Collections.Generic.IEnumerator&lt;T&gt; object that can be used to iterate through the collection.</returns>
     public IEnumerator<T> GetEnumerator()
     {
         return orderedList.GetEnumerator();
     }
 
     #endregion
 
     #region IEnumerable Members
 
     /// <summary>
     /// Returns an enumerator that iterates through a collection.
     /// </summary>
     /// <returns>An System.Collections.IEnumerator object that can be used to iterate through the collection.</returns>
     System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
     {
         return orderedList.GetEnumerator();
     }
 
     #endregion
 
     /// <summary>
     /// Adds an item to the collection.
     /// </summary>
     /// <param name="item">The object to add to the collection.</param>
     public void Add(T item)
     {
         add_(item);
     }
 
     /// <summary>
     /// Removes the first occurrence of item from the collection.
     /// </summary>
     /// <param name="item">The object to remove</param>
     /// <returns>true if item was successfully removed from the collection, otherwise false. This method also returns false if item is not found.</returns>
     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;
 
     /// <summary>
     /// Creates a new <see cref="ActiveOrder" /> class instance.
     /// </summary>
     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<ActiveOrder>
 {
 
     #region IComparer<ActiveOrder> 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<ActiveOrder>
 {
     #region IComparer<ActiveOrder> 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<int, ActiveOrder> ordersByReferenceNumber = new Dictionary<int, ActiveOrder>();
         Dictionary<int, SortedList<ActiveOrder>> buyOrdersByPriceByOrderBookID = new Dictionary<int, SortedList<ActiveOrder>>();
         Dictionary<int, SortedList<ActiveOrder>> sellOrdersByPriceByOrderBookID = new Dictionary<int, SortedList<ActiveOrder>>();
 
         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<ActiveOrder> sellOrderList = null;
                                 SortedList<ActiveOrder> 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<ActiveOrder>(new AscendingPriceComparer());
                                             sellOrdersByPriceByOrderBookID.Add(OrderBookID, sellOrderList);
                                         }
                                         if (!buyOrdersByPriceByOrderBookID.TryGetValue(OrderBookID, out buyOrderList))
                                         {
                                             buyOrderList = new SortedList<ActiveOrder>(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<ActiveOrder> orderedOrders, out decimal BestPrice, out int BestQuantity, out decimal SecondBestPrice, out int SecondBestQuantity)
     {
         IEnumerator<ActiveOrder> 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;
             }
         }
     }   
 };
more ▼

answered Aug 27, 2010 at 05:20 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

Nice! I hope we will also see some non-SQL CLR solutions soon.

Aug 28, 2010 at 01:01 PM JAhlen

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.

Aug 28, 2010 at 03:53 PM Matt Whitfield ♦♦

@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.

Aug 29, 2010 at 06:33 PM Daniel Ross

I've done a SQL one without loops but it runs slower than my cursor solution!

Aug 30, 2010 at 12:55 AM Phil Factor

@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! :)

Aug 31, 2010 at 09:32 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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

    
more ▼

answered Aug 30, 2010 at 12:46 AM

avatar image

Phil Factor
4.2k 8 23 20

@Phil Good improvement! Could you do a test run of all the solutions so far and publish?

Aug 30, 2010 at 01:27 AM JAhlen

@JAhlen - if you want the test harness, drop me an email...

Aug 30, 2010 at 02:44 PM Matt Whitfield ♦♦

Would be interesting to see how a SSIS solution would perform. My guess is that SSIS could win this challenge.

Aug 31, 2010 at 01:49 AM JAhlen

@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.

Aug 31, 2010 at 02:35 AM Daniel Ross

Now fixed. It was a little unkind to entirely eliminate it from the competition just for two wrong rows in 196967.

Sep 07, 2010 at 05:41 AM Phil Factor
(comments are locked)
10|1200 characters needed characters left

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;
 
     /// <summary>
     /// Creates a new <see cref="rowDetail" /> class instance.
     /// </summary>
     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<ActiveOrder>
 {
     ActiveOrderComparison _comparison;
 
     /// <summary>
     /// Constructs a <see cref="SortedList&lt;T&gt;"/> object specifying an IComparer for type T
     /// </summary>
     /// <param name="Comparer">The IComparer for type T that compares objects</param>
     public SortedActiveOrderList(ActiveOrderComparison comparison)
     {
         _comparison = comparison;
     }
 
     int _binarySearch(ActiveOrder value)
     {
         int lo = 0;
         int hi = orderedList.Count - 1;
         while (lo <= hi)
         {
             int i = lo + ((hi - 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;
     }
 
     /// <summary>
     /// Gets the index of the item using a BinarySearch
     /// </summary>
     /// <param name="item">The item to search for</param>
     /// <returns>The index of the item, or it's complement if not found</returns>
     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;
     }
 
     /// <summary>
     /// The internal list object
     /// </summary>
     private List<ActiveOrder> orderedList = new List<ActiveOrder>();
 
     #region IEnumerable<ActiveOrder> Members
 
     /// <summary>
     /// Returns an enumerator that iterates through a collection.
     /// </summary>
     /// <returns>An System.Collections.Generic.IEnumerator&lt;T&gt; object that can be used to iterate through the collection.</returns>
     public IEnumerator<ActiveOrder> GetEnumerator()
     {
         return orderedList.GetEnumerator();
     }
 
     #endregion
 
     #region IEnumerable Members
 
     /// <summary>
     /// Returns an enumerator that iterates through a collection.
     /// </summary>
     /// <returns>An System.Collections.IEnumerator object that can be used to iterate through the collection.</returns>
     System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
     {
         return orderedList.GetEnumerator();
     }
 
     #endregion
 
     /// <summary>
     /// Adds an item to the collection.
     /// </summary>
     /// <param name="item">The object to add to the collection.</param>
     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);
     }
 
     /// <summary>
     /// Removes the first occurrence of item from the collection.
     /// </summary>
     /// <param name="item">The object to remove</param>
     /// <returns>true if item was successfully removed from the collection, otherwise false. This method also returns false if item is not found.</returns>
     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;
 
     /// <summary>
     /// Creates a new <see cref="ActiveOrder" /> class instance.
     /// </summary>
     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<rowDetail> loadQueue = new Queue<rowDetail>();
     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<rowDetail> 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<int, ActiveOrder> ordersByReferenceNumber = new Dictionary<int, ActiveOrder>();
         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<rowDetail> 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<ActiveOrder> orderedOrders, out decimal BestPrice, out int BestQuantity, out decimal SecondBestPrice, out int SecondBestQuantity)
     {
         IEnumerator<ActiveOrder> 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;
             }
         }
     }
 };
more ▼

answered Sep 02, 2010 at 04:48 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

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
Sep 03, 2010 at 01:42 AM JAhlen

oh no! better have a look at that

Sep 03, 2010 at 02:13 AM Daniel Ross

@JAhlen - brilliant. Out of interest, what type of machine are you running on?

Sep 03, 2010 at 02:17 AM Matt Whitfield ♦♦

@Matt - A laptop with dual-core AMD Turion CPU (rather slow) and a SSD disk (very fast). See my blog for details: http://blogical.se/blogs/jahlen/archive/2010/04/25/ssd-a-great-performance-booster-for-tired-laptops.aspx

Sep 03, 2010 at 02:30 AM JAhlen

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.

Sep 04, 2010 at 05:24 AM Peso
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1067
x14
x8
x7
x5

asked: Aug 16, 2010 at 04:02 AM

Seen: 7587 times

Last Updated: Sep 08, 2010 at 12:24 AM

Copyright 2016 Redgate Software. Privacy Policy