Help with understanding how SQL behaves with inner joins

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 16, 2016 at 7:02 am

  #1858969

  -- the DISTINCT appears to be redundant, there were the same number of rows either side of the distinct sort operator.

  SELECT

  o.sys_key, -- "o" is the alias for table dbo.ODORDERS. Prefix all of your columns with the correct alias

  episode_key, drug_code, Usage1, qty, status, disp_qty, Flag_Disp, order_date, BarCode, PharmQty,

  PharmUnit, Available, Reserved, ordertype, OrderDr, disp_date, tmp, sched_ser, usage, Route,

  Divided, DivDose, dose, Taper, Notes, order_time, InteractionFlag, DivDoseAdjust, IndText, ORDERFLOWSTATUS,

  CashBillTransKey, PrescNo, AcceptDate, OverRideReason, PAYNODISP, ORDERPREPSTORE,

  ORDERPREPDATE, PreparedBy, bed_key, patient_id, storekey, store_code,

  pt.patengname

  FROM dbo.ODORDERS o

  INNER JOIN ASTOLOC a

  ON o.bed_key = a.locationkey

  INNER loop JOIN patient pt

  ON pt.patient_id = o.patient_id

  WHERE [status] = 0

  -- the LOOP hint is to encourage usage of one of the many indexes which could support seeks.

  -- The one in the plan (_dta_index_PATIENT_5_336420668__K10_25_26_27_28) is actually ideal.

  -- This might not make the query faster - the optimiser has calculated that the plan you posted

  -- is the "cheapest" for this query.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Igor Micev

  SSC-Dedicated

  Points: 33110

  More actions

  February 16, 2016 at 7:20 am

  #1858976

  spaghettidba (2/16/2016)

  ---

  Your Patient table is horribly overindexed. That's what you get when you accept all the recommendations from the database tuning advisor.

  I recommend that you look at the utilization of those indexes and try to delete unused ones and merge the others as much as possible.

  Based on some of the indexes names it seems that a missing indexes script had been running and proposed indexes created directly.

  Igor Micev,My blog: www.igormicev.com

• nadersam

  SSCertifiable

  Points: 6542

  More actions

  February 16, 2016 at 9:39 am

  #1859017

  Thank you very much guys i will apply those recommendations and try and get back to you with result