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



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


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’


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

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

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.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.