# 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:

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

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

JAhlen
41 1 2 3

@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 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, 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 Thanks for the comment. I have clarified the explanation under Important notes.
Aug 19, 2010 at 01:18 AM
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

sort voted first
 0 more ▼ answered Sep 05, 2010 at 01:38 PM Matt Whitfield ♦♦ 29.5k ● 61 ● 65 ● 87 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 more ▼ answered Aug 22, 2010 at 11:03 PM Daniel Ross 2.9k ● 11 ● 13 ● 14 @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 ♦♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 more ▼ answered Aug 27, 2010 at 05:20 PM Matt Whitfield ♦♦ 29.5k ● 61 ● 65 ● 87 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 ♦♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 Phil Factor Cursor 1CI 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 Phil Factor 3.9k ● 8 ● 9 ● 16 @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 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 more ▼ answered Sep 02, 2010 at 04:48 PM Matt Whitfield ♦♦ 29.5k ● 61 ● 65 ● 87 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 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

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

Topics:

x990
x14
x8
x7
x5

asked: Aug 16, 2010 at 04:02 AM

Seen: 6485 times

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