Solved

SQL: Query Performance

Posted on 2014-10-22
29
149 Views
Last Modified: 2014-10-27
I have a SQL query that first inserts records into temp tables, and then does an overall select from those temp tables. After running the Actual Execution Plan (attached below), and it was determined that the below code takes the highest percent of the batch (34%):

-- 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

Open in new window

I then also tried this, but it doesn't appear to be helping much:

-- CustPymt: Total Payments of type payment including adjustments       
CREATE TABLE #tmpCustPymt                  
(                  
--RefId INT IDENTITY(1,1) primary key nonclustered                  
PartyId INT NOT NULL primary key nonclustered,                  
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

Open in new window

plan.sqlplan
0
Comment
Question by:pzozulka
  • 8
  • 8
  • 7
  • +1
29 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40398677
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).
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40399299
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

Open 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

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40399330
Set functions (SUM, AVG, MAX, MIN, and COUNT) ignores NULL value so there's no need to check for NULL's
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 250 total points
ID: 40399348
Try these steps:

1. Take the select statement only:
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

Open in new window

and paste it in a new query window.

2. In that window in Management Studio click on the "Display Estimated Execution Plan" icon from the top or from the main menu click: Query >  "Display Estimated Execution Plan".

3. If in the execution plan window at the top you will see in green fonts a missing index right click on that text and then on "Display index details". This will open a new window with the commented sql script to create the index. Uncomment it and then replace the generic index name with something meaningful (like IX_col1_col2_..._incl_N : use incl if any cols are included and N how many) and execute it.

4. After the index is created repeat 2 and 3 until there is no any index suggestion.

5. execute the select or the query you posted.

This should do it.,
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40399367
@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

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40399375
@aikimark: He's adding the result of two SUM's, so NULL are already out of question here.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40399403
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40399546
The performance is an index issue and not a SUM with NULL issue.  Try my steps and you will see improvement.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40399798
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 

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40399813
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.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40399862
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40399864
@pzozulka

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

Expert Comment

by:Zberteoc
ID: 40399887
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.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40400190
@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.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 45

Expert Comment

by:aikimark
ID: 40400207
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?
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40400220
@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

Open in new window

0
 
LVL 8

Author Comment

by:pzozulka
ID: 40400224
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40400413
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.
0
 
LVL 8

Author Comment

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

Expert Comment

by:Vitor Montalvão
ID: 40401161
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40402069
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.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40402073
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40402080
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.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 40402291
But by his first example he has:

CREATE TABLE #tmpCustPymt                  
(...)                  
CREATE CLUSTERED INDEX cx ON #tmpCustPymt(PartyId, Final_Amount )  
     
INSERT INTO #tmpCustPymt    ( PartyId, Final_Amount )                  
SELECT T.PartyId, SUM(T.Amount + ISNULL(T9.Amount, 0)) as 'Final_Amount'
FROM [Transaction] T
(...)

It's this I was talking about. A clustered here has only negative impact in the performance, since he's only inserting in the temporary table.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40402446
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.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40402491
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40402531
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.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40402571
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40402610
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.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now