how to optimize sql query which is taken more than 25 sec.

My query is given below


declare @ContactID int
set @ContactID=1
declare @UserTypeID int
set @UserTypeID=1
declare @SubUserType int
set @SubUserType=1
declare @UserID int
set @UserID=0
declare @AgentID int
set @AgentID =0
declare @PaxName varchar(50)
set @PaxName=N'Neharika'
declare @PolicyNo int
set @PolicyNo=53504975
declare @FromDate varchar(50)
set @FromDate=NULL
declare @ToDate varchar(50)
set @ToDate=NULL
declare @Status varchar(50)
set @Status=N'1,2,5'
declare @startIndex int
set @startIndex='0'
declare @endIndex int
set @endIndex='20'

Select
Booking.AgentID ,
Booking.BookingID ,
BookedBy = Case @UserTypeID
when 1 then
case Booking.Type
when 1 then (Select Name from pd_tbSI_Users where ContactID = Booking.CreatedBy) + ' (Booked for : Self )'
when 2 then (Select Name from pd_tbSI_Users where ContactID = Booking.CreatedBy)+ ' (Booked for : Agent - ' + (Select Name from pd_tbSI_Agents where AgentID = Booking.AgentID) + ')'
else (Select Name from pd_tbSI_Agents where AgentID = Booking.AgentID)
end
when 2 then
case Booking.Type
when 0 then (Select Name from pd_tbSI_Agents_Users where ContactID = Booking.CreatedBy)
else (Select Name from pd_tbSI_Users where ContactID = Booking.CreatedBy) + ' (Booked by Travel Protector)'
end
end,

Booking.DepartDate ,
Booking.ReturnDate ,
Booking.CreatedOn ,
Booking.Status ,
strStatus = case Booking.Status
when 1 then 'Ready'
when 2 then 'Booked'
when 3 then 'Booked'
when 5 then 'Cancelled'
end,

Premium = dbo.fn_INS_CustPrice (Booking.InsID ,Booking.Age,Booking.Duration) ,
Booking.Pax ,
Booking.Age ,
Booking.Duration ,
Booking.Destination ,
Booking.InsID ,
Booking.Trip ,
ins.Source,
Paxes = dbo.fn_INS_Booking_AllPaxes(Booking.BookingID)

from pd_tbIns_Booking Booking
inner join pd_tbInsurance ins
on Booking.InsID=ins.InsID
where

Booking.BookingID in
(
Select innerTable.BookingID
from
(
Select
temp.BookingID,
ROW_NUMBER () over(order by temp.createdon desc) as rowNum
from pd_tbIns_Booking temp
where
(
( temp.CreatedBy = case when (@UserTypeID = 2 and @SubUserType = 1) OR
(@UserTypeID = 1 and @SubUserType in (1,2))
then temp.CreatedBy
else @ContactID
end

and

temp.AgentID = case when @UserTypeID = 2
then (Select AgentID from pd_tbSI_Agents_Users
where ContactID = @ContactID )
else temp.AgentID
end
)

OR

(
temp.AgentID in (Select intValue from dbo.fnIntegersArray
(dbo.fn_INS_Booking_SEARCHQUERY(@ContactID,temp.AgentID,@UserTypeID,@SubUserType))
)

and

temp.CreatedBy = case when @UserTypeID = 2 and @SubUserType =2
then @ContactID
else temp.CreatedBy
end
)
)

and

temp.CreatedBy = case when @UserID = 0
then temp.CreatedBy
else @UserID
end

and

temp.AgentID = case when @AgentID = 0
then temp.AgentID
else @AgentID
end
and

temp.CreatedOn >= case when @FromDate is null
then temp.CreatedOn
else CONVERT (datetime,@FromDate,103)
end
and

temp.CreatedOn <= case when @ToDate is null
then temp.CreatedOn
else CONVERT (datetime,@ToDate,103)
end

and

1 = case when @PolicyNo IS not null
then dbo.fn_INS_Booking_PolicyNo(temp.BookingID ,@PolicyNo )
else 1
end

and

1 = case when @PaxName <> ''
then d