the below code uses the cursor based method to update a table, using this method will take over 18 hours to process as the table to update has over 1000000 row and the table to update from has over 187000 rows.
Can any help to speed up the query, I have tried various methods but I either problems getting the syntax right or it doesn't run as expected.
cheers
SET @INSERT_COLS3 = ' (RECORD_IDENTIFIER ,CHANGE_TYPE ,PRO_ORDER ,UPRN ,LOGICAL_STATUS ,BLPU_STATE ,BLPU_STATE_DATE ,BLPU_CLASS ,PARENT_UPRN ,X_COORDINATE ,Y_COORDINATE ,RPA ,LOCAL_CUSDODIAN_CODE ,START_DATE ,END_DATE ,LAST_UPDATE_DATE ,ENTRY_DATE ,ORGANISATION ,WARD_CODE ,PARISH_CODE ,CUSTODIAN_ONE ,CUSTODIAN_TWO ,CAN_KEY) '
DECLARE @UPDATE_QUERY VARCHAR(4000)
DECLARE @DELETE_QUERY VARCHAR(4000)
SET @INSERT_QUERY = 'INSERT INTO '
SET @UPDATE_QUERY = 'UPDATE '
SET @DELETE_QUERY = 'DELETE '
DECLARE LOCAL_CUR CURSOR LOCAL
FOR
SELECT 'BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE'+CAST(FIELD1 AS VARCHAR) TABLE_NAME,
FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7,FIELD8,FIELD9,FIELD10,FIELD11,FIELD12,FIELD13,FIELD14,FIELD15,FIELD16,FIELD17,FIELD18,FIELD19,FIELD20,FIELD21,FIELD22,FIELD23,FIELD24,FIELD25,FIELD26,FIELD27--,FIELD28,FIELD29,FIELD30
FROM MASTER_TABLE
ORDER BY FIELD3
OPEN LOCAL_CUR
FETCH NEXT FROM LOCAL_CUR INTO @TABLE_NAME,@FIELD1,@FIELD2,@FIELD3,@FIELD4,@FIELD5,@FIELD6,@FIELD7,@FIELD8,@FIELD9,@FIELD10,@FIELD11,@FIELD12,@FIELD13,@FIELD14,@FIELD15,@FIELD16,@FIELD17,@FIELD18,@FIELD19,@FIELD20,@FIELD21,@FIELD22,@FIELD23,@FIELD24,@FIELD25,@FIELD26,@FIELD27--,@FIELD28,@FIELD29,@FIELD30
WHILE @@FETCH_STATUS = 0
BEGIN
SET @INSERT_QUERY = 'INSERT INTO '
SET @UPDATE_QUERY = 'UPDATE '
SET @DELETE_QUERY = 'DELETE '
IF @FIELD2 = 'I' and @FIELD1='21'
BEGIN
SET @INSERT_QUERY = @INSERT_QUERY + 'BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21' + @INSERT_COLS3 +
' SELECT '''+ @FIELD1+''', ''' +@FIELD2+''', ' +CAST(@FIELD3 AS VARCHAR)+', ''' +@FIELD4+''', ''' +@FIELD5+''', ''' +@FIELD6+''', ''' +@FIELD7+''', ''' +@FIELD8+''', '''+@FIELD9+''', ''' +@FIELD10+''', ''' +@FIELD11+''', ''' +@FIELD12+''', ''' +@FIELD13+''', ''' +@FIELD14+''', ''' +@FIELD15+''', ''' +@FIELD16+''', ''' +@FIELD17+''', ''' +@FIELD18+''', ''' +@FIELD19+''', ''' +@FIELD20+''', ''' +@FIELD21+''', ''' +@FIELD22+''', ''' +@FIELD23+''''
PRINT @INSERT_QUERY
EXEC (@INSERT_QUERY)
END
ELSE IF @FIELD2 = 'D'and @Field1 = '21'
BEGIN
SET @DELETE_QUERY = @DELETE_QUERY + 'BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21'
+ ' WHERE BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21.RECORD_IDENTIFIER = '''+ @FIELD1+ ''' AND UPRN = ''' +@FIELD4+''''
PRINT @DELETE_QUERY
EXEC (@DELETE_QUERY)
END
ELSE IF @FIELD2 = 'U'and @Field1 = '21'
BEGIN
SET @UPDATE_QUERY = @UPDATE_QUERY + 'BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21' + +
' SET RECORD_IDENTIFIER = '''+ @FIELD1+''',CHANGE_TYPE = ''' +
@FIELD2+''',PRO_ORDER = ' +CAST(@FIELD3 AS VARCHAR)+',UPRN = ''' +@FIELD4+''',LOGICAL_STATUS= ''' +@FIELD5+''' ,BLPU_STATE= ''' +@FIELD6+''',BLPU_STATE_DATE= ''' +@FIELD7+''' ,BLPU_CLASS= ''' +@FIELD8+''' ,PARENT_UPRN= ''' +@FIELD9+''' ,X_COORDINATE= ''' +@FIELD10+''' ,Y_COORDINATE= ''' +@FIELD11+''' ,RPA= ''' +@FIELD12+''' ,LOCAL_CUSDODIAN_CODE= ''' +@FIELD13+''' ,START_DATE= ''' +@FIELD14+''' ,END_DATE = ''' +@FIELD15+''',LAST_UPDATE_DATE= ''' +@FIELD16+''' ,ENTRY_DATE= ''' +@FIELD17+''',ORGANISATION = '''+REPLACE(@FIELD18,'''','''''')+''' ,WARD_CODE= ''' +@FIELD19+''' ,PARISH_CODE= ''' +@FIELD20+''' ,CUSTODIAN_ONE= ''' +@FIELD21+''' ,CUSTODIAN_TWO= ''' +@FIELD22+''' ,CAN_KEY= ''' +@FIELD23+'''' +
+ ' WHERE BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21.RECORD_IDENTIFIER = '''+ @FIELD1+ ''' AND UPRN = ''' +@FIELD4+''''
PRINT @UPDATE_QUERY
EXEC (@UPDATE_QUERY)
END
SET @INSERT_QUERY = ''
SET @UPDATE_QUERY = ''
FETCH NEXT FROM LOCAL_CUR INTO @TABLE_NAME ,@FIELD1, @FIELD2 , @FIELD3, @FIELD4 , @FIELD5, @FIELD6 , @FIELD7 , @FIELD8 , @FIELD9 , @FIELD10 , @FIELD11 , @FIELD12 , @FIELD13 , @FIELD14 , @FIELD15 , @FIELD16 , @FIELD17 , @FIELD18 , @FIELD19 , @FIELD20 , @FIELD21 , @FIELD22 , @FIELD23 , @FIELD24 , @FIELD25 , @FIELD26 , @FIELD27-- , @FIELD28 , @FIELD29 , @FIELD30
END
CLOSE LOCAL_CUR
DEALLOCATE LOCAL_CUR
What version of SQL server are you using? In SQL server 2008 you can use one MERGE statement instead and then you could replace your "slow running cursor" (Yes, T-SQL cursors are often very slow). I havn't investigated your statement in detail but it should be possible as I can see. Also make sure you have index on your columns that exists in your where clause.