Now i changed the cross join into simple join but it returns the total count as 24 instead of total count(1 lack).any logic missing in my below join condition?and any...

CREATE PROCEDURE [dbo].[PAGING_NEW]
@parameter1 NVARCHAR(200),
@parameter2 NVARCHAR(200),
@PAGE INT,
AS
SET NOCOUNT ON;
DECLARE @ROWNUM INT =25
...

I tired this method also using CTE and OFFSET/Fetch methods for finding total count of records.This method also taking more time.so i tried old method.

CREATE PROCEDURE...

Thank You.

ok.thanks.i hope cte will improve performance.

Orginal Query

[DECLARE @LAN VARCHAR(10) = 'EN'
DECLARE @SUBFORMAT VARCHAR(50) = 'MTR'
DECLARE @CASNUM VARCHAR(20)

SELECT DISTINCT
TP.F_PRODUCT AS ID,
...

For union all operator shall i use CTE for using the hints OPTION (RECOMPILE) or OPTION (OPTIMIZE FOR UNKNOWN) at the end of query in my sp?.CTE Is right choice...

Yes i am improving the performance, i am using the hint Option(recompile) in the end of  query.is it better choice compare to OPTION (OPTIMIZE FOR UNKNOWN)?.suggest please because i am going...

Thank You.Finally found the issue.My view contains one column PHT.F_Phrase.I am selecting this column in my original select statement.Actually this column (F_Phrase) in PHR_TRA Table is NVARCHAR(MAX).So it reduces the performance...