A chairde - Morning all! @ypercubeᵀᴹ - looks like EB has cast some light on our issue - but his answer doens't really chime with my tests... I'm going to double-check and reply to his comment under my answer...
> Your 1st (fast) query plan shows 2x Parallel Seq Scan and 1x Parallel Index Only Scan. Your 2nd (slow) query plan has no parallel queries. Damage done.
@Vérace you can add to your answer that one difference between the 2 plans provided (what I can see, I may have missed something else) is that the first (faster) one has Parallel Seq Scan on xformula xformula_1 while the 2nd has Seq Scan on xformula xformula_1.
@Vérace did you manage to get a parallel plan in your tests, while using a function?
@ypercubeᵀᴹ - yes, you were on the money... but the MATERIALIZED directive still makes a difference... I'm finishing my breakfast - will look at this presently - don't want to cross swords with EB without having my homework done! :-) Luckily, my home machine is multi-processor and I was getting parallel plans without functions and the function blocked that - so that's one I can test... I'll keep you posted here when I respond to EB!
I am pulling data from inSQL using the SQL Query below:
SET QUOTED_IDENTIFIER OFF
SELECT * FROM OPENQUERY(INSQL, "SELECT DateTime, [X],[Y], [Z], [A]
FROM WideHistory
WHERE wwRetrievalMode = 'Cyclic'
AND wwResolution = 1000
AND wwVersion = 'Latest'
AND DateTime >= '20210601 20:02:33.000'
AN...