• It looks like that Left Join is essentially an inner join... you are filtering the results back down AFTER you are first putting together the bigger recordset. Make this

    tbl_documents AS a

    INNER JOIN dbo.tbl_documentdetails AS b --WITH(NOLOCK)

    ON a.invoiceid = b.invoiceid

    AND a.orderId = b.orderid

    AND a.billid = b.billid

    AND b.srnumber BETWEEN 1 AND 100

    Having Clustered indexes on the ID columns are a good bet as well.

    ----------------------------------------------------