How Can I Speed This Query Up?

Question

How Can I Speed This Query Up?

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply

water490 SSC Eights! Points: 918 More actions · Answer 1

water490 wrote:

frederico_fonseca wrote:
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?

Is someone able to help me with this?

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 2

water490 wrote:

water490 wrote:
frederico_fonseca wrote:
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?
Is someone able to help me with this?

Probably not...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 3

It turns out that you had an "anomaly" at the end of your link. Here's the correct link...

https://www.brentozar.com/pastetheplan/?id=B1Ons1TUn

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16026 More actions · Answer 4

not much you can do about that query (which is the version I gave you) - the nature of the query does not really allow for pre-filtering or use of other type of construct. only potential (unlikely) improvement would be if the rows with P and C on table dbo.OptionsEOD are less than 50% of overall rows.

on that case retrieving all those rows in advance to a temp table and then adding a clustered index (NOT columnstore) to that temp table with the following columns may help - I'll leave it up to you to try it out

( QUOTE_DATE

, EXPIRATION

, ROOT_SYMBOL

, STRIKE

, UNDERLYING_SYMBOL

)

water490 SSC Eights! Points: 918 More actions · Answer 5

frederico_fonseca wrote:

not much you can do about that query (which is the version I gave you) - the nature of the query does not really allow for pre-filtering or use of other type of construct. only potential (unlikely) improvement would be if the rows with P and C on table dbo.OptionsEOD are less than 50% of overall rows.
on that case retrieving all those rows in advance to a temp table and then adding a clustered index (NOT columnstore) to that temp table with the following columns may help - I'll leave it up to you to try it out
( QUOTE_DATE , EXPIRATION , ROOT_SYMBOL , STRIKE , UNDERLYING_SYMBOL )

all records are either P or C so there is no savings from pre-filtering them.

Its ok. I am happy with the improvement so far. The old query took 2.5 minutes to run. The new one takes 40 seconds. Great improvement. I am super happy with the results.

Thank you for your help. Much appreciated.