Boost query performance with avoiding basic mistake with Select statement. Performance Tip – #42

 

Problem:-

My query is slow what are the basic things I can do to get good performance without going for indexes.

Solution:-

May be this solution help you which I am describing here or it is possible you already aware of it.

 

(a) Avoid function in column :-The most basic tip is avoid any function in where clause which applied on table column.

lets understand this by few examples

Suppose your query written as follows

SELECT * FROM person.person WHERE Substring(FirstName,1,1)= 'K'

OR

SELECT * FROM person.person WHERE LEFT(FirstName,1)= 'K'

(b ) Avoid implicit conversion:-  Try to provide exact data type to avoid implicit conversion.

For example if we have firstname column in table having data type nvarchar(50) and we are comparing it with Varchar(100) then at the time of query it convert implicitly.

Lets understand with following query

DECLARE @Name AS VARCHAR(50)
SET @Name = 'K%'
SELECT * FROM Person.Person WHERE FirstName Like @name

When we run above query we get following query plan

If you see highlighted yellow it shows that query implicitly converted for changing data type

Now above query can be written as follow to avoid implicitly conversion

DECLARE @Name AS NVARCHAR(50)
SET @Name = N'K%'
SELECT * FROM Person.Person WHERE FirstName Like @name

Now when you run above query  you will get following query plan

So if you see there is no extra conversion in above figure.

I hope this small tips help you in  performance.

Thanks

RJ