Final answer, shamelessly borrowing from Jonathan, still without CROSS APPLY.  Also, Jonathan's answer with sample data cte included (copy/paste/run).

/* borrowed */
with
LifeCycleMaster_cte(Revision_ID, ZPartID, LastCheckDate) as (
select 12, 10,...

Well I was close.  Should've summarized from the beginning.  I thought about CROSS APPLY but it seems fussy when there's no TVF because there's still the old ways.  The DISTINCT...

with
LifeCycleMaster_cte(Revision_ID, ZPartID, LastCheckDate) as (
select 12, 10, '12/12/2015'
union all
select 15, 120, '12/01/2014'
union all
select 15, 130, '05/05/2016'
union all
select 20, 170, '09/03/2013'
union all
select 20, 200, '09/05/2016'
union all
select 20, 300,...

One way to simplify things could be to resolve the hierarchical adjacency between airports, countries, and areas using a table.  The ServicLevel case logic could also be replaced by a...

Currently there's no way to join rows from table t...

How is the data model originally populated?  The examples provided set IDENTITY_INSERT ON.  Are the examples representative of how inserts into the Child db tables occur in actual usage cases? ...

How does table t join to table t1?  Are the rows intended to be in sequential 1-to-1 correspondence?  If yes, why not create only 1 table instead of 2?

Is it required to read the file using ADF?  There are a few ways to do it with just Sql Server (and without ADF).   The Json could be read from...

Here's my attempt.  Because you're expecting a lot of rows from this query I wouldn't bother with outer applying top(1)'s.  Rather it might make sense to break the cte into...

Have you considered using a tool to simulate requests?  Some DBA's are cynical about performance claims.  Sometimes vendor examples are designed to support the solution being offered by salesmen.  I've...

You could row multiply and summarize across large sequences using a tally function and it wouldn't matter which db instance.  These peg the cpu at 100%.  If you'd like it...

Thanks for the advice, Ill look into that.

We have a caching issue with our site which can make it extremely slow at times, but I think the main issue...

Is Zoho slow or is your site slow or are both slow?  One way to gain some traction over the situation could be to recreate the data flow without the...

The sample data didn't contain any duplicates so I added a few.

DROP TABLE if exists #mytable;
go
CREATE TABLE #mytable(

Id INTEGER NULL

,Empid INTEGER NULL

,CatId INTEGER NULL

,age NUMERIC(5,2) NULL

,Type VARCHAR(5)...

The alternate way to pass structured data to a stored procedure is json.   Imo json is the way to go.