performance issue with update query

Database
Enthusiast

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.
7 REPLIES
Enthusiast

Re: performance issue with update query

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

Enthusiast

Re: performance issue with update query

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.

Enthusiast

Re: performance issue with update query

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.

Enthusiast

Re: performance issue with update query

Can you share the DDL script of the temp table?

Senior Supporter

Re: performance issue with update query

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. 

Enthusiast

Re: performance issue with update query

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

Senior Supporter

Re: performance issue with update query

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?