SQL: Query Performance

Are you working with Views?
From the Query Plan I can see that the query is spending 35% on a Sort operation and it also suggesting create an index to improve the performance (see the Missing Index in green color).

If the index doesn't work, try a union query where you exclude null null values in the first query:

SELECT T.PartyId, SUM(T.Amount + T9.Amount) as 'Final_Amount'
FROM [Transaction] T
LEFT OUTER JOIN [Transaction] T9 ON
    T9.PaymentTypeId = 9 AND
    T9.AdjustsTransactionId = T.TransactionId
WHERE 
    T.PaymentTypeId = 1
    And T9.Amount Is Not Null
GROUP BY T.PartyId

Select allOpen in new window

And the second union query just sums the T.amount column where the T9.amount values are null

SELECT T.PartyId, SUM(T.Amount) as 'Final_Amount'
FROM [Transaction] T
LEFT OUTER JOIN [Transaction] T9 ON
    T9.PaymentTypeId = 9 AND
    T9.AdjustsTransactionId = T.TransactionId
WHERE 
    T.PaymentTypeId = 1
    And T9.Amount Is Null
GROUP BY T.PartyId

Select allOpen in new window

Set functions (SUM, AVG, MAX, MIN, and COUNT) ignores NULL value so there's no need to check for NULL's

@Vitor

However, you are summing an expression, not a single column.

You are right about SUM() ignoring nulls.  We should try this:

SELECT T.PartyId, (SUM(T.Amount) + SUM(T9.Amount)) as 'Final_Amount'
FROM [Transaction] T
LEFT OUTER JOIN [Transaction] T9 ON
    T9.PaymentTypeId = 9 AND
    T9.AdjustsTransactionId = T.TransactionId
WHERE 
    T.PaymentTypeId = 1
GROUP BY T.PartyId

Select allOpen in new window

@aikimark: He's adding the result of two SUM's, so NULL are already out of question here.

He is SUMming an expression which is an addition process.  Since one side of the addition is an expression, ISNULL(), there are limitations on the optimization that can/will be done.  I'm trying to eliminate the ISNULL() part of the optimization restriction.

As far as I know, an addition of a Null value still results in a Null.

I think there is an EE article on the effect that expressions have on performance.

The performance is an index issue and not a SUM with NULL issue.  Try my steps and you will see improvement.

Zberteoc:
I don't have permission to modify existing database, which includes creating new indexes on real tables. I can only create temp tables and create indexes for those. Is there another way?

aikimark:
Your suggested query does not produce the same results...the amount column is mostly NULL

Vitor:
I am not using Views. At first I did use derived tables by using LEFT JOINs, and in production the query finishes in 1:53. I then took all the derived tables and converted them into TEMP TABLEs and put clustered indexes on them. This actually slowed down the query to 2:03. Probably because INSERTs are expensive.

Here's the query with TEMP TABLEs:

DECLARE @DateFilterTypeId INT = 1			-- @pDateFilterTypeId
DECLARE @startDate DATETIME = '1/1/2000'	-- @pStartDate
DECLARE @endDate DATETIME = '1/1/2018'		-- @pEndDate
DECLARE @entityList VARCHAR(8000) = null	-- @pEntityList
DECLARE @entityType INT = null				-- @pEntityType
IF (@entityList ='') SET @entityList = null

DECLARE @EntitiesList TABLE                        
(                        
entityId int not null primary key                        
)                                 
INSERT INTO @EntitiesList select * from dbo.createnumbertable(@entityList)                        


IF OBJECT_ID('tempdb..#tmpPol') IS NOT NULL                           
DROP TABLE #tmpPol            
IF OBJECT_ID('tempdb..#tmpFunding') IS NOT NULL                           
DROP TABLE #tmpFunding 
IF OBJECT_ID('tempdb..#tmpPolicyCancelDate') IS NOT NULL                           
DROP TABLE #tmpPolicyCancelDate 
IF OBJECT_ID('tempdb..#tmpCustPymt') IS NOT NULL                           
DROP TABLE #tmpCustPymt 
IF OBJECT_ID('tempdb..#tmpRPAdmin') IS NOT NULL                           
DROP TABLE #tmpRPAdmin 
IF OBJECT_ID('tempdb..#tmpTr') IS NOT NULL                           
DROP TABLE #tmpTr 
IF OBJECT_ID('tempdb..#tmpRsrv') IS NOT NULL                           
DROP TABLE #tmpRsrv 

-----------------------------------------------------------------------------------------------------------------------

-- Pol: First policy on the account: Policy#, GA Name                  
CREATE TABLE #tmpPol                  
(                  
--RefId INT IDENTITY(1,1) primary key nonclustered                  
InsurancePolicyId INT NOT NULL,                  
PolicyNumber VARCHAR(40),                  
PremiumFinanceLoanId INT,  
CustomerAccountId INT,                
Name1 VARCHAR(50),                  
Seq INT                  
)                  
CREATE CLUSTERED INDEX cx ON #tmpPol(InsurancePolicyId, PolicyNumber, PremiumFinanceLoanId, CustomerAccountId, Name1, Seq)  

-- Pol: First policy on the account: Policy#, GA Name                    
INSERT INTO #tmpPol                  
( InsurancePolicyId, PolicyNumber, PremiumFinanceLoanId, CustomerAccountId, Name1, Seq )                  
SELECT IP.InsurancePolicyId, IP.PolicyNumber, PFL.PremiumFinanceLoanId, PFL.CustomerAccountId, GA.Name1,
		ROW_NUMBER() OVER (PARTITION BY PFL.PremiumFinanceLoanId ORDER BY IP.InsurancePolicyId, GA.Name1 DESC) AS Seq
FROM PremiumFinanceLoan PFL 
	JOIN InsurancePolicy IP ON IP.PremiumFinanceLoanId = PFL.PremiumFinanceLoanId AND PFL.IsOriginal = 1
	JOIN PolicyEntityMap PEM ON PEM.PolicyId = IP.InsurancePolicyId
	LEFT JOIN Entity GA ON PEM.EntityId = GA.PartyId AND GA.EntityTypeId = 2 

-----------------------------------------------------------------------------------------------------------------------

-- Y - Disbursements/Funding: Funding to Administrator/Agent/Reserves          
CREATE TABLE #tmpFunding                  
(                  
--RefId INT IDENTITY(1,1) primary key nonclustered                  
PartyId INT NOT NULL,                  
FundingToAdministrator MONEY,                  
FundingToAgent MONEY,  
FundingToReserves MONEY               
)                  
CREATE CLUSTERED INDEX cx ON #tmpFunding(PartyId, FundingToAdministrator, FundingToAgent, FundingToReserves)  

-- Y - Disbursements/Funding: Funding to Administrator/Agent/Reserves                    
INSERT INTO #tmpFunding                  
( PartyId, FundingToAdministrator, FundingToAgent, FundingToReserves )                  
SELECT 
	X.PartyId,
	ISNULL(SUM(CASE WHEN X.PayeeType = 2 AND TransferToEntityAR = 0 THEN X.Amount END),0) as 'FundingToAdministrator',
	ISNULL(SUM(CASE WHEN X.PayeeType = 3 AND TransferToEntityAR = 0 THEN X.Amount END),0) as 'FundingToAgent',
	ISNULL(SUM(CASE WHEN X.PayeeType = 3 AND TransferToEntityAR = 1 THEN X.Amount END),0) as 'FundingToReserves'
FROM 
	(	
		SELECT Disbursement.DisbursementId, Disbursement.PartyId, Payee.EntityTypeId as 'PayeeType', TransferToEntityAR, Disbursement.Amount
		FROM Disbursement LEFT JOIN Entity Payee ON Payee.PartyId = Disbursement.PayeeId
		WHERE PaidDate IS NOT NULL AND DisbursementTypeId IN (1,4) AND Payee.EntityTypeId IN (2,3)
	) X
GROUP BY X.PartyId

-----------------------------------------------------------------------------------------------------------------------

-- PolCancelDate: earliest company cancel date (from policy details)         
CREATE TABLE #tmpPolicyCancelDate                
(                  
--RefId INT IDENTITY(1,1) primary key nonclustered                  
PremiumFinanceLoanId INT NOT NULL,                  
MinCancelDate DATETIME              
)                  
CREATE CLUSTERED INDEX cx ON #tmpPolicyCancelDate(PremiumFinanceLoanId, MinCancelDate )  

-- PolCancelDate: earliest company cancel date (from policy details)                  
INSERT INTO #tmpPolicyCancelDate                  
( PremiumFinanceLoanId, MinCancelDate )                  
SELECT InPo.PremiumFinanceLoanId, MIN(InPo.CarrierCancelDate) as 'MinCancelDate'
FROM InsurancePolicy InPo
WHERE InPo.CarrierCancelDate IS NOT NULL
GROUP BY InPo.PremiumFinanceLoanId

-----------------------------------------------------------------------------------------------------------------------

-- CustPymt: Total Payments of type payment including adjustments       
CREATE TABLE #tmpCustPymt                  
(                  
--RefId INT IDENTITY(1,1) primary key nonclustered                  
PartyId INT NOT NULL,                  
Final_Amount MONEY              
)                  
CREATE CLUSTERED INDEX cx ON #tmpCustPymt(PartyId, Final_Amount )  

-- CustPymt: Total Payments of type payment including adjustments                 
INSERT INTO #tmpCustPymt                  
( PartyId, Final_Amount )                  
SELECT T.PartyId, SUM(T.Amount + ISNULL(T9.Amount, 0)) as 'Final_Amount'
FROM [Transaction] T
LEFT OUTER JOIN [Transaction] T9 ON
    T9.PaymentTypeId = 9 AND
    T9.AdjustsTransactionId = T.TransactionId
WHERE 
    T.PaymentTypeId = 1
GROUP BY T.PartyId

-----------------------------------------------------------------------------------------------------------------------

-- RPAdmin: Total payments of type Returned Premium where the payment method is check including adjustments      
CREATE TABLE #tmpRPAdmin                
(                  
--RefId INT IDENTITY(1,1) primary key nonclustered                  
PartyId INT NOT NULL,                  
Final_Amount MONEY              
)                  
CREATE CLUSTERED INDEX cx ON #tmpRPAdmin(PartyId, Final_Amount )  

-- RPAdmin: Total payments of type Returned Premium where the payment method is check including adjustments                  
INSERT INTO #tmpRPAdmin                  
( PartyId, Final_Amount )                  
SELECT T.PartyId, SUM(T.Amount + ISNULL(T9.Amount, 0)) as 'Final_Amount'
FROM [Transaction] T
LEFT OUTER JOIN [Transaction] T9 ON
    T9.PaymentTypeId = 9 AND
    T9.AdjustsTransactionId = T.TransactionId
WHERE 
    T.PaymentTypeId = 3 AND T.PaymentMethodId = 1
GROUP BY T.PartyId

-----------------------------------------------------------------------------------------------------------------------

-- Tr: Total Fees assessed minus waived    
CREATE TABLE #tmpTr               
(                  
--RefId INT IDENTITY(1,1) primary key nonclustered                  
PartyId INT NOT NULL,                  
CustomerFees MONEY              
)                  
CREATE CLUSTERED INDEX cx ON #tmpTr(PartyId, CustomerFees )  

-- Tr: Total Fees assessed minus waived              
INSERT INTO #tmpTr                  
( PartyId, CustomerFees )                  
SELECT 
	T.PartyId,
	((ISNULL(SUM(CASE WHEN T.TransactionTypeId = 2 AND T.AccountingEntryTypeId = 1 AND T.FeeTypeId IS NOT NULL THEN T.Amount END), 0)) +
	(ISNULL(SUM(CASE WHEN T.TransactionTypeId = 2 AND T.AccountingEntryTypeId = 2 AND T.FeeTypeId IS NOT NULL THEN T.Amount END), 0))) as 'CustomerFees'
FROM 
	[Transaction] T
GROUP BY 
	T.PartyId
	
-----------------------------------------------------------------------------------------------------------------------	
	
-- Rsrv: Sum all Amounts received on account where the Method is Transfer/Reapply and the Reason begins with either "Entity AR" or "Reserve Chargeback"    
CREATE TABLE #tmpRsrv               
(                  
--RefId INT IDENTITY(1,1) primary key nonclustered                  
PartyId INT NOT NULL,                  
Final_Amount MONEY              
)                  
CREATE CLUSTERED INDEX cx ON #tmpRsrv(PartyId, Final_Amount )  

-- Rsrv: Sum all Amounts received on account where the Method is Transfer/Reapply and the Reason begins with either "Entity AR" or "Reserve Chargeback"            
INSERT INTO #tmpRsrv                  
( PartyId, Final_Amount )                  
SELECT t.PartyId, ISNULL(SUM(t.Amount),0) as 'Final_Amount'
FROM [Transaction] t
WHERE t.PaymentMethodId = 9 AND (t.UserReason like 'Entity AR%' or t.UserReason like 'Reserve Chargeback%')
GROUP BY t.PartyId

-----------------------------------------------------------------------------------------------------------------------


SELECT DISTINCT
	CA.AccountNumber,
	CA.Name1 as 'CustomerName',
	A.Name1 as 'AgentName',
	Pol.PolicyNumber,
	Pol.Name1 as 'GeneralAgentName',
	Y.FundingToAdministrator as 'FundingToAdministrator',
	Y.FundingToAgent as 'FundingToAgent',
	Y.FundingToReserves as 'FundingToReserves',
	ISNULL(PFL.DiscountAmount,0) as 'DiscountFee',
	ISNULL(dbo.GetUnEarnedPremiumDueExcludeCarrierDisb(CA.PartyId), 0) as UnearnedPremiumBill,
	CustPymt.Final_Amount as 'CustomerPayments',
	Tr.CustomerFees,
	ISNULL(PFL.DiscountRefundAmount,0) as 'DiscountRefund',
	RPAdmin.Final_Amount as 'RPFromAdministrator',
	Rsrv.Final_Amount as 'AppliedFromReserves'

FROM 
	CustomerAccount CA
	JOIN Entity A ON A.PartyId = CA.AgentId
	JOIN PremiumFinanceLoan PFL ON PFL.CustomerAccountId = CA.PartyId --AND PFL.PremiumFinanceLoanId != Pol.PremiumFinanceLoanId
	JOIN InsurancePolicy ipol ON pfl.PremiumFinanceLoanId = ipol.PremiumFinanceLoanId
	LEFT JOIN #tmpPol as Pol ON Pol.CustomerAccountId = CA.PartyId AND Pol.Seq = 1
	LEFT JOIN PolicyEntityMap peMap ON ipol.InsurancePolicyId = peMap.PolicyId  
	LEFT JOIN Entity ga ON peMap.EntityId = ga.PartyId AND ga.EntityTypeId = 2 -- General Agent 
	LEFT JOIN #tmpFunding as Y ON Y.PartyId = CA.PartyId
	LEFT JOIN #tmpPolicyCancelDate as PolCancelDate ON PolCancelDate.PremiumFinanceLoanId = PFL.PremiumFinanceLoanId
	LEFT JOIN #tmpCustPymt CustPymt ON CustPymt.PartyId = CA.PartyId
	LEFT JOIN #tmpRPAdmin RPAdmin ON RPAdmin.PartyId = CA.PartyId
	LEFT JOIN #tmpTr as Tr ON Tr.PartyId = CA.PartyId
	LEFT JOIN #tmpRsrv as Rsrv ON Rsrv.PartyId = CA.PartyId

WHERE 
(
	(@DateFilterTypeId = 1 AND CA.AccountCreateDate BETWEEN @startDate and @endDate) OR
	(@DateFilterTypeId = 2 AND CA.AccountEffectiveDate BETWEEN @startDate and @endDate) OR
	(@DateFilterTypeId = 3 AND 
		(((CA.CancellationEffectiveDate < PolCancelDate.MinCancelDate) AND (CA.CancellationEffectiveDate BETWEEN @startDate and @endDate)) OR
		((CA.CancellationEffectiveDate > PolCancelDate.MinCancelDate) AND (PolCancelDate.MinCancelDate BETWEEN @startDate and @endDate))))
)	 
AND
(
	(@entityList IS NULL) OR
	((@entityType = 1) AND (CA.AgentId  IN (SELECT entityId FROM @EntitiesList))) OR
	((@entityType = 2) AND (CA.SubmittedById  IN (SELECT entityId FROM @EntitiesList))) OR
	((@entityType = 3) AND (ga.PartyId IN (SELECT entityId FROM @EntitiesList)))
)	

GROUP BY 
	CA.PartyId,
	CA.AccountNumber,
	CA.Name1,
	A.Name1,
	Pol.PolicyNumber,
	Pol.Name1,
	PFL.DiscountAmount, PFL.DiscountRefundAmount,
	Y.FundingToAdministrator,
	Y.FundingToAgent,
	Y.FundingToReserves,
	CustPymt.Final_Amount,
	Tr.CustomerFees,
	RPAdmin.Final_Amount,
	Rsrv.Final_Amount
	
ORDER BY CA.AccountNumber


IF OBJECT_ID('tempdb..#tmpPol') IS NOT NULL                           
DROP TABLE #tmpPol            
IF OBJECT_ID('tempdb..#tmpFunding') IS NOT NULL                           
DROP TABLE #tmpFunding 
IF OBJECT_ID('tempdb..#tmpPolicyCancelDate') IS NOT NULL                           
DROP TABLE #tmpPolicyCancelDate 
IF OBJECT_ID('tempdb..#tmpCustPymt') IS NOT NULL                           
DROP TABLE #tmpCustPymt 
IF OBJECT_ID('tempdb..#tmpRPAdmin') IS NOT NULL                           
DROP TABLE #tmpRPAdmin 
IF OBJECT_ID('tempdb..#tmpTr') IS NOT NULL                           
DROP TABLE #tmpTr 
IF OBJECT_ID('tempdb..#tmpRsrv') IS NOT NULL                           
DROP TABLE #tmpRsrv 

Select allOpen in new window

From the query plan the MSSQL engine is complaining about a missing index on Disbursement and that table don't appears in any of your SELECT's and that's why I think Transaction is no a table but a View.

There are several complaints about missing indexes, but overall, the Disbursement table is used on line 80 to create the #tmpFunding temp table, and Transaction table is used on line 120 to create the #tmpCustPymt temp table.

So no views are being used.

@pzozulka

It looks like you did not try either of my suggestions.

If you don't have permissions to add indexes then you will have to ask somebody who has. Generate the scripts and give them to that person.

It makes no sense to copy data in temp tables and that will not improve your performance.

@aikimark: The union queries you suggested would require me to use additional sum functions, see below:

I ran my query vs. your two union query suggestions for the same PartyID, and the results are below:

Using UNION per your suggestions:
PartyId      Final_Amount
128075      0.00
128075      1797.32

My Query
PartyId      Final_Amount
128075      1797.32

Furthermore, your query requires 11034 logical reads with 4 scan counts, and 67 ms, while my original query requires 5517 logical reads, 2 scan counts and 32 ms.

There should be only one row, since the two sets of data should be different.  But I don't have your tables/data with which to test.

What were your performance numbers on the (SUM() + SUM()) SQL?

@aikimark: As for the code below, it works as expected, but am not seeing any performance improvement. Perhaps the problem is indeed with indexes.

SELECT T.PartyId, (SUM(T.Amount) + SUM(T9.Amount)) as 'Final_Amount'
FROM [Transaction] T
LEFT OUTER JOIN [Transaction] T9 ON
    T9.PaymentTypeId = 9 AND
    T9.AdjustsTransactionId = T.TransactionId
WHERE 
    T.PaymentTypeId = 1
GROUP BY T.PartyId

Select allOpen in new window

So without testing with a specific PartyID, and letting both queries run on all partyIDs, both have the same amount of scans and logical reads. The only difference is that my original query takes 166 ms, while sum() + sum() query takes 185 ms as far as elapsed time goes.

You are talking less than .2 seconds.  If .166 seconds represents 34% of a process, I would say that you don't have much of a performance problem.

Zberteoc: After creating the new index, the stored procedure actually is taking 10 seconds longer, and the database size grew by 150 MB.

You are talking less than .2 seconds.  If .166 seconds represents 34% of a process, I would say that you don't have much of a performance problem.

I must agree with aikimark.
Even that your query will take 90% of the time and runs in 30ms, doesn't mean that isn't performant at all. There's always a place that will take more time than others.
You are loosing time with peanuts.

Zberteoc: After creating the new index, the stored procedure actually is taking 10 seconds longer, and the database size grew by 150 MB.

If you run the same query before and after you created the suggested index from the estimated execution plan it is impossible to actually take longer. The fact that the database grew is perfectly normal as it takes space to create indexes. It is a price you'll have to pay but a very cheap one I would say. 150MB is nothing these days.

Looking at the last code you posted I think you are over complicating the issue already. You started from a simple query and you got to that. If you really took the steps as I showed you with the simple initial query I doubt the result would be in longer execution time.

Goodluck.

If you run the same query before and after you created the suggested index from the estimated execution plan it is impossible to actually take longer.

It's not impossible if he only inserts data. If so, indexes will be a bottleneck instead of a performance booster.

If you run the same query before and after you created the suggested index from the estimated execution plan it is impossible to actually take longer.

It's not impossible if he only inserts data. If so, indexes will be a bottleneck instead of a performance booster.

Victor, that is not the case. If you look at the initial query I was referring to you will see that is a select from a join and the result is inserted into a temp table. I suggested to check the estimated execution plan on the select part only and build the indexes as suggested. That has nothing to do with the insert to a temp table as it Has NO indexes so, the bottleneck being the select and not the insert. Even if the temp table had indexes that has nothing to do with the fact that you build indexes on the selected permanent tables.

Again, my solution was to create suggested indexes from the execution plan for the select part and those have nothing to do with the index on the temp table.

The insert part will perform the same if the same result set is inserted. However if that result set is coming much faster the overall duration will be by the same amount less.

I agree however that the cluster index on that temp table is superfluous because the cluster index is basically the entire row so it won't help much unless you are looking for a particular combination of values. In join instead of index seek a cluster index scan will be used, which is not different at all to a table scan.

Vitor, can you please elaborate further why a clustered index has a negative impact on performance on temp tables? I use this approach frequently when LEFT JOINs (derived tables) are performing poorly.

What Victor means is that if you have an index(es) on a table and you insert in it then the data will go into the table itself but the index(es) will have to be inserted/updated as well, which means more work.

As a general idea indexes help performance when you retrieve the data but will have negative impact when you insert, update or delete data. Keeping the indexes updated with the table takes resources when inserting/deleting/updating.

Anyway, in this case the issue with the temp table is not too big as there is only 1 index, clustered and only 2 columns. Everything could fit in a page basically.

The idea with temp tables, is that you will ALWAYS insert into them at least once, and then retrieve from them one or perhaps many times. At least that has been my experience. Having said that, would a non-clustered index work better in these situations?

Also, in my original post where I'm inserting into a temp table, please keep in mind that there are 7 other temp tables doing the same thing with different data.

Like I said, you can leave the index in the temp as it is. Another thing to keep in mind is that if the tables are small in terms of size, indexes are not necessary. A table with 2 columns will not need any indexes even if you have thousands of rows in it.

For instance the 2 columns are taking 8 bytes (int and money = 4+4) so one page of data is 8KB that means it can store about 1000 rows. Even if it will take 10 pages it is still a small amount of data and the SQL engine will most likely ignore your indexes, which makes them useless but the performance will not be impacted really.