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):
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
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.
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.
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.
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.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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:
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])
Also, the last ORDER BY can't be avoided and let the front end application to execute a sort on the returned data?
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.
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 seeksHow would I do this?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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
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:
What are your thoughts on this approach. Not sure how I would implement this yet, or even if that's possible.
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
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
)
What are your thoughts on this approach. Not sure how I would implement this yet, or even if that's possible.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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.
The Query Plan you posted isn't from Production? If not, please send us the Production one. It may be different.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Consider using persisted calculated and indexd columns in the base tables.
Ste5an: This helped dramatically. Thanks.
Ste5an: before I close this question, I wanted to respond to one item:
We removed the above mentioned line of code, and it we believe it is the reason for the following error:
System.Data.SqlClient.SqlE
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.SqlE
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.
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.
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.