x

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
more ▼

asked Jun 07, 2010 at 07:54 AM in Default

Iain 1 gravatar image

Iain 1
1 1 1 1

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.
Jun 07, 2010 at 09:19 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

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
more ▼

answered Jun 07, 2010 at 09:21 AM

Shankar gravatar image

Shankar
12

+1 - well done for the complete answer, faster than my typing!!
Jun 07, 2010 at 09:24 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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 ... !

more ▼

answered Jun 07, 2010 at 09:22 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x58
x11

asked: Jun 07, 2010 at 07:54 AM

Seen: 1443 times

Last Updated: Jun 07, 2010 at 09:12 AM