Hi All,
How to make this query Sargable and make use of index.Initially, it is doing a full table scan but created a nonclustered idx which helped a little bit but I still see the 2nd expression the WHERE is causing the query to take long time to execute. Please advise.
idx(comm_typ_cd) include (party_id,comm_val)
<?query --
SELECT comm_val
,party_id
,@email_domain domain
INTO #comm
FROM C_B_PARTY_COMM(NOLOCK) comm
WHERE COMM_TYP_CD = 'EMAIL'
and SUBSTRING(comm_val, CHARINDEX('@', comm_val) + 1, LEN(comm_val)) = @email_domain
--?>
Regards,
Sam
November 22, 2023 at 9:28 am
The substring causes the scan, every value hof comm_value need to be looked at and computed.
Options: filtered index (if possible)
Add a new column to C_B_PARTY_COMM and keep it up to date with triggers / computed column
November 22, 2023 at 2:40 pm
Try the following
Add a computed column
ALTER TABLE C_B_PARTY_COMM
ADD emailDoman AS (CASE WHEN COMM_TYP_CD = 'EMAIL' THEN SUBSTRING(comm_val, CHARINDEX('@', comm_val) + 1, LEN(comm_val)) ELSE NULL END) PERSISTED;
Add an index on the computed column. It may help if you have a filtered index (especially if WHERE COMM_TYP_CD = 'EMAIL' is a small portion of the table)
CREATE NONCLUSTERED INDEX IDX_C_B_PARTY_COMM_emailDoman
ON C_B_PARTY_COMM (emailDoman)
INCLUDE (party_id,comm_val)
--WHERE COMM_TYP_CD = 'EMAIL' /* OPTIONAL, but should work well with your query - TEST | TEST | TEST */
November 22, 2023 at 2:44 pm
Thanks a lot Jo Pattyn. I really like the filtered index idea. Other thing struck in my mind is computed column.
However,This is a vendor specific database and we cant change the table structures. Thing is, upgrade scripts will break.
Just to add. the Table has 17 million rows and no triggers defined on this table.
select count(*) from C_B_PARTY_COMM
--17223983
>-- rows that match criteria -- 8 million
select count(*)
FROM C_B_PARTY_COMM
WHERE COMM_TYP_CD = 'EMAIL'
--8047849
Attaching the stored proc which is getting stuck near below code section. I also see a cursor being used.
is there a way to avoid using cursors in this case? if you have any suggestions to tune that stored procedure, please let me know.
I am unable to get the complete estimated plan for the entire stored procedure as it contains some temp tables. is there a way to get the estimated plan by replacing temp tables with some permanent tables just for testing or any other alternate way?
I feel that it is getting into a indefinite loop within that stored procedure. its getting stuck in below SELECT code section and running forever. its taking more than an hour and eventually we are KILLING the spid.
SELECT comm_val
,party_id
,@email_domain domain
INTO #comm
FROM C_B_PARTY_COMM(NOLOCK) comm
WHERE COMM_TYP_CD = 'EMAIL'
and SUBSTRING(comm_val, CHARINDEX('@', comm_val) + 1, LEN(comm_val)) = @email_domain
Any info if you need from my end, pls do let me know.
Regards,
Sam
November 22, 2023 at 2:49 pm
you are likely better off creating a separated list of emails into a temp table, do a split of the domain into its own column and then use it to join to main table - and please do put the SP as attachment - you didn't do it or it didn't get uploaded (you need to use .txt for it to work)
and assuming this SP is anything like you show us before using cursors is a mess (as usual) and normally derived from Oracle devs working with SQL Server
November 22, 2023 at 5:24 pm
I would try a divide & conquer approach first:
CREATE TABLE #email_com_types (comm_val varchar(10), party_id int, domain varchar(255));
SELECT comm_val
, party_id
, domain = SUBSTRING(comm_val, CHARINDEX('@', comm_val) + 1, LEN(comm_val))
INTO #email_com_types
FROM C_B_PARTY_COMM(NOLOCK) comm
WHERE COMM_TYP_CD = 'EMAIL';
--==== Select the matching domain
SELECT *
INTO #comm
FROM #email_com_types ect
WHERE ect.domain = @email_domain;
If needed - add an index on the domain column in the temp table. However - it may not use the index depending on how many rows end up matching. SQL Server will escalate to a table scan when there are more than a certain percentage of rows that will match.
Even if you were able to add a persisted computed column - with an index - SQL Server might not use that index. It all depends on how many rows will match the specified domain.
Since this is a vendor supplied database - an indexed view is also not an option because that would also cause issues with upgrades unless you dropped the indexed view prior to any upgrades and created it after the upgrade.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 22, 2023 at 6:45 pm
Thanks a lot frederico and Jeffrey. It means a lot !!
Felt that I have uploaded the stored procedure code. Uploading it now. I see lot of anti-patterns in this stored proc.
Is there an alternate way to get rid of cursor in this specific problem. That cursor loop is getting hung. Will make the above changes and see how the improvement. Kindly provide any suggestions with the Stored proc code.
Regards,
Sam
November 22, 2023 at 6:51 pm
Stored proc code.
create procedure [dbo].[usp_domain_matching_idl]
as
begin
SET NOCOUNT ON
drop table if exists #domainextract
drop table if exists #party
drop table if exists #customers
drop table if exists #Prospects
drop table if exists #hyphenandsuspects
drop table if exists #countrycd
select top 50 * into #domainextract
from CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN
where process_status = 'NEW'
order by email_domain
SELECT DISTINCT country_cd
INTO #countrycd
FROM C_B_PARTY(NOLOCK)
UPDATE #domainextract
SET domain = NULL
WHERE domain collate database_default IN (
SELECT country_cd
FROM #countrycd
)
UPDATE #domainextract
SET d2 = NULL
WHERE d2 collate database_default IN (
SELECT country_cd
FROM #countrycd
)
UPDATE #domainextract
SET d2 = NULL
WHERE d2 = 'uk'
SELECT PARTY_NM
,P.ROWID_OBJECT
,COUNTRY_CD
,PARTY_TYP_CD
,SURF_ID
INTO #party
FROM C_B_PARTY(NOLOCK) P
INNER JOIN C_B_ACCOUNT(NOLOCK) A ON A.PARTY_ID = P.ROWID_OBJECT
AND IS_MERGED IS NULL
AND surf_id IS NOT NULL
WHERE P.BO_CLASS_CODE = 'Organization'
AND P.ACTV_FLG = '1'
AND P.HUB_STATE_IND = 1
AND SP_END_CUSTOMER IS NULL
AND party_nm NOT LIKE '% - %'
SELECT PARTY_NM
,P.ROWID_OBJECT
,COUNTRY_CD
,PARTY_TYP_CD
,SURF_ID
INTO #customers
FROM #party p
WHERE P.PARTY_TYP_CD IN (
'Customer'
,'Customer - Subsidiary'
,'Customer via Partner'
)
SELECT PARTY_NM
,P.ROWID_OBJECT
,COUNTRY_CD
,PARTY_TYP_CD
,SURF_ID
INTO #prospects
FROM #party p
WHERE P.PARTY_TYP_CD = 'Prospect'
select * into #snawd from SERV_NOW_ACCOUNT_WEBSITE_DOMAIN
CREATE NONCLUSTERED INDEX ix_temp1 ON #customers (party_nm);
CREATE NONCLUSTERED INDEX ix_temp2 ON #prospects (party_nm);
CREATE NONCLUSTERED INDEX ix_temp3 ON #domainExtract (domain,d2,email_domain);
drop table if exists #match
CREATE TABLE #MATCH
(
DOMAIN VARCHAR(100) NULL,
D2 VARCHAR(100) NULL,
EMAIL_DOMAIN VARCHAR(100) NULL,
PARTY_NM VARCHAR(100) NULL,
SURF_id VARCHAR(100) NULL
)
CREATE NONCLUSTERED INDEX ix_temp5 ON #match (SURF_id);
declare @domain as varchar(100)
declare @d2 as varchar(100)
declare @email_domain as varchar(100)
declare @country1 as varchar(100)
declare @country2 as varchar(100)
declare @country3 as varchar(100)
DECLARE db_cursor CURSOR FOR
SELECT distinct domain, d2 , email_domain, country1, country2, country3
FROM #domainExtract
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @domain, @d2 ,@email_domain, @country1, @country2, @country3
WHILE @@FETCH_STATUS = 0
BEGIN
drop table if exists #comm
drop table if exists #rel
drop table if exists #acctcount1
drop table if exists #finalParty
SELECT comm_val
,party_id
,@email_domain domain
INTO #comm
FROM C_B_PARTY_COMM (NOLOCK) comm
WHERE COMM_TYP_CD = 'EMAIL'
and SUBSTRING(comm_val, CHARINDEX('@', comm_val) + 1, LEN(comm_val)) = @email_domain
SELECT CHILD_PARTY_ID
,p.PARTY_NM
,COMM_VAL
,domain
,p.surf_id
INTO #rel
FROM C_B_PARTY_REL(NOLOCK) rel
INNER JOIN #comm c ON rel.PARENT_PARTY_ID = c.PARTY_ID
INNER JOIN #Party P ON p.ROWID_OBJECT = rel.CHILD_PARTY_ID
SELECT DISTINCT domain
,surf_id
,count(comm_val) cnt
INTO #acctcount1
FROM #rel
GROUP BY domain
,surf_id
ORDER BY domain
,count(comm_val) DESC
SELECT domain
,surf_id
INTO #finalParty
FROM (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY domain ORDER BY cnt DESC
) AS rn
FROM #acctcount1
) a
ORDER BY domain
,cnt DESC
Insert iNTO #MATCH
Select distinct @domain, @D2, @email_domain, party_nm, SURF_ID from (
-- website country match
select c.PARTY_NM, c.SURF_ID from #snawd w
inner join #customers c
on c.rowid_object = w.PARTY_ROWID collate database_default
where WEDSITE_DOMAIN = @email_domain
and (w.COUNTRY_CD = ISNULL(@country1,'') OR w.COUNTRY_CD = ISNULL(@country2,'') OR w.COUNTRY_CD = ISNULL(@country3,''))
union
select c.PARTY_NM, c.SURF_ID from #snawd w
inner join #prospects c
on c.rowid_object = w.PARTY_ROWID collate database_default
where WEDSITE_DOMAIN = @email_domain
and (w.COUNTRY_CD = ISNULL(@country1,'') OR w.COUNTRY_CD = ISNULL(@country2,'') OR w.COUNTRY_CD = ISNULL(@country3,''))
-- website match
union
select c.PARTY_NM, c.SURF_ID from #snawd w
inner join #customers c
on c.rowid_object = w.PARTY_ROWID collate database_default
where WEDSITE_DOMAIN = @email_domain
union
select c.PARTY_NM, c.SURF_ID from #snawd w
inner join #prospects c
on c.rowid_object = w.PARTY_ROWID collate database_default
where WEDSITE_DOMAIN = @email_domain
-- Domain Account Match
union
select c.PARTY_NM, c.SURF_ID from #finalParty f
inner join #customers c
on c.surf_id = f.surf_id
union
select c.PARTY_NM, c.SURF_ID from #finalParty f
inner join #prospects c
on c.surf_id = f.surf_id
) a
-- select * from #match
IF((select count(*) from #match )= 0 )
begin
-- select 1
INSERT into #MATCH
select @domain, @D2, @email_domain, * from (
select PARTY_NM , SURF_ID
from #customers where party_nm like @domain + ' %' and @domain <> ''
union
select PARTY_NM, SURF_ID
from #prospects where party_nm like @domain + ' %' and @domain <> ''
union
select PARTY_NM , SURF_ID
from #customers where party_nm like @d2 + ' %' and @d2 <> ''
union
select PARTY_NM, SURF_ID
from #prospects where party_nm like @d2 + ' %' and @d2 <> ''
union
select PARTY_NM , SURF_ID
from #customers P where replace(party_nm,' ','') = @d2 and @d2 <> ''
union
select PARTY_NM, SURF_ID
from #prospects where replace(party_nm,' ','') = @d2 and @d2 <> ''
union
select PARTY_NM , SURF_ID
from #customers P where replace(party_nm,' ','') = @domain and @domain <> ''
union
select PARTY_NM, SURF_ID
from #prospects where replace(party_nm,' ','') = @domain and @domain <> ''
union
select PARTY_NM , SURF_ID
from #customers P where replace(party_nm,' ','') like @d2 + '%' and @d2 <> ''
union
select PARTY_NM , SURF_ID
from #prospects P where replace(party_nm,' ','') like @d2 + '%' and @d2 <> ''
union
select PARTY_NM , SURF_ID
from #customers P where replace(party_nm,' ','') like @domain + '%' and @domain <> ''
union
select PARTY_NM , SURF_ID
from #prospects P where replace(party_nm,' ','') like @domain + '%' and @domain <> ''
) b
end
FETCH NEXT FROM db_cursor INTO @domain, @d2 ,@email_domain , @country1, @country2, @country3
END
CLOSE db_cursor
DEALLOCATE db_cursor
--select * from #match
drop table if exists #acctcount
select p.surf_id, count(parent_party_id) cnt into #acctcount
from #party p
inner join #match m
on m.surf_id = p.surf_id collate database_default
inner join C_B_PARTY_REL (NOLOCK) rel
on rel.child_party_id = p.rowid_object and rel.HUB_STATE_IND = 1
group by p.surf_id
--select * from #acctcount order by cnt desc
drop table if exists #finalacctcnt
SELECT *
,ROW_NUMBER() OVER (
ORDER BY cnt DESC
) AS rn
into #finalacctcnt
FROM #acctcount
--select * from #finalacctcnt
drop table if exists #countrymatchemails
drop table if exists #allemails
select distinct m.party_nm, m.surf_id, party_typ_cd, country_cd,de.domain, de.email_domain, de.d2,de.pkey, rn into #countrymatchemails
from #match m
inner join #party p on p.surf_id = m.surf_id collate database_default
inner join #domainextract de on m.EMAIL_DOMAIN = de.EMAIL_DOMAIN collate database_default
and p.country_cd collate database_default= coalesce (de.country1,de.country2,de.country3)
inner join #finalacctcnt a on a.surf_id = m.surf_id collate database_default
--order by contact_email, rn
select distinct m.party_nm, m.surf_id, party_typ_cd, country_cd,de.domain, de.email_domain, de.d2,de.pkey, rn into #allemails
from #match m
inner join #party p on p.surf_id = m.surf_id collate database_default
inner join #domainextract de on m.EMAIL_DOMAIN = de.EMAIL_DOMAIN collate database_default
-- and p.country_cd collate database_default= coalesce (de.country1,de.country2,de.country3)
inner join #finalacctcnt a on a.surf_id = m.surf_id collate database_default
CREATE NONCLUSTERED INDEX ix_temp6 ON #countrymatchemails (party_typ_cd);
CREATE NONCLUSTERED INDEX ix_temp7 ON #allemails (party_typ_cd);
drop table if exists #finaldata
select * into #finaldata
from (
select *, Rank()
over ( PARTITION BY EMAIL_DOMAIN ORDER BY priority , rn ) AS Rank from (
select * , 1 priority from #countrymatchemails where party_typ_cd <> 'Prospect'
union
select *, 2 priority from #allemails where party_typ_cd <> 'Prospect'
) a
) b where rank <= 10
union
select * from (
select *, Rank()
over (PARTITION BY EMAIL_DOMAIN
ORDER BY priority , rn ) AS Rank from (
select * , 1 priority from #countrymatchemails where party_typ_cd = 'Prospect'
union
select *, 2 priority from #allemails where party_typ_cd = 'Prospect'
) a
) b where rank <= 5
order by email_domain
insert into CONTACT_INTEL_STAGING_DOMAIN_MATCH_ACCTS
(DOMAIN_KEY,PARTY_NM, SURF_ID, PARTY_TYP_CD, COUNTRY_CD, domain,email_domain,d2, priority_order)
select distinct pkey, party_nm, surf_id, party_typ_Cd, country_cd, domain, email_domain, d2, rank from #finaldata
declare @batch_id as int
set @batch_id = (select max(batch_id) +1 from CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN)
Update CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN set batch_id = @batch_id
where pkey in (select pkey from #domainextract)
Update CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN set process_status = 'COMPLETE' where batch_id = @batch_id
end
try and get on your developers mind that SQL Server LIKES to have a clustered table on each table. Ideally a UNIQUE clustered index.
so as a rule of thumb, if you are creating a table and you are adding a index to it create it as CLUSTERED!!!! specially if it is a temp table as is the case on lots of indexes on this proc.
and with regards to temp tables - on top of the clustered index it may also be beneficial to create additional indexes for the cases, as you have here, where a second access to the table is required using a separate column.
also get used to create new columns as required (in temp tables) where you are them going to use them as part of a join/where clause and where you have to "clean up" the column on the fly.
this is the case for example for all the queries you have below where you are removing spaces from party_nm
select PARTY_NM , SURF_ID
from #customers P where replace(party_nm,' ','')
on example above you SHOULD create a new column when adding the rows to #customers where you do that replacement.
and it is also one of the cases where you would add another index to the table.
few comments on the code below tagged with "FRED:" - and on top of them change ALL indexes to be clustered
and most likely all those "collate database default" can be removed in the cases where all tables/columns are from temp tables.
create procedure [dbo].[usp_domain_matching_idl]
as
begin
SET NOCOUNT ON
drop table if exists #domainextract
drop table if exists #party
drop table if exists #customers
drop table if exists #Prospects
drop table if exists #hyphenandsuspects
drop table if exists #countrycd
select top 50 * into #domainextract
from CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN
where process_status = 'NEW'
order by email_domain
SELECT DISTINCT country_cd
INTO #countrycd
FROM C_B_PARTY(NOLOCK)
UPDATE #domainextract
SET domain = NULL
WHERE domain collate database_default IN (
SELECT country_cd
FROM #countrycd
)
UPDATE #domainextract
SET d2 = NULL
WHERE d2 collate database_default IN (
SELECT country_cd
FROM #countrycd
)
UPDATE #domainextract
SET d2 = NULL
WHERE d2 = 'uk'
SELECT PARTY_NM
,P.ROWID_OBJECT
,COUNTRY_CD
,PARTY_TYP_CD
,SURF_ID
INTO #party
FROM C_B_PARTY(NOLOCK) P
INNER JOIN C_B_ACCOUNT(NOLOCK) A ON A.PARTY_ID = P.ROWID_OBJECT
AND IS_MERGED IS NULL
AND surf_id IS NOT NULL
WHERE P.BO_CLASS_CODE = 'Organization'
AND P.ACTV_FLG = '1'
AND P.HUB_STATE_IND = 1
AND SP_END_CUSTOMER IS NULL
AND party_nm NOT LIKE '% - %'
SELECT PARTY_NM
,P.ROWID_OBJECT
,COUNTRY_CD
,PARTY_TYP_CD
,SURF_ID
INTO #customers
FROM #party p
WHERE P.PARTY_TYP_CD IN (
'Customer'
,'Customer - Subsidiary'
,'Customer via Partner'
)
SELECT PARTY_NM
,P.ROWID_OBJECT
,COUNTRY_CD
,PARTY_TYP_CD
,SURF_ID
INTO #prospects
FROM #party p
WHERE P.PARTY_TYP_CD = 'Prospect'
select * into #snawd from SERV_NOW_ACCOUNT_WEBSITE_DOMAIN
-- FRED: MISSING CLUSTERED INDEX on PARTY_ROWID
-- FRED: CHANGE FOLLOWING TO BE CLUSTERED INDEXES
CREATE NONCLUSTERED INDEX ix_temp1 ON #customers (party_nm);
CREATE NONCLUSTERED INDEX ix_temp2 ON #prospects (party_nm);
CREATE NONCLUSTERED INDEX ix_temp3 ON #domainExtract (domain,d2,email_domain);
drop table if exists #match
CREATE TABLE #MATCH
(
DOMAIN VARCHAR(100) NULL,
D2 VARCHAR(100) NULL,
EMAIL_DOMAIN VARCHAR(100) NULL,
PARTY_NM VARCHAR(100) NULL,
SURF_id VARCHAR(100) NULL
)
CREATE NONCLUSTERED INDEX ix_temp5 ON #match (SURF_id);
declare @domain as varchar(100)
declare @d2 as varchar(100)
declare @email_domain as varchar(100)
declare @country1 as varchar(100)
declare @country2 as varchar(100)
declare @country3 as varchar(100)
DECLARE db_cursor CURSOR FOR
SELECT distinct domain, d2 , email_domain, country1, country2, country3
FROM #domainExtract
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @domain, @d2 ,@email_domain, @country1, @country2, @country3
WHILE @@FETCH_STATUS = 0
BEGIN
drop table if exists #comm
drop table if exists #rel
drop table if exists #acctcount1
drop table if exists #finalParty
-- FRED: Create a temp table in advance following advise from Jeffrey above
-- ADD A CLUSTERED INDEX to that table on domain so the query below can do a "WHERE DOMAIN = @email_domain
SELECT comm_val
,party_id
,@email_domain domain
INTO #comm
FROM C_B_PARTY_COMM (NOLOCK) comm
WHERE COMM_TYP_CD = 'EMAIL'
and SUBSTRING(comm_val, CHARINDEX('@', comm_val) + 1, LEN(comm_val)) = @email_domain
SELECT CHILD_PARTY_ID
,p.PARTY_NM
,COMM_VAL
,domain
,p.surf_id
INTO #rel
FROM C_B_PARTY_REL(NOLOCK) rel
INNER JOIN #comm c ON rel.PARENT_PARTY_ID = c.PARTY_ID
INNER JOIN #Party P ON p.ROWID_OBJECT = rel.CHILD_PARTY_ID
-- FRED: table acctcount1 is not required here - it is only doing a distinct of domain and surf_id, and the final insert into #finalParty
-- does NOT use the values of cnt neither the row_number values.
-- change it so it does a insert into final table directly from #rel
SELECT DISTINCT domain
,surf_id
,count(comm_val) cnt
INTO #acctcount1
FROM #rel
GROUP BY domain
,surf_id
ORDER BY domain
,count(comm_val) DESC
SELECT domain
,surf_id
INTO #finalParty
FROM (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY domain ORDER BY cnt DESC
) AS rn
FROM #acctcount1
) a
ORDER BY domain
,cnt DESC
Insert iNTO #MATCH
Select distinct @domain, @D2, @email_domain, party_nm, SURF_ID from (
-- FRED: the first 2 queries (of website country match) are basically a subset of the following 2 queries (for website match)
-- Remove the first 2 as not required and are just slowing down the process
-- if at a later stage the criteria changes and there is a requirement to have both sets
-- AS alias of the columns are missing we have no way to know where WEDSITE_DOMAIN is from (is it table #customers or #snawd ?
-- this likely requires a index on that column - either a clustered index or a index on WEDSITE_DOMAIN and include of some of the other columns
-- website country match
select c.PARTY_NM, c.SURF_ID from #snawd w
inner join #customers c
on c.rowid_object = w.PARTY_ROWID collate database_default
where WEDSITE_DOMAIN = @email_domain
and (w.COUNTRY_CD = ISNULL(@country1,'') OR w.COUNTRY_CD = ISNULL(@country2,'') OR w.COUNTRY_CD = ISNULL(@country3,''))
union
select c.PARTY_NM, c.SURF_ID from #snawd w
inner join #prospects c
on c.rowid_object = w.PARTY_ROWID collate database_default
where WEDSITE_DOMAIN = @email_domain
and (w.COUNTRY_CD = ISNULL(@country1,'') OR w.COUNTRY_CD = ISNULL(@country2,'') OR w.COUNTRY_CD = ISNULL(@country3,''))
-- website match
union
select c.PARTY_NM, c.SURF_ID from #snawd w
inner join #customers c
on c.rowid_object = w.PARTY_ROWID collate database_default
where WEDSITE_DOMAIN = @email_domain
union
select c.PARTY_NM, c.SURF_ID from #snawd w
inner join #prospects c
on c.rowid_object = w.PARTY_ROWID collate database_default
where WEDSITE_DOMAIN = @email_domain
-- Domain Account Match
union
select c.PARTY_NM, c.SURF_ID from #finalParty f
inner join #customers c
on c.surf_id = f.surf_id
union
select c.PARTY_NM, c.SURF_ID from #finalParty f
inner join #prospects c
on c.surf_id = f.surf_id
) a
-- select * from #match
IF((select count(*) from #match )= 0 )
begin
-- select 1
-- FRED: see comments on top regarding replacing the queries below with "replace(party_nm, ' ', '')"
INSERT into #MATCH
select @domain, @D2, @email_domain, * from (
select PARTY_NM , SURF_ID
from #customers where party_nm like @domain + ' %' and @domain <> ''
union
select PARTY_NM, SURF_ID
from #prospects where party_nm like @domain + ' %' and @domain <> ''
union
select PARTY_NM , SURF_ID
from #customers where party_nm like @d2 + ' %' and @d2 <> ''
union
select PARTY_NM, SURF_ID
from #prospects where party_nm like @d2 + ' %' and @d2 <> ''
union
select PARTY_NM , SURF_ID
from #customers P where replace(party_nm,' ','') = @d2 and @d2 <> ''
union
select PARTY_NM, SURF_ID
from #prospects where replace(party_nm,' ','') = @d2 and @d2 <> ''
union
select PARTY_NM , SURF_ID
from #customers P where replace(party_nm,' ','') = @domain and @domain <> ''
union
select PARTY_NM, SURF_ID
from #prospects where replace(party_nm,' ','') = @domain and @domain <> ''
union
select PARTY_NM , SURF_ID
from #customers P where replace(party_nm,' ','') like @d2 + '%' and @d2 <> ''
union
select PARTY_NM , SURF_ID
from #prospects P where replace(party_nm,' ','') like @d2 + '%' and @d2 <> ''
union
select PARTY_NM , SURF_ID
from #customers P where replace(party_nm,' ','') like @domain + '%' and @domain <> ''
union
select PARTY_NM , SURF_ID
from #prospects P where replace(party_nm,' ','') like @domain + '%' and @domain <> ''
) b
end
FETCH NEXT FROM db_cursor INTO @domain, @d2 ,@email_domain , @country1, @country2, @country3
END
CLOSE db_cursor
DEALLOCATE db_cursor
-- FRED: table #match needs a clustered index on surf_id
--select * from #match
drop table if exists #acctcount
select p.surf_id, count(parent_party_id) cnt into #acctcount
from #party p
inner join #match m
on m.surf_id = p.surf_id collate database_default
inner join C_B_PARTY_REL (NOLOCK) rel
on rel.child_party_id = p.rowid_object and rel.HUB_STATE_IND = 1
group by p.surf_id
--select * from #acctcount order by cnt desc
drop table if exists #finalacctcnt
SELECT *
,ROW_NUMBER() OVER (
ORDER BY cnt DESC
) AS rn
into #finalacctcnt
FROM #acctcount
--select * from #finalacctcnt
drop table if exists #countrymatchemails
drop table if exists #allemails
-- FRED: table #finalacctcnt needs a clustered index on surf_id
select distinct m.party_nm, m.surf_id, party_typ_cd, country_cd,de.domain, de.email_domain, de.d2,de.pkey, rn into #countrymatchemails
from #match m
inner join #party p on p.surf_id = m.surf_id collate database_default
inner join #domainextract de on m.EMAIL_DOMAIN = de.EMAIL_DOMAIN collate database_default
and p.country_cd collate database_default= coalesce (de.country1,de.country2,de.country3)
inner join #finalacctcnt a on a.surf_id = m.surf_id collate database_default
--order by contact_email, rn
select distinct m.party_nm, m.surf_id, party_typ_cd, country_cd,de.domain, de.email_domain, de.d2,de.pkey, rn into #allemails
from #match m
inner join #party p on p.surf_id = m.surf_id collate database_default
inner join #domainextract de on m.EMAIL_DOMAIN = de.EMAIL_DOMAIN collate database_default
-- and p.country_cd collate database_default= coalesce (de.country1,de.country2,de.country3)
inner join #finalacctcnt a on a.surf_id = m.surf_id collate database_default
CREATE NONCLUSTERED INDEX ix_temp6 ON #countrymatchemails (party_typ_cd);
CREATE NONCLUSTERED INDEX ix_temp7 ON #allemails (party_typ_cd);
drop table if exists #finaldata
select * into #finaldata
from (
select *, Rank()
over ( PARTITION BY EMAIL_DOMAIN ORDER BY priority , rn ) AS Rank from (
select * , 1 priority from #countrymatchemails where party_typ_cd <> 'Prospect'
union
select *, 2 priority from #allemails where party_typ_cd <> 'Prospect'
) a
) b where rank <= 10
union
select * from (
select *, Rank()
over (PARTITION BY EMAIL_DOMAIN
ORDER BY priority , rn ) AS Rank from (
select * , 1 priority from #countrymatchemails where party_typ_cd = 'Prospect'
union
select *, 2 priority from #allemails where party_typ_cd = 'Prospect'
) a
) b where rank <= 5
order by email_domain
insert into CONTACT_INTEL_STAGING_DOMAIN_MATCH_ACCTS
(DOMAIN_KEY,PARTY_NM, SURF_ID, PARTY_TYP_CD, COUNTRY_CD, domain,email_domain,d2, priority_order)
select distinct pkey, party_nm, surf_id, party_typ_Cd, country_cd, domain, email_domain, d2, rank from #finaldata
declare @batch_id as int
set @batch_id = (select max(batch_id) +1 from CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN)
Update CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN set batch_id = @batch_id
where pkey in (select pkey from #domainextract)
-- FRED: as @batch_id is determined above, this update shold be done as part of the one above -
Update CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN set process_status = 'COMPLETE' where batch_id = @batch_id
end
November 27, 2023 at 8:52 pm
Thanks for your valuable inputs.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply