Wednesday, December 8, 2010

Peformance tuning Tips : LIKE Clause

While tuning queries in Teradata , We take care of major performance issues but ignore small cases which might still cause the query to perform badly.

I wanted to mention about one such case of  LIKE clause , which many people good at performance tuning miss it  assuming like patterns does not harm the performance . But in reality this is not so!!

If LIKE is used in a WHERE clause, it is better to try to use one or more leading character in the clause, if at all possible.

eg;   LIKE '%STRING%'  will be processed differently compared to LIKE 'STRING%'

If a leading character   'STRING%'   is used in the begining of like clause , the the Qptimizer makes use of an index to perform on query thereby increasig the performance

But if the leading character' in '%STRING%' is a wildcard(say '%') , then the Optimizer will not be able to use an index, and a full table scan  (FTS ) must be run, which reduces performance and takes more time.

Hence it is suggested to go for '%STRING%'   only if STRING is a part of entire pattern say 'SUBSTRING'

No comments:

Post a Comment