SQL View slow when filtered. Is there a clean way to improve performance?

Let me open with:

SHOWPLAN permission denied in database ‘MyDatabase’.

With that out of the way, I’ll layout my situation.

So, The database I work with has a view that executes fairly quickly.

SELECT * FROM MyView

returns 32 rows in 1 second and includes a non-indexed column of values (IDs) I need to filter on.

If I filter on these IDs directly in the view:

SELECT * FROM MyView WHERE MyView.SomeId = 18

Things slow immensely and it takes 21 seconds to return the 20 rows with that ID.

As an experiment I pushed the unfiltered results into a temporary table and executed the filtered query on the the temporary table:

IF OBJECT_ID('tempdb..#TEMP_TABLE') IS NOT NULL
BEGIN
    DROP TABLE #TEMP_TABLE
END    

SELECT * INTO #TEMP_TABLE
FROM MyView;

SELECT * 
FROM #TEMP_TABLE 
WHERE #TEMP_TABLE.SomeId = 18

DROP TABLE #TEMP_TABLE

And found that it returns the filtered results far faster (roughly 1 second)

Is there a cleaner syntax or pattern that can be implemented to achieve the same performance?

---

UPDATE: View Definition and Description
Manually obfuscated, but I was careful so hopefully there aren’t many errors. Still waiting on SHOWPLAN permissions, so Execution Plan is still pending.

The view’s purpose is to provide a count of all the records that belong to a specific component (CMP.COMPONENT_ID = ‘100’) grouped by location.

“Belonging” is determined by the record’s PROC_CODE (mapped through PROC_ID) being within the CMP’s inclusion range (CMP_INCs) and not in the CMP’s exclusion range (CMP_EXCs).

In practice, exclusion ranges are created for individual codes (the bounds are always equal) making it sufficient to check that the code is not equal a bound.

PROC_CODES can (and don’t always) have an alphabetic prefix or suffix, which makes the ISNUMERIC() comparison necessary.

Records store PROC_IDs for their PROC_CODEs, so it’s necessary to convert the CMP’s PROC_CODE ranges into a set of PROC_IDs for identifying which records belong to that component

The performance issue occurs when trying to filter by DEPARTMENT_ID or LOCATION_ID

[CO_RECORDS] is also a view, but if it’s that deep I’m going turf this to someone with less red tape to fight through.

    CREATE VIEW [ViewsSchema].[MyView] AS 
    WITH 
    CMP_INCs AS (SELECT RNG.*, COALESCE(RNG.RANGE_END, RNG.RANGE_BEG) [SAFE_END] FROM DBEngine.DBO.DB_CMP_RANGE [RNG] WHERE [RNG].COMPONENT_ID = '100'),
    CMP_EXCs AS (SELECT CER.* FROM DBEngine.DBO.DB_CMP_EXC_RANGE CER WHERE CER.COMPONENT_ID = '100'),
    CMP_PROC_IDs AS (
        SELECT 
            DBEngine_ProcTable.PROC_ID          [CMP_PROC_ID],
            DBEngine_ProcTable.PROC_CODE        [CMP_PROC_CODE],
            DB_CmpTable.COMPONENT_ID            [CMP_ID],
            MAX(DB_CmpTable.COMPONENT_NAME)     [CMP_NAME]

        FROM        [DBEngine].DBO.DBEngine_ProcTable   DBEngine_ProcTable
        LEFT JOIN   CMP_INCs                            ON      ISNUMERIC(DBEngine_ProcTable.PROC_CODE) = ISNUMERIC(CMP_INCs.RANGE_BEG) 
                                                        AND(DBEngine_ProcTable.PROC_CODE = CMP_INCs.RANGE_BEG 
                                                         OR DBEngine_ProcTable.PROC_CODE BETWEEN CMP_INCs.RANGE_BEG AND CMP_INCs.SAFE_END)

        INNER JOIN  DBEngine.DBO.DB_CmpTable            ON CMP_INCs.COMPONENT_ID = DB_CmpTable.COMPONENT_ID
        LEFT JOIN   CMP_EXCs    EXCS                    ON EXCS.COMPONENT_ID = DB_CmpTable.COMPONENT_ID AND EXCS.EXCL_RANGE_END = DBEngine_ProcTable.PROC_CODE

        WHERE       EXCS.EXCL_RANGE_BEG IS NULL

        GROUP BY 
            DBEngine_ProcTable.PROC_ID,
            DBEngine_ProcTable.PROC_CODE,
            DBEngine_ProcTable.BILL_DESC,
            DBEngine_ProcTable.PROC_NAME,
            DB_CmpTable.COMPONENT_ID
    )

    SELECT 
         RECORD.LOCATION_NAME               [LOCATION_NAME]
       , RECORD.LOCATION_ID                 [LOCATION_ID]
       , MAX(RECORD.[Department])           [DEPARTMENT]
       , RECORD.[Department ID]             [DEPARTMENT_ID]
       , SUM(RECORD.PROCEDURE_QUANTITY)     [PROCEDURE_COUNT]

    FROM        DBEngineCUSTOMRPT.ViewsSchema.CO_RECORDS        [RECORDS]
    INNER JOIN  CMP_PROC_IDs                                    [CV]        ON [CV].CMP_PROC_ID = [RECORDS].PROC_ID
    CROSS JOIN  (SELECT DATEADD(M, DATEDIFF(M, 0,GETDATE()), 0) [FIRSTOFTHEMONTH])      VARS

    WHERE [RECORDS].TYPE = 1
    AND   ([RECORDS].VOID_DATE IS NULL OR [RECORDS].VOID_DATE >= VARS.[FIRSTOFTHEMONTH] )
    AND   [RECORDS].POST_DATE < VARS.[FIRSTOFTHEMONTH] 
    AND   [RECORDS].DOS_MONTHS_BACK = 2

    GROUP BY [RECORDS].LOCATION_NAME, [RECORDS].[Department ID]
    GO

Answer

Based on the swift down votes, the answer to my question is

‘No, there is not a clean syntax based solution for the improved performance, and asking for one is ignorant of the declarative nature of SQL you simple dirty plebeian’.

From the requests for the view’s definition, it’s clear that performance issues in simple queries should be addressed by fixing the structure of the objects being queried (‘MyView’ in this case) rather than syntactical gymnastics.

For interested parties the issue was resolved by adding a Row_Number() column to the final select in the view definition, wrapping it in a CTE, and using the new column in an always true filter while selecting the original columns.

I have no idea if this is the optimal solution. It doesn’t feel good to me, but it appears to be working.

CREATE VIEW [ViewsSchema].[MyView] AS 
WITH 
CMP_INCs AS (SELECT RNG.*, COALESCE(RNG.RANGE_END, RNG.RANGE_BEG) [SAFE_END] FROM DBEngine.DBO.DB_CMP_RANGE [RNG] WHERE [RNG].COMPONENT_ID = '100'),
CMP_EXCs AS (SELECT CER.* FROM DBEngine.DBO.DB_CMP_EXC_RANGE CER WHERE CER.COMPONENT_ID = '100'),
CMP_PROC_IDs AS (
        SELECT 
            DBEngine_ProcTable.PROC_ID          [CMP_PROC_ID],
            DBEngine_ProcTable.PROC_CODE        [CMP_PROC_CODE],
            DB_CmpTable.COMPONENT_ID            [CMP_ID],
            MAX(DB_CmpTable.COMPONENT_NAME)     [CMP_NAME]

        FROM        [DBEngine].DBO.DBEngine_ProcTable   DBEngine_ProcTable
        LEFT JOIN   CMP_INCs                            ON      ISNUMERIC(DBEngine_ProcTable.PROC_CODE) = ISNUMERIC(CMP_INCs.RANGE_BEG) 
                                                        AND(DBEngine_ProcTable.PROC_CODE = CMP_INCs.RANGE_BEG 
                                                         OR DBEngine_ProcTable.PROC_CODE BETWEEN CMP_INCs.RANGE_BEG AND CMP_INCs.SAFE_END)

        INNER JOIN  DBEngine.DBO.DB_CmpTable            ON CMP_INCs.COMPONENT_ID = DB_CmpTable.COMPONENT_ID
        LEFT JOIN   CMP_EXCs    EXCS                    ON EXCS.COMPONENT_ID = DB_CmpTable.COMPONENT_ID AND EXCS.EXCL_RANGE_END = DBEngine_ProcTable.PROC_CODE

        WHERE       EXCS.EXCL_RANGE_BEG IS NULL

        GROUP BY 
            DBEngine_ProcTable.PROC_ID,
            DBEngine_ProcTable.PROC_CODE,
            DBEngine_ProcTable.BILL_DESC,
            DBEngine_ProcTable.PROC_NAME,
            DB_CmpTable.COMPONENT_ID
),

RESULTS as (
    SELECT 
      RECORD.LOCATION_NAME               [LOCATION_NAME]
    , RECORD.LOCATION_ID                 [LOCATION_ID]
    , MAX(RECORD.[Department])           [DEPARTMENT]
    , RECORD.[Department ID]             [DEPARTMENT_ID]
    , SUM(RECORD.PROCEDURE_QUANTITY)     [PROCEDURE_COUNT]
    , ROW_NUMBER() OVER (ORDER BY TDL.[Medical Department ID], TDL.[BILL_AREA_ID], TDL.JP_POS_NAME) [ROW]

    FROM        DBEngineCUSTOMRPT.ViewsSchema.CO_RECORDS        [RECORDS]
    INNER JOIN  CMP_PROC_IDs                                    [CV]        ON [CV].CMP_PROC_ID = [RECORDS].PROC_ID
    CROSS JOIN  (SELECT DATEADD(M, DATEDIFF(M, 0,GETDATE()), 0) [FIRSTOFTHEMONTH])      VARS

    WHERE [RECORDS].TYPE = 1
    AND   ([RECORDS].VOID_DATE IS NULL OR [RECORDS].VOID_DATE >= VARS.[FIRSTOFTHEMONTH] )
    AND   [RECORDS].POST_DATE < VARS.[FIRSTOFTHEMONTH] 
    AND   [RECORDS].DOS_MONTHS_BACK = 2

    GROUP BY [RECORDS].LOCATION_NAME, [RECORDS].[Department ID]
)
SELECT 
    [LOCATION_NAME]
  , [LOCATION_ID]
  , [DEPARTMENT]
  , [DEPARTMENT_ID]
  , [PROCEDURE_COUNT]
FROM RESULTS 
WHERE [ROW] > 0
GO