sql server - Stored procedure execution is too slow -

i have stored procedure refer multiple tables (four specific i.e refurbref, activationdetailrefurb, activationdetailreplaced, replaceddata) of approx 1 lac of data on each table.

i need bind data stored procedure ui on front end. when tried executing stored procedure on sql server 2008 took 20 minutes execute , fetch result. there's no way user's going wait long gazing @ "please wait loading" user interface.

this procedure:

create procedure [dbo].[usplotfailuredetail]      @fromdate varchar(50),      @todate varchar(50),      @vendorname varchar(50),      @modelname varchar(50) begin     select          d.lotqty,          approvedqty = count(distinct d.serialnumber),         d.dispatchdate,         installed = count(a.serialnumber) + count(r.serialnumber),         doa = sum(case when datediff(day, coalesce(a.activationdate,r.activationdate), f.recorddate) between 0 , 10 1 else 0 end),         bounce = sum(case when datediff(day, coalesce(a.activationdate,r.activationdate), f.recorddate) between 11 , 180 1 else 0 end)              refurbref d      left join          activationdetailrefurb on d.serialnumber= a.serialnumber                                  , d.dispatchdate <= a.activationdate                                   , d.lotqty = a.lotqty     left join          activationdetailreplaced r on d.serialnumber= r.serialnumber                                    , d.dispatchdate <= r.activationdate                                    , d.lotqty = r.lotqty                                    , (a.activationdate null or a.activationdate <= d.dispatchdate)     left join          replaceddata f on f.oldserialnumber = (coalesce (a.serialnumber, r.serialnumber))                        , f.recorddate >= (coalesce (a.activationdate, r.activationdate))              d.dispatchdate between @fromdate , @todate           , d.vendorname = @vendorname          , d.model = @modelname     group          d.lotqty, d.dispatchdate end 

there 2 types of results procedure extracts, results based on vendor , on model. if result extracted based on vendor i.e using @fromdate, @todate , @vendor, procedure takes less 2 minutes execute , result. when 4 variables used in procedure above takes not less 20 minutes execute.

is there way optimize query increase performance of procedure?

thanks in advance

given information provided @ refurbref.model number see if there covering index on field. bet there not based on jumping 20 minutes once added criteria. additionally, change variables dates varchar.

@fromdate date,    @todate date, 

hope helps, jason