Performance issue

Hello, we are experiencing severe performance degradation. The hardware is OK, we use SQL2005, our DB includes currently about 8.5 millions of images. We run montly DB mainenance (with indexes rebuilt, shrinking etc.) Conquest is currently capable of writing only one image per second, or so, while sending is still fast (more than 10 images/s). It seems that HW capabilities (10 GB RAM, 4 Xeon processors) are not fully exploited, I rarely see more than 25% processor usage (ie one processor). Any hints (besides of reducing the images, that would be a problem)?

regards, Federico

Hello, I disabled the DB, and in fact the speed increased significantly (from 5min 30sec to 20 sec for a patient containing 3 series /302 images/96 MB uncompressed).

Here it is some log snips:

pacsuser.log
20110105 17:21:56 "C-Store","FUSIONMGMT"
20110105 17:21:57 "C-Store","FUSIONMGMT"
20110105 17:21:58 "C-Store","FUSIONMGMT"
20110105 17:21:59 "C-Store","FUSIONMGMT"
20110105 17:22:00 "C-Store","FUSIONMGMT"
20110105 17:22:01 "C-Store","FUSIONMGMT"
20110105 17:22:02 "C-Store","FUSIONMGMT"
20110105 17:22:04 "C-Store","FUSIONMGMT"
20110105 17:22:05 "C-Store","FUSIONMGMT"
20110105 17:22:09 "C-Store","FUSIONMGMT"
20110105 17:22:11 "C-Store","FUSIONMGMT"
20110105 17:22:15 "C-Store","FUSIONMGMT"
20110105 17:22:16 "C-Store","FUSIONMGMT"
20110105 17:22:20 "C-Store","FUSIONMGMT"
20110105 17:22:21 "C-Store","FUSIONMGMT"
20110105 17:22:25 "C-Store","FUSIONMGMT"
20110105 17:22:26 "C-Store","FUSIONMGMT"
20110105 17:22:30 "C-Store","FUSIONMGMT"
20110105 17:22:31 "C-Store","FUSIONMGMT"
20110105 17:22:32 "C-Store","FUSIONMGMT"
20110105 17:22:33 "C-Store","FUSIONMGMT"
20110105 17:22:34 "C-Store","FUSIONMGMT"
20110105 17:22:35 "C-Store","FUSIONMGMT"
20110105 17:22:36 "C-Store","FUSIONMGMT"

serverstatus.log:

20110105 17:21:41 DGATE (1.4.15, build Tue Sep 29 18:20:16 2009, bits 32) is running as threaded server
20110105 17:21:41 Database type: ODBC connection
20110105 17:21:41 Started 1 export queue thread(s)
05/01/2011 17.21.43 [FUSIONDCM] User interface test: local server is running!
05/01/2011 17.21.43 [FUSIONDCM] set normal log from GUI
05/01/2011 17.21.46 [FUSIONDCM] set debug level from GUI
05/01/2011 17.21.46 [FUSIONDCM] set debug log from GUI
05/01/2011 17.21.55 [FUSIONDCM]
05/01/2011 17.21.55 [FUSIONDCM] UPACS THREAD 0: STARTED AT: Wed Jan 05 17:21:55 2011
05/01/2011 17.21.55 [FUSIONDCM] A-ASSOCIATE-RQ Packet Dump
05/01/2011 17.21.55 [FUSIONDCM] Calling Application Title : "FUSIONMGMT"
05/01/2011 17.21.55 [FUSIONDCM] Called Application Title : "FUSIONDCM"
05/01/2011 17.21.55 [FUSIONDCM] Application Context : "1.2.840.10008.3.1.1.1", PDU length: 16384
05/01/2011 17.21.55 [FUSIONDCM] Number of Proposed Presentation Contexts: 1
05/01/2011 17.21.55 [FUSIONDCM] Presentation Context 0 "1.2.840.10008.5.1.4.1.1.4" 1
05/01/2011 17.21.55 [FUSIONDCM] Server Command := 0001
05/01/2011 17.21.55 [FUSIONDCM] Message ID := 0003
05/01/2011 17.21.55 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.21.55 [FUSIONDCM] FreeStore Left 663300 on R:\
05/01/2011 17.21.56 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000002_1294244515_0000.dcm
05/01/2011 17.21.56 [FUSIONDCM] Server Command := 0001
05/01/2011 17.21.56 [FUSIONDCM] Message ID := 0005
05/01/2011 17.21.56 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.21.56 [FUSIONDCM] FreeStore Left 663300 on R:\
05/01/2011 17.21.57 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000004_1294244516_0001.dcm
05/01/2011 17.21.57 [FUSIONDCM] Server Command := 0001
05/01/2011 17.21.57 [FUSIONDCM] Message ID := 0007
05/01/2011 17.21.57 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.21.57 [FUSIONDCM] FreeStore Left 663300 on R:\
05/01/2011 17.21.58 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000006_1294244517_0002.dcm
05/01/2011 17.21.58 [FUSIONDCM] Server Command := 0001
05/01/2011 17.21.58 [FUSIONDCM] Message ID := 0009
05/01/2011 17.21.58 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.21.58 [FUSIONDCM] FreeStore Left 663299 on R:\
05/01/2011 17.21.59 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000008_1294244518_0003.dcm
05/01/2011 17.21.59 [FUSIONDCM] Server Command := 0001
05/01/2011 17.21.59 [FUSIONDCM] Message ID := 000b
05/01/2011 17.21.59 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.21.59 [FUSIONDCM] FreeStore Left 663299 on R:\
05/01/2011 17.22.00 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000010_1294244519_0004.dcm
05/01/2011 17.22.00 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.00 [FUSIONDCM] Message ID := 000d
05/01/2011 17.22.00 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.00 [FUSIONDCM] FreeStore Left 663299 on R:\
05/01/2011 17.22.01 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000012_1294244520_0005.dcm
05/01/2011 17.22.01 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.01 [FUSIONDCM] Message ID := 000f
05/01/2011 17.22.01 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.01 [FUSIONDCM] FreeStore Left 663299 on R:\
05/01/2011 17.22.02 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000014_1294244521_0006.dcm
05/01/2011 17.22.02 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.02 [FUSIONDCM] Message ID := 0011
05/01/2011 17.22.02 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.02 [FUSIONDCM] FreeStore Left 663299 on R:\
05/01/2011 17.22.04 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000016_1294244522_0007.dcm
05/01/2011 17.22.04 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.04 [FUSIONDCM] Message ID := 0013
05/01/2011 17.22.04 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.04 [FUSIONDCM] FreeStore Left 663299 on R:\
05/01/2011 17.22.05 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000018_1294244524_0008.dcm
05/01/2011 17.22.05 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.05 [FUSIONDCM] Message ID := 0015
05/01/2011 17.22.05 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.05 [FUSIONDCM] FreeStore Left 663299 on R:\
05/01/2011 17.22.09 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000020_1294244525_0009.dcm
05/01/2011 17.22.10 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.10 [FUSIONDCM] Message ID := 0017
05/01/2011 17.22.10 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.10 [FUSIONDCM] FreeStore Left 663299 on R:\
05/01/2011 17.22.11 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000022_1294244530_000a.dcm
05/01/2011 17.22.11 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.11 [FUSIONDCM] Message ID := 0019
05/01/2011 17.22.11 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.11 [FUSIONDCM] FreeStore Left 663299 on R:\
05/01/2011 17.22.15 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000024_1294244531_000b.dcm
05/01/2011 17.22.15 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.15 [FUSIONDCM] Message ID := 001b
05/01/2011 17.22.15 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.15 [FUSIONDCM] FreeStore Left 663299 on R:\
05/01/2011 17.22.16 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000001_1294244535_000c.dcm
05/01/2011 17.22.16 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.16 [FUSIONDCM] Message ID := 001d
05/01/2011 17.22.16 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.16 [FUSIONDCM] FreeStore Left 663299 on R:\
05/01/2011 17.22.20 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000003_1294244536_000d.dcm
05/01/2011 17.22.20 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.20 [FUSIONDCM] Message ID := 001f
05/01/2011 17.22.20 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.20 [FUSIONDCM] FreeStore Left 663299 on R:\
05/01/2011 17.22.21 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000005_1294244540_000e.dcm
05/01/2011 17.22.21 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.21 [FUSIONDCM] Message ID := 0021
05/01/2011 17.22.21 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.21 [FUSIONDCM] FreeStore Left 663298 on R:\
05/01/2011 17.22.25 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000007_1294244541_000f.dcm
05/01/2011 17.22.25 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.25 [FUSIONDCM] Message ID := 0023
05/01/2011 17.22.25 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.25 [FUSIONDCM] FreeStore Left 663298 on R:\
05/01/2011 17.22.26 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000009_1294244545_0010.dcm
05/01/2011 17.22.26 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.26 [FUSIONDCM] Message ID := 0025
05/01/2011 17.22.26 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.26 [FUSIONDCM] FreeStore Left 663298 on R:\
05/01/2011 17.22.30 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000011_1294244546_0011.dcm
05/01/2011 17.22.30 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.30 [FUSIONDCM] Message ID := 0027
05/01/2011 17.22.30 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.30 [FUSIONDCM] FreeStore Left 663298 on R:\
05/01/2011 17.22.31 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000013_1294244550_0012.dcm
05/01/2011 17.22.31 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.31 [FUSIONDCM] Message ID := 0029
05/01/2011 17.22.31 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.31 [FUSIONDCM] FreeStore Left 663298 on R:\
05/01/2011 17.22.32 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000015_1294244551_0013.dcm
05/01/2011 17.22.32 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.32 [FUSIONDCM] Message ID := 002b
05/01/2011 17.22.32 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.32 [FUSIONDCM] FreeStore Left 663298 on R:\
05/01/2011 17.22.33 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000017_1294244552_0014.dcm
05/01/2011 17.22.33 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.33 [FUSIONDCM] Message ID := 002d
05/01/2011 17.22.33 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.33 [FUSIONDCM] FreeStore Left 663298 on R:\
05/01/2011 17.22.34 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000019_1294244553_0015.dcm
05/01/2011 17.22.34 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.34 [FUSIONDCM] Message ID := 002f
05/01/2011 17.22.34 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.34 [FUSIONDCM] FreeStore Left 663298 on R:\
05/01/2011 17.22.35 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000021_1294244554_0016.dcm
05/01/2011 17.22.35 [FUSIONDCM] Server Command := 0001
05/01/2011 17.22.35 [FUSIONDCM] Message ID := 0031
05/01/2011 17.22.35 [FUSIONDCM] [recompress]: recompressed with mode = un (strip=0)
05/01/2011 17.22.35 [FUSIONDCM] FreeStore Left 663298 on R:\
05/01/2011 17.22.36 [FUSIONDCM] Written file: R:\rawdata\VA2813\20090114\1.3.12.2.1107.5.2.7.20113.30000009011410025164000000010\1.3.12.2.1107.5.2.7.20113.30000009011410040721800002067\000023_1294244555_0017.dcm
05/01/2011 17.22.36 [FUSIONDCM] UPACS THREAD 0: ENDED AT: Wed Jan 05 17:22:36 2011
05/01/2011 17.22.36 [FUSIONDCM] UPACS THREAD 0: TOTAL RUNNING TIME: 41 SECONDS

Any other hints?

best, Federico

Hi,

yes, DB mainenance is running, I double chechked (and I successfully manually triggered mainenance, too). I realized that the issue is in fact in a couple of triggers that we added, disabling the triggers restore the performances. In fact, a part of the triggers substitutes unallowed chars with "_". As a consequence, we have 36 patient IDs with "_", and many names etc with this char. Do you think this can be the issue? can we test another version for checking this specific issue?

Best, Federico

Here are the triggers:
(on images table)
USE [Conquest]
GO
/****** Object: Trigger [dbo].[NewDicomFile] Script Date: 01/11/2011 13:17:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <ENPQ Consulting>
-- Create date: <17 dic 2008>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[NewDicomFile]
ON [dbo].[DICOMImages]
for INSERT
AS
BEGIN
DECLARE

@SeriesInst varchar (64),
@SeriesInstance varchar (64),
@ImagePat varchar (64),
@FsnSubject_ID int,
@Dicom_image_path varchar (254),
@SOPInstanc varchar (64),
@SOP_Instanc varchar (64),
@StudyInsta varchar (64),
@StudyInstance varchar (64),
@StudyDate char (8),
@StudyTime varchar (16),
@StudyDescr varchar (64),
@Nome_Progetto varchar (64),
@Status varchar (64),
@ID_PR int,
@ID_ST int,
@NumFile int,
@newNumFile int,
@UserID int,
@MAXID_PR int,
@PR_NAME varchar (64),
@i int
SET NOCOUNT ON;

SELECT @SeriesInst = (Select SeriesInst FROM inserted)
SELECT @ImagePat = (Select ImagePat FROM inserted)
SELECT @Dicom_image_path = (Select ObjectFile FROM inserted)
SELECT @SOPInstanc = (Select SOPInstanc FROM inserted)
SELECT @StudyInsta = (Select StudyInsta FROM inserted)
SELECT @StudyDate = (Select StudyDate FROM inserted)
SELECT @StudyTime = (Select StudyTime FROM inserted)
SELECT @StudyDescr = (Select StudyDescr FROM inserted)

select @PR_NAME = REPLACE( REPLACE ( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
@StudyDescr, '^','_'), '#','_'),'!','_'),'%','_'),'~','_'),'@','_'),'.','_'),';','_'),'<','_'),'>','_'),'|','_'),'"','_'),' ','_'),'?','_'),':','_'),'/','_'),'\','_'),'*','_');

SELECT @SeriesInstance = (Select SeriesInst FROM FUSION.fusion.Dcm2NiiSeries where SeriesInst=@SeriesInst);
if @SeriesInstance is null
begin
--SELECT @FsnSubject_ID = (Select FsnSubjectID FROM FUSION.fusion.Fsn2SubjectID where PatientID =@ImagePat);
Insert into FUSION.fusion.Dcm2NiiSeries (SeriesInst,NumberOfFiles,PatientID,SeriesRawPath,LastInsert,Details,ErrorCounter)values (@SeriesInst,1,@ImagePat,@Dicom_image_path,getdate(), 'pending',0);
end
else
begin
SELECT @NumFile = (Select NumberOfFiles FROM FUSION.fusion.Dcm2NiiSeries where SeriesInst=@SeriesInst);
SELECT @newNumFile= @NumFile +1 ;
Update FUSION.fusion.Dcm2NiiSeries set NumberOfFiles=@newNumFile where SeriesInst=@SeriesInst;
Update FUSION.fusion.Dcm2NiiSeries set Details='pending' where SeriesInst=@SeriesInst;
Update FUSION.fusion.Dcm2NiiSeries set ErrorCounter=0 where SeriesInst=@SeriesInst;
Update FUSION.fusion.Dcm2NiiSeries set FileType = NULL where SeriesInst=@SeriesInst;
Update FUSION.fusion.Dcm2NiiSeries set NiftiSeriesPath = NULL where SeriesInst=@SeriesInst;
Update FUSION.fusion.Dcm2NiiSeries set SeriesRawPath = @Dicom_image_path where SeriesInst=@SeriesInst;
Update FUSION.fusion.Dcm2NiiSeries set TR = NULL where SeriesInst=@SeriesInst;
Update FUSION.fusion.Dcm2NiiSeries set LastInsert = getdate() where SeriesInst=@SeriesInst;
Update FUSION.fusion.Dcm2NiiSeries set PerfPhysic = NULL where SeriesInst=@SeriesInst;
end

SELECT @ID_PR = (Select ProjectID FROM FUSION.fusion.Projects where ProjectName=@PR_NAME);
if @ID_PR is null
begin
SELECT @MAXID_PR = (Select IDValue FROM FUSION.fusion.MaxID WHERE IDName = 'ProjectID');
if @MAXID_PR is null
begin
SELECT @ID_PR = 1;
Insert into FUSION.fusion.MaxID (IDName,IDValue) values ('ProjectID',@ID_PR);
end
else
begin
SELECT @ID_PR = (@MAXID_PR+1);
Update FUSION.fusion.MaxID set IDValue = @ID_PR where IDName = 'ProjectID';
end
-- SELECT @UserID =( Select UserID FROM FUSION.fusion.Users where FusionRole='Administrator');
-- Insert into FUSION.fusion.ProjectsMembers (UserID, ProjectID) values (@UserID,@ID_PR);
Insert into FUSION.fusion.Projects(ProjectID,ProjectName,ProjectStartDate,Status)values (@ID_PR,@PR_NAME,@StudyDate,'ToBeAssigned');
Insert into FUSION.fusion.ProjectStudies (StudyInst,PatientID,StudyDate,StudyTime,ProjectID) values (@StudyInsta,@ImagePat,@StudyDate,@StudyTime,@ID_PR);
end
else
begin
--SELECT @ID_PR = (Select ID_PR FROM FUSION.fusion.Progetti where NOME_PROG=@StudyDescr);
SELECT @StudyInstance = (Select StudyInst FROM FUSION.fusion.ProjectStudies where StudyInst=@StudyInsta);
if @StudyInstance is null
Insert into FUSION.fusion.ProjectStudies (StudyInst,PatientID,StudyDate,StudyTime,ProjectID) values (@StudyInsta,@ImagePat,@StudyDate,@StudyTime,@ID_PR);
end
END

===========================

(on patients table)

USE [Conquest]
GO
/****** Object: Trigger [dbo].[NewSubject] Script Date: 01/11/2011 13:23:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[NewSubject]
ON [dbo].[DICOMPatients]
for INSERT
AS

BEGIN
DECLARE
@PatientID varchar(64),
@PatientNam varchar (64),
@PatientBir char(8),
@PatientSex varchar (16),
@result varchar (64),
@data_ins datetime,
@ID_PZT int
SET NOCOUNT ON;

SELECT @PatientID = (Select PatientID FROM inserted)
SELECT @PatientNam = (Select PatientNam FROM inserted)
SELECT @PatientBir = (Select PatientBir FROM inserted)
SELECT @PatientSex = (Select PatientSex FROM inserted)
--@data_ins=getdate;

SELECT @ID_PZT = (SELECT FsnSubjectID FROM FUSION.fusion.Subjects where SubjectName=@PatientNam and SubjectBirthdate=@PatientBir);
if @ID_PZT is null
begin
Insert into FUSION.fusion.Subjects (SubjectName,SubjectBirthdate,SubjectSex)values (@PatientNam, @PatientBir,@PatientSex);
SELECT @ID_PZT = (SELECT FsnSubjectID FROM FUSION.fusion.Subjects where SubjectName=@PatientNam and SubjectBirthdate=@PatientBir);
Insert into FUSION.fusion.Fsn2DcmSubjects(PatientID, FsnSubjectID)values (@PatientID, @ID_PZT);
end
else
begin
SELECT @ID_PZT =(SELECT FsnSubjectID FROM FUSION.fusion.Subjects where (SubjectName=@PatientNam and SubjectBirthdate=@PatientBir));
Insert into FUSION.fusion.Fsn2DcmSubjects (PatientID, FsnSubjectID)values (@PatientID, @ID_PZT);
end
END