Please help with cursorless cursor
-
Hi,
I am working on updating a cursor to use a cursorless cursor, or temp tables to make things better. I know it is bad to use a cursor so I tried modifying the stored proceedure to use temp tables or a table variable. It seems to run a little bit better then the cursor but not as well as I would have expected. It is running on 75000 rows but refering to about 40 other tables. It is taking about 7 hours to finish. Can somone offer a better solution? Thanks.
John
Here is the proceedure
USE [John Test] --Update DB Name
DECLARE @strFILEFORMAT NVARCHAR(4000)
DECLARE @strSOURCE NVARCHAR(4000)
DECLARE @strUPDATE_FIELD NVARCHAR(4000)
DECLARE @intFIELDORDER INT
DECLARE @strFILEFORMAT_1 NVARCHAR(4000)
DECLARE @strSQL NVARCHAR(4000)
DECLARE @I INT
DECLARE @ROW_COUNT int
-- CREATE TABLE VARIABLE OF FILES TO LOOP THROUGH
-- LOOP THROUGH EACH FIELD PER FILE FORMAT
DECLARE @TABLEVAR table (
ID_Num int identity(1,1) ,FILEFORMAT Varchar(255), TableSource varchar(255), FIELDNAME varchar(255), FIELDORDER varchar(3))
insert into @TABLEVAR
SELECT FILEFORMAT, TableSource, FIELDNAME, FIELDORDER
FROM DBO.Transpose_Data
--WHERE FILEFORMAT in ('Payable Claims Data INT')
SET @I = 1
SET NOCOUNT ON
SET @Row_Count = (SELECT COUNT(*) FROM @TABLEVAR)
WHILE @I <= @Row_Count
BEGIN
SET @strUPDATE_FIELD = (Select fieldname from @tablevar where ID_NUM=@I)
SET @strFILEFORMAT= (Select fileformat from @tablevar where ID_NUM=@I)
SET @strSOURCE=(Select tablesource from @tablevar where ID_NUM=@I)
SET @intFIELDORDER=(Select FIELDORDER from @tablevar where ID_NUM=@I)
SET @strFILEFORMAT_1 = @strFILEFORMAT
SET @strFILEFORMAT = '[DBO].[QC_' + @strFILEFORMAT + ']'
SET @strUPDATE_FIELD = @strFILEFORMAT + '.[' + @strUPDATE_FIELD + ']'
-- RECOGNIZE FILE FORMAT FIND APPROPRIATE QC TABLE
-- UPDATE TOTAL COUNT QC RESULTS
SET @strSQL = 'UPDATE Transpose_Data SET TOTCNT =
(SELECT TOTCNT FROM ' + @strFILEFORMAT + ' WHERE QC_TYPE = ''NULL VALUES'' AND TableSource = ''' + @strSOURCE + ''')
WHERE TableSource = ''' + @strSOURCE + ''' AND FILEFORMAT = ''' + @strFILEFORMAT_1 + ''''
Exec sp_executesql @STRSQL
-- UPDATE NULL VALUES QC RESULTS
SET @strSQL = 'UPDATE Transpose_Data SET NULLVAL =
(SELECT ' + @strUPDATE_FIELD + ' FROM ' + @strFILEFORMAT + ' WHERE QC_TYPE = ''NULL VALUES'' AND TableSource = ''' + @strSOURCE + ''')
WHERE TableSource = ''' + @strSOURCE + ''' AND FIELDORDER = '+ CAST(@intFIELDORDER AS VARCHAR(10)) + ' AND FILEFORMAT = ''' + @strFILEFORMAT_1 + ''''
Exec sp_executesql @STRSQL
-- UPDATE EMPTY STRING VALUES QC RESULTS
SET @strSQL = 'UPDATE Transpose_Data SET EMPTYVAL =
(SELECT ' + @strUPDATE_FIELD + ' FROM ' + @strFILEFORMAT + ' WHERE QC_TYPE = ''EMPTY STRING/NULL VALUES'' AND TableSource = ''' + @strSOURCE + ''')
WHERE TableSource = ''' + @strSOURCE + ''' AND FIELDORDER = '+ CAST(@intFIELDORDER AS VARCHAR(10)) + ' AND FILEFORMAT = ''' + @strFILEFORMAT_1 + ''''
Exec sp_executesql @STRSQL
-- UPDATE MIN VALUES QC RESULTS
SET @strSQL = 'UPDATE Transpose_Data SET MINVAL =
(SELECT ' + @strUPDATE_FIELD + ' FROM ' + @strFILEFORMAT + ' WHERE QC_TYPE = ''MIN VAL'' AND TableSource = ''' + @strSOURCE + ''')
WHERE TableSource = ''' + @strSOURCE + ''' AND FIELDORDER = '+ CAST(@intFIELDORDER AS VARCHAR(10)) + ' AND FILEFORMAT = ''' + @strFILEFORMAT_1 + ''''
Exec sp_executesql @STRSQL
-- UPDATE MAX VALUES QC RESULTS
SET @strSQL = 'UPDATE Transpose_Data SET MAXVAL =
(SELECT ' + @strUPDATE_FIELD + ' FROM ' + @strFILEFORMAT + ' WHERE QC_TYPE = ''MAX VAL'' AND TableSource = ''' + @strSOURCE + ''')
WHERE TableSource = ''' + @strSOURCE + ''' AND FIELDORDER = '+ CAST(@intFIELDORDER AS VARCHAR(10)) + ' AND FILEFORMAT = ''' + @strFILEFORMAT_1 + ''''
Exec sp_executesql @STRSQL
-- UPDATE MIN DATE VALUES QC RESULTS
-- SET @strSQL = 'UPDATE Transpose_Data SET MINDTVAL =
-- (SELECT ' + @strUPDATE_FIELD + ' FROM ' + @strFILEFORMAT + ' WHERE QC_TYPE = ''MIN NON-DEFAULT DT'' AND TableSource = ''' + @strSOURCE + ''')
-- WHERE TableSource = ''' + @strSOURCE + ''' AND FIELDORDER = '+ CAST(@intFIELDORDER AS VARCHAR(10)) + ' AND FILEFORMAT = ''' + @strFILEFORMAT_1 + ''''
-- Exec sp_executesql @STRSQL
-- UPDATE DEFAULT DATE CNT VALUES QC RESULTS
SET @strSQL = 'UPDATE Transpose_Data SET DEFAULTDTCNT =
(SELECT ' + @strUPDATE_FIELD + ' FROM ' + @strFILEFORMAT + ' WHERE QC_TYPE = ''DEFAULT DT CNT'' AND TableSource = ''' + @strSOURCE + ''')
WHERE TableSource = ''' + @strSOURCE + ''' AND FIELDORDER = '+ CAST(@intFIELDORDER AS VARCHAR(10)) + ' AND FILEFORMAT = ''' + @strFILEFORMAT_1 + ''''
Exec sp_executesql @STRSQL
-- UPDATE EXCEPTION DATE CNT VALUES QC RESULTS
SET @strSQL = 'UPDATE Transpose_Data SET DateException =
(SELECT ' + @strUPDATE_FIELD + ' FROM ' + @strFILEFORMAT + ' WHERE QC_TYPE = ''Exception_Datetime'' AND TableSource = ''' + @strSOURCE + ''')
WHERE TableSource = ''' + @strSOURCE + ''' AND FIELDORDER = '+ CAST(@intFIELDORDER AS VARCHAR(10)) + ' AND FILEFORMAT = ''' + @strFILEFORMAT_1 + ''''
Exec sp_executesql @STRSQL
-- UPDATE EXCEPTION NUMERIC CNT VALUES QC RESULTS
SET @strSQL = 'UPDATE Transpose_Data SET NumericException =
(SELECT ' + @strUPDATE_FIELD + ' FROM ' + @strFILEFORMAT + ' WHERE QC_TYPE = ''Exception_Numeric'' AND TableSource = ''' + @strSOURCE + ''')
WHERE TableSource = ''' + @strSOURCE + ''' AND FIELDORDER = '+ CAST(@intFIELDORDER AS VARCHAR(10)) + ' AND FILEFORMAT = ''' + @strFILEFORMAT_1 + ''''
Exec sp_executesql @STRSQL
-- SELECT NEXT SOURCE & FIELD COMBINATION TO UPDATE
SET @I = @I + 1
END
SET NOCOUNT OFF
-
The only way you will be able to significantly reduce the amount of time this takes is to re-engineer this SP to get away from row-by-row processing. Removing the cursor was a step in the right direction, but you are still processing one row at a time. The main purpose, outside of the additional overhead added, of getting rid of cursors is to prevent row-by-row processing. If you came up with a SET based solution, you would see your performance improve tremendously.
It is, however, difficult to help you with what you are trying to do without more information. Can you provide DDL for the TransposeData table, a couple/few of the tables that will get updated by your dynamic SQL, and the corresponding TransposeData rows for those tables? I don't have any more time today to help, but I will look at this more tomorrow.
-
Thanks for taking a look. I'm not sure if my last post went through so I'm trying it again. Any way what I am trying to do is profile some data we are importing into our QC database for our datawarehouse. We import tremendous amounts of data and we are trying to profile the data before we port it into our data warehouse. Anyway, we have a number of QC tables that basically list out the fields for a table and then list different QC types which are the counts or summary data for the profiling of data in the fields. We are looking into buyg software to do it for us like datiris which I am impressed with but for the time being this is what we have. For instance one qc table might look like this:
TableSource varchar 100 0
QC_TYPE varchar 50 0
TOTCNT varchar 50 1
RECORD_CODE varchar 50 1
PROCESSOR_NUMBER varchar 50 1
TRANSACTION_COUNT varchar 50 1
TOTAL_GROSS_COST varchar 50 1
TOTAL_OTHER_PAYER_AMOUNT_PAID varchar 50 1
TOTAL_PATIENT_PAID_AMOUNT varchar 50 1
TOTAL_CLIENT_AMOUNT_DUE varchar 50 1
FILLER varchar 2231 1
SEQ_ID int 4 1
basically the first three fields are the table qc_type totcnt and then all the fields for that table. The data might look like this
Tablesource QC_TYPE TOTCNT RECORD_CODE
Table1 Nullval 999999 0
Table1 minval 999999 1
Table1 maxlength 999999 88888888
Then what I was trying to do is make a table that takes all of the qc stats from the individual QC tables and transposes the results into one complete table that has everything in it. It looks like this.
FILEFORMAT nvarchar 255 1
TableSource nvarchar 255 1
FieldOrder smallint 2 1
FieldName nvarchar 255 1
MaxLength smallint 2 1
TotCnt nvarchar 255 1
NullVal nvarchar 255 1
EmptyVal nvarchar 255 1
MinVal nvarchar 255 1
MaxVal nvarchar 255 1
DefaultDtCnt nvarchar 255 1
DateException nvarchar 255 1
NumericException nvarchar 255 1
And the data would be listing al the tables,fields,and summary (profile) information. I am looking to do something that would give the same results but I'm not sure how to make it set based. At least the processing times have been reduced from 9 hours to 7. and thats 22% performance increase! If you have any ideas at all I would appreciate it. Thanks again.
John
P.S Sorry if my grammar formatting is bad I have my 18 month daughter at my feet.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply