Monday, June 17, 2013

[T-SQL] performance tunning.

[T-SQL] performance tunning.

performance tunning.

Posted: 16 Jun 2013 11:50 PM PDT

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_IDLEFT OUTER JOIN W_ORGANIZATION LOCSITEORG ON LOCSITE.ORG_ID = LOCSITEORG.ORG_IDJOIN W_ORGANIZATION PARENT_ORG ON DBO.ORG_ID_FROM_DDID(VISIT.DATA_DOMAIN_ID) = PARENT_ORG.ORG_IDJOIN W_PATIENT PATIENT ON VISIT.PAT_ID = PATIENT.PAT_IDJOIN 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_IDLEFT 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

how to get name only from these column of enmae from emp table?

Posted: 16 Jun 2013 06:33 PM PDT

Hai friends, My emp Table iscreate table emp(location varchar(20),ename varchar(30))insert into emp (location,ename) values('A','00001:ravi')insert into emp (location,ename) values('A','00002:rahie')insert into emp (location,ename) values('B','00003:raghul')insert into emp (location,ename) values('B','00004:ram')my requring output isonly name from ename column of emp table

Need Query for a problem

Posted: 16 Jun 2013 01:57 AM PDT

Hello Everybody,Consider the following table,create table SampleTable(Category varchar(100), Organisams varchar(100) )Insert into Sampletable values ('Animals','Lion')Insert into Sampletable values ('Birds','Dove')Insert into Sampletable values ('Plants','Neem')Insert into Sampletable values ('Animals','Tiger')Insert into Sampletable values ('Birds','Eagle')Insert into Sampletable values ('Plants','Mango Tree')Insert into Sampletable values ('Animals','Cow')Insert into Sampletable values ('Birds','Parrot')Insert into Sampletable values ('Plants','Lime Tree')I Need following output,Category | Organisms-----------------------------Animals | Lion,Tiger,CowBirds | Dove,Eagle,ParrotPlants | Neem,Mango Tree,Lime TreeThanks in Advance
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog