Need help to optimize this query

Question

Can you help me to optimize this query of mine? I've ran out of ways to optimize this. The data retrieval time here is 6 mins and I want to lessen it. Hope you can help me.

-- Main query
DECLARE @ProdClass VARCHAR(10)
,@ModelID VARCHAR(10)
,@ServiceStartDate DATETIME
,@ServiceEndDate DATETIME
,@SvcID VARCHAR(10)
,@StateID VARCHAR(10)
,@AreaID VARCHAR(10)

 SET @ProdClass = 'WPS'
 SET @ModelID = 'BM'
 SET @ServiceStartDate = convert(datetime, '10/1/2007')
 SET @ServiceEndDate = CONVERT(DATETIME, '11/1/2007')
 SET @SvcID = '358'
 SET @StateID = 'JB'
 SET @AreaID = ''

SELECT DISTINCT cus.MCUS_CUSID
    ,cus.MCUS_ENAME
    ,mod.MMOD_ENAME
    ,rou.MROU_SERNO
    ,svc.MSVC_SVCID AS SVC_NAME
    ,ar.MARE_ENAME
    ,dbo.ufn_GetLastXApptDate(rou.MROU_ROUID) AS MROU_APTDT
    ,rou.MROU_LSVDT
    ,(
        CASE MROU_FRSVE
        WHEN 'Y'
            THEN 'FREE'
        WHEN 'N'
            THEN 'NORMAL'
        END
    ) AS SERVICE_DUE_TYPE
FROM dbo.MROU_FIL AS rou
INNER JOIN dbo.MCUS_FIL AS cus ON rou.MROU_CUSID = cus.MCUS_CUSID
INNER JOIN dbo.MMOD_FIL AS mod ON rou.MROU_MODID = mod.MMOD_MODID
INNER JOIN dbo.MSVC_FIL AS svc ON rou.MROU_SVCID = svc.MSVC_SVCID
INNER JOIN dbo.MADR_FIL AS adr ON cus.MCUS_CUSID = adr.MADR_CUSID
INNER JOIN dbo.MSTA_FIL AS st ON adr.MADR_STAID = st.MSTA_STAID
INNER JOIN dbo.MARE_FIL AS ar ON adr.MADR_AREID = ar.MARE_AREID
INNER JOIN dbo.FAPT_FIL AS apt ON rou.MROU_ROUID = apt.FAPT_ROUID
WHERE rou.MROU_STAT = 'ACTIVE'
AND rou.MROU_CUSPF = 'MY'
AND apt.FAPT_APTTY in ('mf','m1','m2','m3','m4','m5','m6', 'mm')
AND ((@ProdClass = '') OR (@ProdClass <> '' AND rou.MROU_CLSID = @ProdClass))
AND ((@ModelID = '') OR (@ModelID <> '' AND rou.MROU_MODID = @ModelID))
AND (
    ((@ServiceStartDate = '') OR (@ServiceStartDate <> '' AND rou.MROU_LSVDT >= @ServiceStartDate))
    AND
    ((@ServiceEndDate = '') OR (@ServiceEndDate <> '' AND rou.MROU_LSVDT <= @ServiceEndDate))
)
AND ((@SvcID = '') OR (@SvcID <> '' AND svc.MSVC_SVCID = @SvcID))
AND ((@StateID = '') OR (@StateID <> '' AND adr.MADR_STAID = @StateID))
AND ((@AreaID = '') OR (@AreaID <> '' AND adr.MADR_AREID = @AreaID))
ORDER BY rou.MROU_LSVDT


-- function -- 
ALTER FUNCTION [dbo].[ufn_GetLastXApptDate] 
(
@rouid numeric(18,0)
)
RETURNS datetime
AS
BEGIN
    DECLARE @ApptDate datetime

    SELECT @ApptDate = MAX(FAPT_APTDT)
    FROM dbo.FAPT_FIL
    WHERE FAPT_ROUID = @rouid
        AND FAPT_STAT= 'X'
        AND FAPT_APTTY in ('mf','m1','m2','m3','m4','m5','m6', 'mm')

    RETURN @ApptDate

END

Answer 1

SQL Server has to call the scalar function for every row. To give it freedom to execute the function's query as a set based operation, move the function inline. Change:

,dbo.ufn_GetLastXApptDate(rou.MROU_ROUID) AS MROU_APTDT

to:

,(
 SELECT  MAX(FAPT_APTDT)
 FROM    dbo.FAPT_FIL
 WHERE   FAPT_ROUID = rou.MROU_ROUID
         AND FAPT_STAT= 'X'
         AND FAPT_APTTY in ('mf','m1','m2','m3','m4','m5','m6', 'mm')
 )

Your query also looks like a search query that might benefit from a different version of the execution plan for different values of parameters. You can force a new query plan for each execution by adding:

option  (recompile)

at the end of the query.