Why does my SQL script take ages to run? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Why does my SQL script take ages to run?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-19 : 17:23:56

iyabo writes "Hi,
Everytime I try to run the script below, it seriously slows the SQL server down or "kills" it - and it also takes hours to run. Just by scanning over it, can you please tell me if you can see where the problem is (maybe too many left joins). Thank you.

select
local_refno as LocalPatientIdentifier,
left(ptnt_name, charindex(char(32), ptnt_name) ) as FirstName,
case when ptnt_name <> '' then ltrim(right(ptnt_name, len(ptnt_name) - charindex( char(32), ptnt_name) )) else '' end as Surname,
convert(char(10), base_table.dob,3) as DateOfBirth,
addr as Address,
ltrim(left(right(base_table.pstcd,4),3)) as CodeOfUsualAddress,
ltrim(left(right(base_table.pstcd,4),3)) as CodeOfNationality,
base_table.pstcd,
convert(char(10), base_table.cepi_sd, 103) as EpisodeStart,
convert(char(10), base_table.cepi_ed, 103) as EpisodeEnd,
base_table.spclty as Specialty,
base_table.spclty as MainSpef,
SPEC.spclty_name as Description,
base_table.hrg as HRG,
base_table.prchsr + '-' + 'xxx' + '-' + 'xx' as Contract,
base_table.band as Band,
case when activity_type in ('ELEC', 'NON_ELEC')
then
LPRICE.ip_val
when activity_type in ('DC')
then
LPRICE.dc_val
end as BandValue,
base_table.cntrt_refno_spec + base_table.cntrt_refno_prod + 'xx' as ContractSerialNumber,
mrtl_stts as MaritalStatusCode,
sex as SexCode,
convert(char(10), base_table.hpspll_sd,103) as AdmissionDate,
convert(char(10), base_table.hpspll_ed,103) as DischargeDate,
base_table.ptnt_clasfn as PatientClassificationCode,
base_table.ptnt_cat as AdminCategoryCode,
base_table.adm_mthd as AdmissionMethodCode,
base_table.adm_sce as AdmissionSourceCode,
base_table.dschg_mthd as DischargeMethodCode,
base_table.dschg_dest as DischargeDestinationCode,
base_table.cnsltnt as ConsultantCode,
base_table.dgnss as PrimarydiagnosisCode,
DGNSS2.dgnss as SubsidiaryDiagnosisCode,
DGNSS3.dgnss as SecondaryDiagnosisCode1,
DGNSS4.dgnss as SecondaryDiagnosisCode2,

OPRTN1.oprtn as PrimaryOperationCode,
OPRTN2.oprtn as SecondaryOperationCode1,
OPRTN3.oprtn as SecondaryOperationCode2,
OPRTN4.oprtn as SecondaryOperationCode3,

OPRTN1.oprtn_date as PrimaryOperationDate,
OPRTN2.oprtn_date as SecondaryOperationDate1,
OPRTN3.oprtn_date as SecondaryOperationDate2,
OPRTN4.oprtn_date as SecondaryOperationDate3,

base_table.reg_gp as RegisteredGpCode,
base_table.reg_gpprct as RegisteredGpPracticeCode,
base_table.ref_gp as ReferrerCode,
base_table.ref_gpprct as ReferringOrganisationCode,
hosp as LocalHospitalCode,
base_table.ckey as SourceEntityRecno

from base_table

left join cepi
on cepi.ckey = base_table.ckey

left join spclty as SPEC
on SPEC.spclty = base_table.spclty

left join ptnt_oprtn as OPRTN1
on OPRTN1.ckey = base_table.ckey
and OPRTN1.oprtn_mkr = 1

left join ptnt_oprtn as OPRTN2
on OPRTN1.ckey = base_table.ckey
and OPRTN1.oprtn_mkr = 2

left join ptnt_oprtn as OPRTN3
on OPRTN1.ckey = base_table.ckey
and OPRTN1.oprtn_mkr = 3

left join ptnt_oprtn as OPRTN4
on OPRTN1.ckey = base_table.ckey
and OPRTN1.oprtn_mkr = 4

left join ptnt_dgnss as DGNSS2
on DGNSS2.ckey = base_table.ckey
and DGNSS2.dgnss_mkr = 2

left join ptnt_dgnss as DGNSS3
on DGNSS3.ckey = base_table.ckey
and DGNSS3.dgnss_mkr = 3

left join ptnt_dgnss as DGNSS4
on DGNSS4.ckey = base_table.ckey
and DGNSS4.dgnss_mkr = 4

left join lu_prc_trf2 as LPRICE
on LPRICE.band = base_table.band"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-05-19 : 17:47:49

There are so many possibilities and many of them not visible just from looking at the syntax. You're joining 11 tables which can have a serious impact. How many rows are in these tables? The order in which they're joined can sometimes make a difference. Are there any indexes defined in this database? I don't see a WHERE clause anywhere in your statement, so it is working on the entire contents of the tables. You're retrieving 37 different fields which will have an impact. What is the client that is returning the data? It may be waiting to display any results until the entire result set is completed, which can make things appear to be slower, especially when you're processing all the contents of your tables.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources