SQL Query Optimization

The query below takes a very long time to execute in production, and I need help optimizing it.

I've also attached the execution plan, and the statistics io and time are below from the test SQL DB (same as production except fewer records):

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Portfolio'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(25 row(s) affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 20403, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NonCashTransactionType'. Scan count 0, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Transaction'. Scan count 31, logical reads 863, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'customfielddata'. Scan count 35, logical reads 70, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContactInfo'. Scan count 4, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Address'. Scan count 2, logical reads 2808, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CustomerAccount'. Scan count 1, logical reads 1757, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SecondaryAccountStatus'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PrimaryAccountStatus'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Entity'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#B0B105DC'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Disbursement'. Scan count 2, logical reads 872, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 687 ms,  elapsed time = 1774 ms.

Open in new window


declare @ReleaseDate DateTime = '3/11/2015'    
declare @PartyID varchar(max) = null
declare @GroupBy varchar(100) = ''     
declare @OrderBy varchar(100) = ''    
   
  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED        
  
declare @portfolioCode varchar(100)        
select @portfolioCode = code from Portfolio         

-- seach start with table        
;with StartWith as          
(        
select 'Unit'data        
union        
select 'Suite' data        
union        
select 'Ste' data        
union        
select '#' data        
union        
select 'Floor' data        
union        
select 'FL' data        
union        
select 'Room' data        
union        
select 'RM' data        
union        
select 'Building' data        
union        
select 'BLDG' data        
union        
select 'Apartment' data        
union        
select 'APT' data        
)        


select 
	@portfolioCode + '-' +cast(ca.AccountNumber as varchar(10)) as PAccountNumber,
	ca.Name1 as PName1,        
	'Agent: ' + ca.Code + ' - ' + ca.AgentName as Agent, 
	ca.RealAddress1 as PAddress1,  
	case when ca.BankruptcyStatusTypeId is not null then 'T'         
		 else case when ca.NDIP = 'True' then 'T'         
			       else ''         
			  end         
	end as PBKAccount,        
	ca.AlternateAccountNumber as PAltAccountNumber,
	ca.Amount,        
	-- secondary info        
	@portfolioCode + '-' + +cast(matched.AccountNumber as varchar(10)) as SAccountNumber,         
	case when matched.BankruptcyStatusTypeId is not null then 'T'         
		 else case when matched.NDIP = 'True' then 'T'         
				   else ''         
			  end         
	end as SBKAccount,        
	matched.Name1 as SName1,
	matched.Address1 as SAddress1,
	matched.AlternateAccountNumber as SAltAccountNumber,        
	matched.NetWriteOffAmount,
	matched.PrimaryStatus,
	matched.SecondStatus,
	matched.TotalUncollectedFees,
	matched.PIBalance      
  
from         
--------------------------------------- primary         
(
	select 
		a.AccountNumber,
		a.OldCustomerAccountNumber, 
		a.Name1,
		a.Name2,        
		a.GoverningRegionId,
		a.PartyId,
		ds.Amount,        
		case when a1.data is null then b.Address1 else null end as Address1 ,        
		case when a2.data is null then b.Address2 else null end as Address2,        
		MP.ContactInfo as MainPhone,
		d.ContactInfo as Email,
		e.Data as USPSAddressCode,        
		f.Data as NDIP,
		a.BankruptcyStatusTypeId,
		b.Address1 as RealAddress1,        
		a.AlternateAccountNumber,
		p.Name1 as AgentName,
		p.Code        
	from 
		CustomerAccount a
		join (	select addr.PartyId, addr.Address1, addr.Address2, ROW_NUMBER() over (partition by addr.PartyId order by addr.AddressId) as seq
				from [Address] addr 
				where addr.IsMailing = 1
			 ) b on a.PartyId = b.PartyId and b.seq = 1   
		--join [Address] b on a.PartyId = b.PartyId and b.IsMailing = 1       
		join entity p on a.AgentId = p.PartyId        
		join Disbursement ds on a.PartyID = ds.Partyid        
  
		left join StartWith a1 on SUBSTRING(b.Address1,1,LEN(a1.data)) = a1.data         
		left join StartWith a2 on SUBSTRING(b.Address2,1,LEN(a2.data)) = a2.data        
		left join ContactInfo MP on a.PartyId = MP.PartyId and MP.ContactInfoTypeId = 1 -- MainPhone        
		left join ContactInfo d on a.PartyId = d.PartyId and d.ContactInfoTypeId = 9  -- email        
		left join CustomFieldData e on a.PartyId = e.PrimaryId and e.FieldId = 220  --USPSAddressCode        
		left join CustomFieldData f on a.PartyId = f.PrimaryId and f.FieldId = 228 --NDIP        
	where 
		--IsPrimary = 1 and 
		DisbursementTypeId = 3 and
		StatusId  = 1   
		and ReleaseDate = @ReleaseDate      
		and (@PartyID is null or a.AccountNumber IN (SELECT num FROM dbo.createnumbertable(@PartyID)))        
) ca          
------------------ primary         
join         
------------------ secondary        
(
	select 
		a.NetWriteOffAmount,
		totalWriteOff, 
		a.AccountNumber,
		a.OldCustomerAccountNumber,        
		a.Name1,
		a.Name2,
		b.Address1,
		b.address2,
		a.GoverningRegionId,         
		c.ContactInfo as MainPhone,
		d.ContactInfo as Email,
		e.Data as USPSAddressCode,        
		a.AlternateAccountNumber,  
		CASE WHEN a.PrimaryAccountStatusId = 1 and lower(h.Data) = 'true' then 'NOITC' else p.Name end as PrimaryStatus,
		s.Name as SecondStatus,        
		f.Data as NDIP,
		a.BankruptcyStatusTypeId,
		isnull(a.TotalUncollectedFees,0) as TotalUncollectedFees,        
		a.AccountBalance - isnull(a.TotalUncollectedFees,0) as PIBalance        
		-- TotalUncollectedFees has value when portfolio is FIFC        
	from 
		CustomerAccount a 
		join (	select addr.PartyId, addr.Address1, addr.Address2, ROW_NUMBER() over (partition by addr.PartyId order by addr.AddressId) as seq
				from [Address] addr 
				where addr.IsMailing = 1
			 ) b on a.PartyId = b.PartyId and b.seq = 1
		--join [Address] b on a.PartyId = b.PartyId and b.IsMailing = 1   
		join PrimaryAccountStatus p on a.PrimaryAccountStatusId = p.PrimaryAccountStatusId         
		join SecondaryAccountStatus s on a.SecondaryAccountStatusId = s.SecondaryAccountStatusId         
		left join ContactInfo c on a.PartyId = c.PartyId and c.ContactInfoTypeId = 1        
		left join ContactInfo d on a.PartyId = d.PartyId and d.ContactInfoTypeId = 9        
		left join CustomFieldData e on a.PartyId = e.PrimaryId and e.FieldId = 220         
		left join CustomFieldData f on a.PartyId = f.PrimaryId and f.FieldId = 228        
		left join CustomFieldData h on a.PartyId = h.PrimaryId and h.FieldId = 201 --NOITC        
		cross apply 
		(        
			select SUM(amount) as totalWriteOff        
			from [Transaction] t join NonCashTransactionType b on t.NonCashTransactionTypeId = b.NonCashTransactionTypeId        
			where (b.Name like '%Write Off' )        
				and PartyId = a.PartyId         
		) g        
	where 
		--IsPrimary = 1 and 
		a.AccountBalance > 0         
		or totalWriteOff>0  -- Sum of all write offs (not including recoveries) > 0        
) matched         
------------------ secondary        
on  
ca.OldCustomerAccountNumber = matched.AccountNumber --The secondary account number equals the primary account?s Old Account     
   
-- for name match        
or 
(
	ca.AccountNumber != matched.AccountNumber 
	and ca.GoverningRegionId = matched.GoverningRegionId         
	and (         
		charindex( dbo.GetSearchableString(dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(ca.Name1))),        
					dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(matched.Name1)) ) > 0        
		or         
		charindex( dbo.GetSearchableString(dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(ca.Name1))),        
					dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(matched.Name2)) ) > 0        
		or         
		charindex( case when rtrim(ltrim(ca.Name2)) <> '' then dbo.GetSearchableString(dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(ca.Name2))) else null end ,        
					dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(matched.Name1)) ) > 0         
		or         
		charindex( case when rtrim(ltrim(ca.Name2)) <> '' then dbo.GetSearchableString(dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(ca.Name2))) else null end ,        
					dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(matched.Name2)) ) > 0          
		)
)        
or
          
-- for address match        
(
	ca.AccountNumber != matched.AccountNumber 
	and ca.GoverningRegionId = matched.GoverningRegionId         
	and (         
		charindex( dbo.GetSearchableString(Replace(dbo.RemoveSpecialWordName2(ca.Address1),'P O','PO') ),        
					Replace(dbo.RemoveSpecialWordName2(matched.Address1),'P O','PO') ) > 0        
		or         
		charindex( dbo.GetSearchableString(Replace(dbo.RemoveSpecialWordName2(ca.Address1),'P O','PO') ),        
					Replace(dbo.RemoveSpecialWordName2(matched.Address2),'P O','PO') ) > 0        
		or         
		charindex( case when rtrim(ltrim(ca.Address2)) <> '' then dbo.GetSearchableString( Replace(dbo.RemoveSpecialWordName2(ca.Address2),'P O','PO')) else null end ,        
					Replace(dbo.RemoveSpecialWordName2(matched.Address1),'P O','PO') ) > 0         
		or         
		charindex( case when rtrim(ltrim(ca.Address2)) <> '' then dbo.GetSearchableString( Replace(dbo.RemoveSpecialWordName2(ca.Address2),'P O','PO')) else null end ,        
					Replace(dbo.RemoveSpecialWordName2(matched.Address2),'P O','PO') ) > 0         
		)
)            
or ca.MainPhone = matched.MainPhone         
or ca.Email = matched.Email         
or case RTRIM(LTRIM(ca.USPSAddressCode)) when '' then null else ca.USPSAddressCode end = matched.USPSAddressCode 


--where DisbursementTypeId = 3 and StatusId  = 1 --and ReleaseDate = @ReleaseDate        
order by         
case when @GroupBy = 'Agent Name' then ca.AgentName end,        
case @OrderBy 
when 'Account Number' then cast(ca.AccountNumber AS varchar(10))        
when 'Customer Name' then ca.Name1        
when 'Disbursement Amount' then Right('0000000' + convert(varchar(100), ca.Amount), 10) end asc        

Open in new window

executionPlan.sqlplan
LVL 8
pzozulkaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Functions are a problem, especially as your usage leads to predicates where no indices could be used.

The only approach I see, would be using a temporary table. Push the result of your query without the function predicates into a temp table. Then apply the function predicates on this table.
0
pzozulkaAuthor Commented:
ste5an:

It seems like all the functions are being used in the join conditions between the two derived tables. Are you suggesting that instead of using derived tables (subqueries in the FROM clause) to use temp tables instead?

So in other words, do this:

insert into #tmpA
select * from tableA

insert into #tmpB
select * from tableB

select * from #tmpB join #tmpA on ...

instead of this:

select *
from (select * from tableA) join (select * from tableB) on ...

Let me know if this is what you had in mind.
0
ste5anSenior DeveloperCommented:
First of all, rewrite it. Use more CTE's to increase readability. As @PartyID is a variable, I would also make the number table derived from it a variable:

 
-- Constants.
DECLARE @NOITC INT; SET @NOITC = 201;
DECLARE @MAIN_PHONE INT; SET @MAIN_PHONE = 1;
DECLARE @EMAIL INT; SET @EMAIL = 9;
DECLARE @USPS_ADDRESS_CODE INT; SET @USPS_ADDRESS_CODE = 220;
DECLARE @NDIP INT; SET @NDIP = 228;

-- Variables
DECLARE @ReleaseDate DATETIME = '3/11/2015';    
DECLARE @PartyID VARCHAR(MAX) = NULL;
DECLARE @PartyTable TABLE ( num INT );
DECLARE @GroupBy VARCHAR(100) = '';     
DECLARE @OrderBy VARCHAR(100) = '';    
DECLARE @portfolioCode VARCHAR(100);        

INSERT  INTO @PartyTable
        SELECT  num
        FROM    dbo.createnumbertable(@PartyID);

SELECT  @portfolioCode = code
FROM    Portfolio;   
  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;         

WITH    StartWith
          AS ( SELECT   'Unit' data
               UNION
               SELECT   'Suite' data
               UNION
               SELECT   'Ste' data
               UNION
               SELECT   '#' data
               UNION
               SELECT   'Floor' data
               UNION
               SELECT   'FL' data
               UNION
               SELECT   'Room' data
               UNION
               SELECT   'RM' data
               UNION
               SELECT   'Building' data
               UNION
               SELECT   'BLDG' data
               UNION
               SELECT   'Apartment' data
               UNION
               SELECT   'APT' data
             ),
        Mailing
          AS ( SELECT   addr.PartyId ,
                        addr.Address1 ,
                        addr.Address2 ,
                        ROW_NUMBER() OVER ( PARTITION BY addr.PartyId ORDER BY addr.AddressId ) AS seq
               FROM     [Address] addr
               WHERE    addr.IsMailing = 1
             ),
        [Primary]
          AS ( SELECT   a.AccountNumber ,
                        a.OldCustomerAccountNumber ,
                        a.Name1 ,
                        a.Name2 ,
                        a.GoverningRegionId ,
                        a.PartyId ,
                        ds.Amount ,
                        CASE WHEN a1.data IS NULL THEN b.Address1
                             ELSE NULL
                        END AS Address1 ,
                        CASE WHEN a2.data IS NULL THEN b.Address2
                             ELSE NULL
                        END AS Address2 ,
                        MP.ContactInfo AS MainPhone ,
                        d.ContactInfo AS Email ,
                        e.data AS USPSAddressCode ,
                        f.data AS NDIP ,
                        a.BankruptcyStatusTypeId ,
                        b.Address1 AS RealAddress1 ,
                        a.AlternateAccountNumber ,
                        p.Name1 AS AgentName ,
                        p.Code
               FROM     CustomerAccount a
                        JOIN Mailing b ON a.PartyId = b.PartyId
                                          AND b.seq = 1
                        JOIN entity p ON a.AgentId = p.PartyId
                        JOIN Disbursement ds ON a.PartyID = ds.Partyid
                        LEFT JOIN StartWith a1 ON SUBSTRING(b.Address1, 1, LEN(a1.data)) = a1.data
                        LEFT JOIN StartWith a2 ON SUBSTRING(b.Address2, 1, LEN(a2.data)) = a2.data
                        LEFT JOIN ContactInfo MP ON a.PartyId = MP.PartyId
                                                    AND MP.ContactInfoTypeId = @MAIN_PHONE
                        LEFT JOIN ContactInfo d ON a.PartyId = d.PartyId
                                                   AND d.ContactInfoTypeId = @EMAIL
                        LEFT JOIN CustomFieldData e ON a.PartyId = e.PrimaryId
                                                       AND e.FieldId = @USPS_ADDRESS_CODE
                        LEFT JOIN CustomFieldData f ON a.PartyId = f.PrimaryId
                                                       AND f.FieldId = @NDIP
               WHERE    DisbursementTypeId = 3
                        AND StatusId = 1
                        AND ReleaseDate = @ReleaseDate
                        AND ( @PartyID IS NULL
                              OR a.AccountNumber IN ( SELECT    num
                                                      FROM      @PartyTable )
                            )
             ),
        [Secondary]
          AS ( SELECT   a.NetWriteOffAmount ,
                        totalWriteOff ,
                        a.AccountNumber ,
                        a.OldCustomerAccountNumber ,
                        a.Name1 ,
                        a.Name2 ,
                        b.Address1 ,
                        b.address2 ,
                        a.GoverningRegionId ,
                        c.ContactInfo AS MainPhone ,
                        d.ContactInfo AS Email ,
                        e.Data AS USPSAddressCode ,
                        a.AlternateAccountNumber ,
                        CASE WHEN a.PrimaryAccountStatusId = 1
                                  AND LOWER(h.Data) = 'true' THEN 'NOITC'
                             ELSE p.Name
                        END AS PrimaryStatus ,
                        s.Name AS SecondStatus ,
                        f.Data AS NDIP ,
                        a.BankruptcyStatusTypeId ,
                        ISNULL(a.TotalUncollectedFees, 0) AS TotalUncollectedFees ,
                        a.AccountBalance - ISNULL(a.TotalUncollectedFees, 0) AS PIBalance
               FROM     CustomerAccount a
                        JOIN Mailing b ON a.PartyId = b.PartyId
                                          AND b.seq = 1
                        JOIN PrimaryAccountStatus p ON a.PrimaryAccountStatusId = p.PrimaryAccountStatusId
                        JOIN SecondaryAccountStatus s ON a.SecondaryAccountStatusId = s.SecondaryAccountStatusId
                        LEFT JOIN ContactInfo c ON a.PartyId = c.PartyId
                                                   AND c.ContactInfoTypeId = @MAIN_PHONE
                        LEFT JOIN ContactInfo d ON a.PartyId = d.PartyId
                                                   AND d.ContactInfoTypeId = @EMAIL
                        LEFT JOIN CustomFieldData e ON a.PartyId = e.PrimaryId
                                                       AND e.FieldId = @USPS_ADDRESS_CODE
                        LEFT JOIN CustomFieldData f ON a.PartyId = f.PrimaryId
                                                       AND f.FieldId = @NDIP
                        LEFT JOIN CustomFieldData h ON a.PartyId = h.PrimaryId
                                                       AND h.FieldId = @NOITC
                        CROSS APPLY ( SELECT    SUM(amount) AS totalWriteOff
                                      FROM      [Transaction] t
                                                JOIN NonCashTransactionType b ON t.NonCashTransactionTypeId = b.NonCashTransactionTypeId
                                      WHERE     b.Name LIKE '%Write Off'
                                                AND PartyId = a.PartyId
                                    ) g
               WHERE    a.AccountBalance > 0
                        OR totalWriteOff > 0
             )
    SELECT  @portfolioCode + '-' + CAST(ca.AccountNumber AS VARCHAR(10)) AS PAccountNumber ,
            ca.Name1 AS PName1 ,
            'Agent: ' + ca.Code + ' - ' + ca.AgentName AS Agent ,
            ca.RealAddress1 AS PAddress1 ,
            CASE WHEN ca.BankruptcyStatusTypeId IS NOT NULL THEN 'T'
                 ELSE CASE WHEN ca.NDIP = 'True' THEN 'T'
                           ELSE ''
                      END
            END AS PBKAccount ,
            ca.AlternateAccountNumber AS PAltAccountNumber ,
            ca.Amount ,
            @portfolioCode + '-' + +CAST(matched.AccountNumber AS VARCHAR(10)) AS SAccountNumber ,
            CASE WHEN matched.BankruptcyStatusTypeId IS NOT NULL THEN 'T'
                 ELSE CASE WHEN matched.NDIP = 'True' THEN 'T'
                           ELSE ''
                      END
            END AS SBKAccount ,
            matched.Name1 AS SName1 ,
            matched.Address1 AS SAddress1 ,
            matched.AlternateAccountNumber AS SAltAccountNumber ,
            matched.NetWriteOffAmount ,
            matched.PrimaryStatus ,
            matched.SecondStatus ,
            matched.TotalUncollectedFees ,
            matched.PIBalance
    FROM    [Primary] ca
            JOIN [Secondary] matched ON ca.OldCustomerAccountNumber = matched.AccountNumber
                                        OR ( ca.AccountNumber != matched.AccountNumber
                                             AND ca.GoverningRegionId = matched.GoverningRegionId
                                             AND ( CHARINDEX(dbo.GetSearchableString(dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(ca.Name1))),
                                                             dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(matched.Name1))) > 0
                                                   OR CHARINDEX(dbo.GetSearchableString(dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(ca.Name1))),
                                                                dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(matched.Name2))) > 0
                                                   OR CHARINDEX(CASE WHEN RTRIM(LTRIM(ca.Name2)) <> ''
                                                                     THEN dbo.GetSearchableString(dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(ca.Name2)))
                                                                     ELSE NULL
                                                                END, dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(matched.Name1))) > 0
                                                   OR CHARINDEX(CASE WHEN RTRIM(LTRIM(ca.Name2)) <> ''
                                                                     THEN dbo.GetSearchableString(dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(ca.Name2)))
                                                                     ELSE NULL
                                                                END, dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(matched.Name2))) > 0
                                                 )
                                           )
                                        OR ( ca.AccountNumber != matched.AccountNumber
                                             AND ca.GoverningRegionId = matched.GoverningRegionId
                                             AND ( CHARINDEX(dbo.GetSearchableString(REPLACE(dbo.RemoveSpecialWordName2(ca.Address1), 'P O', 'PO')),
                                                             REPLACE(dbo.RemoveSpecialWordName2(matched.Address1), 'P O', 'PO')) > 0
                                                   OR CHARINDEX(dbo.GetSearchableString(REPLACE(dbo.RemoveSpecialWordName2(ca.Address1), 'P O', 'PO')),
                                                                REPLACE(dbo.RemoveSpecialWordName2(matched.Address2), 'P O', 'PO')) > 0
                                                   OR CHARINDEX(CASE WHEN RTRIM(LTRIM(ca.Address2)) <> ''
                                                                     THEN dbo.GetSearchableString(REPLACE(dbo.RemoveSpecialWordName2(ca.Address2), 'P O', 'PO'))
                                                                     ELSE NULL
                                                                END, REPLACE(dbo.RemoveSpecialWordName2(matched.Address1), 'P O', 'PO')) > 0
                                                   OR CHARINDEX(CASE WHEN RTRIM(LTRIM(ca.Address2)) <> ''
                                                                     THEN dbo.GetSearchableString(REPLACE(dbo.RemoveSpecialWordName2(ca.Address2), 'P O', 'PO'))
                                                                     ELSE NULL
                                                                END, REPLACE(dbo.RemoveSpecialWordName2(matched.Address2), 'P O', 'PO')) > 0
                                                 )
                                           )
                                        OR ca.MainPhone = matched.MainPhone
                                        OR ca.Email = matched.Email
                                        OR CASE RTRIM(LTRIM(ca.USPSAddressCode))
                                             WHEN '' THEN NULL
                                             ELSE ca.USPSAddressCode
                                           END = matched.USPSAddressCode
    ORDER BY CASE WHEN @GroupBy = 'Agent Name' THEN ca.AgentName
             END ,
            CASE @OrderBy
              WHEN 'Account Number' THEN CAST(ca.AccountNumber AS VARCHAR(10))
              WHEN 'Customer Name' THEN ca.Name1
              WHEN 'Disbursement Amount' THEN RIGHT('0000000' + CONVERT(VARCHAR(100), ca.Amount), 10)
            END ASC;        

Open in new window


Using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  is as bad as using NOLOCK cause they are the same. It may lead to (really) wrong data.

Now it's easier to spot some possibilities:

LEFT JOIN StartWith a1 ON SUBSTRING(b.Address1, 1, LEN(a1.data)) = a1.data
LEFT JOIN StartWith a2 ON SUBSTRING(b.Address2, 1, LEN(a2.data)) = a2.data

Open in new window


This prevents the use of an index for Address. It can be rewritten to

LEFT JOIN StartWith a1 ON b.Address1 LIKE a1.data
LEFT JOIN StartWith a2 ON b.Address2 LIKE a2.data

Open in new window


Just add an % to your StartsWith CTE:

WITH    StartWith
          AS ( SELECT   'Unit%' data
               UNION
               SELECT   'Suite%' data
               UNION
               SELECT   'Ste%' data
               UNION
               SELECT   '#%' data
               UNION
               SELECT   'Floor%' data
               UNION
               SELECT   'FL%' data
               UNION
               SELECT   'Room%' data
               UNION
               SELECT   'RM%' data
               UNION
               SELECT   'Building%' data
               UNION
               SELECT   'BLDG%' data
               UNION
               SELECT   'Apartment%' data
               UNION
               SELECT   'APT%' data
             ),..

Open in new window


CROSS APPLY is executed per row.

CROSS APPLY ( SELECT    SUM(amount) AS totalWriteOff
		  FROM      [Transaction] t
					JOIN NonCashTransactionType b ON t.NonCashTransactionTypeId = b.NonCashTransactionTypeId
		  WHERE     b.Name LIKE '%Write Off'
					AND PartyId = a.PartyId
		) g

Open in new window


Here is b.Name LIKE '%Write Off' the problem. No index could be used. Make this a temporary table and use it:

DECLARE @CrossApply TABLE ( PartyID INT , totalWriteOff INT ); -- Choose the proper types.

INSERT  INTO @CrossApply
        SELECT  PartyID ,
                SUM(Amount) AS totalWriteOff
        FROM    [Transaction] t
                JOIN NonCashTransactionType b ON t.NonCashTransactionTypeId = b.NonCashTransactionTypeId
        WHERE   b.Name LIKE '%Write Off';

Open in new window

 

as

CROSS APPLY ( SELECT    CA.totalWriteOff
		  FROM      @CrossApply CA
		  WHERE     CA.PartyID = a.PartyID
		) g

Open in new window


This CROSS APPLY should be worth some performance.

The rest depends on

CHARINDEX(
	dbo.GetSearchableString(dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(ca.Name1))),
	dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(matched.Name1))
	) > 0

Open in new window


Cause these functions are not really optimizable. Here it depends on the concrete function and use-case. An option may be a persisted calculated column in the base table.
intermediate.sql
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
How long it takes to run?
By the statistics is less than 2 seconds:
" SQL Server Execution Times:
   CPU time = 687 ms,  elapsed time = 1774 ms."


There are some clustered index scan and it will be good to transform those in indexes seeks and that's may be the reason that the Query Plan is recommending the creation of the following index:
USE [PbsDev3]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Address] ([IsMailing])
INCLUDE ([AddressId],[PartyId],[Address1],[Address2])

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Also, the last ORDER BY can't be avoided and let the front end application to execute a sort on the returned data?
0
pzozulkaAuthor Commented:
Thanks ste5an, I will look into your suggestions -- they look very helpful, but not sure how much savings I'll get. My gut feeling tells me the functions are the problem. I'll let you know.

Vitor Montalvão: In production, it takes more than 10 minutes to execute, and because we have a timeout limit for our application, it errors out. I re-ran my query on my test DB without the ORDER BY, and saved 5 second, going from 1:17 to 1:12.
There are some clustered index scan and it will be good to transform those in indexes seeks
How would I do this?
0
ste5anSenior DeveloperCommented:
I think you would gain a lot from the CROSS APPLY approach. Cause it is no longer necessary to execute that query with that non-sargeable predicate over and over again.

When the @CrossApply temporary table is large, then better us a temporary table #CrossApply instead of the table variable. Cause you can create an index on #CrossApply(PartyID, totalWriteOff).

btw, after reading it right now, seems like you can drop that CROSS APPLY now and switch to a LEFT JOIN.
intermediate.sql
0
pzozulkaAuthor Commented:
Would a temp table #CrossApply be better, or a LEFT JOIN?
0
ste5anSenior DeveloperCommented:
The temp table is an option when we have many rows in it.

Use the LEFT JOIN even with the table variable. I have no sample at hand, but the worst case for CROSS APPLY is that it is evaluated RBAR, thus it is recalculated for each row, which is really expensive in time.
0
pzozulkaAuthor Commented:
Here are some stats, using my test DB:

Original query: 2:38
Re-written to use CTEs: 2:27

Moving forward, I used the re-written query:

Added an % to your StartsWith CTE, and changed the JOINs on the StartsWith table as suggested above: 2:36

Removed this suggestion as it did not improve performance.

Then, replaced the cross apply with first adding records to the temp table, and then joining on this temp table: 2:13

I also tried replacing the cross apply with a left join using a derived table instead of temp table like the below: 2:29
LEFT JOIN 
(
	SELECT  PartyID, SUM(Amount) AS totalWriteOff
	FROM    [Transaction] t
			JOIN NonCashTransactionType b ON t.NonCashTransactionTypeId = b.NonCashTransactionTypeId
	WHERE   b.Name LIKE '%Write Off'
	GROUP BY PartyID
) g ON g.PartyID = a.PartyId

Open in new window


So I'm still not getting the performance improvement I was hoping for. My manager suggested to see if the functions in the JOIN conditions can be merged. Notice how the the functions are being repeated, twice for ca.Name1, and twice for ca.Name2, and again for matched.Name1, and matched.Name2:
AND ( CHARINDEX(dbo.GetSearchableString(dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(ca.Name1))),
			dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(matched.Name1))) > 0
	OR CHARINDEX(dbo.GetSearchableString(dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(ca.Name1))),
			dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(matched.Name2))) > 0
	OR CHARINDEX(CASE WHEN RTRIM(LTRIM(ca.Name2)) <> ''
					THEN dbo.GetSearchableString(dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(ca.Name2)))
					ELSE NULL
			END, dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(matched.Name1))) > 0
	OR CHARINDEX(CASE WHEN RTRIM(LTRIM(ca.Name2)) <> ''
					THEN dbo.GetSearchableString(dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(ca.Name2)))
					ELSE NULL
			END, dbo.RemoveSpecialWordName1(dbo.RemoveSpecialWordName2(matched.Name2))) > 0
	)

Open in new window

Perhaps running them somehow ahead of time and storing in a variable, and then calling on that variable will reduce the function calls by 50%?

What are your thoughts on this approach. Not sure how I would implement this yet, or even if that's possible.
0
ste5anSenior DeveloperCommented:
Well, have you haven't told us anything about the number of rows involved.

Also have you taken a look the execution plan? Do you have indices to support your queries?

And for the functions: the only way I see to improve this is to create calculated persisted columns in the base tables.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you create the recommended index?
The Query Plan you posted isn't from Production? If not, please send us the Production one. It may be different.
0
ste5anSenior DeveloperCommented:
Perhaps running them somehow ahead of time and storing in a variable, and then calling on that variable will reduce the function calls by 50%?

Consider using persisted calculated and indexd columns in the base tables.
0
pzozulkaAuthor Commented:
Consider using persisted calculated and indexd columns in the base tables.

Ste5an: This helped dramatically. Thanks.
0
pzozulkaAuthor Commented:
Ste5an: before I close this question, I wanted to respond to one item:
Using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  is as bad as using NOLOCK cause they are the same. It may lead to (really) wrong data.

We removed the above mentioned line of code, and it we believe it is the reason for the following error:

System.Data.SqlClient.SqlException: Transaction (Process ID 142) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
0
ste5anSenior DeveloperCommented:
This maybe surely the cause. But setting this isolation level or using NOLOCK is not the solution for a deadlock problem.

Mostly all deadlock I've seen in the past in such cases were avoided by using appropriate indices. These did not only avoid the deadlocks, but they also decreased the execution time.

And last but not least, in my own words: Getting a wrong report (query result) faster makes no sense.
1
pzozulkaAuthor Commented:
Best Solution: "Cause these functions are not really optimizable. Here it depends on the concrete function and use-case. An option may be a persisted calculated column in the base table." -- ste5an
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.