CPU disk ratio error from 'like' statements

Analytics

CPU disk ratio error from 'like' statements

We are parsing out URL strings in code and are receiving a CPU disk ratio error message.  Can someone help identify the major contributors to CPU disk ratio error?

The below are all included in case statements creating new variables.  For example:

CASE WHEN URL LIKE ANY ( '%help%',’%travel%’ ) THEN 1 else 0 end AS variable_travel

Questions:

·         Is there a limit of how many words can be included in a LIKE ANY statement?  (’%help%’,’%health%’,’%travel%’)

·         What is the effect of using NOT LIKE statements?   Should these be avoided? For example like any (’%help%’,’%health%’,’%travel%’)  and not like ‘%helpful%’

·         What is the effect of multiple wild cards such as  '%free_stuff%'

2 REPLIES
Teradata Employee

Re: CPU disk ratio error from 'like' statements

Please share the error code and message being received? It sounds like a workload management rule that the system/performance administrators have put in place. There are no system limitations around complexity or number of expressions. If there are patterns to the strings being searched for, a review of the regex functions might be in order. 

Of of course more parsing means more CPU utilization but there are not system limits on that, only rules on workloads that are defined by the administrators and must be negotiated with that team. 

Re: CPU disk ratio error from 'like' statements

Probably the like statements results in a full table scan and thus a high io/cpu estimate.

Here the effect is a bit explained:

http://www.dwhpro.com/teradata-indexing-2/