title: SQL not efficient enough, tuning assistance required
body: <p>We have some SQL that is ok on smaller data volumes but poor once we scale up to selecting from larger volumes. Is there a faster alternative style to achieve the same output as below? The idea is to pull back a single unique row to get latest version of the data... The SQL does reference another view but this view runs very fast - so we expect the issue is here below and want to try a different approach</p>
  
  <pre><code>SELECT *
  FROM 
     (SELECT (select CustomerId from PremiseProviderVersionsToday 
              where PremiseProviderId = b.PremiseProviderId) as CustomerId, 
              c.D3001_MeterId, b.CoreSPID, a.EnteredBy, 
              ROW_NUMBER() OVER (PARTITION BY b.PremiseProviderId
              ORDER BY a.effectiveDate DESC) AS rowNumber
      FROM PremiseMeterProviderVersions a, PremiseProviders b, 
           PremiseMeterProviders c
      WHERE (a.TransactionDateTimeEnd IS NULL 
      AND a.PremiseMeterProviderId = c.PremiseMeterProviderId 
      AND b.PremiseProviderId = c.PremiseProviderId)
     ) data
  WHERE data.rowNumber = 1
  </code></pre>