title: Can this sql code be made more efficient?
body: <p>I have following piece of sql, whilst it is functioning as expected, it is a bit slow to return results (by slow I'm talking about 10 seconds to return 1000 results from a months date range). Is it possible for it to be made more efficient and/or quicker? Just to add these are the following indexes I have on the tables:-</p>
  
  <ul>
  <li>RecordID - Primary Key Unique Clustered</li>
  <li>Department - Non clustered </li>
  <li>Direction - Non clustered </li>
  <li>LocalCallGroup - Non clustered </li>
  <li>ServiceProvider - Non clustered </li>
  <li>StartTime- Non clustered </li>
  <li>UserID- Non clustered </li>
  <li>UserLocalStartTime - Non clustered </li>
  <li>UserLocalTimeOffset - Non clustered </li>
  <li>UserNumber - Non clustered </li>
  </ul>
  
  <p><strong>Declaration of variables here, removed for post</strong></p>
  
  <pre><code>    SET @TerminatingSQL = '
      SELECT 
          UserNumber, 
          ImageDirection = ''in'', 
          CallingNumber = CASE WHEN callingnumber IN(''Unavailable'',''Unknown'',''+44anonymous@10.81.253.12'',''0anonymous@10.81.253.12'') THEN ''Anonymous'' ELSE callingnumber END,
          CalledNumber,
          StartTime = dateadd(ms,(-datepart(ms,(startTime))),(startTime)),
          AnswerTime = dateadd(ms,(-datepart(ms,(answerTime))),(answerTime)),
          ReleaseTime =  dateadd(ms,(-datepart(ms,(releaseTime))),(releaseTime)),         
          CallDuration =  dateadd(ms,(-datepart(ms,(releaseTime))),(releaseTime)) - dateadd(ms,(-datepart(ms,(answerTime))),(answerTime)),
          TotalDuration = dateadd(ms,(-datepart(ms,(releaseTime))),(releaseTime)) - dateadd(ms,(-datepart(ms,(startTime))),(startTime)),
          terminationCause,
          recordID
      FROM
          dbo.TABLEA' + @Table +'
      WHERE
          serviceProvider IN ( SELECT serviceProvider FROM ccNumbers WHERE CRMID = ' + CONVERT(VARCHAR(10),@CrmId,103) + ')
          AND startTime between ''' + CONVERT(VARCHAR(10),@Fromdate,112) + ''' AND ''' + CONVERT(VARCHAR(10), @ToDate, 112) + '''
          AND Direction = ''terminating''
          AND (Department = ''' + @Department + ''' OR ''' + @Department + ''' = ''ALL'')
          AND (userid = ''' + @Userid + ''' OR ''' + @Userid + ''' = ''ALL'')'
  
      SET @OriginatingSQL = '
      SELECT 
          UserNumber, 
          ImageDirection = ''out'', 
          CallingNumber = CASE WHEN callingnumber IN(''Unavailable'',''Unknown'',''+44anonymous@10.81.253.12'',''0anonymous@10.81.253.12'') THEN ''Anonymous'' ELSE callingnumber END,
          CalledNumber,
          StartTime = dateadd(ms,(-datepart(ms,(startTime))),(startTime)),
          AnswerTime = dateadd(ms,(-datepart(ms,(answerTime))),(answerTime)),
          ReleaseTime =  dateadd(ms,(-datepart(ms,(releaseTime))),(releaseTime)),         
          CallDuration =  dateadd(ms,(-datepart(ms,(releaseTime))),(releaseTime)) - dateadd(ms,(-datepart(ms,(answerTime))),(answerTime)),
          TotalDuration = dateadd(ms,(-datepart(ms,(releaseTime))),(releaseTime)) - dateadd(ms,(-datepart(ms,(startTime))),(startTime)),
          terminationCause,
          recordID
      FROM
          dbo.TABLEA' + @Table +'
      WHERE
          serviceProvider IN ( SELECT serviceProvider FROM ccNumbers WHERE CRMID = ' + CONVERT(VARCHAR(10),@CrmId,103) + ')
          AND startTime between ''' + CONVERT(VARCHAR(10),@Fromdate,112) + ''' AND ''' + CONVERT(VARCHAR(10), @ToDate, 112) + '''
          AND Direction = ''originating''
          AND (Department = ''' + @Department + ''' OR ''' + @Department + ''' = ''ALL'')
          AND (userid = ''' + @Userid + ''' OR ''' + @Userid + ''' = ''ALL'')'
  
      SET @MainSelectSQL = @TerminatingSQL + ' Union '  + @OriginatingSQL
  
      SET @MainSQL = 'SELECT TOP (' + @PageSize + ') 
          [t1].CalledNumber,
          [t1].CallingNumber,
          [t1].UserNumber, 
          [t1].StartTime,
          [t1].AnswerTime,
          [t1].ReleaseTime,
          [t1].ImageDirection,
          [t1].CallDuration,
          [t1].TotalDuration,
          [t1].TerminationCause
      FROM (
          SELECT ROW_NUMBER() OVER (
              ORDER BY [t0].startTime) as [row_number], 
              [t0].CalledNumber,
              [t0].CallingNumber,
              [t0].UserNumber,
              [t0].StartTime,
              [t0].AnswerTime,
              [t0].ReleaseTime,
              [t0].ImageDirection,
              [t0].CallDuration,
              [t0].TotalDuration,
              [t0].TerminationCause
          FROM
              (' + @MainSelectSQL + ')  AS [t0] 
            )  AS [t1]
  
      WHERE [t1].[row_number] &gt; ' + @Page + ' * ' + @PageSize +';'
  
      EXEC (@MainSQL)
  
  -- Work out the total number of rows, but don't bother if we have the number already (i.e. when they keep the same parameters and just click paging.
  
  IF  (@CurrentCount IS NULL)
      BEGIN
          DECLARE @TotalCountSQL nvarchar(4000)
          DECLARE @ParameterList NVARCHAR(4000)   
  
          SET @ParameterList = '@TotalCount int OUTPUT'
          SET @TotalCountSQL = 'SELECT @TotalCount = COUNT(recordId) FROM (' + @MainSelectSQL + ') as a'
  
          EXEC SP_EXECUTESQL @TotalCountSQL,@ParameterList,@TotalCount=@TotalCount OUTPUT
      END
  ELSE
      BEGIN
          SET @TotalCount = @CurrentCount;
      END
  END
  </code></pre>