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