Just creating indexes on JOIN, WHERE and GROUP clause columns doesn’t mean that your query will always return your required results quickly. It is query optimizer which selects proper index for a query to give you an optimum performance but query optimizer can only suggest optimum query plan by using proper indexes WHEN your are helping it by writing good query syntax.
Using any type of function (system or user defined) in WHERE or JOIN clause can dramatically decrease query performance because this practice create hurdles in query optimizer work of proper index selection. One common example is TRIM functions, which are commonly used by developers in WHERE clause. For more understandings, let’s compare performance of two queries, one with TRIM function in WHERE clause and other one without TRIM functions.
USE AdventureWorks
GO
SELECT pr.ProductID,pr.Name,pr.Produc tNumber,wo.* fROM Production.WorkOrder wo
INNER JOIN Production.Product pr
ON PR.ProductID = wo.ProductID
WHERE LTRIM(RTRIM(pr.name)) = 'HL Mountain Handlebars'
GO
SELECT pr.ProductID,pr.Name,pr.Produc tNumber,wo.* fROM Production.WorkOrder wo
INNER JOIN Production.Product pr
ON PR.ProductID = wo.ProductID
WHERE pr.name = 'HL Mountain Handlebars'
Though outputs of both queries are same but first query took almost 99% of total execution time. This huge difference is just because of these trim functions so on production databases we must avoid these TRIM and other functions in both JOIN and WHERE clauses.
Nice article , you have indeed covered topic in details with sample query and nice explanation. query optimization is long walk to me and these kind of small tips which really mean a lot while getting a faster query response. hope to see more
ReplyDeleteJavin
10 tips on working fast in Unix Commands
You should clarify that the problem is using ANY function or operation (not just trim) on the "database" side of the comparation.
ReplyDeleteFor example, the following line won't cause any problem:
WHERE pr.Name = RTRIM(LTRIM(' HL Mountain Handlebars ' )
is this applicable to Oracle Database as well?
ReplyDeleteIs there any difference in the output if we use the below condition:
ReplyDeleteLTRIM(RTRIM(example1.name)) = LTRIM(RTRIM(example2.name))
instead of WHERE LTRIM(RTRIM(pr.name)) = 'HL Mountain Handlebars'
Note: the column 'name' in exapmle1 is of 'char' datatype whereas the column 'name in exapmle2 is of 'varchar' datatype.
What is work around to get TRIMMED data?
ReplyDeleteIs It a same case in Mysql database as well?
ReplyDeleteYes its exactly same in MySQL as well.
Deletewhat's the alternative ? i have to use ltrim to remove the empty in the begining of the world
ReplyDeleteIn production we try our best to remove extra spaces at the time of record insertion/update, mostly performed on frontend and never allow users to add extra spaces.
DeleteI have a varchar field, for which I have to do string matching. Without RTRIM, how else can I do it? Does RTRIM hav ebad performance even on Informix Database? In case of poor performance, what is the alternative?
ReplyDeleteSave the data in format?
Deletei cannot find any difference in sql server 2016 with ltrim&rtrim and without trimming...ssms execution plan showing same result for both queries.......
ReplyDelete