Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


performance tunning.


performance tunning.

Author
Message
ekant_alone
ekant_alone
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 468
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
holly.irick
holly.irick
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 6
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.
Mike John
Mike John
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2674 Visits: 5958
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.



Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16592 Visits: 17024
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ekant_alone
ekant_alone
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 468
Thanks alot. Will try to fix it ASAP
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5699 Visits: 7660
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.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search