• Home
  • SQL Server
    • Articles
    • T-SQL Puzzles
    • Output Puzzles
    • Interview Questions
    • Performance Tuning
    • SQL SERVER On Linux
    • Resources
  • SSRS
    • SSRS Articles
    • Interview Questions
  • SSAS
    • SSAS Articles
    • DAX
  • SQL Puzzles
  • Interview Questions
    • SQL Interview Questions
    • Data Interview Questions
  • New Features(SQL SERVER)
    • SQL SERVER 2017
    • SQL SERVER 2016
    • SQL SERVER On Linux
  • Social
    • Expert Exchange
      • Top Expert in SQL
      • Yearly Award
      • Certifications
      • Achievement List
      • Top Expert of the Week
    • HackerRank (SQL)
    • StackOverflow
    • About Me
      • Contact Me
      • Blog Rules

Improving my SQL BI Skills

Improving my SQL BI Skills

Tag Archives: and Residuals

Implicit Conversions are Bad for Performances? Why?

26 Friday Jun 2015

Posted by Pawan Kumar Khowal in SQL Performance Tuning

≈ 1 Comment

Tags

26 performance tuning questions and solutions, Aaron Bertrand : Bad habits to kick : choosing the wrong data type, and Residuals, Beware of Implicit Conversions | Database Performance, Convert Implicit and the related performance issues with SQL Server, How to tune SQL queries, Identifying and Correcting SQL Server Implicit Conversion, Implicit Conversions, Implicit Conversions - MSDN Blogs, Implicit Conversions are Bad for Performances, Implicit Conversions are Bad for Performances? Why?, Implicit Conversions that cause Index Scans, Interview questions for SQL Server Performance Tuning, Is CONVERT_IMPLICIT Better Than CONVERT?, Join Performance, Looking for SQL Optimization Interview Questions, Mini-Tip #10 - Implicit vs. Explicit Conversion, Performance Effects of SQL Server Implicit Conversions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, SQL Complex Queries, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL SERVER Interview questions, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL SERVER Tips, SQL Tuning Overview, The Seven Sins against TSQL Performance, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, Type casting impact over execution performance in C#


Implicit Conversions are Bad for Performances? Why?

Pawan Kumar Khowal – Why Implicit Conversions are bad for Performances_NoCopy

What is Implicit Conversion?

When you write your T-SQL queries, you often write where clause and compare column values with expressions and constants. Now if these things are NOT of same data type then SQL Server will NOT throw any error for it but internally it will implicitly convert one of the data type to match with the other one. This is called Implicit Conversion. Common places where we see implicit conversions are where and from clause.

Pawan Kumar Khowal - Implicit Conversion

Pic – Implicit Conversion

You can see the Implicit Conversion in execution plan by hovering over the warning over operators (Like we have on Select Operator). Well Implicit conversion is NOT a good thing in the execution plan, and can lead to excessive CPU use.

In some cases, converting from one type to another may cause a loss of precision also. Please check out the chart showing all the data types and their conversions at http://msdn.microsoft.com/en-us/library/ms187928.aspx.

Now the million dollars question why Implicit Conversion is bad?

It is bad because in this case we may get a sub optimal plan. Let’s go through an example of Implicit Conversion in a where clause. Before that let’s first create a table called Customers, insert some data and create some indexes on it. In this example we are comparing Varchar value to an NVarchar value.

--

--Create a Sample table

CREATE TABLE Customers
(
	 ID INT
	,NAME VARCHAR(35)
)
GO

--Insert couple of data

INSERT INTO Customers VALUES (1,’Ramesh’),(2,’Ganesh’),(3,’Isha’),(4,’Sharlee’),(5,’Satya’),(6,’Rajesh’)

--Insert some more rows using a number table

INSERT INTO Customers
SELECT t.number , ‘Rims’
	FROM master..spt_values t
CROSS APPLY
	(SELECT Number FROM master..spt_values q WHERE q.number = t.number ) r
WHERE t.Number between 1 and 5000

--

Create some indexes on the customers table.

--

CREATE CLUSTERED INDEX Ix_ID ON Customers(ID)
CREATE NONCLUSTERED INDEX Ix_Name ON Customers(Name)

--

Now we execute below query and check out its execution plan.

--

SELECT ID,Name FROM Customers WHERE Name = N’Satya’

--

Pawan Kumar Khowal - Implicit Conversion1

In the above query we are reading complete leaf level pages using nonclustered index scan even if we have a nonclustered index on Name column and on top of that we are getting an implicit conversion so all in all we can say that our query is not performing well.

Now the question is why the optimizer is converting Name from Varchar data type to Nvarchar data type. This is because of data Type Precedence. When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.

Now let’s rewrite our query.

--

SELECT ID, Name FROM Customers WHERE Name = ‘Satya’

--

Please note that we have just removed the character N from our old query. Now let’s execute both the queries and check their execution plans.

Pawan Kumar Khowal - Implicit Conversion2

Ok so our old query is taking 81% cost and the second query is taking 19% of the cost and we have an index seek in 2nd query compared to index scan in the first query. So it is clearly evident that due to implicit conversion the cost of our query grows from 19% to 81% and also notes that we are reading complete table which is not necessary since in our case we have a nonclustered index on LastName column. All in this entire means our query is 4 times more scalable.

Summary

Now if you have a proper database design in place and if you prevent implicit conversions then your queries will perform better as less CPU and IO usage will be there. Basically you got to understand how your query optimizer works and sometimes if you can help him a little bit then you application can perform in an excellent manner. Now even you have a do a conversion do it explicitly so that SQL Server don’t have to perform it internally for you.

That’s all folks; I hope you’ve enjoyed learning about how we can eliminate implicit conversions for performance, and I’ll see you soon with more “Performance Tuning” articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com

Share this

  • Facebook
  • Twitter
  • WhatsApp
  • Email
  • LinkedIn
  • Skype
  • Reddit
  • Tumblr
  • Pinterest
  • Pocket
  • Telegram

Blog Stats

  • 733,669 hits

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,112 other followers

Pawan Khowal

502 SQL Puzzles with answers

Achievement - 500 PuzzlesJuly 18th, 2018
The big day is here. Finally presented 500+ puzzles for SQL community.

200 SQL Server Puzzle with Answers

The Big DayAugust 19th, 2016
The big day is here. Completed 200 SQL Puzzles today

Archives

December 2019
M T W T F S S
« Sep    
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

Top Articles

  • pawankkmr.files.wordpress…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.files.wordpress…
  • pawankkmr.files.wordpress…
  • pawankkmr.files.wordpress…

Archives

  • September 2018 (2)
  • August 2018 (6)
  • July 2018 (25)
  • June 2018 (22)
  • May 2018 (24)
  • April 2018 (33)
  • March 2018 (35)
  • February 2018 (53)
  • January 2018 (48)
  • December 2017 (32)
  • November 2017 (2)
  • October 2017 (20)
  • August 2017 (8)
  • June 2017 (2)
  • March 2017 (1)
  • February 2017 (18)
  • January 2017 (2)
  • December 2016 (5)
  • November 2016 (23)
  • October 2016 (2)
  • September 2016 (14)
  • August 2016 (6)
  • July 2016 (22)
  • June 2016 (27)
  • May 2016 (15)
  • April 2016 (7)
  • March 2016 (5)
  • February 2016 (7)
  • December 2015 (4)
  • October 2015 (23)
  • September 2015 (31)
  • August 2015 (14)
  • July 2015 (16)
  • June 2015 (29)
  • May 2015 (25)
  • April 2015 (44)
  • March 2015 (47)
  • November 2012 (1)
  • July 2012 (8)
  • September 2010 (26)
  • August 2010 (125)
  • July 2010 (2)

Article Categories

  • Analysis Services (6)
    • DAX (6)
  • Data (2)
    • Data warehousing (2)
  • Integration Services (2)
  • Magazines (3)
  • Reporting Services (4)
  • SQL SERVER (820)
    • Download SQL Interview Q's (212)
    • SQL Concepts (323)
    • SQL Performance Tuning (155)
    • SQL Puzzles (331)
    • SQL SERVER 2017 Linux (6)
    • SQL Server Interview Questions (308)
    • SQL SERVER Puzzles (332)
    • T SQL Puzzles (547)
    • Tricky SQL Queries (439)
  • UI (30)
    • ASP.NET (5)
    • C# (13)
    • CSS (9)
    • OOPS (3)
  • Uncategorized (5)

Recent Posts

  • Microsoft SQL Server 2019 | Features added to SQL Server on Linux September 26, 2018
  • SQL Puzzle | The Map Puzzle September 26, 2018
  • SQL OUTPUT PUZZLE | The Equal to puzzle August 17, 2018
  • SQL Puzzle | How to alter a column from null to not null August 14, 2018
  • Advanced SQL | Fill the Price for missing months August 13, 2018
  • SQL Puzzle | Multiple ways to add Date and Time to a DATETIME | SQL Interview Question August 10, 2018
  • SQL Puzzle | The Grouping Puzzle – A Single SELECT | SQL Interview Question August 7, 2018
  • SQL Puzzle | The Compare * 100 Puzzle | SQL Interview Question August 1, 2018
  • SQL Server Interview Questions & Answers – SET 22(10 Questions & Answers) July 30, 2018
  • SQL OUTPUT PUZZLE | The JOINs – How well do you know them ? | SQL Interview Question[Updated Answers] July 28, 2018
  • SQL OUTPUT PUZZLE | The IN & NOT IN – How well do you know them ? | SQL Interview Question July 28, 2018
  • SQL Puzzle | Count total occurrence of a character in all columns | SQL Interview Question July 28, 2018
  • SQL Puzzle | Ranking Puzzle – 1 | SQL Interview Question July 27, 2018
  • SQL Server on LINUX | Performance best practices and configuration guidelines July 26, 2018
  • SQL Server 2017 on LINUX FAQs – II – Microsoft July 25, 2018
  • SQL Puzzle | The Hierarchy Spacing Puzzle | SQL Interview Question July 22, 2018
  • SQL Server Question | Can we declare a variable/ define a column as INTEGER or DEC? July 21, 2018
  • Updated SQL Puzzle | The INNER JOIN – Two Rows PUZZLE | Solution without UNION/UNION All July 20, 2018
  • SQL Puzzle | The NOT in Range Puzzle – SINGLE SELECT | SQL Interview Question July 20, 2018
  • SQL Puzzle | The INNER JOIN – Two Rows PUZZLE | SQL Interview Question July 20, 2018
  • SQL Server | Have you ever gave length in the Float data type declaration? | SQL Interview Question July 19, 2018
  • SQL Puzzle | Remove trailing zeros puzzle – The DOUBLE PRECISION/FLOAT(53) data type | Advanced SQL July 19, 2018
  • SQL Puzzle | The SINGLE INSERT Puzzle / The Double INTO Puzzle | Advanced SQL July 18, 2018
  • SQL Puzzle | The JOIN Puzzle – A Single SELECT | SQL Interview Question July 18, 2018
  • SQL Server 2017 on LINUX | Install sqlcmd and bcp the SQL Server command-line tools on Ubuntu July 17, 2018
  • SQL Server 2017 on LINUX | Installing SQL Server on Ubuntu July 17, 2018
  • SQL Server 2017 on LINUX | How to install SQL Operations Studio(SOS) on LINUX(Ubuntu) July 17, 2018
  • SQL Puzzle | Multiple ways to convert datetime to date with 00:00:00.000 Time | SQL Interview Question July 16, 2018
  • SQL Puzzle | Have you ever used SELECT TOP 0 ? – Multiple ways to create a new table from existing | SQL Interview Question July 12, 2018
  • SQL Server 2017 on LINUX FAQs – Microsoft July 11, 2018

Create a website or blog at WordPress.com

Cancel
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.