set based query

Question

question

Iain 1 asked Jun 7, '10

set based query

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

t-sql cursor set-based

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther commented · Jun 07, 2010 at 09:19 AM

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.

0 ·

Answer 1 · 2010-06-07T09:21:16Z

Shankar answered Jun 7, '10

Basically you have 2 tables to operate. try Join conditions. This is not a complete code. but similar. if you have the necessary indexes in the master_table, this should run faster.

Insert into BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21   
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
where FIELD1='21' and FIELD2 = 'I' 
ORDER BY FIELD3

Update t21
SET t21 RECORD_IDENTIFIER = '''+ @FIELD1
..
..
FROM BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21 
inner JOIN MASTER_TABLE ON join....condition
where FIELD1='21' and FIELD2 = 'U' 
ORDER BY FIELD3

delete t21
from 
BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21 t21
inner join MASTER_TABLE on Join..condition
where FIELD1='21' and FIELD2 = 'D' 
ORDER BY FIELD3

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack ♦♦ commented · Jun 07, 2010 at 09:24 AM

+1 - well done for the complete answer, faster than my typing!!

0 ·

Answer 2 · 2010-06-07T09:22:11Z

Fatherjack answered Jun 7, '10

I havent had a long time to review your script but it seems to be comparing the MASTER_TABLE to the BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE and doing either INSERTs, UPDATEs or DELETEs based on certain column values.

The DELETE seems to be the easiest to deal with as its simply looking for a D and a value of 21 in Field1. Can this not become

DELETE 
FROM BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE 
FROM MASTER_TABLE as m 
INNER JOIN BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE as b on
m.Keycolumn = b.keycolumn 
WHERE m.FIELD2='D' and m.Field1=21

using http://msdn.microsoft.com/en-us/library/ms189835.aspx as reference

I have got this far and a new answer has been added - someone beat me to the complete solution ... !

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question

set based query

2 Answers

Write an Answer

question details

question

set based query

2 Answers

Write an Answer

question details

Related Questions