[T-SQL] Is there a way to improve this SELECT statement for performance?

---

Is there a way to improve this SELECT statement for performance? Posted: 30 Aug 2013 01:23 AM PDT Hello all, I have to run this query during inserts. It works fine for thousands of records but once it hits tens or hundreds of thousands of records the performance is in minutes and not seconds. Is there a way to improve it? : SELECT DISTINCT @SeasonStamp, r.RunnerID, r.RunID AS RunID, (SELECT COUNT(DISTINCT RunnerID) from RACES WITH (NOLOCK) where universalid = r.universalid AND rtrim(RunID) = rtrim(r.RunID) AND RaceDate >= @CutoffDate) AS NUMOFSponsorS, (SELECT COUNT(DISTINCT RaceDate) from RACES WITH (NOLOCK) where universalid = r.universalid AND rtrim(RunID) = rtrim(r.RunID) AND RaceDate >= @CutoffDate AND RunnerID like r.RunnerID + '%') AS NUMBEROFinhouseMeets, (SELECT COUNT(DISTINCT RaceDate) from RACES WITH (NOLOCK) where universalid = r.universalid AND rtrim(RunID) = rtrim(r.RunID) AND RaceDate >= @CutoffDate AND RunnerID NOT like r.RunnerID + '%') AS NUMBEROFoutsideMeets, r.universalidFROM RACES r WITH (NOLOCK)WHERE r.RaceDate >= @CutoffDateAND RunnerID like @Sponsor+'%'AND r.ZorX = @ZorX

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610