title: SQL Query taking too long. How to make my queries more efficient? Creating smaller tables from database tables?
body: <p>I'm new to SQL and I'm trying to make a simple Query where I gather a lot of information about one order from many different tables. </p>
  
  <p>This is my code (it's just mostly select statements):</p>
  
  <pre><code>Select 
  
  o.Customer_Purchase_Order_Number as Shopify_Num,
  o.Control_Number, 
  
  o.Date_Entered as AMT_Timestamp,
  
  case when p.PickTicket_Number is null then i.PickTicket_Number else p.PickTicket_Number end PickTicket_Number,
  
  i.Invoice_Number,
  
  CASE WHEN  o.Order_Status=30 THEN 'CANCELED' 
  WHEN  o.Order_Status=45 THEN 'SHIPPED' 
  WHEN  o.Order_Status=10 THEN 'NORMAL' 
  WHEN  o.Order_Status=20 THEN 'NORMAL' 
  END Order_Status, 
  
  od.Odet_Line_Number,
  od.Item_Number,
  st.Description,
  od.Color_Code,
  c.Color_Description,
  
  o.Order_Value as Total_Price_Before_Discount, 
  od.Price*od.Quantity_Ordered as Line_Price_Before_Discount, 
  od.Discount_Value, 
  od.Discount_Percentage, 
  od.Price*od.Quantity_Ordered-od.Discount_Value as Line_Price_After_Discount,
  
  SUM(od.Price*od.Quantity_Ordered-od.Discount_Value) OVER(PARTITION BY o.control_number) AS Total_Price_After_Discount,
  
  od.Tax_Value, od.Tax_Percentage,
  
  od.Quantity_Ordered,
  od.Quantity_Allocated,
  od.Quantity_Invoiced,
  
  CASE WHEN  od.Line_Status=90 THEN 'ALLOCATED'
  WHEN  od.Line_Status=80 THEN 'CANCELED' 
  WHEN  od.Line_Status=70 THEN 'SHIPPED' 
  WHEN  od.Line_Status=50 THEN 'NORMAL' 
  END Line_Status,
  
  SUM(od.Freight_Charges) OVER(PARTITION BY o.control_number) AS Shipping_Cost_To_Customer,
  
  case When sh.packslip is not null then 'SHIPPED &amp; UPLOADED' ELSE z.status END Accellos_Status,
  sh.Date_modfy Accellos_Timestamp, 
  
  o.Warehouse_Code,
  b.UPS_Tracking_Number AMT_Tracking_Number, 
  sm.TRACKTRACE Accellos_Tracking_Number, 
  sh.SHIP_NAME ,
  sh.SHIP_ADD1 ,
  sh.SHIP_ADD2 ,
  sh.SHIP_CITY ,
  sh.SHIP_PROV ,
  sh.SHIP_ZIP,
  o.Ship_Via_Code, 
  sv.Description_1 as Ship_Descrp,
  sh.SHIP_VIA,
  sh.SHIP_SERVC,
  sh.SERVICE,
  sh.SHIP_NUM,
  sh.COST_SHIP,
  sh.SHIP_WGHT
  
  from [JMNYC-AMTDB].[AMTPLUS].[dbo].Orders o (nolock)
  join [JMNYC-AMTDB].[AMTPLUS].[dbo].Order_Detail od (nolock) on o.Company_Code=od.Company_Code and o.Division_Code=od.Division_Code and o.Control_Number=od.Control_Number
  left join [JMNYC-AMTDB].[AMTPLUS].[dbo].PickTickets P (nolock) on o.Company_Code=p.Company_Code and o.Division_Code=p.Division_Code and o.Control_Number=p.Control_Number
  left join [JMNYC-AMTDB].[AMTPLUS].[dbo].Invoices i (nolock) on o.Company_Code=i.Company_Code and o.Division_Code=i.Division_Code and o.Control_Number=i.Control_Number
  left join [JMNYC-AMTDB].[AMTPLUS].[dbo].box b (nolock) on o.Company_Code=b.Company_Code and o.Division_Code=b.Division_Code and i.PickTicket_Number=b.PickTicket_Number
  
  left join [JMNYC-AMTDB].[AMTPLUS].[dbo].Color c (nolock) on od.Color_Code = c.Color_Code
  left join [JMNYC-AMTDB].[AMTPLUS].[dbo].Style st (nolock) on o.Company_Code=st.Company_Code and o.Division_Code=st.Division_Code and od.item_number=st.item_number and c.color_code = st.color_code 
  left join [JMNYC-AMTDB].[AMTPLUS].[dbo].Ship_Via_File sv (nolock) on o.ship_via_code = sv.ship_via_code
  
  left join pickhead ph (nolock) on p.PickTicket_Number=ph.packslip
  left join Z_Status z on ph.PROCSTEP=z.procstep
  left join SHIPHIST sh (nolock) on case when p.PickTicket_Number is null then i.PickTicket_Number else p.PickTicket_Number end=sh.packslip
  
  left join SHIPMSTR2 sm (nolock) on case when p.PickTicket_Number is null then i.PickTicket_Number else p.PickTicket_Number end=sm.packslip
  
  where LTRIM(RTRIM(o.Company_Code))+LTRIM(RTRIM(o.Division_Code))='03001'  and o.Customer_Number='ecom2x'
  and o.Customer_Purchase_Order_Number='3112480'
  </code></pre>
  
  <p>This query is taking about 10 seconds to run. Is there any way to make it more efficient? It's already pretty barebones. I'm assuming I should try to minimize any redundant tables first since these tables I'm joining are pretty large. Is there anything I can do if I need all the tables I chose? A lot of these table I'm joining have dozens of columns when I'm joining them for just one specific column out of the whole table. Does that change anything? </p>
  
  <p>So I could create a bunch of new tables from my database tables, selecting for only the columns I need. Then I can use those tables instead. I tried to do something like that but it only made the query run never-ending (or atleast over a minute). </p>
  
  <p>This is what I tried: </p>
  
  <p>I think it fails because I essentially told it to recreate this huge table which takes a lot of time.</p>
  
  <pre><code>SELECT Company_Code, Division_Code, Control_Number, Customer_Purchase_Order_Number, 
         Date_Entered, Order_Status, Order_Value, 
         Warehouse_Code, Ship_Via_Code 
         into #o from [JMNYC-AMTDB].[AMTPLUS].[dbo].Orders OT (nolock)
  
  SELECT Company_Code, Division_Code, Control_Number, Odet_Line_Number, 
         Item_Number, Color_Code, Price, 
         Discount_Value, Discount_Percentage, Tax_Value, Tax_Percentage,
         Quantity_Ordered, Quantity_Allocated, Quantity_Invoiced, Line_Status, Freight_Charges 
         into #od from [JMNYC-AMTDB].[AMTPLUS].[dbo].Order_Detail ODT (nolock) 
  .
  .
  .
  from #o (nolock)
  join #od (nolock) on o.Company_Code=od.Company_Code and o.Division_Code=od.Division_Code and o.Control_Number=od.Control_Number
  left join #p (nolock) on o.Company_Code=p.Company_Code and o.Division_Code=p.Division_Code and o.Control_Number=p.Control_Number
  .
  .
  .
  </code></pre>
  
  <p>Does anyone know how to speed this code up/ make it more efficient in general?</p>