troubleshooting Question

SQL Query Optimization

Avatar of pzozulka
pzozulka asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
17 Comments5 Solutions80 ViewsLast Modified:
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.

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        
executionPlan.sqlplan
ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 5 Answers and 17 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 5 Answers and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros