June 5, 2023 at 2:33 am
no difference.
your code was only looking for the the friday date on a particular situation e.g. when the EXPIRATION = Thursday
so by determining all the FRIDAY dates in advance, subtracting one day from it means we can replace the following critirea
(DATEADD(DAY,1,EXPIRATION) NOT IN (SELECT DISTINCT T2.EXPIRATION FROM DBO.Stock AS T2)) THEN 'Y' ELSE 'N'
with the "t2.expiration is null" associated with a left outer join to the temp table.
i ran this code. i think this is good enough for my purposes. the run time for the over all query is less than 1 min. before it was close to 3 min. i am happy with the improvement. thank you again for your time on this. i appreciate it very much
June 5, 2023 at 2:39 am
small error on my original code - the temp table was referring an non existing alias (t1)- easy to fix I think.
another attempt at simplifying it assuming that I correctly understood you only interested on the cases where EXPIRATION is the last "working" day of the week
drop table if exists #weeklastday;
/*
create table with distinct entries for expiration that are deedmed to be end of week.
Assumption is that from the descripition of the problem this date will always be a Friday (if not a bank holiday) or the Thursday immediately before it
and as the original code was only interested in finding if adding 1 day to a thursday did not fall on the following Friday
we deduct 1 day from this date so the original query can avoid doing another dateadd operation to find a match
*/
select distinct t1.EXPIRATION
, dateadd(day, -23, t1.EXPIRATION) as from_quote_date_23
, dateadd(day, -30, t1.EXPIRATION) as from_quote_date_30
into #weeklastday
from dbo.Stock t1
where datename(weekday, t1.EXPIRATION) = 'Friday'
;
create clustered index #ci_weeklastday on #weeklastday
(EXPIRATION
)
;
-- now add thursdays where they were last day of the week - e.g. when expiration + 1 day does not exist on table above already
insert into #weeklastday
select distinct t1.EXPIRATION
, dateadd(day, -23, t1.EXPIRATION) as from_quote_date_23
, dateadd(day, -30, t1.EXPIRATION) as from_quote_date_30
from dbo.Stock t1
where datename(weekday, t1.EXPIRATION) = 'Thursday'
and not exists (select *
from #weeklastday w2
where w2.EXPIRATION = dateadd(day, 1, t1.EXPIRATION)
)
;
select t6.UNDERLYING_SYMBOL
, t6.QUOTE_DATE
, t6.expiration
, t6.ROOT_SYMBOL
from (select t4.UNDERLYING_SYMBOL
, t4.QUOTE_DATE
, t4.expiration
, t4.ROOT_SYMBOL
, row_number() over (partition by t4.UNDERLYING_SYMBOL, t4.QUOTE_DATE order by t4.ROOT_SYMBOL) as root_symbol_seq
from (select t1.UNDERLYING_SYMBOL
, t1.QUOTE_DATE
, t1.expiration
, t1.ROOT_SYMBOL
, 'Y' as end_of_week
from dbo.Stock as t1
inner join #weeklastday t2
on t2.EXPIRATION = t1.EXPIRATION
and t1.QUOTE_DATE between t2.from_quote_date_30 and t2.from_quote_date_23
--where datediff(day, t1.QUOTE_DATE, t1.expiration) >= 23
-- and datediff(day, t1.QUOTE_DATE, t1.expiration) <= 30
) as t4
inner join (select t3.UNDERLYING_SYMBOL
, t3.QUOTE_DATE
, max(t3.expiration) as expiration
from (select t1.UNDERLYING_SYMBOL
, t1.QUOTE_DATE
, t1.expiration
, t1.ROOT_SYMBOL
, 'Y' end_of_week
from dbo.Stock as t1
inner join #weeklastday t2
on t2.EXPIRATION = t1.EXPIRATION
and t1.QUOTE_DATE between t2.from_quote_date_30 and t2.from_quote_date_23
--where datediff(day, t1.QUOTE_DATE, t1.expiration) >= 23
-- and datediff(day, t1.QUOTE_DATE, t1.expiration) <= 30
) as t3
where t3.end_of_week = 'Y'
group by t3.UNDERLYING_SYMBOL
, t3.QUOTE_DATE
) as t5
on t4.UNDERLYING_SYMBOL = t5.UNDERLYING_SYMBOL
and t4.QUOTE_DATE = t5.QUOTE_DATE
and t4.expiration = t5.expiration
) as t6
where t6.root_symbol_seq = 1
June 5, 2023 at 2:52 am
small error on my original code - the temp table was referring an non existing alias (t1)- easy to fix I think.
another attempt at simplifying it assuming that I correctly understood you only interested on the cases where EXPIRATION is the last "working" day of the week
drop table if exists #weeklastday;
/*
create table with distinct entries for expiration that are deedmed to be end of week.
Assumption is that from the descripition of the problem this date will always be a Friday (if not a bank holiday) or the Thursday immediately before it
and as the original code was only interested in finding if adding 1 day to a thursday did not fall on the following Friday
we deduct 1 day from this date so the original query can avoid doing another dateadd operation to find a match
*/
select distinct t1.EXPIRATION
, dateadd(day, -23, t1.EXPIRATION) as from_quote_date_23
, dateadd(day, -30, t1.EXPIRATION) as from_quote_date_30
into #weeklastday
from dbo.Stock t1
where datename(weekday, t1.EXPIRATION) = 'Friday'
;
create clustered index #ci_weeklastday on #weeklastday
(EXPIRATION
)
;
-- now add thursdays where they were last day of the week - e.g. when expiration + 1 day does not exist on table above already
insert into #weeklastday
select distinct t1.EXPIRATION
, dateadd(day, -23, t1.EXPIRATION) as from_quote_date_23
, dateadd(day, -30, t1.EXPIRATION) as from_quote_date_30
from dbo.Stock t1
where datename(weekday, t1.EXPIRATION) = 'Thursday'
and not exists (select *
from #weeklastday w2
where w2.EXPIRATION = dateadd(day, 1, t1.EXPIRATION)
)
;
select t6.UNDERLYING_SYMBOL
, t6.QUOTE_DATE
, t6.expiration
, t6.ROOT_SYMBOL
from (select t4.UNDERLYING_SYMBOL
, t4.QUOTE_DATE
, t4.expiration
, t4.ROOT_SYMBOL
, row_number() over (partition by t4.UNDERLYING_SYMBOL, t4.QUOTE_DATE order by t4.ROOT_SYMBOL) as root_symbol_seq
from (select t1.UNDERLYING_SYMBOL
, t1.QUOTE_DATE
, t1.expiration
, t1.ROOT_SYMBOL
, 'Y' as end_of_week
from dbo.Stock as t1
inner join #weeklastday t2
on t2.EXPIRATION = t1.EXPIRATION
and t1.QUOTE_DATE between t2.from_quote_date_30 and t2.from_quote_date_23
--where datediff(day, t1.QUOTE_DATE, t1.expiration) >= 23
-- and datediff(day, t1.QUOTE_DATE, t1.expiration) <= 30
) as t4
inner join (select t3.UNDERLYING_SYMBOL
, t3.QUOTE_DATE
, max(t3.expiration) as expiration
from (select t1.UNDERLYING_SYMBOL
, t1.QUOTE_DATE
, t1.expiration
, t1.ROOT_SYMBOL
, 'Y' end_of_week
from dbo.Stock as t1
inner join #weeklastday t2
on t2.EXPIRATION = t1.EXPIRATION
and t1.QUOTE_DATE between t2.from_quote_date_30 and t2.from_quote_date_23
--where datediff(day, t1.QUOTE_DATE, t1.expiration) >= 23
-- and datediff(day, t1.QUOTE_DATE, t1.expiration) <= 30
) as t3
where t3.end_of_week = 'Y'
group by t3.UNDERLYING_SYMBOL
, t3.QUOTE_DATE
) as t5
on t4.UNDERLYING_SYMBOL = t5.UNDERLYING_SYMBOL
and t4.QUOTE_DATE = t5.QUOTE_DATE
and t4.expiration = t5.expiration
) as t6
where t6.root_symbol_seq = 1
this code produces same output as original so that means the logic is correct and is faster. thank you!
June 5, 2023 at 4:09 am
how much faster? can can you also put new explain plan.
June 5, 2023 at 4:40 am
how much faster? can can you also put new explain plan.
there is no official cut off. the lowest possible is best. the run time for the entire 1000 line query was close to 3 minutes before and now its less than 50 seconds so that is a good improvement. i have been timing the various sub-queries using gettime(). each of the two problem queries now take less than 7 seconds each to run. this is a huge improvement from before. before each was taking close to 50 seconds.
here is the execution plan:
https://www.brentozar.com/pastetheplan/?id=HkHrEksIn
do you see any other opportunities for improvement?
June 5, 2023 at 4:49 am
that plan is from you version of the change e.g. where you still keep the dateadd and your temp table does not filter or change the expiration date.
can you give actual plans for my first and my second changes as well as duration.
June 5, 2023 at 5:40 am
that plan is from you version of the change e.g. where you still keep the dateadd and your temp table does not filter or change the expiration date.
can you give actual plans for my first and my second changes as well as duration.
no problem. here you go
first one:
https://www.brentozar.com/pastetheplan/?id=Hko2VgoUh
time: 19 sec
second one:
https://www.brentozar.com/pastetheplan/?id=SJqfHliLh
time: 16 sec
thank you so much!
June 5, 2023 at 6:41 am
can you try this slight variation of my second example and give runtime and plan as well.
drop table if exists #weeklastday;
drop table if exists #temp1;
select distinct t1.EXPIRATION
into #temp1
from dbo.Stock t1
create clustered index #ci_temp1 on #temp1
(EXPIRATION
)
;
/*
create table with distinct entries for expiration that are deedmed to be end of week.
Assumption is that from the descripition of the problem this date will always be a Friday (if not a bank holiday) or the Thursday immediately before it
and as the original code was only interested in finding if adding 1 day to a thursday did not fall on the following Friday
we deduct 1 day from this date so the original query can avoid doing another dateadd operation to find a match
*/
select distinct t1.EXPIRATION
, dateadd(day, -23, t1.EXPIRATION) as from_quote_date_23
, dateadd(day, -30, t1.EXPIRATION) as from_quote_date_30
into #weeklastday
from #temp1 t1
where datename(weekday, t1.EXPIRATION) = 'Friday'
;
create clustered index #ci_weeklastday on #weeklastday
(EXPIRATION
)
;
-- now add thursdays where they were last day of the week - e.g. when expiration + 1 day does not exist on table above already
insert into #weeklastday
select distinct t1.EXPIRATION
, dateadd(day, -23, t1.EXPIRATION) as from_quote_date_23
, dateadd(day, -30, t1.EXPIRATION) as from_quote_date_30
from #temp1 t1
where datename(weekday, t1.EXPIRATION) = 'Thursday'
and not exists (select *
from #temp1 w2
where w2.EXPIRATION = dateadd(day, 1, t1.EXPIRATION)
)
;
select t6.UNDERLYING_SYMBOL
, t6.QUOTE_DATE
, t6.expiration
, t6.ROOT_SYMBOL
from (select t4.UNDERLYING_SYMBOL
, t4.QUOTE_DATE
, t4.expiration
, t4.ROOT_SYMBOL
, row_number() over (partition by t4.UNDERLYING_SYMBOL, t4.QUOTE_DATE order by t4.ROOT_SYMBOL) as root_symbol_seq
from (select t1.UNDERLYING_SYMBOL
, t1.QUOTE_DATE
, t1.expiration
, t1.ROOT_SYMBOL
, 'Y' as end_of_week
from dbo.Stock as t1
inner join #weeklastday t2
on t2.EXPIRATION = t1.EXPIRATION
and t1.QUOTE_DATE between t2.from_quote_date_30 and t2.from_quote_date_23
--where datediff(day, t1.QUOTE_DATE, t1.expiration) >= 23
-- and datediff(day, t1.QUOTE_DATE, t1.expiration) <= 30
) as t4
inner join (select t3.UNDERLYING_SYMBOL
, t3.QUOTE_DATE
, max(t3.expiration) as expiration
from (select t1.UNDERLYING_SYMBOL
, t1.QUOTE_DATE
, t1.expiration
, t1.ROOT_SYMBOL
, 'Y' end_of_week
from dbo.Stock as t1
inner join #weeklastday t2
on t2.EXPIRATION = t1.EXPIRATION
and t1.QUOTE_DATE between t2.from_quote_date_30 and t2.from_quote_date_23
--where datediff(day, t1.QUOTE_DATE, t1.expiration) >= 23
-- and datediff(day, t1.QUOTE_DATE, t1.expiration) <= 30
) as t3
where t3.end_of_week = 'Y'
group by t3.UNDERLYING_SYMBOL
, t3.QUOTE_DATE
) as t5
on t4.UNDERLYING_SYMBOL = t5.UNDERLYING_SYMBOL
and t4.QUOTE_DATE = t5.QUOTE_DATE
and t4.expiration = t5.expiration
) as t6
where t6.root_symbol_seq = 1
June 5, 2023 at 1:45 pm
SELECT T1.UNDERLYING_SYMBOL,
T1.QUOTE_DATE,
MAX(T1.EXPIRATION) AS EXPIRATION
FROM dbo.Stock AS T1
WHERE DATEDIFF(DAY, T1.QUOTE_DATE, T1.EXPIRATION) >= 23 AND
DATEDIFF(DAY, T1.QUOTE_DATE, T1.EXPIRATION) <= 30 AND
CASE
WHEN DATEDIFF(DAY, 0, T1.EXPIRATION) % 7 = 4 /*'Friday'*/ THEN 'Y'
WHEN DATEDIFF(DAY, 0, T1.EXPIRATION) % 7 = 3 /*'Thursday'*/ AND NOT EXISTS(SELECT T2.* FROM DBO.Stock AS T2 WHERE T2.EXPIRATION = DATEADD(DAY,1,T1.EXPIRATION)) THEN 'Y'
ELSE 'N'
END = 'Y'
GROUP BY T1.UNDERLYING_SYMBOL, T1.QUOTE_DATE
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 5, 2023 at 3:54 pm
can you try this slight variation of my second example and give runtime and plan as well.
drop table if exists #weeklastday;
drop table if exists #temp1;
select distinct t1.EXPIRATION
into #temp1
from dbo.Stock t1
create clustered index #ci_temp1 on #temp1
(EXPIRATION
)
;
/*
create table with distinct entries for expiration that are deedmed to be end of week.
Assumption is that from the descripition of the problem this date will always be a Friday (if not a bank holiday) or the Thursday immediately before it
and as the original code was only interested in finding if adding 1 day to a thursday did not fall on the following Friday
we deduct 1 day from this date so the original query can avoid doing another dateadd operation to find a match
*/select distinct t1.EXPIRATION
, dateadd(day, -23, t1.EXPIRATION) as from_quote_date_23
, dateadd(day, -30, t1.EXPIRATION) as from_quote_date_30
into #weeklastday
from #temp1 t1
where datename(weekday, t1.EXPIRATION) = 'Friday'
;
create clustered index #ci_weeklastday on #weeklastday
(EXPIRATION
)
;
-- now add thursdays where they were last day of the week - e.g. when expiration + 1 day does not exist on table above already
insert into #weeklastday
select distinct t1.EXPIRATION
, dateadd(day, -23, t1.EXPIRATION) as from_quote_date_23
, dateadd(day, -30, t1.EXPIRATION) as from_quote_date_30
from #temp1 t1
where datename(weekday, t1.EXPIRATION) = 'Thursday'
and not exists (select *
from #temp1 w2
where w2.EXPIRATION = dateadd(day, 1, t1.EXPIRATION)
)
;
select t6.UNDERLYING_SYMBOL
, t6.QUOTE_DATE
, t6.expiration
, t6.ROOT_SYMBOL
from (select t4.UNDERLYING_SYMBOL
, t4.QUOTE_DATE
, t4.expiration
, t4.ROOT_SYMBOL
, row_number() over (partition by t4.UNDERLYING_SYMBOL, t4.QUOTE_DATE order by t4.ROOT_SYMBOL) as root_symbol_seq
from (select t1.UNDERLYING_SYMBOL
, t1.QUOTE_DATE
, t1.expiration
, t1.ROOT_SYMBOL
, 'Y' as end_of_week
from dbo.Stock as t1
inner join #weeklastday t2
on t2.EXPIRATION = t1.EXPIRATION
and t1.QUOTE_DATE between t2.from_quote_date_30 and t2.from_quote_date_23
--where datediff(day, t1.QUOTE_DATE, t1.expiration) >= 23
-- and datediff(day, t1.QUOTE_DATE, t1.expiration) <= 30
) as t4
inner join (select t3.UNDERLYING_SYMBOL
, t3.QUOTE_DATE
, max(t3.expiration) as expiration
from (select t1.UNDERLYING_SYMBOL
, t1.QUOTE_DATE
, t1.expiration
, t1.ROOT_SYMBOL
, 'Y' end_of_week
from dbo.Stock as t1
inner join #weeklastday t2
on t2.EXPIRATION = t1.EXPIRATION
and t1.QUOTE_DATE between t2.from_quote_date_30 and t2.from_quote_date_23
--where datediff(day, t1.QUOTE_DATE, t1.expiration) >= 23
-- and datediff(day, t1.QUOTE_DATE, t1.expiration) <= 30
) as t3
where t3.end_of_week = 'Y'
group by t3.UNDERLYING_SYMBOL
, t3.QUOTE_DATE
) as t5
on t4.UNDERLYING_SYMBOL = t5.UNDERLYING_SYMBOL
and t4.QUOTE_DATE = t5.QUOTE_DATE
and t4.expiration = t5.expiration
) as t6
where t6.root_symbol_seq = 1
the query run time is 2 sec..wow!
the plan:
https://www.brentozar.com/pastetheplan/?id=SJbohuoU2
Thank you!!
there are two others that now are too slow. they are taking up 55% of run time so I would like to see if there is a way to optimize those. the run time is about 10 sec each. here is the plan:
https://www.brentozar.com/pastetheplan/?id=HyeXUYo82
Is there any way to optimize it?
June 6, 2023 at 6:43 am
regarding your last query (from the second plan). two options below
option 1 is just a adjustment of your original query - does same thing but with 1 less join. may or not be faster
option 2 is a rewrite - has a big assumption that your original query would NEVER return more than 1 row per combination of the following columns. if there is a case it does then the query is giving incorrect results.
-- original query
select t3.UNDERLYING_SYMBOL
, t3.QUOTE_DATE
, t3.EXPIRATION
, t3.ROOT_SYMBOL
, t3.STRIKE
, t3.call_bid
, t3.call_ask
, t4.put_bid
, t4.put_ask
, abs((t3.call_ask + t3.call_bid) / 2.0 - (t4.put_ask + t4.put_bid) / 2.0) as price_difference
from (select t1.UNDERLYING_SYMBOL
, t1.QUOTE_DATE
, t1.EXPIRATION
, t1.ROOT_SYMBOL
, t1.STRIKE
, t1.BID_EOD as call_bid
, t1.ASK_EOD as call_ask
from dbo.OptionsEOD as t1
inner join dbo.VIXNearTermExpiration as t5
on t1.UNDERLYING_SYMBOL = t5.UNDERLYING_SYMBOL
and t1.QUOTE_DATE = t5.QUOTE_DATE
and t1.EXPIRATION = t5.EXPIRATION
and t1.ROOT_SYMBOL = t5.ROOT_SYMBOL
where t1.OPTION_TYPE = 'C'
) as t3
inner join (select t2.UNDERLYING_SYMBOL
, t2.QUOTE_DATE
, t2.EXPIRATION
, t2.ROOT_SYMBOL
, t2.STRIKE
, t2.BID_EOD as put_bid
, t2.ASK_EOD as put_ask
from dbo.OptionsEOD as t2
inner join dbo.VIXNearTermExpiration as t6
on t2.UNDERLYING_SYMBOL = t6.UNDERLYING_SYMBOL
and t2.QUOTE_DATE = t6.QUOTE_DATE
and t2.EXPIRATION = t6.EXPIRATION
and t2.ROOT_SYMBOL = t6.ROOT_SYMBOL
where OPTION_TYPE = 'P'
) as t4
on t3.UNDERLYING_SYMBOL = t4.UNDERLYING_SYMBOL
and t3.QUOTE_DATE = t4.QUOTE_DATE
and t3.EXPIRATION = t4.EXPIRATION
and t3.STRIKE = t4.STRIKE
-- option 1
select t2.*
from (
select t3.UNDERLYING_SYMBOL
, t3.QUOTE_DATE
, t3.EXPIRATION
, t3.ROOT_SYMBOL
, t3.STRIKE
, t3.call_bid
, t3.call_ask
, t4.put_bid
, t4.put_ask
, abs((t3.call_ask + t3.call_bid) / 2.0 - (t4.put_ask + t4.put_bid) / 2.0) as price_difference
from (select t1.UNDERLYING_SYMBOL
, t1.QUOTE_DATE
, t1.EXPIRATION
, t1.ROOT_SYMBOL
, t1.STRIKE
, t1.BID_EOD as call_bid
, t1.ASK_EOD as call_ask
from dbo.OptionsEOD as t1
where t1.OPTION_TYPE = 'C'
) as t3
inner join (select t2.UNDERLYING_SYMBOL
, t2.QUOTE_DATE
, t2.EXPIRATION
, t2.ROOT_SYMBOL
, t2.STRIKE
, t2.BID_EOD as put_bid
, t2.ASK_EOD as put_ask
from dbo.OptionsEOD as t2
where OPTION_TYPE = 'P'
) as t4
on t3.UNDERLYING_SYMBOL = t4.UNDERLYING_SYMBOL
and t3.QUOTE_DATE = t4.QUOTE_DATE
and t3.EXPIRATION = t4.EXPIRATION
and t3.STRIKE = t4.STRIKE
) t2
inner join dbo.VIXNearTermExpiration as t6
on t2.UNDERLYING_SYMBOL = t6.UNDERLYING_SYMBOL
and t2.QUOTE_DATE = t6.QUOTE_DATE
and t2.EXPIRATION = t6.EXPIRATION
and t2.ROOT_SYMBOL = t6.ROOT_SYMBOL
-- option 2 - see big assumption
select t2.UNDERLYING_SYMBOL
, t2.QUOTE_DATE
, t2.EXPIRATION
, t2.ROOT_SYMBOL
, t2.STRIKE
, t2.put_bid
, t2.put_ask
, t2.call_bid
, t2.call_ask
, abs((t2.call_ask + t2.call_bid) / 2.0 - (t2.put_ask + t2.put_bid) / 2.0) as price_difference
from (select t2.UNDERLYING_SYMBOL
, t2.QUOTE_DATE
, t2.EXPIRATION
, t2.ROOT_SYMBOL
, t2.STRIKE
, max(case when t2.OPTION_TYPE = 'P' then t2.BID_EOD else null end) as put_bid
, max(case when t2.OPTION_TYPE = 'P' then t2.ASK_EOD else null end) as put_ask
, max(case when t2.OPTION_TYPE = 'C' then t2.BID_EOD else null end) as call_bid
, max(case when t2.OPTION_TYPE = 'C' then t2.ASK_EOD else null end) as call_ask
, max(case when t2.OPTION_TYPE = 'P' then 'Y' else null end) as has_put
, max(case when t2.OPTION_TYPE = 'C' then 'Y' else null end) as has_call
from dbo.OptionsEOD as t2
inner join dbo.VIXNearTermExpiration as t6
on t2.UNDERLYING_SYMBOL = t6.UNDERLYING_SYMBOL
and t2.QUOTE_DATE = t6.QUOTE_DATE
and t2.EXPIRATION = t6.EXPIRATION
and t2.ROOT_SYMBOL = t6.ROOT_SYMBOL
where t2.OPTION_TYPE in ('P', 'C')
group by t2.UNDERLYING_SYMBOL
, t2.QUOTE_DATE
, t2.EXPIRATION
, t2.ROOT_SYMBOL
, t2.STRIKE
) t2
where t2.has_put = 'Y'
and t2.has_call = 'Y'
June 6, 2023 at 3:29 pm
regarding your last query (from the second plan). two options below
option 1 is just a adjustment of your original query - does same thing but with 1 less join. may or not be faster
option 2 is a rewrite - has a big assumption that your original query would NEVER return more than 1 row per combination of the following columns. if there is a case it does then the query is giving incorrect results.
-- original query
select t3.UNDERLYING_SYMBOL
, t3.QUOTE_DATE
, t3.EXPIRATION
, t3.ROOT_SYMBOL
, t3.STRIKE
, t3.call_bid
, t3.call_ask
, t4.put_bid
, t4.put_ask
, abs((t3.call_ask + t3.call_bid) / 2.0 - (t4.put_ask + t4.put_bid) / 2.0) as price_difference
from (select t1.UNDERLYING_SYMBOL
, t1.QUOTE_DATE
, t1.EXPIRATION
, t1.ROOT_SYMBOL
, t1.STRIKE
, t1.BID_EOD as call_bid
, t1.ASK_EOD as call_ask
from dbo.OptionsEOD as t1
inner join dbo.VIXNearTermExpiration as t5
on t1.UNDERLYING_SYMBOL = t5.UNDERLYING_SYMBOL
and t1.QUOTE_DATE = t5.QUOTE_DATE
and t1.EXPIRATION = t5.EXPIRATION
and t1.ROOT_SYMBOL = t5.ROOT_SYMBOL
where t1.OPTION_TYPE = 'C'
) as t3
inner join (select t2.UNDERLYING_SYMBOL
, t2.QUOTE_DATE
, t2.EXPIRATION
, t2.ROOT_SYMBOL
, t2.STRIKE
, t2.BID_EOD as put_bid
, t2.ASK_EOD as put_ask
from dbo.OptionsEOD as t2
inner join dbo.VIXNearTermExpiration as t6
on t2.UNDERLYING_SYMBOL = t6.UNDERLYING_SYMBOL
and t2.QUOTE_DATE = t6.QUOTE_DATE
and t2.EXPIRATION = t6.EXPIRATION
and t2.ROOT_SYMBOL = t6.ROOT_SYMBOL
where OPTION_TYPE = 'P'
) as t4
on t3.UNDERLYING_SYMBOL = t4.UNDERLYING_SYMBOL
and t3.QUOTE_DATE = t4.QUOTE_DATE
and t3.EXPIRATION = t4.EXPIRATION
and t3.STRIKE = t4.STRIKE
-- option 1
select t2.*
from (
select t3.UNDERLYING_SYMBOL
, t3.QUOTE_DATE
, t3.EXPIRATION
, t3.ROOT_SYMBOL
, t3.STRIKE
, t3.call_bid
, t3.call_ask
, t4.put_bid
, t4.put_ask
, abs((t3.call_ask + t3.call_bid) / 2.0 - (t4.put_ask + t4.put_bid) / 2.0) as price_difference
from (select t1.UNDERLYING_SYMBOL
, t1.QUOTE_DATE
, t1.EXPIRATION
, t1.ROOT_SYMBOL
, t1.STRIKE
, t1.BID_EOD as call_bid
, t1.ASK_EOD as call_ask
from dbo.OptionsEOD as t1
where t1.OPTION_TYPE = 'C'
) as t3
inner join (select t2.UNDERLYING_SYMBOL
, t2.QUOTE_DATE
, t2.EXPIRATION
, t2.ROOT_SYMBOL
, t2.STRIKE
, t2.BID_EOD as put_bid
, t2.ASK_EOD as put_ask
from dbo.OptionsEOD as t2
where OPTION_TYPE = 'P'
) as t4
on t3.UNDERLYING_SYMBOL = t4.UNDERLYING_SYMBOL
and t3.QUOTE_DATE = t4.QUOTE_DATE
and t3.EXPIRATION = t4.EXPIRATION
and t3.STRIKE = t4.STRIKE
) t2
inner join dbo.VIXNearTermExpiration as t6
on t2.UNDERLYING_SYMBOL = t6.UNDERLYING_SYMBOL
and t2.QUOTE_DATE = t6.QUOTE_DATE
and t2.EXPIRATION = t6.EXPIRATION
and t2.ROOT_SYMBOL = t6.ROOT_SYMBOL
-- option 2 - see big assumption
select t2.UNDERLYING_SYMBOL
, t2.QUOTE_DATE
, t2.EXPIRATION
, t2.ROOT_SYMBOL
, t2.STRIKE
, t2.put_bid
, t2.put_ask
, t2.call_bid
, t2.call_ask
, abs((t2.call_ask + t2.call_bid) / 2.0 - (t2.put_ask + t2.put_bid) / 2.0) as price_difference
from (select t2.UNDERLYING_SYMBOL
, t2.QUOTE_DATE
, t2.EXPIRATION
, t2.ROOT_SYMBOL
, t2.STRIKE
, max(case when t2.OPTION_TYPE = 'P' then t2.BID_EOD else null end) as put_bid
, max(case when t2.OPTION_TYPE = 'P' then t2.ASK_EOD else null end) as put_ask
, max(case when t2.OPTION_TYPE = 'C' then t2.BID_EOD else null end) as call_bid
, max(case when t2.OPTION_TYPE = 'C' then t2.ASK_EOD else null end) as call_ask
, max(case when t2.OPTION_TYPE = 'P' then 'Y' else null end) as has_put
, max(case when t2.OPTION_TYPE = 'C' then 'Y' else null end) as has_call
from dbo.OptionsEOD as t2
inner join dbo.VIXNearTermExpiration as t6
on t2.UNDERLYING_SYMBOL = t6.UNDERLYING_SYMBOL
and t2.QUOTE_DATE = t6.QUOTE_DATE
and t2.EXPIRATION = t6.EXPIRATION
and t2.ROOT_SYMBOL = t6.ROOT_SYMBOL
where t2.OPTION_TYPE in ('P', 'C')
group by t2.UNDERLYING_SYMBOL
, t2.QUOTE_DATE
, t2.EXPIRATION
, t2.ROOT_SYMBOL
, t2.STRIKE
) t2
where t2.has_put = 'Y'
and t2.has_call = 'Y'
thank you!
option 1 - it is producing more records than the original. do you know why?
option 2 - your assumption is ok. this one produces the same results as original but faster. it is taking 7 sec vs 10 sec compared to earlier
June 6, 2023 at 5:15 pm
option 1 is incorrect - just discard it.
can you give the plan for the second? was expecting it to be a bit faster than that.
June 6, 2023 at 5:24 pm
option 1 is incorrect - just discard it.
can you give the plan for the second? was expecting it to be a bit faster than that.
no problem
here you go
June 9, 2023 at 2:55 am
option 1 is incorrect - just discard it.
can you give the plan for the second? was expecting it to be a bit faster than that.
no problem
here you go
https://www.brentozar.com/pastetheplan/?id=B1Ons1TUn%5B/quote%5D
Did you get a chance to look into this?
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply