The following Query is taking too long to execute

indian143

Hi, I am executing the following query, its taking too long to execute, can anybody give me any advice what can I do to improve its performance, because its taking hours of time to execute. Thanks in advance.

SELECT
vw_OwnerXML5010.Owner,
vw_OwnerXML5010.Billing_EIN AS TP_EIN,
vw_OwnerXML5010.TP_Name AS TP_NAME,
vw_OwnerXML5010.County_Code AS County_Code,
(
SELECT
(
SELECT
vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
(
Select
vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
vw_ServicesXML5010.Modifier1 As "Modifier1",
vw_ServicesXML5010.Modifier2 AS "Modifier2",
vw_ServicesXML5010.Modifier3 AS "Modifier3",
vw_ServicesXML5010.Modifier4 AS "Modifier4",
vw_ServicesXML5010.From_Date AS "From_Date",
vw_ServicesXML5010.To_Date AS "To_Date",
vw_ServicesXML5010.Revenue_Code AS "Revenue_Code",
vw_ServicesXML5010.Place_Of_Service AS "Place_Of_Service",
vw_ServicesXML5010.Taxonomy_Code AS "Taxonomy_Code"
FROM
vw_ServicesXML5010
WHERE
vw_ServiceFacilityXML5010.Service_Facility_NPI = vw_ServicesXML5010.Service_Facility_NPI and
vw_ServiceFacilityXML5010.County_Code = vw_ServicesXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_ServicesXML5010.Billing_EIN
FOR XML PATH('Service'), TYPE , ELEMENTS XSINIL
)
FROM
vw_ServiceFacilityXML5010
WHERE
vw_ServiceFacilityXML5010.County_Code = vw_OwnerXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_OwnerXML5010.Billing_EIN
FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE
)
FROM
dbo.vw_SDMCPhaseTwoParentLevelXML vw_OwnerXML5010
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')

Here are the following Views:

CREATE VIEW [dbo].[vw_SDMCPhaseTwoParentLevelXML]
AS
SELECT
DISTINCT 'DMH' AS [Owner]
,'' AS [Billing_NPI]
,SUBSTRING(RTRIM(LE.TaxId), 1, 9) AS [Billing_EIN]
,'' AS DirectIndicator
,CO.PK_Geographic_Location_Code AS [County_Code]
,CO.County_Name AS [TP_Name]
FROM
dbo.LegalEntity LE
INNER JOIN dbo.county CO
ON LE.FKCountyLKPId = CO.PKCountyId
Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
AND LEFT(LegalEntityNbr,3) IN ('000')
AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
GO

CREATE VIEW [dbo].[vw_ServiceFacilityXML5010]
AS
SEL

CHill60

CHill60 wrote:

You might get a better response with some sample data and expected results

This is getting old now. When posting SQL queries and asking "what is wrong" or "how does this not work" or ... well, anything, you should include some sample data for all of the tables used in your query. Nobody wants to use up their spare time (which is how this forum works) traipsing through 200+ lines of code in the off-chance they might spot something that could be improved. Help us to help you.

indian143

Some of the tables in that Query have 15 Columns too, I don't know how can I put all that Data here on the forum itself, it will be more huge message than currently, I am assuming this current message itself is too big. What I am asking is, are there any places that I can be suspicious of taking more time than normal. Any help would be very very helpful buddy. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

Lost User

indian143 wrote:

What I am asking is, are there any places that I can be suspicious of taking more time than normal.

The usual things. Converting varchars to dates is a big timewaster, having magical numbers shows a design-flaw, and every function that is called on each value will slow the thing down fast. 8 Ways to Fine-tune your SQL Queries (for production databases) | Sisense[^]

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.