003 - Optimizing: First Value Last Value in a Set - How to Optimize the 3 SQL Layers

In this video, we are dissecting a problem found on Reddit. First we will be generating 250,000 records and then we will be looking at the performance between a Self Join, FIRST_VALUE, LAST_VALUE window functions, and a CROSS APPLY with a sub query. ** I noticed there is an additional Index on the main table labeled CrossApply. Just to absolve me from any sort of 'i see what you did there' type d*ckery: the cross apply does not use that index (https://imgur.com/d2gZ8lx). the Cross Apply uses the Name covering index. Apologies. ** First_Value Last_Value cpu/elapsed without the Join (https://imgur.com/UyRXmlw) ** another query was given to me: SELECT Names, LTRIM(MIN(CONCAT(dates,score))) AS first_value ,LTRIM(max(CONCAT(dates,score))) AS last_value FROM NameScoreDate GROUP by Names unfortunately.. it also performed slower than the cross apply. Great idea, just not performant. https://imgur.com/wkxrsf3 (this new query without a RIGHT, since LTRIM in MSSQL doesnt have a second argument) https://imgur.com/7mNMA8y (new query with a RIGHT, since that is what you would need to do to get the values) ---- Table of Contents ---- 00:00 Intro 00:26 Title 00:33 About the DataSet 01:36 Building the DataSet 03:27 Self Join Performance (Parallelism) 04:36 FIRST_VALUE LAST_VALUE Performance (Parallelism) 05:54 CROSS APPLY / MIN() MAX() Performance 07:30 Closing 07:55 Outro ---- Important links for this video ---- ---- Support Me ---- Patreon: https://www.patreon.com/elricsims Ko-Fi: https://ko-fi.com/elricsims ---- SQL Plugins (I get paid commission these) --- SQL Complete Affiliate Link: https://secure.2checkout.com/order/cart.php?PRODS=5023603&QTY=1&AFFILIATE=114121&__c=1 #SQLTuning #SQLOptimization #SQLPerformance