High Impact of date functions

General
Enthusiast

High Impact of date functions

Hi,

I'm interested to know  what the experience is of using the relatively new date functions.

I have two separate queries both of which were experiencing high impact CPU and being aborted.

I reduced them to their bare components. ie removed any joins, windows function and grouping until all that was left was the date functions.

 

The first was MONTHS_BETWEEN  eg. MONTHS_BETWEEN(date1, date2)

which I switched to the less accurate ((date2-date1)) /30.4 (INTEGER)

 

The second was TRUNC. eg,TRUNC(date1) to give the start of the month.

This was changed to date1-EXTRACT(DAY from date1) + 1

 

They ran with little impact.

I'm disappointed that usage of these very useful functions appears to be less efficient.

I'm running on Teradata V15.10.

 

Is there a limitation on how they can be used ?

 

 


Accepted Solutions
Junior Contributor

Re: High Impact of date functions

If the functions are in SYSLIB, too, your DBA might consider to DROP them (or replace with a SQL UDF calling the one TD_SYSFNLIB if he's cares for possible problems if users hardcoded SYSLIB).

When dbc.FunctionsF.ExecProtectionMode is set to P then it's running in Protected Mode, which results in much more (10+?) CPU.

 

Did you compare Dev & Prod regarding CPU?

1 ACCEPTED SOLUTION
5 REPLIES
Junior Contributor

Re: High Impact of date functions

High CPU might indicate you're actually using UDFs from the old Oracle UDF pack running in protected mode.

The parser checks SYSLIB before TD_SYSFNLIB for unknown functions.

 

SHOW SELECT ... should not return info about those functions.

 

And this should return TD_SYSFNLIB only:

 

SELECT DatabaseName, FunctionName, SpecificName, ParameterDataTypes
FROM dbc.FunctionsV
WHERE FunctionName IN ('TRUNC', 'MONTHS_BETWEEN')

 

Enthusiast

Re: High Impact of date functions

Thank you.
I can only see these two functions in TD_SYSFNLIB so am I right in assuming I'm using the new version?
Enthusiast

Re: High Impact of date functions

Further update.
It appears the location of these functions on our Dev and Production machines is different and I need to call the functions with the TD database name
Junior Contributor

Re: High Impact of date functions

If the functions are in SYSLIB, too, your DBA might consider to DROP them (or replace with a SQL UDF calling the one TD_SYSFNLIB if he's cares for possible problems if users hardcoded SYSLIB).

When dbc.FunctionsF.ExecProtectionMode is set to P then it's running in Protected Mode, which results in much more (10+?) CPU.

 

Did you compare Dev & Prod regarding CPU?

Enthusiast

Re: High Impact of date functions

Thanks again for your help.
I haven't checked the CPU but directing to the Teradata version works.
Apparently they are going to look into rectifying at the next upgrade