Querying an enormous table (~150 million rows) with multiple joins - SQL Server First off, I don't have SHOWPLAN permission on this server or permission to add indexes, etc. I am not a database administrator by trade; I'm just trying to query a database. There is already indexes set up on both tables using the join variables in the order in which they appear in the join.

Here is the code:

SELECT rt.VehicleID,rt.PersonalAutoCovID,SUM(CAST(rd.OutputValue AS INT)) VehicleScore
FROM #RatingDataTemp1 rt
INNER JOIN modelingdata md ON md.BranchID = rt.BranchID
    AND (rt.EditEffectiveDate &gt;= md.EffectiveDate OR md.EffectiveDate IS NULL)
    AND (rt.EditEffectiveDate &lt; md.ExpirationDate OR md.ExpirationDate IS NULL)
GROUP BY rt.VehicleID,rt.PersonalAutoCovID


#RatingDataTemp1 is 250K rows, and modelingdata is 150M rows. This join currently takes 4 minutes, but I really feel it should be faster. Any way I can speed it up?
Try running the cases seperately.  If that's faster you can work on combining them into one query.  eg something like the not strictly-equivilent:

SELECT rt.VehicleID,rt.PersonalAutoCovID,SUM(CAST(rd.OutputValue AS INT)) VehicleScore
FROM #RatingDataTemp1 rt
INNER JOIN modelingdata md 
    ON md.BranchID = rt.BranchID
    AND (rt.EditEffectiveDate &gt;= md.EffectiveDate )
    AND (rt.EditEffectiveDate &lt; md.ExpirationDate )
GROUP BY rt.VehicleID,rt.PersonalAutoCovID


and

SELECT rt.VehicleID,rt.PersonalAutoCovID,SUM(CAST(rd.OutputValue AS INT)) VehicleScore
FROM #RatingDataTemp1 rt
INNER JOIN modelingdata md 
    ON md.BranchID = rt.BranchID
    AND md.EffectiveDate is null
    AND md.ExpirationDate IS NULL
GROUP BY rt.VehicleID,rt.PersonalAutoCovID


And if the target date range is small, consider changing the #RatingDataTemp1 to have one row per day per BranchID and doing a straight inner join on both columns.