Why is my SQL script taking ages to run?

[iyabo]

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

[steve0]

Have you checked the simple stuff....?
Are your indexes up to date?
tables 'optimized'?
Did you try EXPLAIN before the SELECT?
How big are the tables?
Is your my.cnf tweaked?
Got enough memory?

[CharleneH]

without a where clause you are essential scanning tables on every join.