SQL Server Cross Apply on itself + Cross Apply performance issues

Question

I have the following:

CREATE TABLE #MASTER_POSANDTRANS (ID CHAR(30), 
                                  QUANTITY INT, 
                                  START_OR_TRADE_DATE DATE) 

VALUES 
    ('APPLES ','150000','20150501'), ('PEARS','220000','20150501'), 
    ('APPLES ','-75000','20150506'), ('APPLES ','-65000','20150508'), 
    ('APPLES ','10000','20150516'),  ('APPLES ','-20000','20150519'
    ('PEARS','-110000','20150506'),  ('PEARS','-100000','20150519')

I want to be able to say "Ok at the beginning I had 150,000 apples, therefore my overall apple position was LONG"

Here is how I am currently solving this little puzzle:

SELECT 
    MPT.ID, 
    MPT.QUANTITY, 
    MPT.START_OR_TRADE_DATE, 
    APPLY_RES.QUANTITY as 'FIRST AVAILABLE  QUANTITY', 
    CASE 
       WHEN APPLY_RES.QUANTITY > 0 
         THEN 'LONG' 
         ELSE 'SHORT' 
    END as 'L/S Indicator'
FROM 
    #MASTER_POSANDTRANS as MPT 
CROSS APPLY
    (SELECT TOP 1 
         MPT_APPLY.QUANTITY 
     FROM 
         #MASTER_POSANDTRANS as MPT_APPLY
     WHERE 
         MPT_APPLY.ID = MPT.ID) APPLY_RES

The table of provided is, a simplification of the result of multiple queries and union alls that spit out about 387 rows in roughly 3 seconds. However, when I try and apply my solution to this 387 rows result, my query returns the correct result but takes 21 seconds instead of 3.

Any suggestions for improvement?

Little more to the puzzle:

The first available "transaction" decides if I am long or short
Another, more complex problem (for those who like puzzles) is to take an average weighted holding to decide if I was long or short during the period. So for example, imagine that after the last apples transaction on the 19th, where I had 0 apples left, I sold another 1million apples. This would mean that from the 19th to the 31st of May I was short 1 million. (31-19) * -1,000,000 < (19-1)*(Average of apples sold/bought during the month) therefore my holding of apples during that month could be considered as short. If at any point the two balanced, I would take the value in point 1.

Run through SQL Fiddle, this doesn't appear to generate the result you want... http://sqlfiddle.com/#!3/ea1c3/1/0 — SQL Tactics, Aug 03 '15 at 18:50
Thanks for the link. That is pretty cool stuff! I took a look, the results generated are correct. I bought 150,000 apples at the beginning of the month (or the first available transaction date for may) and therefore all subsequent apple transactions are marked as long. Same with pears (220,000 was the first transaction so all other transactions are marked as long) — buellboy, Aug 04 '15 at 10:04
Edit: I should rephrase: "Ok at the beginning I had 150,000 apples, therefore my overall apple position was LONG" to "Ok at the beginning I had 150,000 apples, therefore my overall apple for the time period was LONG, therefore all transactions (+ve or -ve) should have the "LONG" marker" — buellboy, Aug 04 '15 at 10:09

score 0 · Accepted Answer · answered Aug 10 '15 at 08:28

You Cross Apply Select is missing an Order By (date?). It is not deterministic and there is no guarantee that you will indeed get the 1st record.

On you sample data, this query has a better query plan:

SELECT
    MPT.ID,
    MPT.QUANTITY,
    MPT.START_OR_TRADE_DATE
    , APPLY_RES.QUANTITY as 'FIRST AVAILABLE  QUANTITY',
    CASE
    WHEN APPLY_RES.QUANTITY > 0
        THEN 'LONG'
    ELSE 'SHORT'
    END as 'L/S Indicator'
From MASTER_POSANDTRANS AS MPT
Inner Join (
    Select ID, QUANTITY, N = ROW_NUMBER() over (Partition by ID order by START_OR_TRADE_DATE)
    From MASTER_POSANDTRANS
) as APPLY_RES on APPLY_RES.ID = MPT.ID
Where APPLY_RES.N = 1
--Order By MPT.ID, MPT.START_OR_TRADE_DATE

SQL Server Cross Apply on itself + Cross Apply performance issues

1 Answers1