TIMESTAMP(6) WITH TIME ZONE, Alternate query for performance improvement

Database
Visitor

TIMESTAMP(6) WITH TIME ZONE, Alternate query for performance improvement

Hi,

 

I'm having the 'Creation_TM' field as timestamp(6) datatype in source table. I needed  'Creation_TM_PST' in 'America Pacific' timezone. I'm getting proper result with the below mentioned query but when i use this logic in stored proc for updating to ' 'Creation_TM_PST', the execution time is more. Can someone suggest me an alternative query to improve performance. The datatype for 'Creation_TM'_PST' is 'Timestamp(0) with Time zone' so far in the baseTable, i can even change the  datatype 'Creation_TM'_PST' , if any one has alternative solution. 

 

SyntaxEditor Code Snippet

Set CREATION_TM_PST = CAST(SUBSTRING(CAST(CREATION_TM AS CHAR(26)) FROM 1 FOR 19)||'+00:00' AS TIMESTAMP(0)) AT 'America Pacific'

 

1 REPLY
Teradata Employee

Re: TIMESTAMP(6) WITH TIME ZONE, Alternate query for performance improvement

When you say your stored procedure is updating and you have a performance issue, the first thing I suspect is that you are updating one row at a time.  This is normally a bad idea.  See http://developer.teradata.com/blog/geoc/2011/04/set-processing - "Updating thousands of rows one row at a time in Teradata is like fetching a gallon of water with a spoon."