June 7, 2010 at 3:19 am
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
June 7, 2010 at 8:18 am
try this. You didn't provide the table layouts or test data to test this with. So at least this will get you started. If you provide these, then I can test this solution as well
-- Perform the deletes first
delete t21
from BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21 t21
inner join MASTER_TABLE M
on t21.RECORD_IDENTIFIER = M.Field1
and t21.UPRN = M.FIELD4
where t21.Field2 = 'D'
and t21.Field1 = '21'
-- update existing records
update t21
set t21.RECORD_IDENTIFIER = M.Field1,
t21.CHANGE_TYPE = M.Field2,
t21.Pro_Order = cast(M.Field3 as varchar),
t21.UPRN = M.FIELD4,
t21.LOGICAL_STATUS= M.FIELD5,
t21.BLPU_STATE= M.FIELD6,
t21.BLPU_STATE_DATE= M.FIELD7,
t21.BLPU_CLASS= M.FIELD8,
t21.PARENT_UPRN= M.FIELD9,
t21.X_COORDINATE= M.FIELD10,
t21.Y_COORDINATE= M.FIELD11,
t21.RPA= M.FIELD12,
t21.LOCAL_CUSDODIAN_CODE= M.FIELD13,.
t21.START_DATE= M.FIELD14,
t21.END_DATE = M.FIELD15,
t21.LAST_UPDATE_DATE= M.FIELD16,
t21.ENTRY_DATE= M.FIELD17,
t21.ORGANISATION = REPLACE(M.FIELD18,'''',''''''),
t21.WARD_CODE= M.FIELD19,
t21.PARISH_CODE= M.FIELD20,
t21.CUSTODIAN_ONE= M.FIELD21,
t21.CUSTODIAN_TWO= M.FIELD22,
t21.CAN_KEY= M.FIELD23
from BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21 T21
inner join MASTER_TABLE M
on t21.RECORD_IDENTIFIER = M.Field1
and t21.UPRN = M.FIELD4
where t21.Field2 = 'U'
and t21.Field1 = '21'
-- add new records
insert into BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21
(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)
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
FROM MASTER_TABLE
where Field2 = 'I'
and Field1 = '21'
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply