Thoughtful Thursday # 2

Continuing the trend…

What I’m thinking about: I should be focusing more on my python skills. I will have to brush up using a few online tutorials before I start class.

What I’m working on: I have a few on-going projects. I started collecting more stock data for my financial model to assist me with options trades. I’ve been writing new SQL scripts to join and analyze the data. Reading more about machine learning has me pondering the features I’d include in my predictive model.

What I’m eating: Nothing healthy this week. I have a weakness for Double bacon butter burgers from Culvers. I get one almost every week. This will change when my work hours shift in a couple weeks. I will miss them!

What I’m Playing: I’ve been grinding away in Path of Exile’s Betrayal League. My main build this league has been Storm Brand. I love it!

What I’m changing: My sleep schedule is going to changing over the next week and it is going to be rough. I am a night owl; however, I’m a little excited to try being a morning person again. My life has changed a lot over the past three years, and maybe the mornings are where(when) I need to be.

A Warning in my Execution Plan

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

Thoughtful Thursday #1

Inspired by Timothy Ferris’ 5-Bullet Friday where he lists 5 things he is keeping up on, I am going to list some things that I’m doing and thinking. I call the exercise Thoughtful Thursday.

Timothy Ferris is a best selling author and self-proclaimed human guinea pig. If you are unfamiliar with his work, I highly recommend checking out his blog here: https://tim.blog/

What I’m Learning: This week I’ve been focusing my efforts on improving my SQL skills. I completed SQL Server Performance Tuning part 1 last week and have been working my way through part 2 this week. I’ve learned a lot about the techniques used to improve both SQL server and SQL query performance. If you’re interested in learning about SQL server optimization and already know the basics, I recommend starting with this course from Udemy: https://www.udemy.com/sql-server-performance-tuning-part-1/

What I’m Watching: Lately I’ve been watching The Office on repeat. The antics of Michael Scott and company rarely get old to me. Although I love the show, I can’t say I love every episode. One of the episodes I regularly skip is Scott’s Tots. It is just too cringe…

What I’m Reading: I’ve been slowly working my way through Linear Regression And Correlation: A Beginner’s Guide. I am reading this as a way to prepare myself for the Data Analysis Boot Camp I’m attending through the University of Minnesota.

What I’m Eating: I ventured into the world of dried fruit this week! Historically, the only dried foods I’ve enjoyed are types of jerky. That is why I run http://ultimatejerkyreview.com. However, I had some delicious dried strawberries yesterday and I think I’m hooked!  

What I’m Thinking: Why did I wait so long to try a kindle? I used to love books, but after I started using the kindle app on my ipad, I’ve been powering through books much easier than their paper equivalent. Coincidentally, today when I was looking through reddit, I came across this study which perfectly captures the reasons reading on a kindle is so much less intimidating than picking up the hardcover: https://digest.bps.org.uk/2019/01/14/study-identifies-the-most-effective-mental-strategies-that-people-use-to-get-through-aversive-challenges/