June 17, 2013 at 6:50 am
I have a query. This select query has few fat tables attached to each other. Is there a way i can improve the performance of this query? currently it is table more that 50 secs.
SELECT VISIT.PAT_VISIT_ID id, (DBO.GET_CD_TITLE_FROM_CD_ID(VISIT.VISIT_TYP_CD, ''Visit Type'')) code_title, '''' code_mnemonic, VISIT.CRTE_DTTM created_at, VISIT.VISIT_END_DTTM ends_at, DBO.GET_CD_TITLE_BY_CDID_USERCODE(LOCSITE.SITE_TYP_CD, ''Mnemonic'') location_code_mnemonic, DBO.GET_CD_TITLE_BY_CDID_USERCODE(LOCSITE.SITE_TYP_CD, ''title'') location_code_title, LOCSITE.SITE_DESC location_site_description, LOCSITE.SITE_NM location_site_name, LOCSITEORG.ORG_NM organization_name, LOCSITEORG.ORG_ABBR organization_abbr, LOCSITEORG.ORG_ID organization_id, LOCSITEORG.CRTE_DTTM organization_created_at, LOCSITEORG.UPDT_DTTM organization_updated_at, VISIT.PAT_ID patient_id, VISIT.SCHEDULED_PROV_ID performer_id, PERFORMER.NM_FIRST performer_name_first, PERFORMER.NM_LAST performer_name_last, PERFORMER.NM_MID performer_name_middle, PERFORMER.NM_SUFFIX performer_suffix, (CASE WHEN PRINC.USER_NM = @P0 THEN 1 ELSE 0 END ) is_mine, DBO.GET_CD_TITLE_BY_CDID_USERCODE(VISIT.VISIT_REASON, ''Mnemonic'') reason_mnemonic, DBO.GET_CD_TITLE_BY_CDID_USERCODE(VISIT.VISIT_REASON, ''description'') reason_original_text,
DBO.GET_CD_TITLE_BY_CDID_USERCODE(VISIT.VISIT_REASON, ''title'')
reason_title, VISIT.VISIT_REASON_TXT reason_text, ( CASE ( SELECT COUNT(ALIAS_USE)
FROM W_CODE_ALIAS WHERE STS_CD =''137'' AND CD_ID = (VISIT.VISIT_REASON) AND ALIAS_USE =
''requiresConsent'') WHEN 0 THEN 0 ELSE 1 END) reason_requires_consent, VISIT.VISIT_START_DTTM
start_date, VISIT.SCHED_START_DTTM sched_start_date,
DBO.GET_CD_TITLE_BY_CDID_USERCODE(VISIT.LIFECYCLE_CD, ''title'') state, VISIT.UPDT_DTTM updated_at, PARENT_ORG.ORG_ABBR parent_org_abbr, PARENT_ORG.ORG_NM parent_org_name, PARENT_ORG.ORG_ID parent_org_id, PATIENT.NM_LAST patient_nm_last, PATIENT.NM_FIRST patient_nm_first, PATIENT.NM_MID patient_nm_mid, PATIENT.DOB patient_dob, DBO.GET_CD_TITLE_BY_CDID_USERCODE(PATIENT.GENDER_CD, ''Mnemonic'') patient_gender, DBO.GET_PATIENT_MRNS_BY_PATID(VISIT.PAT_ID) patient_mrn, MPI.ENTITY_ALIAS patient_mpi, PATIENT.UPDT_SRC_ID authority_id, PATREL.PAT_RELTN_ID patient_provider_rltnshp_id
FROM W_PATIENT_VISIT VISIT
LEFT OUTER JOIN W_LOCATION_SITE LOCSITE ON VISIT.ENTITY_LOC_ID = LOCSITE.LOC_SITE_ID
LEFT OUTER JOIN W_ORGANIZATION LOCSITEORG ON LOCSITE.ORG_ID = LOCSITEORG.ORG_ID
JOIN W_ORGANIZATION PARENT_ORG ON DBO.ORG_ID_FROM_DDID(VISIT.DATA_DOMAIN_ID) = PARENT_ORG.ORG_ID
JOIN W_PATIENT PATIENT ON VISIT.PAT_ID = PATIENT.PAT_ID
JOIN W_ENTITY_ALIAS MPI ON (VISIT.PAT_ID = MPI.ENTITY_ID
AND MPI.ALIAS_TYP_CD = '1000516'
AND MPI.STS_CD = '137')
LEFT OUTER JOIN W_PERSON PERFORMER ON VISIT.SCHEDULED_PROV_ID = PERFORMER.PERS_ID
LEFT OUTER JOIN W_MPI_USERID_VIEW PRINC ON (PRINC.PERS_ID = VISIT.SCHEDULED_PROV_ID)
LEFT OUTER JOIN W_PATIENT_RELATIONSHIP PATREL ON (VISIT.PAT_ID = PATREL.PAT_ID
AND PATREL.STS_CD = '137'
AND PATREL.ENTITY_TYP_CD = '935'
AND PATREL.ENTITY_ID IN
(SELECT PERS_ID
FROM W_ORG_USER_VIEW
WHERE USER_NM = @P1)
AND PATREL.RLTN_CD IN
(SELECT CD_ID
FROM W_CODE_ALIAS
WHERE CD_SET_CD IN
(SELECT CD_SET_CD
FROM W_CODE_SET
WHERE
CD_SET_NM ='Provider-Patient Relationship')
AND ALIAS_USE='title'
AND STS_CD = '137')
)
WHERE VISIT.STS_CD = '137'
AND VISIT.SCHEDULED_PROV_ID IN (@P2,
@P3,
@P4,
@P5,
@P6)
AND VISIT.SCHED_START_DTTM >= DBO.CONVERT_DATE_FROM_TO(@P7, @P8)
AND VISIT.SCHED_START_DTTM < dateadd(DAY,1,DBO.CONVERT_DATE_FROM_TO(@P9, @P10))
ORDER BY VISIT.VISIT_START_DTTM ASC
June 17, 2013 at 9:33 am
You have a ton of in-line function calls, those have to be evaluated for each row. Figure out a way to replace those: maybe set up a temp table / CTE to load with the results from the selected rows. Think about setting up table-valued functions rather than scalar ones.
June 17, 2013 at 9:34 am
There will be lots of things you can do.
Can you post the table definitions (create table etc) index definitions, sample execution plan for how this query is being executed, and a definition of what the function you are calling in the where clause "CONVERT_DATE_FROM_TO" is doing?
That will give people a starting point. Thanks
Mike John
I will second the note on in-line function calls - I started at the where clause and worked backwards so missed those.
June 17, 2013 at 10:31 am
There are a LOT of possible culprits in here. The excessive amount of scalar functions is certainly one of them. By my count there are 9 columns that are direct inline scalar function calls with a few more thrown into the joins and where predicates. I would be my hat that the scalar functions are the worst part of your performance woes. This level of scalar UDF means that your query is looking at every single row of these tables and given the names I am guessing these tables are quite large.
There are also a lot of variables in here (why are the names so generic?). I doubt you changed the variable names when you posted since all the rest of the information seems to be the real stuff. In general you will find working with code a lot easier if you give reasonable names to your variables. We have no idea what datatypes are here in your columns and all your variables. There may be tons of implicit conversions here too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2013 at 11:47 am
Thanks alot. Will try to fix it ASAP
June 21, 2013 at 1:18 pm
ekant,
If you continue to get stuck, one of the things that will help us the most is the .sqlplan. If you take a look in my sig, there's a link to an article that'll walk you through what you really want to get us for the best support. It's the index/tuning link.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply