Link to home
Create AccountLog in
Avatar of soozh
soozhFlag for Sweden

asked on

TSQL - statement performance in stored procedure

Using MS SQL Server 2008.

I have a stored procedure that I am trying to improve its performance.
It reads in over 100 000 records and returns the results after about 6 seconds.  This is ok for me.

However now I have added a new statement which I have commented in the code.

The data consists of a series of patient visits for eye treatment.   I need to update each patient/eye visit with a diagnose given only on the patients/eyes first visit.  (Ursprung = ‘T’)
This update statement takes about 5 minutes.  I figure that I must be doing something wrong with the indexes in the table variable I am using.

Any advice?

ALTER  PROCEDURE [dbo].[GetStandardData] 
	@DateFrom date,
	@DateTo date,
	@Gender int,	/* 0 = man, 1 = woman, 2 = both */
    @StartAge int,
    @EndAge int,
    @Diagnose varchar(35),
    @MembraneType varchar(35),
    @OriginInPeriod int = 1
  
AS
BEGIN

declare @reportdata table(
  vst_id varchar(100),
  EyeID varchar(20),

  kli_KlinikNr int ,
  Klinik varchar(35),
  PatientID varchar(15),

  Öga char,
  Kön char,
  Besöksdag datetime,
  ÄrUrsprung char,
  MånaderFrånOrigin int,
  ÅlderBesök int,
  DiagnosAktuelltÖga varchar(50),
  Membrantyp varchar(50),
  BehTyp_Ingen int,
  BehTyp_PDT int,
  BehTyp_Laser int,
  BehTyp_Lucentis int,
  BehTyp_Macugen int,
  BehTyp_Avastin int,
  BehTyp_Triamcinolon int,
  BehTyp_AnecortAveac int,
  BehTyp_Annan int,
  OmEjBehandlOrsak varchar(50)
 
UNIQUE (kli_KlinikNr, EyeID, ÄrUrsprung, vst_id), 
UNIQUE (EyeID, ÄrUrsprung, Besöksdag, vst_id ), 
UNIQUE (ÄrUrsprung, EyeID, Besöksdag, vst_id ), 
UNIQUE (EyeID, vst_id ) 

);


declare @SelectEndDate datetime ;
declare @SelectStartDate datetime ;

-- Set up the search from the start of the register if we are interested in all records (@origininperiod=0)
if @OriginInPeriod = 0 set @SelectStartDate = '2007-01-01' 
else set @SelectStartDate = @DateFrom ;

-- If @OriginInPeriod = 1 then we are interested in all records upto today. (that have an original in the given period)
if @OriginInPeriod = 1 set @SelectEndDate = GETDATE()
else set @SelectEndDate = @DateTo ;  

select @SelectStartDate, @SelectEndDate ;

insert into @reportdata (
  vst_id,
  EyeID,
  kli_KlinikNr,
  Klinik,
  PatientID,
  Öga,
  Kön,
  Besöksdag,
  ÄrUrsprung,
  MånaderFrånOrigin,
  ÅlderBesök,
  DiagnosAktuelltÖga,
  Membrantyp,
  BehTyp_Ingen,
  BehTyp_PDT,
  BehTyp_Laser,
  BehTyp_Lucentis,
  BehTyp_Macugen,
  BehTyp_Avastin,
  BehTyp_Triamcinolon,
  BehTyp_AnecortAveac,
  BehTyp_Annan,
  OmEjBehandlOrsak 
)

select 
  vst_id,
  EyeID,
  kli_KlinikNr,
  kli_Namn as Klinik,
  pat_pid as PatientID,
  Öga,
  Kön,
  Besöksdag,
  ÄrUrsprung,
  MånaderFrånOrigin,
  ÅlderBesök,
  DiagnosAktuelltÖga,
  Membrantyp,
  BehTyp_Ingen,
  BehTyp_PDT,
  BehTyp_Laser,
  BehTyp_Lucentis,
  BehTyp_Macugen,
  BehTyp_Avastin,
  BehTyp_Triamcinolon,
  BehTyp_AnecortAveac,
  BehTyp_Annan,
  OmEjBehandlOrsak 
  
 from vw_VisitEyeData ved
 where ÄrGodkänd = 'T' 
/* this where condition is a mess but i have a seperate thread about it */

 and (Besöksdag between @SelectStartDate and @SelectEndDate)
 and substring(pat_pid, 11, 1) % 2 != @Gender
 and (ÅlderBesök between @StartAge and @EndAge)
 and EyeID in (Select EyeID from vw_VisitEyeData where pat_pid = ved.pat_pid and Öga = ved.Öga and ÄrGodkänd = 'T' and ÄrUrsprung = 'T' and DiagnosAktuelltÖga like @Diagnose) 
 and (Select count(EyeID) from vw_VisitEyeData where pat_pid = ved.pat_pid and Öga = ved.Öga and ÄrGodkänd = 'T' and ÄrUrsprung = 'T') = 1


/* THIS STATEMENT TAKES 5 MINUTES */
update @ReportData 
  set DiagnosAktuelltÖga = 
  ( select DiagnosAktuelltÖga From @ReportData rd
    where 
      rd.EyeId=[@ReportData].EyeID 
      and rd.ÄrUrsprung = 'T' 
  )
where  ÄrUrsprung <> 'T'

select * from @reportdata order by Besöksdag, EyeID, ÄrUrsprung, vst_id ;

return 0 ;
 
END

Open in new window

SOLUTION
Avatar of Wim_Bl
Wim_Bl
Flag of Belgium image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of soozh

ASKER

Hello,

I thought that these statements in the table definition created indexes:

UNIQUE (kli_KlinikNr, EyeID, ÄrUrsprung, vst_id),
UNIQUE (EyeID, ÄrUrsprung, Besöksdag, vst_id ),
UNIQUE (ÄrUrsprung, EyeID, Besöksdag, vst_id ),
UNIQUE (EyeID, vst_id )

Also i suspect you are talking about temporary tables and not table variables because of the '#' sign before the table name in your reply.

When i changed it to @reportdata i get the following error message :

Msg 102, Level 15, State 1, Procedure GetStandardData, Line 58
Incorrect syntax near '@reportData'.
Hi,

you have to change every @reportData to #reportData to get this working. The Unique keys that you mention only make sure that no duplicates can be entered for these columns.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account