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

implicitConversion

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

NoConversion

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

I hope this small tips help you in  performance.

Thanks

RJ

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s