Slow in the Application, Fast in SSMS?

Question

Faced weird problem today.

I have one Stored Procedure that shows result in 1 second in SSMS but take about 10 minute to fill data with DataAdapter.fill() method. And important point is after altering stored procedure it works fine again with DataAdapter.fill() method".

Here is my SP code If any one want but there is no issue in this sp.

PROCEDURE [dbo].[SR_Get_CustomerVehicle_VINHistory] 
    @CustomerId varchar(100),
    @VinNo varchar(100)=null,
    @Stock varchar(100)=null,   
    @DlrshipId int
AS
BEGIN


   BEGIN
           Create table #Vehicle (VIN varchar(25),Model varchar(50),Make varchar(50),Year int,License varchar(50),Color varchar(20),Options varchar(250),StockNo varchar(30),EngNo varchar(50) )

           declare @VIN varchar(25)
           declare @Model varchar(50)
           declare @Make varchar(50)
           declare @Year int
           declare @License varchar(50)
           declare @Color varchar(20)
           declare @Options varchar(50)
           declare @StockNo varchar(30)
           declare @EngNo varchar(50)

           declare @i int=1
           declare @Count int
           ------------------------------------------------------------------------
           Create table #CurVehicle (ROWNumber INT IDENTITY(1,1),VIN varchar(25),Model varchar(50),Make varchar(50),Year int,License varchar(50),Color varchar(20),Options varchar(250),StockNo varchar(30),EngNo varchar(50) )

           insert into #CurVehicle(VIN,Model,Make,Year,License,Color,Options,StockNo,EngNo) 
           select distinct v.VIN,Mo.Model,Mk.Make,v.Year, ISNULL(v.License,'') License, ISNULL(cl.ColorName,'') Color,ModalType as Options,ISNULL(v.StockNo,'') as StockNo,ISNULL(v.ModelNumber,'') as EngNo
           from SR_CustomerVehicle v
           left join  CustomerMaster c on v.CustomerId = c.pkCustomerId
           left join SL_Model Mo on Mo.PKModelId = v.FKModelId
           left join SL_Make Mk on Mk.PKMakeId = v.FKMakeId
           left join SL_CarColorMaster cl on cl.PKColorId = v.FKStateColorId
           where  
           c.pkCustomerId = @CustomerId
           and VIN is not null and v.ModifyDate = (Select max(ModifyDate) from SR_customervehicle where VIN = v.VIN)
           and v.ModifyDate > CASE WHEN (Select max(opendate) from SR_Service where VIN = v.VIN) IS NULL THEN V.ModifyDate - 1 ELSE 
           (Select max(opendate) from SR_Service where VIN = v.VIN) END  
           and v.dlrshipid = @DlrshipId and c.dlrshipid = @DlrshipId    
           and v.VIN like (Case when isnull(@VinNo,'')='' then isnull(v.VIN,'') else '%' + isnull(@VinNo,'')  end)
           and isnull(v.StockNo,'') like (Case when isnull(@Stock,'')='' then isnull(v.StockNo,'') else isnull(@Stock,'')+'%' end)


            set @i = 1
           -----------------------------------------------------------------------------------------------------

           insert into #CurVehicle(VIN,Model,Make,Year,License,Color,Options,StockNo,EngNo) 
           select distinct cv.VIN,Mo.Model,Mk.Make,cv.Year,isnull(cv.License,'') License, ISNULL(cl.ColorName,'') Color , isnull(cv.Options,'') Options, ISNULL(cv.StockNo,'') StockNo, ISNULL(cv.EngNo,'') EngNo
           from CustomerMaster c
           left join  SR_Service v on v.fkCustomerId = c.pkCustomerId
           left join SR_ROVehicle cv on v.PkRONumber = cv.fkRONumber
            left join SL_Model Mo on Mo.PKModelId = cv.FKModelId
           left join SL_Make Mk on Mk.PKMakeId = cv.FKMakeId
           left join SL_CarColorMaster cl on cl.PKColorId = cv.FKStateColorId

           where  
           c.pkCustomerId = @CustomerId
           and v.VIN is not null
           and v.OpenDate = (Select max(opendate) from SR_service where VIN = v.VIN and fkCustomerId=@CustomerId)
           and convert(date, v.OpenDate) >= (Select convert(date,isnull(max(ModifyDate),v.OpenDate-1)) from SR_CustomerVehicle where VIN = v.VIN and CustomerId=@CustomerId) 
           and v.DlrshipId = @DlrshipId and c.DlrshipId = @DlrshipId 
           and cv.VIN Like (Case when isnull(@VinNo,'')='' then isnull(cv.VIN,'') else '%' + isnull(@VinNo,'') end)
           and isnull(cv.StockNo,'') like (Case when isnull(@Stock,'')='' then isnull(cv.StockNo,'') else isnull(@Stock,'')+'%' end)

            set @i = 1

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

           insert into #CurVehicle(VIN,Model,Make,Year,License,Color,Options,StockNo,EngNo) 
           select distinct v.VIN,(Select Model From SL_Model where PKModelId=i.FKModelId and DlrshipId=i.DlrshipId),
           (Select Make From SL_Make where PKMakeId=i.FKMakeId and DlrshipId=i.DlrshipId),i.Year,ISNULL(i.License,'') License, 
           (Select ColorName From SL_CarColorMaster Where PKColorId = isnull(i.FKExteriorColorId,'0')) as Color,ISNULL(i.Options,'') Options, isnull(i.StockNo,'') StockNo, ISNULL(i.ModelNo,'') as EngNo
           from SL_SALES v
           left join  CustomerMaster c on v.FKCustomerId = c.pkCustomerId
           left join SL_CarInventory i on v.VIN = i.VIN
           where  
           v.FKCustomerId = @CustomerId
           and (i.FKMakeId is not null or i.FKModelId is not null) 
           and v.DlrshipId = @DlrshipId and c.DlrshipId = @DlrshipId and i.DlrshipId = @DlrshipId
           and i.VIN like (Case when isnull(@VinNo,'')='' then isnull(i.VIN,'') else '%' + isnull(@VinNo,'') end)
           and isnull(i.StockNo,'') like (Case when isnull(@Stock,'')='' then isnull(i.StockNo,'') else isnull(@Stock,'')+'%' end)


            SELECT * FROM #CurVehicle ORDER BY VIN ;

            drop table #CurVehicle
            drop table #Vehicle
  END

END

One thing to note here that altering stored procedure solve the issue.