title: Need help speeding this query
body: <p>This is my query which takes about 5 seconds fetching 50 records. The total rows in DB are about 100K.</p>
  
  <pre><code>exec sp_executesql N'
  set arithabort off;
  set transaction isolation level read uncommitted;
  With cte 
  as (
      Select 
          ROW_NUMBER() OVER (Order By  d.LastStatusChangedDateTime  desc ) peta_rn,
          d.DocumentID
      From Documents d
          Inner Join Users u on d.UserID = u.UserID
          Inner Join IGroupes ig on ig.IGroupID = d.IGroupID
          Inner Join ITypes it on it.ITypeID = d.ITypeID 
      Where 1=1  
          And DateDiff(day, @0, d.LastStatusChangedDateTime ) &gt;= 0  
          And DateDiff(day, d.LastStatusChangedDateTime, @1) &gt;= 0  
          And (d.JurisdictionID = @2 
              Or DocumentStatusID = @3 
              Or DocumentStatusID = @4
              Or DocumentStatusID = @5 
              Or DocumentStatusID = @6) 
  ) 
  Select 
      d.DocumentID, 
      d.IsReEfiled, 
      d.IGroupID, 
      d.ITypeID, 
      d.RecordingDateTime, 
      d.CreatedByAccountID, 
      d.JurisdictionID, 
      d.LastStatusChangedDateTime as LastStatusChangedDateTime,  
      d.IDate, 
      d.InstrumentID, 
      d.DocumentStatusID,
      ig.Abbreviation as IGroupAbbreviation, 
      u.Username, 
      j.JDAbbreviation, 
      inf.DocumentName,
      it.Abbreviation as ITypeAbbreviation, 
      d.DocumentDate, 
      ds.Abbreviation as DocumentStatusAbbreviation,  
      ds.Name as DocumentStatusName,
      (Select 
          Cast(
              Case 
                  When d.DocumentID = (   Select  Top 1 doc.DocumentID 
                                          From    Documents doc 
                                          Where   doc.JurisdictionID = d.JurisdictionID 
                                                  And doc.DocumentStatusID = d.DocumentStatusID
                                          Order By LastStatusChangedDateTime) 
                  Then 1 
                  Else 0 
              End as bit))
          as CanChangeStatus,
      Upper((
          Select  Top 1 Stuff((
                          Select  ''='' + dbo.GetDocumentNameFromParams(Business, FirstName, MiddleName, LastName, Abbreviation, NameTypeID)  
                          From    DocumentNames dn
                                  Left Join Titles t on dn.TitleID = t.TitleID               
                          Where   DocumentID = d.DocumentID
                          For     XML PATH('''')),1,1,''''))) 
      as FlatDocumentName 
  From    
      Documents d 
      Left Join IGroupes ig On d.IGroupID = ig.IGroupID 
      Left Join ITypes it On d.ITypeID = it.ITypeID 
      Left Join Users u On u.UserID = d.UserID 
      Left Join DocumentStatuses ds On d.DocumentStatusID = ds.DocumentStatusID 
      Left Join InstrumentFiles inf On d.DocumentID = inf.DocumentID
      Left Join Jurisdictions j on j.JurisdictionID = d.JurisdictionID 
      Inner Join cte on cte.DocumentID = d.DocumentID 
  Where   
      1=1 
      And peta_rn&gt;=@13 
      AND peta_rn&lt;=@14 
  Order   
      by peta_rn
  ',N'@0 datetime,@1 datetime,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,@9 int,@10 int,@11 int,@12 int,@13 int,@14 int',
  @0='2013-03-01 00:00:00',@1='2013-04-30 00:00:00',@2=1,@3=5,@4=9,@5=4,@6=1,@7=1,@8=5,@9=9,@10=4,@11=1,@12=1,@13=0,@14=50
  </code></pre>
  
  <p>I confirm the part which takes time is this culprit subquery:</p>
  
  <pre><code>(Select 
          Cast(
              Case 
                  When d.DocumentID = (   Select  Top 1 doc.DocumentID 
                                          From    Documents doc 
                                          Where   doc.JurisdictionID = d.JurisdictionID 
                                                  And doc.DocumentStatusID = d.DocumentStatusID
                                          Order By LastStatusChangedDateTime) 
                  Then 1 
                  Else 0 
              End as bit))
  </code></pre>
  
  <p>As an alternative I tried to use this solution:</p>
  
  <pre><code>exec sp_executesql N'
  set arithabort off;
  set transaction isolation level read uncommitted;
  With cte 
  as (
      Select  ROW_NUMBER() OVER (Order By  d.LastStatusChangedDateTime  desc ) peta_rn,
              CASE    WHEN ROW_NUMBER() OVER ( 
                          PARTITION BY d.JurisdictionID, d.DocumentStatusID ORDER BY LastStatusChangedDateTime ) = 1 
                      THEN 1 
                      ELSE 0 
              END 
              AS  CanChangeStatus,
              d.DocumentID
      From    Documents d
              Inner Join Users u on d.UserID = u.UserID
              Inner Join IGroupes ig on ig.IGroupID = d.IGroupID
              Inner Join ITypes it on it.ITypeID = d.ITypeID 
      Where   1=1   
              And (d.JurisdictionID = @2 
                  Or DocumentStatusID = @3 
                  Or DocumentStatusID = @4
                  Or DocumentStatusID = @5 
                  Or DocumentStatusID = @6) 
  ) 
  Select 
      d.DocumentID, 
      d.IsReEfiled, 
      d.IGroupID, 
      d.ITypeID, 
      d.RecordingDateTime, 
      d.CreatedByAccountID, 
      d.JurisdictionID, 
      d.LastStatusChangedDateTime as LastStatusChangedDateTime,  
      d.IDate, 
      d.InstrumentID, 
      d.DocumentStatusID,
      ig.Abbreviation as IGroupAbbreviation, 
      u.Username, 
      j.JDAbbreviation, 
      inf.DocumentName,
      it.Abbreviation as ITypeAbbreviation, 
      d.DocumentDate, 
      ds.Abbreviation as DocumentStatusAbbreviation,  
      ds.Name as DocumentStatusName,
      cte.CanChangeStatus,  
      Upper((
          Select  Top 1 Stuff((
                          Select  ''='' + dbo.GetDocumentNameFromParams(Business, FirstName, MiddleName, LastName, Abbreviation, NameTypeID)  
                          From    DocumentNames dn
                                  Left Join Titles t on dn.TitleID = t.TitleID               
                          Where   DocumentID = d.DocumentID
                          For     XML PATH('''')),1,1,''''))) 
      as FlatDocumentName                                
  From 
      Documents d 
      Left Join IGroupes ig On d.IGroupID = ig.IGroupID 
      Left Join ITypes it On d.ITypeID = it.ITypeID 
      Left Join Users u On u.UserID = d.UserID 
      Left Join DocumentStatuses ds On d.DocumentStatusID = ds.DocumentStatusID
      Left Join InstrumentFiles inf On d.DocumentID = inf.DocumentID 
      Left Join Jurisdictions j on j.JurisdictionID = d.JurisdictionID 
      Inner Join cte on cte.DocumentID = d.DocumentID 
  Where 
      1=1 
      And peta_rn&gt;=@13 
      AND peta_rn&lt;=@14 
  Order 
      by peta_rn
  ',N'@0 datetime,@1 datetime,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,@9 int,@10 int,@11 int,@12 int,@13 int,@14 int',
  @0='2010-03-01 00:00:00',@1='2013-04-30 00:00:00',@2=1,@3=5,@4=9,@5=4,@6=1,@7=1,@8=5,@9=9,@10=4,@11=1,@12=1,@13=0,@14=50
  </code></pre>
  
  <p>But it still takes much more than 4 seconds. Ideally, I'd like to expect the result in under 1 second. Is there any way to speed this up? I already have indexes on all search columns and which includes <code>JurisdictionID</code> and <code>DocumentStatusID</code></p>