Query Slow down due to structure of WHERE clause

Question

I have a query that creates an @TABLE of a population of interest. It's structure is like this:

DECLARE @SepsisTbl TABLE (
    PK INT IDENTITY(1, 1) PRIMARY KEY
    , Name                VARCHAR(500)
    , MRN                 INT
    , Account             INT
    , Age                 INT -- Age at arrival
    , Arrival             DATETIME
    , Triage_StartDT      DATETIME
    , Left_ED_DT          DATETIME
    , Disposition         VARCHAR(500)
    , Mortality           CHAR(1)
);

WITH Patients AS (
    SELECT UPPER(Patient)             AS [Name]
    , MR#
    , Account
    , DATEDIFF(YEAR, AgeDob, Arrival) AS [Age_at_Arrival]
    , Arrival
    , Triage_Start
    , TimeLeftED
    , Disposition
    , CASE
        WHEN Disposition IN (
            'Medical Examiner', 'Morgue'
        )
        THEN 'Y'
        ELSE 'N'
      END                             AS [Mortality]

    FROM SMSDSS.c_Wellsoft_Rpt_tbl
    WHERE Triage_Start IS NOT NULL
    AND (
        Diagnosis LIKE '%SEPSIS%'
        OR
        Diagnosis LIKE '%SEPTIC%'
    )
)

INSERT INTO @SepsisTbl
SELECT * FROM Patients

From this point forward I have 5 more queries of the same sort that are looking for different types of orders that I then LEFT OUTER JOIN onto this table. My question is, why does my performance degrade so much when I change the where clause of the tables from this:

AND A.Account IN (
    SELECT Account
    FROM SMSDSS.c_Wellsoft_Rpt_tbl
    WHERE (
        Diagnosis LIKE '%SEPSIS%'
        OR
        Diagnosis LIKE '%SEPTIC%'
    )

to this:

AND A.Account IN (
    SELECT Account
    FROM @SepsisTbl
)

The run time goes from 2.5 minutes to over 10 minutes with still no results. The CTE itself runs as fast as I can press F5.

Thank you,

The optimizer doesn't keep stats on table variables, which might have an impact. Depending on how many rows you have, a temp table may be faster. — andreamc, Feb 10 '16 at 01:12
@RBarryYoung I can do that when I get back in to the office tomorrow. — MCP_infiltrator, Feb 10 '16 at 01:28

score 1 · Accepted Answer · edited May 23 '17 at 11:45

1

I suspect that the problem is because the table variable doesn't have an index on Account. If you add an index on Account then I would expect better performance.

See the answer to this question for details on how to add an index: Creating an index on a table variable

edited May 23 '17 at 11:45

Community

1
1

answered Feb 10 '16 at 01:05

DeanOC

7,142
6
42
56

Query Slow down due to structure of WHERE clause

1 Answers1