title: Optimizing specific sql query
body: <p>I am not an SQL expert, please help, how can I optimize this query?</p>
  
  <p>I don't have indexes, only the table called master_table and more tables, I must optimize this query to get the same results, I can create indexes if I have to, but I cannot change the creation table....</p>
  
  <pre><code>select month(date_hour), passenger, nationality, passport, airline,
     count(*) N_Viagens
  from masterTable
  group by month(date_hour), passenger, airline, nationality, passport 
  having count(*) &gt; 10
  </code></pre>
  
  <p>next is the code to create the table but I can't edit that code, only the query or creating indexes:</p>
  
  <pre><code>select
  p.birthdate, p.gender, p.passport, p.name + ' ' + p.surname passenger, p.nationality,
      r.class, r.flightNR, r.payment, r.ticketNR,
      f.src_AP_ID, f.dest_AP_ID, f.AL_ID, f.date_hour, f.AirCrft_Code,ac.manufacturer, ac.model,
      SA.City 'Origin City', SA.Country 'Origin Country', SA.Name 'Origin Airport', 
      DA.City 'Dest City', DA.Country 'Dest Country', DA.Name 'Dest Airport',
      al.Name airline, al.IATA, al.icao
  into masterTable 
  from passenger p
  join reservation r on r.passport = p.passport
  join flight f on f.flightNR = r.flightNR
  join airport SA on f.src_AP_ID = SA.AP_Id 
  join airport DA on f.dest_AP_ID = DA.AP_Id 
  join aircraft ac on f.airCrft_Code = ac.code
  join airline al on f.AL_ID = al.AL_ID 
  </code></pre>
  
  <p>without index:</p>
  
  <pre><code>SQL Server Execution Times: CPU time = 10125 ms,  elapsed time = 17052 ms.
  </code></pre>
  
  <p>PEOPLE I THINK I DID IT, THANKS TO ALL OF YOU, THANK YOU AGAIN</p>
  
  <p>I created the index like this:</p>
  
  <pre><code>create index idx_MasterTable_Passenger on masterTable(passport, airline)
  </code></pre>
  
  <p>and the query I changed to this:</p>
  
  <pre><code>select month(date_hour), max(passenger) as passenger, nationality, passport, airline, count(*) N_Viagens
  from masterTable
  group by airline, nationality, passport, month(date_hour)
  having count(*) &gt; 10
  </code></pre>
  
  <p>What you think, is it acceptable?</p>
  
  <pre><code>SQL Server Execution Times: CPU time = 8362 ms,  elapsed time = 5721 ms.
  </code></pre>
  
  <p>I will talk to the teacher if he agrees with this</p>
  
  <p><strong>The teacher did not accepted this changes, we cannot change tables or query, only creating a good index.... suggestions??</strong></p>