Hi Jay,
I see it. FETCH. FETCH is a pig. If you can find a way to get rid of the fetch and keep everything SQL the process will speed up dramatically.
>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
>
>/* IF @ProcessDocument = 'Y' AND @PageCount > 1
>
> BEGIN
>
> SET @Merge_Script = LEFT(@Merge_Script,LEN(LTRIM(RTRIM(@Merge_Script))) -1)
> SET @Merge_Script = @Merge_Script + '|' + @ImageExportDirectory + @Path + '|' + @Export_FileName
> SET @Merge_Script = @TiffMergerDirectory + 'Tiff_Merger.exe "' + @Merge_Script + '"'
>
>
>
>
>
> END
>
> IF @ProcessDocument = 'Y' AND @PageCount = 1
>
> BEGIN
>
>
>
>
>
> END
>
> IF @ProcessDocument = 'N'
>
> BEGIN
>
>
> PRINT 'Skipped: ' + @FirstFile
>
> END
>*/
> 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
>
------------------------------------------------
John Koziol, ex-MVP, ex-MS, ex-FoxTeam. Just call me "X"
"When the going gets weird, the weird turn pro" - Hunter Thompson (Gonzo) RIP 2/19/05
