This is one example of using a cursor in my script. Later in the code there are shell functions that are being run and these take awhile as they are doing writes
of files to the disk. Merging, copying, etc. I'm trying to find a way to speed up the overall process, but not rock the boat too much because the logic works and
I'm under some time constraints to get this done. This script runs for well over 12 hours, but it's hard for me to know how much of that is disk-write related, and
how much is due to my logic and use of cursors.
This is a script that will run only when we need to do a conversion, so it's ok if it takes a few hours, but 15 seems excessive.
That said, would changing my use of cursors make a large difference? There are two major loops. The first for patients with about 4,000 records, and the next for
documents per patient. Total is a couple hundred thousand. How would I change my SQL to use temporary tables or whatever to speed it up?
I've include the full code after this block just for reference in case someone sees some obvious things that I could due without shaking the logic up too much.
Cursor block:
DECLARE PAGE CURSOR
Forward_Only
Read_Only
FOR
SELECT
DD.PATH,
DD.Filename,
DD.PAGE
FROM IMPACTMD.dbo.Doc_Detail DD
WHERE documentid = @documentid
ORDER BY DD.Filename, DD.PAGE
OPEN PAGE
FETCH NEXT
FROM PAGE
INTO
@PATH ,
@Filename,
@PAGE
SET @ProcessDocument = 'Y'
SET @PageCount = 0
WHILE @@FETCH_STATUS = 0
.
.
.
Full code:
SET NOCOUNT ON
DECLARE
@poscode AS VARCHAR(10),
@posdesc AS VARCHAR(100),
@acctno AS VARCHAR(20),
@provcode AS VARCHAR(20),
@PatientID AS VARCHAR(25),
@ChartNumber AS VARCHAR(30),
@ChartID AS VARCHAR(30),
@Last_Name AS VARCHAR(30),
@First_Name AS VARCHAR(30),
@Middle_Initial AS VARCHAR(5),
@Sex AS CHAR(1),
@DOB AS CHAR(10),
@SSN AS CHAR(11),
@ID AS VARCHAR(20),
@LastName AS VARCHAR(30),
@FirstName AS VARCHAR(30),
@MiddleInitial AS VARCHAR(5),
@TITLE AS VARCHAR(10),
@Suffix AS VARCHAR(10),
@UPIN AS VARCHAR(20),
@NAME AS VARCHAR(100),
@LocID VARCHAR(20),
@DocDateTime AS VARCHAR(8),
@DocumentName AS VARCHAR(80),
@OldDocDateTime AS VARCHAR(22),
@OldSubject AS VARCHAR(80),
@Filename AS VARCHAR(50),
@PATH AS VARCHAR(50),
@PAGE AS VARCHAR(10),
@documentid AS VARCHAR(40),
@SQL AS VARCHAR(8000),
@COPYSCRIPT AS nvarchar(4000),
@CREATEDIRSCRIPT AS nvarchar(4000),
@DELETEDIRSCRIPT AS nvarchar(4000),
@Folder AS VARCHAR(100),
@Export_FileName AS VARCHAR(100),
@Merge_Script AS nvarchar(4000),
@RTFScript AS nvarchar(4000),
@Copy_Script AS nvarchar(4000),
@PageCount AS INTEGER,
@cmd AS varchar(1000),
@SourceFileExists AS nvarchar(1),
@TargetFileExists AS nvarchar(1),
@ProcessDocument AS nvarchar(1),
@FirstFile AS VARCHAR(100),
@ImageRootDirectory AS VARCHAR(100),
@ImageExportDirectory AS VARCHAR(100),
@TiffMergerDirectory AS VARCHAR(100),
@RTFConverterDirectory AS VARCHAR(100),
@TreatMergeAsCopy AS nvarchar(1)
SET @TreatMergeAsCopy = 'Y'
SET @ImageRootDirectory = 'G:\Fox_Meadows\Document_Conversion\ImpactMD\Images\'
SET @ImageExportDirectory = 'G:\Fox_Meadows\Documents\'
SET @TiffMergerDirectory = 'G:\Fox_Meadows\Document_Conversion\ImpactMD\Tiff\'
SET @RTFConverterDirectory = 'G:\Fox_Meadows\Document_Conversion\ImpactMD\Tiff\'
USE ImpactMD
DECLARE
ChartID CURSOR
Forward_Only
FOR
SELECT DISTINCT (P.ChartID)
FROM IMPACTMD.dbo.Patients P
INNER JOIN IMPACTMD.dbo.Doc_Header H ON H.PatientID = P.PatientID
OPEN ChartID
FETCH NEXT
FROM ChartID
INTO @ChartID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''
PRINT '
PRINT 'ChartID: ' + @ChartID
PRINT '
DECLARE Patient CURSOR
Forward_Only
Read_Only
FOR
SELECT DISTINCT
ISNULL(CONVERT(VARCHAR(8),H.DATE,112),'') AS DocumentDate,
ISNULL(H.DocumentName,'') ,
ISNULL(H.documentid,'') ,
ISNULL(FT.DESCRIPTION,'')
FROM IMPACTMD.dbo.Patients P
INNER JOIN IMPACTMD.dbo.Doc_Header H
ON H.PatientID = P.PatientID
INNER JOIN IMPACTMD.dbo.Folder_Template FT
ON H.FolderID = FT.FolderID
WHERE P.ChartID = @ChartID
OPEN Patient
FETCH NEXT
FROM Patient
INTO
@DocDateTime ,
@DocumentName,
@documentid ,
@Folder
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE PAGE CURSOR
Forward_Only
Read_Only
FOR
SELECT
DD.PATH,
DD.Filename,
DD.PAGE
FROM IMPACTMD.dbo.Doc_Detail DD
WHERE documentid = @documentid
ORDER BY DD.Filename, DD.PAGE
OPEN PAGE
FETCH NEXT
FROM PAGE
INTO
@PATH ,
@Filename,
@PAGE
SET @ProcessDocument = 'Y'
SET @PageCount = 0
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #SourceFiles(s varchar(1000))
SELECT @cmd = 'dir /B ' + '"' + @ImageRootDirectory + @Path + @FileName + '"'
INSERT #SourceFiles EXEC master..xp_cmdshell @cmd
IF EXISTS (SELECT * FROM #SourceFiles WHERE s = @FileName)
BEGIN
SET @SourceFileExists = 'Y'
END
ELSE
BEGIN
SET @SourceFileExists = 'N'
END
DROP TABLE #SourceFiles
IF @Filename COLLATE Latin1_General_CI_AI LIKE '%.TIF%' COLLATE Latin1_General_CI_AI OR @Filename COLLATE Latin1_General_CI_AI LIKE '%.DOC%' COLLATE Latin1_General_CI_AI
BEGIN
IF @PAGE = 1
BEGIN
SET @PageCount = 1
SET @FirstFile = @ImageRootDirectory + @PATH + @Filename
SET @Export_FileName = 'EXP_' + @Filename COLLATE Latin1_General_CI_AI
SET @Merge_Script = @ImageRootDirectory + @PATH + @Filename + ','
SET @Copy_Script = 'copy ' + '"' + @ImageRootDirectory + @PATH COLLATE Latin1_General_CI_AI + @Filename COLLATE Latin1_General_CI_AI + '" "' + @ImageExportDirectory + @Path COLLATE Latin1_General_CI_AI + @Export_FileName + '"'
CREATE TABLE #TargetFiles(s varchar(1000))
SELECT @cmd = 'dir /B ' + '"' + @ImageExportDirectory + @Path + @Export_FileName + '"'
INSERT #TargetFiles EXEC master..xp_cmdshell @cmd
IF EXISTS (SELECT * FROM #TargetFiles WHERE s = @Export_FileName)
BEGIN
SET @TargetFileExists = 'Y'
SET @ProcessDocument = 'N'
END
ELSE
BEGIN
SET @TargetFileExists = 'N'
END
DROP TABLE #TargetFiles
END
IF @PAGE <> 1 ANd @Filename COLLATE Latin1_General_CI_AI LIKE '%.TIF%' COLLATE Latin1_General_CI_AI
BEGIN
SET @PageCount = @PageCount + 1
SET @Merge_Script = @Merge_Script + @ImageRootDirectory + @PATH + @Filename + ','
END
IF @ProcessDocument = 'Y' AND @TreatMergeAsCopy = 'Y'
BEGIN
SET @Copy_Script = 'copy ' + '"' + @ImageRootDirectory + @PATH COLLATE Latin1_General_CI_AI + @Filename COLLATE Latin1_General_CI_AI + '" "' + @ImageExportDirectory + @Path COLLATE Latin1_General_CI_AI + 'EXP_' + @FileName + '"'
PRINT 'COPY: ' + @Copy_Script
EXEC MASTER.dbo.xp_cmdshell @Copy_Script, no_output
END
END
FETCH NEXT
FROM PAGE
INTO
@PATH ,
@Filename,
@PAGE
END
CLOSE PAGE
DEALLOCATE PAGE
FETCH NEXT
FROM Patient
INTO
@DocDateTime ,
@DocumentName,
@documentid ,
@Folder
END
CLOSE Patient
DEALLOCATE Patient
FETCH NEXT
FROM ChartID
INTO
@ChartID
END
CLOSE ChartID
DEALLOCATE ChartID
SET NOCOUNT OFF
