Since I investigate a lot of SQL queries at my job, I’ve been studying techniques to optimize SQL Server functionality, create efficient indexes and examine SQL Query performance. I’ve learned a lot about reviewing execution plans, monitoring system performance, and using tools like Query Store and Wait Stats to dissect queries hogging resources. Overall, it has been interesting to learn more about the SQL Optimizer and how it all really works “under-the-hood.”
My ongoing personal project is developing trading systems to assist me in trading stocks and options. I recently collected a dataset of stock symbols and ohlcv figures for 2018 and matched it to the curated option order flow I collected over the year. I toggled the Include Live Query Statistics option and executed my query to join the datasets. When my execution plan displayed, I noticed the SELECT operator had a warning icon:
I expanded the properties and saw this message:
Type conversion in expression (CONVERT_IMPLICIT(nvarchar(5),[sopn].[sym],0)=[edge].[dbo].[FlowTotal].[Sym]) may affect "SeekPlan" in query plan choice, Type conversion in expression (CONVERT_IMPLICIT(nvarchar(5),[sxpr].[sym],0)=[edge].[dbo].[FlowTotal].[Sym]) may affect "SeekPlan" in query plan choice, Type conversion in expression (CONVERT_IMPLICIT(nvarchar(5),[s].[sym],0)=[edge].[dbo].[FlowTotal].[Sym]) may affect "SeekPlan" in query plan choice
This is a clear example of why reviewing your execution plans is an important part of SQL optimization. For example, this warning is essentially telling me I used a different data type to express the same data. One table stores stock symbols as nvarchar and another table stores stock symbols as varchar. In order to join the tables on the SYM column, one side gets converted so the data types match. Instead of forcing the system to convert the data, I adjusted the data type in one of the tables so they match. This is the result:
A warning free execution plan!
BONUS CONTENT (not by me):
A mini discussion on the differences between nvarchar and varchar:
https://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar
What is the use of writing N’ ‘ in query sql server
https://stackoverflow.com/questions/28519231/what-is-the-use-of-writing-n-in-query-sql-server
Eric Kleppen Wins 