September 30, 2016 at 9:19 am
Hello All,
I have been struggling with this plan all morning. I have attempted different indexes. Statistics are up to date. But I keep getting a query plan thats increasing from 1 million rows to a astronomical amount. The table PatientProfile has several left joined look up tables. I am struggling to fix this issue and in some cases adding a index seemed to make it worse.
Any help appreciated.
Attached is the execution plan.
***SQL born on date Spring 2013:-)
September 30, 2016 at 9:44 am
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 the table "Language" that I'm still trying to figure out.
Attached is the new query plan
***SQL born on date Spring 2013:-)
September 30, 2016 at 9:52 am
Can you post the code?
September 30, 2016 at 9:53 am
And maybe the DDL for the tables including indexes?
September 30, 2016 at 9:58 am
Here is the query
SELECT DISTINCT
Patientsourceid = Pp.Patientprofileid
, Alternateid = Pp.Patientid
, Pp.Pid
, Databaseid = Db.Databaseid
, Patientstatus = CASE
WHEN Pp.Pstatus = 'A' THEN 'Active'
WHEN Pp.Pstatus = 'I' THEN 'Inactive'
WHEN Pp.Pstatus = 'X' THEN 'Deceased'
WHEN Pp.Pstatus = 'O' THEN 'Obsolete'
ELSE 'Unknown'
END
, Patientname = ISNULL(Pp.First, '')+' '+ISNULL(Pp.Middle, '')+' '+ISNULL(Pp.Last, '')+' '+ISNULL(Pp.Suffix, '')
, Firstname = ISNULL(Pp.First, 'N/A')
, Lastname = ISNULL(Pp.Last, 'N/A')
, Middlename = ISNULL(Pp.Middle, 'N/A')
, Suffix = ISNULL(Pp.Suffix, 'N/A')
, Birthdate = ISNULL(Pp.Birthdate, '01/01/2099')
, Gender = CASE
WHEN Pp.Sex = 'M' THEN 'Male'
WHEN Pp.Sex = 'F' THEN 'Female'
ELSE 'Unknown'
END
, Ethnicity = CASE
WHEN Eth.[Description] LIKE 'Hisp%' THEN 'Hispanic or Latino'
WHEN Eth.[Description] LIKE 'Not%' THEN 'Non-Hispanic or Latino'
WHEN Eth.[Description] LIKE '%Declined%' THEN 'Declined'
ELSE 'Unknown'
END
, Maritalstatus = ISNULL(Ms.]Description], 'Unknown')
--, Preferredlanguage = ISNULL(ISNULL(L.Shortdescription, Languagelist.Description), 'Unknown')
, Employmentstatus = ISNULL(Em.[Description], 'N/A')
, Occupation = ISNULL(Pp.Empoccup, 'N/A')
, [Address] = ISNULL(ISNULL(Pp.Address1, '')+' '+ISNULL(Pp.Address2, ''), 'N/A')
, City = ISNULL(Pp.City, 'N/A')
, County = ISNULL(Pp.County, 'N/A')
, [State] = ISNULL(Pp.[State], 'N/A')
, Zip = ISNULL(Pp.Zip, 'Other')
, Primaryphone = CASE
WHEN NULLIF(Pp.Phone1, '') IS NOT NULL THEN '('+SUBSTRING(Pp.Phone1, 1, 3)+') '+SUBSTRING(Pp.Phone1, 4, 3)+'-'+SUBSTRING(Pp.Phone1, 7, 4)
ELSE 'N/A'
END
, EmailAddress = ISNULL(Pp.Emailaddress, 'N/A')
--, Primarycareprovider = ISNULL(Pc.Listname, 'N/A')
--, Preferredcareprovider = ISNULL(Prf.Listname, 'N/A')
--, Primaryinsurancecarrier = ISNULL(Ic.Listname, 'N/A')
--, Facility = ISNULL(Fac.Listname, 'No Facility Assigned')
----, Advanceddirectives = ISNULL(Ad.Description, 'None')
--, Legalguardian = ISNULL(Guard.Description, 'None')
-- , Changetrackingid = CHECKSUM(Db.Databaseid, Pp.Patientid, Pp.Pstatus, Pp.First, Pp.Last, Pp.Birthdate, Pp.Sex, Eth.Description, Ms.Description, ISNULL((ISNULL(L.Shortdescription, Languagelist.Description))), 'Unknown'), Pp.Address1, Pp.City, Pp.County, Pp.State, Pp.Zip, Pp.Phone1, Pp.Emailaddress, Pc.Listname, Prf.Listname, Fac.Listname )---, Ad.Description, Guard.Description, Pp.Pid)
FROM
Patientprofile Pp
INNER JOIN Visanalyticshouse.Dimension.[Database] Db ON Db.Databasekey = Pp.Databasekey
LEFT JOIN Lists Eth ON Pp.Ethnicitymid = Eth.Medlistsid AND Eth.Databasekey = Pp.Databasekey
LEFT JOIN Lists Ms ON Ms.Medlistsid = Pp.Maritalstatusmid AND Ms.Databasekey = Pp.Databasekey
LEFT JOIN Lists Em ON Em.Medlistsid = Pp.Empstatusmid AND Em.Databasekey = Pp.Databasekey
LEFT JOIN Lists Languagelist ON Languagelist.Medlistsid = Pp.Preflanguagemid AND Languagelist.Databasekey = Pp.Databasekey
-- LEFT JOIN [Language] L ON Pp.Languageid = L.Languageid AND L.Databasekey = Pp.Databasekey
-- LEFT JOIN Patientinsurance pi ON Pi.Databasekey = Pp.Databasekey AND Pi.Patientprofileid = Pp.Patientprofileid AND Pi.Orderforclaims = 1
--LEFT JOIN Insurancecarriers Ic ON Ic.Insurancecarriersid = Pi.Insurancecarriersid AND Ic.Databasekey = Pp.Databasekey
--LEFT JOIN Doctorfacility Pc ON Pc.Doctorfacilityid = Pp.Primarycaredoctorid AND Pc.Databasekey = Pp.Databasekey
--LEFT JOIN Doctorfacility Prf ON Prf.Doctorfacilityid = Pp.Doctorid AND Prf.Databasekey = Pp.Databasekey
--LEFT JOIN Doctorfacility Fac ON Fac.Doctorfacilityid = Pp.Facilityid AND Fac.Databasekey = Pp.Databasekey
-- LEFT JOIN Directiv Ad ON Ad.Pid = Pp.Pid AND Ad.Databasekey = Pp.Databasekey AND Ad.Description IN('Do not resuscitate', 'No heroic measures', 'No electrical cardioversion', 'No artificial life support', 'No artificial ventilatory support')
-- LEFT JOIN Patientrelationship Pr ON Pr.Patientprofileid = Pp.Patientprofileid AND Pr.Databasekey = Pp.Databasekey AND Pr.Type = 5
-- LEFT JOIN Lists Guard ON Guard.Medlistsid = Pr.Relationshiptypemid AND Guard.Databasekey = Pr.Databasekey;
***SQL born on date Spring 2013:-)
September 30, 2016 at 10:02 am
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), 'Unknown')" in the select it jumps to 3.8 million rows in the execution plan.
The current index on the Language table is
CREATE NONCLUSTERED INDEX [IX_Language_DatabasKey_LanguageID] ON [dbo].[Language]
(
[DatabaseKey] ASC,
[LanguageId] ASC
)
***SQL born on date Spring 2013:-)
September 30, 2016 at 10:11 am
Is it possible that you could post the actual execution plan instead of the estimated plan? That will tell us much more. What you posted may not be the plan that is actually being used.
Looking at your query (and not having any DDL or sample data) I see that you are doing 4 LEFT joins to your Lists table; in some cases, for a single value. I suspect that you could find a way to reduce this to a single join. In each case the self join is being performed using hash match; each case with a high estimated cost. I bet you can really speed things up if you can get rid of those.
LEFT JOIN Lists Eth ON Pp.Ethnicitymid = Eth.Medlistsid AND Eth.Databasekey = Pp.Databasekey
LEFT JOIN Lists Ms ON Ms.Medlistsid = Pp.Maritalstatusmid AND Ms.Databasekey = Pp.Databasekey
LEFT JOIN Lists Em ON Em.Medlistsid = Pp.Empstatusmid AND Em.Databasekey = Pp.Databasekey
LEFT JOIN Lists Languagelist ON Languagelist.Medlistsid = Pp.Preflanguagemid AND Languagelist.Databasekey = Pp.Databasekey
-- Itzik Ben-Gan 2001
September 30, 2016 at 10:13 am
Could we see the DDL including indexes for the tables?
September 30, 2016 at 10:20 am
Alan.B (9/30/2016)
Is it possible that you could post the actual execution plan instead of the estimated plan? That will tell us much more. What you posted may not be the plan that is actually being used.Looking at your query (and not having any DDL or sample data) I see that you are doing 4 LEFT joins to your Lists table; in some cases, for a single value. I suspect that you could find a way to reduce this to a single join. In each case the self join is being performed using hash match; each case with a high estimated cost. I bet you can really speed things up if you can get rid of those.
LEFT JOIN Lists Eth ON Pp.Ethnicitymid = Eth.Medlistsid AND Eth.Databasekey = Pp.DatabasekeyLEFT JOIN Lists Ms ON Ms.Medlistsid = Pp.Maritalstatusmid AND Ms.Databasekey = Pp.Databasekey
LEFT JOIN Lists Em ON Em.Medlistsid = Pp.Empstatusmid AND Em.Databasekey = Pp.Databasekey
LEFT JOIN Lists Languagelist ON Languagelist.Medlistsid = Pp.Preflanguagemid AND Languagelist.Databasekey = Pp.Databasekey
Looks like Lists is a super lookup table, one lookup table to control them all. We have a table like that here.
September 30, 2016 at 10:22 am
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.
***SQL born on date Spring 2013:-)
September 30, 2016 at 10:38 am
Any reason there aren't any primary keys on any of the tables? For instance, this table: Patientprofile?
September 30, 2016 at 10:42 am
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 is actually a Dimension Build from those source staging tables. We do incremental loads but sometimes do a full pull. I can add a Clustered Index if you think it would help and just drop it on full pulls.
***SQL born on date Spring 2013:-)
September 30, 2016 at 10:46 am
At this point I was just wondering, no recommendations yet.
September 30, 2016 at 11:21 am
I can't see any of the indexes, but perhaps you can combine the Lists lookups as below.
Also, you'll probably get best performance by clustering the Lists table by ( Databasekey, Medlistsid ) if it's not already.
SELECT DISTINCT
...
, Lall.Ethnicity
, Lall.Maritalstatus
, Lall.Preferredlanguage
, Lall.Employmentstatus
, Occupation = ISNULL(Pp.Empoccup, 'N/A')
...
FROM
Patientprofile Pp
INNER JOIN Visanalyticshouse.Dimension.[Database] Db ON Db.Databasekey = Pp.Databasekey
OUTER APPLY (
SELECT
ISNULL(MAX(CASE WHEN L.Medlistsid = Pp.Ethnicitymid
THEN CASE
WHEN L.Description LIKE 'Hisp%' THEN 'Hispanic or Latino'
WHEN L.Description LIKE 'Not%' THEN 'Non-Hispanic or Latino'
WHEN L.Description LIKE '%Declined%' THEN 'Declined'
ELSE 'Unknown'
END END), 'Unknown') AS Ethnicity,
ISNULL(MAX(CASE WHEN L.Medlistsid = Pp.Maritalstatusmid,
THEN L.Description
END), 'Unknown') AS Maritalstatus,
--add similar code for: Employmentstatus and Preferredlanguage
FROM Lists L
WHERE L.Databasekey = Pp.Databasekey AND
L.Medlistsid IN ( Pp.Ethnicitymid, Pp.Maritalstatusmid, Pp.Empstatusmid, Pp.Preflanguagemid )
) AS Lall
LEFT JOIN [Language] L ON Pp.Languageid = L.Languageid AND L.Databasekey = Pp.Databasekey
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 30, 2016 at 11:29 am
Is there one column or set of columns in PatientProfile that uniquely identifies each row of data?
If so, which column or columns?
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply