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

JAhlen gravatar image

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
@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 ♦♦
(comments are locked)
10|1200 characters needed characters left

7 answers: sort voted first
more ▼

answered Sep 05, 2010 at 01:38 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

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

answered Aug 22, 2010 at 11:03 PM

Daniel Ross gravatar image

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 ♦♦
(comments are locked)
10|1200 characters needed characters left
more ▼

answered Aug 27, 2010 at 05:20 PM

Matt Whitfield gravatar image

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 ♦♦
(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

Phil Factor gravatar image

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
(comments are locked)
10|1200 characters needed characters left
more ▼

answered Sep 02, 2010 at 04:48 PM

Matt Whitfield gravatar image

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

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.

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:

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