Need help streamlining view with multiple subquery outside lookups
-
I am in the process of setting up a system to validate customer records that my company is submitting for American Express accounts. Amex requires that certain data in the record be verified prior to submission, including matching area codes for all phone numbers and zip codes for all addresses against the state the customer is located in. In order to perform this validation, I have set up a query which uses multiple lookup tables to check the phone numbers and zip codes. Unfortunately, the validation process takes about 10 minutes to return the data, and ties up our CRM software in the process (the nature of the data requires that the query be run against live data instead of a static mirror). Any suggestions as to how to streamline the processing/reduce the overhead and processing time to run the query? Would it work better if instead of using subqueries, I created separate views that I referenced in the primary view, or would that not make any difference?
The query in question (due to it's complexity, I've inserted a horizontal line in between each field to make it easier to read):
SELECT DISTINCT
PCI.ACCOUNTNO,
dbo.udf_StripPunctuation(UPPER(LEFT(ISNULL(PCI.[Corporate Name], PCI.[DBA Name]), 25))) AS Corp_Name,
CASE WHEN ISNULL(dbo.udf_StripPunctuation(UPPER(LEFT(ISNULL(PCI.[Corporate Name], PCI.[DBA Name]), 25))), '') = '' THEN 'Invalid' ELSE 'Valid' END AS Corp_Name_Valid,
CASE WHEN ISNULL(dbo.udf_StripPunctuation(UPPER(LEFT(ISNULL(PCI.[Corporate Name], PCI.[DBA Name]), 25))), '') = '' THEN 'Corporate name required.' ELSE NULL END AS Corp_Name_Issue,
dbo.udf_StripPunctuation(UPPER(LEFT(PCI.[DBA Name], 25))) AS DBA_Name,
dbo.udf_StripPunctuation(UPPER(LEFT(PCI.[Location Address], 25))) AS Business_Street1,
dbo.udf_StripPunctuation(ISNULL(UPPER(LEFT(PCI.[Location Address 2], 25)), '')) AS Business_Street2,
dbo.udf_StripPunctuation(UPPER(LEFT(PCI.[Location City], 25))) AS Business_City,
dbo.udf_StripPunctuation(UPPER(LEFT(PCI.[Location State], 2))) AS Business_State,
LEFT(PCI.[Location Zip], 5) AS Business_Zip,
CASE WHEN dbo.udf_StripPunctuation(UPPER(LEFT(PCI.[Location State], 2))) =
(SELECT State
FROM (SELECT Zip5, State
FROM COGSWELL.Datamart_Lookups.dbo.[MatchData - Zip-City] ZPC) ZPC
WHERE Zip5 = LEFT(PCI.[Location Zip], 5)) THEN 'Valid' ELSE 'Invalid' END AS Business_Zip_Valid,
CASE WHEN dbo.udf_StripPunctuation(UPPER(LEFT(PCI.[Location State], 2))) =
(SELECT State
FROM (SELECT Zip5, State
FROM COGSWELL.Datamart_Lookups.dbo.[MatchData - Zip-City] ZPC) ZPC
WHERE Zip5 = LEFT(PCI.[Location Zip], 5)) THEN NULL ELSE 'Location Zip does not match state.' END AS Business_Zip_Issue,
LEFT(dbo.udf_ExtractNum(PCI.[Location Phone]), 3) AS Business_AC,
CASE WHEN LEFT(dbo.udf_ExtractNum(PCI.[Location Phone]), 3) IN ('311', '411', '456', '500', '611', '700', '710', '711', '800', '811', '822', '833', '844', '855', '866', '877', '880', '881', '882', '888', '900', '911') THEN 'Valid'
ELSE CASE WHEN dbo.udf_StripPunctuation(UPPER(LEFT(PCI.[Location State], 2))) =
(SELECT State
FROM (SELECT AC, [State Code] AS State
FROM COGSWELL.Datamart_Lookups.dbo.[MatchData - AC-State] ACS) ACS
WHERE AC = LEFT(dbo.udf_ExtractNum(PCI.[Location Phone]), 3)) THEN 'Valid' ELSE 'Invalid' END END AS Business_AC_Valid,
CASE WHEN LEFT(dbo.udf_ExtractNum(PCI.[Location Phone]), 3) IN ('311', '411', '456', '500', '611', '700', '710', '711', '800', '811', '822', '833', '844', '855', '866', '877', '880', '881', '882', '888', '900', '911') THEN 'Multi-State AC'
ELSE CASE WHEN dbo.udf_StripPunctuation(UPPER(LEFT(PCI.[Location State], 2))) =
(SELECT State
FROM (SELECT AC, [State Code] AS State
FROM COGSWELL.Datamart_Lookups.dbo.[MatchData - AC-State] ACS) ACS
WHERE AC = LEFT(dbo.udf_ExtractNum(PCI.[Location Phone]), 3)) THEN NULL ELSE 'Business AC does not match state.' END END AS Business_AC_Issue,
ISNULL(LEFT(dbo.udf_ExtractNum(PCI.[Location Fax]), 3), '') AS Business_Fax_AC,
CASE WHEN ISNULL(LEFT(dbo.udf_ExtractNum(PCI.[Location Fax]), 3), '') IN ('311', '411', '456', '500', '611', '700', '710', '711', '800', '811', '822', '833', '844', '855', '866', '877', '880', '881', '882', '888', '900', '911') THEN 'Valid'
WHEN ISNULL(LEFT(dbo.udf_ExtractNum(PCI.[Location Fax]), 3), '') = '' THEN ''
ELSE CASE WHEN dbo.udf_StripPunctuation(UPPER(LEFT(PCI.[Location State], 2))) =
(SELECT State
FROM (SELECT AC, [State Code] AS State
FROM COGSWELL.Datamart_Lookups.dbo.[MatchData - AC-State] ACS) ACS
WHERE AC = ISNULL(LEFT(dbo.udf_ExtractNum(PCI.[Location Fax]), 3), '')) THEN 'Valid' ELSE 'Invalid' END END AS Business_Fax_AC_Valid,
CASE WHEN ISNULL(LEFT(dbo.udf_ExtractNum(PCI.[Location Fax]), 3), '') IN ('311', '411', '456', '500', '611', '700', '710', '711', '800', '811', '822', '833', '844', '855', '866', '877', '880', '881', '882', '888', '900', '911') THEN 'Multi-State AC'
WHEN ISNULL(LEFT(dbo.udf_ExtractNum(PCI.[Location Fax]), 3), '') = '' THEN ''
ELSE CASE WHEN dbo.udf_StripPunctuation(UPPER(LEFT(PCI.[Location State], 2))) =
(SELECT State
FROM (SELECT AC, [State Code] AS State
FROM COGSWELL.Datamart_Lookups.dbo.[MatchData - AC-State] ACS) ACS
WHERE AC = ISNULL(LEFT(dbo.udf_ExtractNum(PCI.[Location Fax]), 3), '')) THEN NULL ELSE 'Business Fax AC does not match state.' END END AS Business_Fax_AC_Issue,
CASE WHEN ISNULL(LEFT(PCI.SIC, 4), '0000') = '' THEN '0000' ELSE ISNULL(LEFT(PCI.SIC, 4), '0000') END AS MCC,
CASE WHEN MCC_CODES.MCC IS NULL THEN 'Invalid' ELSE 'Valid' END AS MCC_Valid,
CASE WHEN MCC_CODES.MCC IS NULL THEN 'Invalid MCC Code' ELSE NULL END AS MCC_Issue,
CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] AND CONVERT(int, ISNULL(AMX_MCC.Annual_Vol, CONVERT(money, PCI.[Monthly Volume]) * 12 * 0.20)) < 5000 THEN 'Y' ELSE 'N' END AS Home_Based,
ISNULL(UPPER(LEFT(EMCI.[E-mail Address], 254)), '') AS Email_Address,
ISNULL(UPPER(LEFT(WSCI.[Web Site], 254)), '') AS URL_Address,
dbo.udf_ExtractNum(PCI.[Owner 1 SSN]) AS Signer_SSN,
CASE WHEN LEN(dbo.udf_ExtractNum(PCI.[Owner 1 SSN])) = 9 THEN 'Valid' ELSE 'Invalid' END AS Signer_SSN_Valid,
CASE WHEN LEN(dbo.udf_ExtractNum(PCI.[Owner 1 SSN])) = 9 THEN NULL ELSE 'Valid Owner SSN Required' END AS Signer_SSN_Issue,
dbo.udf_StripPunctuation(UPPER(LEFT(CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN '' ELSE PCI.[Owner 1 Home Address 1] END, 25))) AS Home_Address,
dbo.udf_StripPunctuation(UPPER(LEFT(CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN '' ELSE PCI.[Owner 1 Home City] END, 25))) AS Home_City,
dbo.udf_StripPunctuation(UPPER(LEFT(CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN '' ELSE PCI.[Owner 1 Home State] END, 2))) AS Home_State,
LEFT(CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN '' ELSE PCI.[Owner 1 Home Zip] END, 5) AS Home_Zip,
CASE WHEN dbo.udf_StripPunctuation(UPPER(LEFT(CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN PCI.[Location State] ELSE PCI.[Owner 1 Home State] END, 2))) =
(SELECT State
FROM (SELECT Zip5, State
FROM COGSWELL.Datamart_Lookups.dbo.[MatchData - Zip-City] ZPC) ZPC
WHERE Zip5 = LEFT(CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN PCI.[Location Zip] ELSE PCI.[Owner 1 Home Zip] END, 5)) THEN 'Valid' ELSE 'Invalid' END AS Home_Zip_Valid,
CASE WHEN dbo.udf_StripPunctuation(UPPER(LEFT(CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN PCI.[Location State] ELSE PCI.[Owner 1 Home State] END, 2))) =
(SELECT State
FROM (SELECT Zip5, State
FROM COGSWELL.Datamart_Lookups.dbo.[MatchData - Zip-City] ZPC) ZPC
WHERE Zip5 = LEFT(CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN PCI.[Location Zip] ELSE PCI.[Owner 1 Home Zip] END, 5)) THEN NULL ELSE 'Home Zip does not match state.' END AS Home_Zip_Issue,
CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN '' ELSE LEFT(dbo.udf_ExtractNum(PCI.[Owner 1 Home Phone]), 3) END AS Home_AC,
CASE WHEN CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN '' ELSE LEFT(dbo.udf_ExtractNum(PCI.[Owner 1 Home Phone]), 3) END IN ('311', '411', '456', '500', '611', '700', '710', '711', '800', '811', '822', '833', '844', '855', '866', '877', '880', '881', '882', '888', '900', '911') THEN 'Valid'
WHEN CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN '' ELSE LEFT(dbo.udf_ExtractNum(PCI.[Owner 1 Home Phone]), 3) END = '' THEN ''
ELSE CASE WHEN dbo.udf_StripPunctuation(UPPER(LEFT(PCI.[Owner 1 Home State], 2))) =
(SELECT State
FROM (SELECT AC, [State Code] AS State
FROM COGSWELL.Datamart_Lookups.dbo.[MatchData - AC-State] ACS) ACS
WHERE AC = CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN '' ELSE LEFT(dbo.udf_ExtractNum(PCI.[Owner 1 Home Phone]), 3) END) THEN 'Valid' ELSE 'Invalid' END END AS Home_AC_Valid,
CASE WHEN CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN '' ELSE LEFT(dbo.udf_ExtractNum(PCI.[Owner 1 Home Phone]), 3) END IN ('311', '411', '456', '500', '611', '700', '710', '711', '800', '811', '822', '833', '844', '855', '866', '877', '880', '881', '882', '888', '900', '911') THEN 'Multi-State AC'
WHEN CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN '' ELSE LEFT(dbo.udf_ExtractNum(PCI.[Owner 1 Home Phone]), 3) END = '' THEN ''
ELSE CASE WHEN dbo.udf_StripPunctuation(UPPER(LEFT(PCI.[Owner 1 Home State], 2))) =
(SELECT State
FROM (SELECT AC, [State Code] AS State
FROM COGSWELL.Datamart_Lookups.dbo.[MatchData - AC-State] ACS) ACS
WHERE AC = CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN '' ELSE LEFT(dbo.udf_ExtractNum(PCI.[Owner 1 Home Phone]), 3) END) THEN NULL ELSE 'Home AC does not match state.' END END AS Home_AC_Issue,
CASE WHEN ISNULL(dbo.udf_ExtractNum(PCI.[Corporate Tax ID #]), '') = '' THEN dbo.udf_ExtractNum(PCI.[Owner 1 SSN]) ELSE dbo.udf_ExtractNum(PCI.[Corporate Tax ID #]) END AS Fed_Tax_ID,
CASE WHEN CONVERT(int, ISNULL(AMX_MCC.Annual_Vol, CONVERT(money, PCI.[Monthly Volume]) * 12 * 0.20)) < 5000 THEN '01' ELSE ISNULL(AFFILIATED_AMX.Pricing_Code, '00') END AS Pricing_Code,
CASE WHEN ISNULL(AFFILIATED_AMX.Disc_Rate, '') <> '' THEN AFFILIATED_AMX.Disc_Rate
ELSE '0' + CASE WHEN PCI.[Location Address] = PCI.[Owner 1 Home Address 1] THEN CASE WHEN ISNULL(AMX_MCC.Discount_Rate, '') <> '' THEN CONVERT(VARCHAR(4), AMX_MCC.Discount_Rate) ELSE '350' END
WHEN CONVERT(int, ISNULL(AMX_MCC.Annual_Vol, CONVERT(money, PCI.[Monthly Volume]) * 12 * 0.20)) < 5000 THEN CASE WHEN ISNULL(AMX_MCC.Discount_Rate, '') <> '' THEN CONVERT(VARCHAR(4), AMX_MCC.Discount_Rate) ELSE '350' END ELSE CASE WHEN ISNULL(AMX_MCC.Discount_Rate, '') <> '' THEN CONVERT(VARCHAR(4), AMX_MCC.Discount_Rate) ELSE '350' END END END AS Discount_Rate
FROM dbo.[V_DM GM_Sales Primary Contact Info] PCI INNER JOIN
(SELECT ACCOUNTNO, Processor, [Service Type], [MID #], [Disc Rate], [Txn Fee], [Application/Setup Fee], RECID
FROM [V_DM GM_Sales Service Detail]
WHERE ([Service Type] LIKE 'AMX%') AND ([MID #] IS NULL) OR
([Service Type] LIKE 'AMX%') AND (LEN([MID #]) = 0)) AMX_INFO ON
PCI.ACCOUNTNO = AMX_INFO.ACCOUNTNO COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN
dbo.CAL ON PCI.ACCOUNTNO = dbo.CAL.ACCOUNTNO INNER JOIN
dbo.CONTHIST ON PCI.ACCOUNTNO = dbo.CONTHIST.ACCOUNTNO LEFT OUTER JOIN
(SELECT DISTINCT MCC, Description, [Volume Estimator], [Merchant Type]
FROM COGSWELL.Datamart_Lookups.dbo.AMX_MCC_CODES TABLE1) MCC_CODES ON LEFT(PCI.SIC, 4) = MCC_CODES.MCC LEFT OUTER JOIN
(SELECT PCI.ACCOUNTNO, AMX_MCC.MCC, PCI.[Monthly Volume] * 12 * AMX_MCC.[Volume Estimator] AS Annual_Vol, CASE WHEN CONVERT(int,
ISNULL(PCI.[Avg Tx], 1)) < 150 THEN AMX_MCC.[Rate 1] WHEN CONVERT(int, ISNULL(PCI.[Avg Tx], 1)) BETWEEN 150 AND
249 THEN AMX_MCC.[Rate 2] WHEN CONVERT(int, ISNULL(PCI.[Avg Tx], 1)) BETWEEN 250 AND
499 THEN AMX_MCC.[Rate 3] WHEN CONVERT(int, ISNULL(PCI.[Avg Tx], 1)) BETWEEN 500 AND
1499 THEN AMX_MCC.[Rate 4] WHEN CONVERT(int, ISNULL(PCI.[Avg Tx], 1)) BETWEEN 1500 AND
2999 THEN AMX_MCC.[Rate 5] WHEN CONVERT(int, ISNULL(PCI.[Avg Tx], 1))
>= 3000 THEN AMX_MCC.[Rate 6] ELSE AMX_MCC.[Rate 1] END AS Discount_Rate
FROM [V_DM GM_Sales Primary Contact Info] PCI INNER JOIN
COGSWELL.Datamart_Lookups.dbo.AMX_MCC_CODES AMX_MCC ON LEFT(PCI.SIC, 4) = AMX_MCC.MCC
WHERE (PCI.[Monthly Volume] * 12 * AMX_MCC.[Volume Estimator] BETWEEN AMX_MCC.[Min Volume] AND AMX_MCC.[Max Volume]))
AMX_MCC ON PCI.ACCOUNTNO = AMX_MCC.ACCOUNTNO COLLATE SQL_Latin1_General_CP1_CI_AS LEFT OUTER JOIN
(SELECT [V_DM GM_Sales Service Detail].[MID #], RIGHT([V_DM GM_Sales Service Detail].[MID #], 4) AS StoreID,
[V_DM GM_Sales Service Detail].ACCOUNTNO, CASE WHEN RIGHT([V_DM GM_Sales Service Detail].[MID #], 4)
= '0001' THEN 'C' ELSE 'S' END AS Hierarchy_Code, CASE WHEN RIGHT([V_DM GM_Sales Service Detail].[MID #], 4)
<> '0001' THEN 'Y' ELSE 'N' END AS Affiliation_Indicator, CASE WHEN RIGHT([V_DM GM_Sales Service Detail].[MID #], 4)
<> '0001' THEN ISNULL(AMX_CORP_CAP.Company_Owned_CAP, '') ELSE '' END AS Company_Owned_CAP,
CASE WHEN RIGHT([V_DM GM_Sales Service Detail].[MID #], 4)
<> '0001' THEN CASE WHEN ISNULL(AMX_CORP_CAP.Company_Owned_CAP, '')
= '' THEN Affiliated_Order_Num ELSE '' END ELSE '' END AS Affiliated_Order_Num,
CASE WHEN RIGHT([V_DM GM_Sales Service Detail].[MID #], 4) <> '0001' THEN '01' ELSE '' END AS Affiliation_Ref_Code,
CASE WHEN RIGHT([V_DM GM_Sales Service Detail].[MID #], 4) <> '0001' THEN '00' ELSE ' ' END AS Pricing_Code,
CASE WHEN RIGHT([V_DM GM_Sales Service Detail].[MID #], 4) <> '0001' THEN Disc_Rate ELSE NULL END AS Disc_Rate
FROM [V_DM GM_Sales Service Detail] WITH (NOLOCK) INNER JOIN
(SELECT LEFT([MID #], 7) AS MerchID
FROM [V_DM GM_Sales Service Detail] WITH (NOLOCK)
WHERE (Processor LIKE 'Concord%') AND ([Service Type] = 'MC / VSA') AND (isnumeric([MID #]) = 1)
GROUP BY LEFT([MID #], 7)
HAVING (COUNT(ACCOUNTNO) > 1)) MULTI_MID ON LEFT([V_DM GM_Sales Service Detail].[MID #], 7)
= MULTI_MID.MerchID COLLATE SQL_Latin1_General_CP1_CI_AS LEFT OUTER JOIN
(SELECT PRIMARY_ACCT.Merch_ID AS Linking_MID, AMX_MID.[MID #] AS Company_Owned_CAP,
CASE Disc_Rate WHEN '0000' THEN NULL ELSE Disc_Rate END AS Disc_Rate,
PRIMARY_ACCT.Order_Num AS Affiliated_Order_Num
FROM (SELECT ACCOUNTNO, [MID #], LEFT('0' + dbo.udf_ExtractNum([Disc Rate]) + '00000', 4) AS Disc_Rate
FROM [V_DM GM_Sales Service Detail] WITH (NOLOCK)
WHERE (isnumeric([MID #]) = 1) AND ([Service Type] = 'AMX') AND (Processor LIKE 'Concord%') AND ([MID #] IS NOT NULL)
AND ([MID #] <> '')) AMX_MID RIGHT OUTER JOIN
(SELECT [V_DM GM_Sales Service Detail].ACCOUNTNO, LEFT([V_DM GM_Sales Service Detail].[MID #], 7) AS Merch_ID,
Order_Num.Order_Num
FROM [V_DM GM_Sales Service Detail] WITH (NOLOCK) LEFT OUTER JOIN
(SELECT AMEX_ESA_EXPORT.ACCOUNTNO, AMEX_ESA_EXPORT.Order_Num
FROM AMEX_ESA_EXPORT INNER JOIN
(SELECT ACCOUNTNO, MAX(RecID) AS LastOrder
FROM AMEX_ESA_EXPORT
GROUP BY ACCOUNTNO) LastOrder ON AMEX_ESA_EXPORT.RecID = LastOrder.LastOrder AND
AMEX_ESA_EXPORT.ACCOUNTNO = LastOrder.ACCOUNTNO COLLATE SQL_Latin1_General_CP1_CI_AS)
Order_Num ON
[V_DM GM_Sales Service Detail].ACCOUNTNO = Order_Num.ACCOUNTNO COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE ([V_DM GM_Sales Service Detail].[Service Type] = 'MC / VSA') AND
([V_DM GM_Sales Service Detail].Processor LIKE 'Concord%') AND
(RIGHT([V_DM GM_Sales Service Detail].[MID #], 4) = '0001')) PRIMARY_ACCT ON
AMX_MID.ACCOUNTNO = PRIMARY_ACCT.ACCOUNTNO) AMX_CORP_CAP ON
MULTI_MID.MerchID = AMX_CORP_CAP.Linking_MID
WHERE ([V_DM GM_Sales Service Detail].Processor LIKE 'Concord%') AND ([V_DM GM_Sales Service Detail].[Service Type] = 'MC / VSA'))
AFFILIATED_AMX ON PCI.ACCOUNTNO = AFFILIATED_AMX.ACCOUNTNO COLLATE SQL_Latin1_General_CP1_CI_AS LEFT OUTER JOIN
dbo.[V_DM GM_Sales Email Contact Info] EMCI ON PCI.ACCOUNTNO = EMCI.ACCOUNTNO LEFT OUTER JOIN
dbo.[V_DM GM_Sales Web Site Contact Info] WSCI ON PCI.ACCOUNTNO = WSCI.ACCOUNTNO LEFT OUTER JOIN
(SELECT ACCOUNTNO, Processor, [Service Type], [MID #], [Disc Rate], [Txn Fee], [Application/Setup Fee]
FROM [V_DM GM_Sales Service Detail]
WHERE ([Service Type] LIKE 'MC / VSA%') AND ([MID #] IS NOT NULL) AND ISNUMERIC([MID #]) = 1) VISA_MC_INFO ON
PCI.ACCOUNTNO = VISA_MC_INFO.ACCOUNTNO COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE (dbo.CAL.REF LIKE 'Activate Amex Card%') AND (dbo.CONTHIST.SRECTYPE = 'T') AND (dbo.CONTHIST.ACTVCODE = 'UND') AND
(dbo.CONTHIST.REF LIKE 'Visa/MC MID#%') AND (dbo.CONTHIST.RESULTCODE = 'MRV') AND (AMX_INFO.[MID #] IS NULL OR
AMX_INFO.[MID #] <> 'Submitted') OR
(dbo.CAL.REF LIKE 'Resubmit Amex Card%') AND (dbo.CONTHIST.SRECTYPE = 'T') AND (dbo.CONTHIST.ACTVCODE = 'UND') AND
(dbo.CONTHIST.REF LIKE 'Visa/MC MID#%') AND (dbo.CONTHIST.RESULTCODE = 'MRV') AND (AMX_INFO.[MID #] = 'Submitted') OR
(dbo.CAL.REF LIKE 'Activate Amex Card%') AND (dbo.CONTHIST.SRECTYPE = 'T') AND (dbo.CONTHIST.ACTVCODE = 'UWI') AND
(dbo.CONTHIST.RESULTCODE = 'MRV') AND (AMX_INFO.[MID #] IS NULL OR
AMX_INFO.[MID #] <> 'Submitted') OR
(dbo.CAL.REF LIKE 'Resubmit Amex Card%') AND (dbo.CONTHIST.SRECTYPE = 'T') AND (dbo.CONTHIST.ACTVCODE = 'UWI') AND
(dbo.CONTHIST.RESULTCODE = 'MRV') AND (AMX_INFO.[MID #] = 'Submitted') OR
(dbo.CAL.REF LIKE 'Amex MID%') AND (dbo.CONTHIST.SRECTYPE = 'T') AND (dbo.CONTHIST.ACTVCODE = 'UND') AND
(dbo.CONTHIST.REF LIKE 'Visa/MC MID#%') AND (dbo.CONTHIST.RESULTCODE = 'MRV') AND (AMX_INFO.[MID #] IS NULL OR
AMX_INFO.[MID #] <> 'Submitted')
-
I barely know where to start.
A couple of things.
How many rows are in [V_DM GM_Sales Primary Contact Info]?
Is your data, such as phone numbers, truly messed up enough that you can't simply take the left three characters to get the area code? If that above view is large, those deeply nested udf calls are killing you.
I hope your connection to the COGSWELL linked server is insanely fast, as if it's not, that's hurting you as well. Any chance of pulling those lookup tables across temporarily, and seeing how much of an impact that has?
There are a whole lot of other things, but without a ton more info, there is no way for me to know if they are possible to change. A quite example is where you have the "LIKE" statements in your WHERE clause, comparing to things like CAL.REF starting wtih "Activate Amex Card%". If that is a status of some sort, and if there is an ID for it, it can probably be optimized quite a bit. As I said, we don't know your data, so not questioning the skills of whoever wrote this.
-
>>How many rows are in [V_DM GM_Sales Primary Contact Info]? <<
About 47,000 records.
>>Is your data, such as phone numbers, truly messed up enough that you can't simply take the left three characters to get the area code? If that above view is large, those deeply nested udf calls are killing you.<<
Most of the phone numbers in the database are formatted as (###)###-####. However, enough of them have been entered incorrectly (too many digits, not enough digits, hard-coded formatting by inexperienced employees, etc.) that I have to run them through the udf to clean them up and not cause errors.
>>I hope your connection to the COGSWELL linked server is insanely fast, as if it's not, that's hurting you as well. <<
The servers are connected together via a 1GB switch.
>>Any chance of pulling those lookup tables across temporarily, and seeing how much of an impact that has?<<
I'll give it a try - it's certainly a possibility.
>>There are a whole lot of other things, but without a ton more info, there is no way for me to know if they are possible to change. A quite example is where you have the "LIKE" statements in your WHERE clause, comparing to things like CAL.REF starting wtih "Activate Amex Card%". If that is a status of some sort, and if there is an ID for it, it can probably be optimized quite a bit.<<
The text I'm looking for with the LIKE statements represent pending or completed actions in our CRM system. Unfortunately, they don't have ID's that I can use, and can have varying endings, depending on the customer account they were completed or pending for; thus the use of LIKE instead of = in the WHERE clause.
>> As I said, we don't know your data, so not questioning the skills of whoever wrote this.<<
No offense taken - I'm looking for help, not looking to jump on people for imagined slights <G>.
-
The table size is smaller than I was expecting, so that's a good thing. I still think that turning those lookups into joins with local tables will help. For example, if you do an OUTER JOIN on state, you can use Isnull(<LookupStateColumn>,'Message for invalid state here'. Those sorts of things should speed it up quite a bit.
Does your udf that gets the area code do anything other than strip off the parenthesis and then grab 3 numbers? I'm asking if there is a chance that it can be handled with a REPLACE function, or multiple REPLACE functions.
It definitely sounds like you've got your work cut out for you due to lax constraints within the CRM app, and I certainly know how it feels to have to "dance with the one who brung ya." I'd still look for any areas where a udf can be replaced with built-in SQL functions, and attempt to eliminate as many of those subqueries (especially the ones where you have a subquery calling a derived table which uses a udf) as possible, either through joins, or possibly temp tables built upfront.
I'd take baby steps, comparing results of each change, as well as execution time. You might want to clear your caches each time when you do so, so that you don't get false positive results as you make changes.
-
Simply moving the lookup tables to the local server (but keeping the code otherwise the same) made a significant impact - query time was reduced from 8:56 to 2:39 (over a 70% reduction!).
How would you suggest setting up the lookups as joins? Have multiple aliases for each table, each of which would be an outer join on a specific field? It seems to me (and I could be wrong) that doing that would increase the complexity of the view further, and cause it to take longer. Am I missing something?
As far as the udf's go, I'm not sure what, if anything, I can do with them - they do the following (which I need to clean the data):
udf_StripPunctuation - removes any non alphanumeric characters from the string - needed to put names and addresses with periods, commas, ampersands, slashes, and anything else our data entry people choose to use into a format that Amex will accept;
udf_ExtractNum - removes any non-numeric characters from the string - needed to normalize phone numbers from varying methods of data entry.
-
Glad to hear that localizing the lookup tables helped.
As for the lookups as joins, this is an intelligent guess at best, without knowing or having access to everything. As an example, the column you create with:
CASE WHEN LEFT(dbo.udf_ExtractNum(PCI.[Location Phone]), 3) IN ('311', '411', '456', '500', '611', '700', '710', '711', '800', '811', '822', '833', '844', '855', '866', '877', '880', '881', '882', '888', '900', '911') THEN 'Multi-State AC'
ELSE CASE WHEN dbo.udf_StripPunctuation(UPPER(LEFT(PCI.[Location State], 2))) =
(SELECT State
FROM (SELECT AC, [State Code] AS State
FROM COGSWELL.Datamart_Lookups.dbo.[MatchData - AC-State] ACS) ACS
WHERE AC = LEFT(dbo.udf_ExtractNum(PCI.[Location Phone]), 3)) THEN NULL ELSE 'Business AC does not match state.' END END AS Business_AC_Issue,
...could be replaced by doing a left outer join on AC and [State Code] to the state lookup table (I call it "ls" for this example), and then using this code:
CASE
WHEN LEFT(dbo.udf_ExtractNum(PCI.[Location Phone]), 3) IN ('311', '411', '456', '500', '611', '700', '710', '711', '800', '811', '822', '833', '844', '855', '866', '877', '880', '881', '882', '888', '900', '911')
THEN
'Multi-State AC'
ELSE
CASE
WHEN dbo.udf_StripPunctuation(UPPER(LEFT(PCI.[Location State], 2))) = ls.[State_Code]
THEN
NULL
ELSE
'Business AC does not match state.'
END
END AS Business_AC_Issue,
That should in theory run much faster, but again, test it and make sure that it not only is faster, but that it returns identical results.
Regarding the udfs, whether you can replace them or not is one of those "It depends" answers. If there are a finite number of of potential non-numeric characters that you'll encounter (and you know your data better than I), there might be options. If it turns out, for instance, that the only things that show up in a phone number are parentheses, dashes, "x", "ext", and ".", for instance, you could write:
Replace( Replace( Replace( Replace( Replace( Replace( State, '(', '' ), ')', '' ), '-', '' ), 'x', '' ), 'ext', '' ), '.', '' )
While that looks strange, it works like a charm, and should outperform any udf that does the same thing. Again, it depends on your data as to whether that would be sufficient to cover all of your bases.
It sounds like you are making progress as it is, so if once you reach the point that you are sastified with performance, you'll find that you get diminishing returns from further optimization. Only you can know where that line is drawn.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply