The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 31%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENN%3C/text%3E%3C/svg%3E)
Naomi Nosonovsky
8,451
Reputation points
Good day,
I am getting this error: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
on a relatively simple view. What are my options to fix the problem? Bellow is the view definition:
/*
2025-08-04 Naomi created the view
select * from im_automation.accent_changes
*/
CREATE OR ALTER VIEW im_automation.accent_changes
AS
WITH cte
AS (
SELECT CAST('New NPI' AS VARCHAR(10)) AS request_Type,
CAST('Change' AS VARCHAR(100)) AS Change_Type,
CASE
WHEN f.main_status = 'M'
AND COALESCE(f.sub_status, '') = '' THEN
'Active'
WHEN f.main_status = 'M'
AND f.sub_status IN ( 'L', 'M' ) THEN
'Active'
WHEN f.main_status = 'N'
AND f.sub_status = 'C'
AND f.specialty IN ( 'PT', 'OT', 'SLP' ) THEN
'Active'
WHEN f.main_status = 'N'
AND f.sub_status = 'C'
AND f.tin_number = ' 850879488' THEN
'Active'
WHEN f.main_status = 'N'
AND f.sub_status IN ( 'R', 'T' ) THEN
'Termed'
WHEN f.main_status = 'D' THEN
'Termed'
ELSE
'' -- blank?
END AS Status,
f.snapshot_date,
f.tin_number,
f.npi,
IIF(f.prov_group = 'Y', f.clinic_name, f.prov_last_name) AS prov_last_name,
f.prov_first_name,
f.prov_mi,
f.clinic_name,
f.prov_group,
f.specialty,
f.sub_specialty,
f.remit_address1,
f.remit_address2,
f.remit_city,
f.remit_state,
f.remit_zip,
f.loc_address1,
f.loc_address2,
f.loc_city,
f.loc_state,
f.loc_zip,
f.loc_county,
f.license_number,
f.license_state,
f.license_issue_date,
f.acn_prov_id,
f.provider_id,
f.office_location_id,
f.main_status,
f.sub_status,
f.status_date,
f.loc_eff_date,
f.loc_term_date,
f.tin_eff_date,
f.tin_term_date,
f.prov_phone,
f.prov_fax,
f.gender,
CASE
WHEN f.client_eff_date < '6/26/2019' THEN
'6/26/2019'
ELSE
f.client_eff_date
END AS [OHCS Eff Date],
f.client_term_date,
f.cred_status_eff_date
FROM rv.hub_npi h
INNER JOIN im_automation.stg_accent_snapshot f
ON h.npi_hk = f.npi_hk
WHERE h.source = 'ACCENT'
AND h.load_date >= CAST(DATEADD(DAY, -1, CURRENT_TIMESTAMP) AS DATE)
UNION ALL
SELECT CAST('New Tin' AS VARCHAR(10)) AS request_Type,
'Add' AS Change_Type,
CASE
WHEN f.main_status = 'M'
AND COALESCE(f.sub_status, '') = '' THEN
'Active'
WHEN f.main_status = 'M'
AND f.sub_status IN ( 'L', 'M' ) THEN
'Active'
WHEN f.main_status = 'N'
AND f.sub_status = 'C'
AND f.specialty IN ( 'PT', 'OT', 'SLP' ) THEN
'Active'
WHEN f.main_status = 'N'
AND f.sub_status = 'C'
AND f.tin_number = ' 850879488' THEN
'Active'
WHEN f.main_status = 'N'
AND f.sub_status IN ( 'R', 'T' ) THEN
'Termed'
WHEN f.main_status = 'D' THEN
'Termed'
ELSE
'' -- blank?
END AS Status,
f.snapshot_date,
f.tin_number,
f.npi,
IIF(f.prov_group = 'Y', f.clinic_name, f.prov_last_name) AS prov_last_name,
f.prov_first_name,
f.prov_mi,
f.clinic_name,
f.prov_group,
f.specialty,
f.sub_specialty,
f.remit_address1,
f.remit_address2,
f.remit_city,
f.remit_state,
f.remit_zip,
f.loc_address1,
f.loc_address2,
f.loc_city,
f.loc_state,
f.loc_zip,
f.loc_county,
f.license_number,
f.license_state,
f.license_issue_date,
f.acn_prov_id,
f.provider_id,
f.office_location_id,
f.main_status,
f.sub_status,
f.status_date,
f.loc_eff_date,
f.loc_term_date,
f.tin_eff_date,
f.tin_term_date,
f.prov_phone,
f.prov_fax,
f.gender,
CASE
WHEN f.client_eff_date < '6/26/2019' THEN
'6/26/2019'
ELSE
f.client_eff_date
END AS [OHCS Eff Date],
f.client_term_date,
f.cred_status_eff_date
FROM rv.hub_tin h
INNER JOIN im_automation.stg_accent_snapshot f
ON f.tin_number = h.tin
WHERE h.source = 'ACCENT'
AND h.load_date >= CAST(DATEADD(DAY, -1, CURRENT_TIMESTAMP) AS DATE)
UNION ALL
SELECT 'New Address' AS request_type,
'Add' AS change_type,
CASE
WHEN f.main_status = 'M'
AND COALESCE(f.sub_status, '') = '' THEN
'Active'
WHEN f.main_status = 'M'
AND f.sub_status IN ( 'L', 'M' ) THEN
'Active'
WHEN f.main_status = 'N'
AND f.sub_status = 'C'
AND f.specialty IN ( 'PT', 'OT', 'SLP' ) THEN
'Active'
WHEN f.main_status = 'N'
AND f.sub_status = 'C'
AND f.tin_number = ' 850879488' THEN
'Active'
WHEN f.main_status = 'N'
AND f.sub_status IN ( 'R', 'T' ) THEN
'Termed'
WHEN f.main_status = 'D' THEN
'Termed'
ELSE
'' -- blank?
END AS Status,
f.snapshot_date,
f.tin_number,
f.npi,
IIF(f.prov_group = 'Y', f.clinic_name, f.prov_last_name) AS prov_last_name,
f.prov_first_name,
f.prov_mi,
f.clinic_name,
f.prov_group,
f.specialty,
f.sub_specialty,
f.remit_address1,
f.remit_address2,
f.remit_city,
f.remit_state,
f.remit_zip,
f.loc_address1,
f.loc_address2,
f.loc_city,
f.loc_state,
f.loc_zip,
f.loc_county,
f.license_number,
f.license_state,
f.license_issue_date,
f.acn_prov_id,
f.provider_id,
f.office_location_id,
f.main_status,
f.sub_status,
f.status_date,
f.loc_eff_date,
f.loc_term_date,
f.tin_eff_date,
f.tin_term_date,
f.prov_phone,
f.prov_fax,
f.gender,
CASE
WHEN f.client_eff_date < '6/26/2019' THEN
'6/26/2019'
ELSE
f.client_eff_date
END AS [OHCS Eff Date],
f.client_term_date,
f.cred_status_eff_date
FROM rv.hub_address h
INNER JOIN im_automation.stg_accent_snapshot f
ON f.service_address_hk = h.address_hk
WHERE h.Source = 'ACCENT'
AND h.load_date >= CAST(DATEADD(DAY, -1, CURRENT_TIMESTAMP) AS DATE)
UNION ALL
SELECT 'New Name' AS request_rype,
'Change' AS change_type,
CASE
WHEN f.main_status = 'M'
AND COALESCE(f.sub_status, '') = '' THEN
'Active'
WHEN f.main_status = 'M'
AND f.sub_status IN ( 'L', 'M' ) THEN
'Active'
WHEN f.main_status = 'N'
AND f.sub_status = 'C'
AND f.specialty IN ( 'PT', 'OT', 'SLP' ) THEN
'Active'
WHEN f.main_status = 'N'
AND f.sub_status = 'C'
AND f.tin_number = ' 850879488' THEN
'Active'
WHEN f.main_status = 'N'
AND f.sub_status IN ( 'R', 'T' ) THEN
'Termed'
WHEN f.main_status = 'D' THEN
'Termed'
ELSE
'' -- blank?
END AS Status,
f.snapshot_date,
f.tin_number,
f.npi,
IIF(f.prov_group = 'Y', f.clinic_name, f.prov_last_name) AS prov_last_name,
f.prov_first_name,
f.prov_mi,
f.clinic_name,
f.prov_group,
f.specialty,
f.sub_specialty,
f.remit_address1,
f.remit_address2,
f.remit_city,
f.remit_state,
f.remit_zip,
f.loc_address1,
f.loc_address2,
f.loc_city,
f.loc_state,
f.loc_zip,
f.loc_county,
f.license_number,
f.license_state,
f.license_issue_date,
f.acn_prov_id,
f.provider_id,
f.office_location_id,
f.main_status,
f.sub_status,
f.status_date,
f.loc_eff_date,
f.loc_term_date,
f.tin_eff_date,
f.tin_term_date,
f.prov_phone,
f.prov_fax,
f.gender,
CASE
WHEN f.client_eff_date < '6/26/2019' THEN
'6/26/2019'
ELSE
f.client_eff_date
END AS [OHCS Eff Date],
f.client_term_date,
f.cred_status_eff_date
FROM rv.hub_npi h
INNER JOIN bv.view_practitioner v
ON v.npi = h.npi
INNER JOIN im_automation.stg_accent_snapshot f
ON f.npi = v.npi
WHERE h.source = 'ACCENT'
AND h.load_date >= CAST(DATEADD(DAY, -1, CURRENT_TIMESTAMP) AS DATE)
AND EXISTS
(
SELECT 1
FROM rv.sat_pdm_practitioner_accent s
WHERE s.npi_hk = v.npi_hk
AND s.load_date < v.load_date
)
UNION ALL
-- Add - Solo
SELECT CAST('New Solo' AS VARCHAR(10)) AS request_Type,
'Add' AS change_type,
CASE
WHEN f.main_status = 'M'
AND COALESCE(f.sub_status, '') = '' THEN
'Active'
WHEN f.main_status = 'M'
AND f.sub_status IN ( 'L', 'M' ) THEN
'Active'
WHEN f.main_status = 'N'
AND f.sub_status = 'C'
AND f.specialty IN ( 'PT', 'OT', 'SLP' ) THEN
'Active'
WHEN f.main_status = 'N'
AND f.sub_status = 'C'
AND f.tin_number = ' 850879488' THEN
'Active'
WHEN f.main_status = 'N'
AND f.sub_status IN ( 'R', 'T' ) THEN
'Termed'
WHEN f.main_status = 'D' THEN
'Termed'
ELSE
'' -- blank?
END AS Status,
f.snapshot_date,
f.tin_number,
f.npi,
IIF(f.prov_group = 'Y', f.clinic_name, f.prov_last_name) AS prov_last_name,
f.prov_first_name,
f.prov_mi,
f.clinic_name,
f.prov_group,
f.specialty,
f.sub_specialty,
f.remit_address1,
f.remit_address2,
f.remit_city,
f.remit_state,
f.remit_zip,
LEFT(f.loc_address1, 30) AS svc_address_line1,
LEFT(f.loc_address2, 30) AS svc_address_line2,
f.loc_city AS svc_city,
f.loc_state AS svc_state,
f.loc_zip AS svc_zip,
f.loc_county AS svc_county,
f.license_number,
f.license_state,
f.license_issue_date,
f.acn_prov_id,
f.provider_id,
f.office_location_id,
f.main_status,
f.sub_status,
f.status_date,
f.loc_eff_date,
f.loc_term_date,
f.tin_eff_date,
f.tin_term_date,
f.prov_phone,
f.prov_fax,
f.gender,
CASE
WHEN f.client_eff_date < '6/26/2019' THEN
'6/26/2019'
ELSE
f.client_eff_date
END AS [OHCS Eff Date],
f.client_term_date,
f.cred_status_eff_date
FROM bv.view_solo_practitioner s
INNER JOIN im_automation.stg_accent_snapshot f
ON f.npi = s.npi
WHERE s.source = 'ACCENT'
AND s.load_date >= CAST(DATEADD(DAY, -1, CURRENT_TIMESTAMP) AS DATE)),
cte2
AS (SELECT cte.Status,
cte.snapshot_date,
CASE
WHEN cte.prov_group = 'Y' THEN
'Group'
WHEN cte.prov_group = 'N'
AND
(
SELECT COUNT(DISTINCT (npi))
FROM im_automation.stg_accent_snapshot a1
WHERE a1.tin_number = cte.tin_number
-- AND a1.snapshot_date >= @load_date
) = 1 THEN
'Solo'
WHEN cte.prov_group = 'N'
AND
(
SELECT COUNT(DISTINCT (npi))
FROM im_automation.stg_accent_snapshot a1
WHERE a1.tin_number = cte.tin_number
-- AND a1.snapshot_date >= @load_date
) > 1 THEN
'Rendering'
END AS ProviderType,
cte.tin_number,
cte.npi,
cte.prov_last_name,
cte.prov_first_name,
cte.prov_mi,
cte.clinic_name,
cte.prov_group,
CASE
WHEN cte.Status = 'Active' THEN
cte.snapshot_date
ELSE
NULL
END AS [OHCS Change Date],
CASE
WHEN cte.Status = 'Termed' THEN
cte.status_date
ELSE
NULL
END AS [OHCS Term Date],
cte.specialty,
cte.sub_specialty,
cte.remit_address1 AS remit_address_line1,
cte.remit_address2 AS remit_address_line2,
cte.remit_city,
cte.remit_state,
cte.remit_zip,
LEFT(cte.loc_address1, 30) AS svc_address_line1,
LEFT(cte.loc_address2, 30) AS svc_address_line2,
cte.loc_city AS svc_city,
cte.loc_state AS svc_state,
cte.loc_zip AS svc_zip,
cte.loc_county AS svc_county,
cte.license_number,
cte.license_state,
cte.license_issue_date,
cte.acn_prov_id,
cte.provider_id,
cte.office_location_id,
cte.main_status,
cte.sub_status,
cte.status_date,
cte.loc_eff_date,
cte.loc_term_date,
cte.tin_eff_date,
cte.tin_term_date,
cte.prov_phone,
cte.prov_fax,
cte.gender,
cte.[OHCS Eff Date],
cte.client_term_date,
cte.cred_status_eff_date,
cte.request_Type,
cte.Change_Type
FROM cte)
SELECT 'Automation OHCS' AS source,
cte.request_Type,
cte.Change_Type,
CAST(CASE
WHEN request_Type IN ( 'Add', 'Change' ) THEN
CASE
WHEN cte.svc_address_line1 LIKE '%at home%'
OR cte.svc_address_line1 LIKE '%in home%'
OR cte.svc_address_line1 LIKE '%PO Box%'
OR cte.svc_address_line1 LIKE '%telehealth%'
OR cte.svc_address_line1 LIKE '%telemental%'
OR cte.svc_address_line1 LIKE '%virtual%' THEN
', Invalid PLSV Address'
ELSE
''
END + CASE
WHEN cte.remit_address_line1 = '' THEN
', No BILL Address'
ELSE
''
END + CASE
WHEN cte.office_location_id = '' THEN
', No MAID'
ELSE
''
END + CASE
WHEN svc_address_line1 = '' THEN
', No PLSV Address'
ELSE
''
END + CASE
WHEN cte.ProviderType NOT IN ( 'Group', 'Solo', 'Rendering' ) THEN
', No ProvType'
ELSE
''
END + CASE
WHEN NOT EXISTS
(
SELECT sp.specialty,
tx.taxonomy
FROM rv.lnk_specialty_taxonomy lnk
INNER JOIN rv.hub_specialty sp
ON lnk.specialty_hk = sp.specialty_hk
INNER JOIN rv.hub_taxonomy tx
ON tx.taxonomy_hk = lnk.taxonomy_hk
WHERE sp.specialty = cte.specialty
) THEN
', No Specialty Match'
ELSE
''
END
END AS VARCHAR(MAX)) AS [Exclusion Reason],
-- 'Professional' AS category,
CAST(cte.ProviderType AS VARCHAR(10)) AS provider_type,
CAST(CASE
WHEN cte.ProviderType = 'Rendering'
AND cte.specialty IN ( 'MT', 'DC', 'LAG' )
AND NOT EXISTS
(
SELECT 1
FROM im_automation.stg_accent_snapshot d1
WHERE d1.prov_group = 'Y'
AND d1.tin_number = cte.tin_number
) THEN
'Yes'
ELSE
'No'
END AS VARCHAR(3)) AS SocialGroup,
CAST(cte.Status AS VARCHAR(10)) AS status,
cte.[OHCS Eff Date] AS effective_date,
CASE
WHEN cte.ProviderType IN ( 'Group, Solo' ) THEN
[cte].[OHCS Eff Date]
WHEN cte.ProviderType = 'Rendering' THEN
(
SELECT TOP (1)
dt
FROM
(
SELECT FIRST_VALUE(cte.[OHCS Eff Date]) OVER (PARTITION BY npi ORDER BY cte.[OHCS Eff Date]) AS dt
FROM cte
UNION
SELECT MIN(f.firstcontracteffectivedate) AS dt
FROM stg.facets_full f
WHERE f.npi = cte.npi
) AS subselect
)
END AS earliest_effective_date,
[cte].[OHCS Change Date] AS change_date,
[OHCS Term Date] AS term_date,
CASE
WHEN Status = 'Termed' THEN
CASE
WHEN ProviderType IN ( 'Group, Solo' ) THEN
[OHCS Term Date]
WHEN ProviderType = 'Rendering' THEN
(
SELECT TOP (1)
dt
FROM
(
SELECT LAST_VALUE([OHCS Term Date]) OVER (PARTITION BY npi ORDER BY [cte].[OHCS Term Date]) AS dt
FROM cte a
WHERE a.npi = cte.npi
UNION ALL
SELECT MAX(hn.load_date)
FROM rv.hub_npi hn
WHERE NOT EXISTS
(
SELECT 1 FROM stg.stg_facets_full f WHERE f.npi_hk = hn.npi_hk
)
AND hn.source = 'FACETS'
) subselect
ORDER BY dt DESC
)
END
END AS latest_term_date,
--CASE
-- WHEN Status = 'Termed' THEN
-- CASE
-- WHEN ProviderType IN ( 'Group, Solo' ) THEN
-- NULL
-- WHEN ProviderType = 'Rendering' THEN
-- (
-- SELECT TOP (1)
-- dt
-- FROM
-- (
-- SELECT LAST_VALUE([OHCS Term Date]) OVER (PARTITION BY npi, tin_number ORDER BY [cte].[OHCS Term Date]) AS dt
-- FROM cte a
-- WHERE a.npi = cte.npi
-- AND a.tin_number = cte.tin_number
-- UNION ALL
-- SELECT MAX(hn.load_date)
-- FROM rv.hub_npi hn
-- WHERE NOT EXISTS
-- (
-- SELECT 1
-- FROM stg.stg_facets_full f
-- WHERE f.npi_hk = hn.npi_hk
-- AND f.primary_contracted_relationship = 'Clinician Only'
-- )
-- AND hn.source = 'FACETS'
-- ) subselect
-- ORDER BY dt DESC
-- )
-- END
--END AS [SG Latest Term Date],
cte.tin_number,
cte.npi,
CAST(cte.provider_id AS VARCHAR(50)) AS provider_id,
CAST(cte.prov_last_name AS VARCHAR(100)) AS provider_last_name,
CAST(cte.prov_first_name AS VARCHAR(30)) AS provider_first_name,
CAST(cte.prov_mi AS VARCHAR(30)) AS provider_middle_initial,
CAST(cte.clinic_name AS VARCHAR(100)) AS clinic_name,
-- cte.prov_group,
CAST(CASE
WHEN cte.ProviderType = 'Group' THEN
'70'
WHEN cte.ProviderType IN ( 'Solo', 'Rendering' ) THEN
tx.T50_specialty
END AS VARCHAR(100)) AS t50_specialty,
CAST(CASE
WHEN cte.ProviderType = 'Group' THEN
'24'
WHEN cte.ProviderType IN ( 'Solo', 'Rendering' ) THEN
tx.T50_provider_type
END AS VARCHAR(100)) AS t50_provider_type,
CAST(CASE
WHEN cte.ProviderType = 'Group' THEN
'193200000X'
WHEN cte.ProviderType IN ( 'Solo', 'Rendering' ) THEN
tx.taxonomy
END AS VARCHAR(10)) AS t15_taxonomy,
CAST(cte.license_number AS VARCHAR(15)) AS license_number,
CAST(cte.license_state AS VARCHAR(50)) AS license_state,
cte.license_issue_date,
CAST(svc_address_line1 AS VARCHAR(50)) AS svc_address_line1,
CAST(svc_address_line2 AS VARCHAR(50)) AS svc_address_line2,
CAST(cte.svc_city AS VARCHAR(30)) AS svc_city,
CAST(cte.svc_state AS VARCHAR(2)) AS svc_state,
CAST(cte.svc_zip AS VARCHAR(9)) AS svc_zip,
-- cte.svc_county,
CAST(cte.remit_address_line1 AS VARCHAR(50)) AS remit_address_line1,
CAST(cte.remit_address_line2 AS VARCHAR(50)) AS remit_address_line2,
CAST(cte.remit_city AS VARCHAR(30)) AS remit_city,
CAST(cte.remit_state AS VARCHAR(2)) AS remit_state,
CAST(cte.remit_zip AS VARCHAR(9)) AS remit_zip,
-- cte.specialty,
CAST(cte.sub_specialty AS VARCHAR(1000)) AS sub_specialty
--cte.acn_prov_id,
--cte.provider_id,
--cte.office_location_id,
--cte.main_status,
--cte.sub_status,
--cte.status_date,
--cte.loc_eff_date,
--cte.loc_term_date,
--cte.tin_eff_date,
--cte.tin_term_date,
--cte.prov_phone,
--cte.prov_fax,
--cte.gender,
--cte.client_term_date,
--cte.cred_status_eff_date
FROM cte2 cte
LEFT JOIN bv.view_specialty_taxonomy tx
ON cte.specialty = tx.specialty
AND tx.source = 'OHCS';
SQL Server | SQL Server Transact-SQL
{count} votes
-
Naomi Nosonovsky 8,451 Reputation points
2025-08-04T23:00:26.3533333+00:00 I've tested the view using select * from cte2 and it works. So the error must be in the final select
-
Naomi Nosonovsky 8,451 Reputation points
2025-08-04T23:17:37.41+00:00 If I comment this code:
CASE WHEN Status = 'Termed' THEN CASE WHEN ProviderType IN ( 'Group, Solo' ) THEN [OHCS Term Date] -- WHEN ProviderType = 'Rendering' THEN -- ( -- SELECT TOP (1) -- dt -- FROM -- ( -- SELECT LAST_VALUE([OHCS Term Date]) OVER (PARTITION BY npi ORDER BY [cte].[OHCS Term Date]) AS dt -- FROM cte a -- WHERE a.npi = cte.npi -- UNION ALL -- SELECT MAX(hn.load_date) -- FROM rv.hub_npi hn -- WHERE NOT EXISTS -- ( -- SELECT 1 FROM stg.stg_facets_full f WHERE f.npi_hk = hn.npi_hk -- ) -- AND hn.source = 'FACETS' -- ) subselect -- ORDER BY dt DESC -- ) END END AS latest_term_date,it also works, so the problem is in this code
-
Erland Sommarskog 124.3K Reputation points MVP Volunteer Moderator2025-08-05T20:40:10.7533333+00:00 on a relatively simple view.
If this is one of your simple views, I don't want to see your complex ones!
Typically, when you run into this, you will need to find a way to break up the query with intermediate temp tables or similar. I note that there are some table sources that are views themselves - and how complex they are, I don't know. But it could make the optimizer choke.
-
Naomi Nosonovsky 8,451 Reputation points
2025-08-05T23:53:30.99+00:00 I've changed the view into a stored procedure and saved results of cte2 into #firstResults temp table, e.g. select * into #FirstResults from cte2. In the rest of the code I used #firstResults table instead of cte2 (still aliased as cte). Now the query works although takes 2 min to generate output.
Sign in to comment
Sign in to answer