The clustered index issue is hard as these are staging tables and the method we use from change tracking is prone to duplicate inserts sometimes. Its far from ideal. I...

Yes, Unfortunately we are not using SSIS so this is the "Transformation" from source to dimension using SQl procs.

WOW, you have to be joking. 14 seconds row count on the money. Attached is the execution plan.

Thank you both so very much. Holy molly :-D:-D

Hi Scott,

I tried your code and I get this error with the MAX()

Msg 8124, Level 16, State 1, Line 16

Multiple columns are specified in an aggregated expression containing an outer...

It would have to be combination of PatientProfileID and Databaskey in the Source system its PatientProfileID as the PK but we are currently loading multiple source systems into one tables...

I should have said this, my appologies. This is a Data Warehouse Enviroment. Those heaps are staging/landing tables. We do not use SSIS against my judgement so this code...

Thats absolutely correct Lynn, I'm not a fan of it but I have no choice as its a source Production table.

I messaged you both the information you asked for.

When I uncomment Language table on the join I get the correct estimate of a little over 1 million rows, however if I then uncomment this "Preferredlanguage = ISNULL(ISNULL(L.Shortdescription, Languagelist.Description),...

Here is the query

SELECT DISTINCT

Patientsourceid = Pp.Patientprofileid

, Alternateid = Pp.Patientid

, Pp.Pid

, Databaseid = Db.Databaseid

, Patientstatus = CASE

WHEN Pp.Pstatus = 'A'...

UPDATE

I added a index on the table "List" DatabaseKey,MedlistsID and got a significant improvement. I still have a issue and have narrowed it down to a single join with...

Most front end BI I see is done in tools like Tableau, Power BI, SISENSE, SSRS... So I guess i'm confused why you would need to be strong in C#...

Thanks gentlemen I'm still tuning this query but I got it from 7 hours to a couple minutes now which is acceptable. I started doing the temp table idea by...

Hi Erik,

Thanks for the suggestion going to try both a CTE and a temp table. I'll report back my results as soon as i'm done.

Thanks

It's mainly a performance reason. I thought filtered index works best with columns with columns with a lot of NULLS and would a filtered index get used by the query...

Thanks guys I had just never seen it before and so the syntax threw me. It makes sense now. Thanks !!