Optimization MAKE YOUR QUERIES FASTER MATT MARTIN SQL

Optimization MAKE YOUR QUERIES FASTER MATT MARTIN SQL Saturday Atlanta June 2017

About MATT MARTIN | CHIEF TECHNOLOGY OFFICER | SQLETL Twitter: @sqletl_matt Website: www. sqletl. com Email: matt@sqletl. com • 9 years experience • SQL Server, SSIS, EDIS, and Big Data § Enjoy Big Data projects and query tuning www. SQLETL. com 2

Agenda Mega Chainsaws Corp. 1. 2. 3. 4. 5. Company Sales Database Overview Tables involved in Sales Query Identify bottlenecks Restructure query to run faster Q&A Cool Stuff this course will teach you 1. How to generate random data of any size/type 2. How to size all tables in a database 3. How to read an execution plan and identify opportunities for performance gains www. SQLETL. com 3

1. Mega Chainsaws Corp Time to Generate some data USE MEGA_CHAINSAWS GO exec USP_LOAD_DUMMY_DATA_TO_DB www. SQLETL. com 4

1. Mega Chainsaws Corp Table Name Data Description CUST_ID Customer information (names) DPT Departments INVC_DTL Invoice line detail INVC_HDR Invoice header INVC_STAT_CD Invoice status PO_DTL Purchase order detail PO_HDR Purchase order header PO_STAT_CD PO status code PO_SUB_STAT_ CD PO sub status code SKU Product sku/description/retail SKU_STAT_CD SKU status (active/clearance/inactive) SLS_REP_ID Sales Rep name SLS_REP_STAT_ CD Employee Status Code (Active/Term) www. SQLETL. com Tip 1: Know your data size USE MEGA_CHAINSAWS GO exec usp_get_tbl_sizes 5

2. Examine Slow Query Over a dozen table joins Clustered Indexes on larger tables do not align (PO_DTL and INVC_DTL) LIKE statement in the WHERE clause multiple times, combined with OR www. SQLETL. com 6

2. Bottlenecks to look for 1. 2. 3. 4. 5. SELECTS in the WHERE clause Range Joins Like Operators Non-covering indexes Clustered Indexes not aligning and forcing a hash join on large tables AND IHDR. INVC_DT = (SELECT MAX(INVC_DT) FROM INVC_HDR WHERE INVC_ID = IHDR. INVC_ID) AND IHDR. INVC_DT BETWEEN POH. ORD_DT AND DATEADD(DAY, 90, POH. ORD_DT) OR lower(C. CUST_NM) LIKE '%alvarez%' www. SQLETL. com 7

3. Current Execution Plan Biggest Performance Killers Hash Matches Full Table Scans (no index) www. SQLETL. com 8

3. Optimize this Query Break your query into smaller chunks Leverage SELECT INTO statements (They are fast) Use temp tables to pre-process data www. SQLETL. com 9

3. Optimize this Query Walk through new Stored Proc www. SQLETL. com 10

4. Take-aways Do not fall in the trap of optimization to death. Is it really worth an extra 3 -4 hours of work to shave off another 5 seconds on a query? Know your data size. Small tables are not worth your time to optimize queries against. Odds are it will run a full table scan anyways due to diminished returns on optimization Check your clustered indexes for header/detail relational tables. If you can leverage an Identity seed as the clustered index for the header and detail, almost always do it. Why? - The optimizer has to compare 1 column to merge the two tables against and its an integer so it will be fast www. SQLETL. com 11

Optimization MATT MARTIN CTO | SQLETL Twitter: @sqletl_matt Website: www. sqletl. com Email: matt@sqletl. com www. SQLETL. com 12