ms sql server wrong query execution plan taking too long

sql server 11.0.2100

query is  

DECLARE  @FromBrCode INT =1001
DECLARE @ToBrCode INT =1637
DECLARE @Cdate DATE= '31-mar-2017'

SELECT a.PrdCd, a.Name, sum(b.Balance4) as Balance
FROM D009021 a, D010014 b
WHERE a.PrdCd=ltrim(rtrim(substring(b.PrdAcctId,1,8)))
AND substring(b.PrdAcctId,9,24)='000000000000000000000000'
AND a.LBrCode=b.LBrCode
AND a.LBrCode BETWEEN @FromBrCode AND @ToBrCode
AND b.CblDate=
(SELECT max(c.CblDate) FROM D010014 c
WHERE c.PrdAcctId=b.PrdAcctId
AND c.LBrCode=b.LBrCode
AND c.CblDate<=@Cdate)
GROUP BY a.PrdCd, a.Name
HAVING sum(b.Balance4)<>0
ORDER BY a.PrdCd

Select allOpen in new window

1.  this particular query taking too much time to complete execution
2. same problem in diffrent sql server
3. no table lock found , processor and memory usage normal while query running
4. normal "select top 1000 rows" working and showing output instantly in both table (D009021 , D010014)
5. reindex and rebuild done in both tables but problem not resolved (D009021 , D010014)
6. same query is working if we reduce number of branch
(
DECLARE  @FromBrCode INT =1001
DECLARE @ToBrCode INT =1001
)


7> same query is working if we replace any one variable and use value directly


AND a.LBrCode BETWEEN @FromBrCode AND @ToBrCode


changed to


AND a.LBrCode BETWEEN 1001 AND @ToBrCode


8> same query is working and giving output within 2 mins if we add "OPTION (RECOMPILE)" at end


9> tried to clean cache query execution plan and optimized new one but problem not resolved


10> found query estimate plan and actual execution plan is different please check screen shot


 
thanks in advance for any help.