All Forums Database
@m 9 posts Joined 02/12
01 Mar 2012
performance issue with update query

Hi, please help me in optimising this update query it staking more than 6 hrs or so still not geting result.

 

 

update sandbox.MS_TEST_EMAILCOOKIERANKPBJAN1

from sandbox.MS_TEST_EMAILCOOKIERANKPBJAN1

set datediff= Web_Registration_Start_Dt - Xref_Create_Dt;

where Web_Registration_Start_Dt between '2011-12-25' and '2012-01-28'

 

 

 

sandbox.MS_TEST_EMAILCOOKIERANKPBJAN1

 

this is temp table.

 

Tnx in advance.

 

Stefans 38 posts Joined 02/12
01 Mar 2012

>>set datediff= Web_Registration_Start_Dt - Xref_Create_Dt;

where Web_Registration_Start_Dt between '2011-12-25' and '2012-01-28'

 

Can you check the syntax as the where condition is not utilized....

Stalin

@m 9 posts Joined 02/12
01 Mar 2012

hi stalin,

 

 

 

>>set datediff= Web_Registration_Start_Dt - Xref_Create_Dt;

where Web_Registration_Start_Dt between '2011-12-25' and '2012-01-28'

 

Can you check the syntax as the where condition is not utilized....

 

 

 

that semi colon is not there ......  so that where clause is utilised.

karthik_2244 36 posts Joined 12/10
01 Mar 2012

update sandbox.MS_TEST_EMAILCOOKIERANKPBJAN1

from sandbox.MS_TEST_EMAILCOOKIERANKPBJAN1

set datediff= Web_Registration_Start_Dt - Xref_Create_Dt

where Web_Registration_Start_Dt between '2011-12-25' and '2012-01-28'

 

no need to have ';' at the end of the set datediff line.

Stefans 38 posts Joined 02/12
01 Mar 2012

Can you share the DDL script of the temp table?

Stalin

ulrich 816 posts Joined 09/09
02 Mar 2012

can you also share the explain?

from the plain SQL it should be something like this

 

  1) First, we lock a distinct xxx"pseudo table" for write on

     a RowHash to prevent global deadlock for xxx.tab_t. 

  2) Next, we lock xxx.tab_t for write. 

  3) We do an all-AMPs UPDATE from xxx.tab_t by way of an

     all-rows scan with a condition of ("xxx.tab_t.a <= 5"). 

  4) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> No rows are returned to the user as the result of statement 1. 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

@m 9 posts Joined 02/12
02 Mar 2012

not yet found optimised result

 

 

update cdw_sandbox.MS_TEST_EMAILCOOKIERANKPBJAN1

from cdw_sandbox.MS_TEST_EMAILCOOKIERANKPBJAN1

set datediff=( Web_Registration_Start_Dt - Xref_Create_Dt)day(4)

 

 

tried this ..... 

datediff is integer.

 

can anyone resolve this issue...

ulrich 816 posts Joined 09/09
02 Mar 2012

You need to share more info if you want support from other people.

Would be good to see

The DDL of the table

Row counts - whole table, where conditions

explain of the statement

Do you have viewpoint access? Did you checked the state of the query?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.