SQL Server Performance Forum – Threads Archive

INSERT INTO SELECT – help

Hi, I have an INSERT INTO SELECT * from table inner join table2 in a stored proc, that inserts about (growing) 4500 rows. Now it is taking too long and causing a timeout.
How can I optimize its performance? Ideas? TIA!

4500 is not much. you can easily do an INSERT like that with a few hundred thousand rows in a few seconds. Describe ‘too long’ and how much is acceptable, and perhaps the query?
Does the destination table have too many indexes? Is the destination table too big? May be you can drop the indexes, do the insert and add the indexes back? ***********************
Dinakar Nethi
SQL Server MVP
***********************

It takes 5 + minutes. The INSERT is where most of the processing occurs. Dropping the indices did not help increase perf.
Any advice?

how may rows does the SELECT return? Its probably the SELECT thats slowing down? Can you post your query? ***********************
Dinakar Nethi
SQL Server MVP
***********************

About 4500. The select runs under a minute. I noticed when I put on the execution plan, there is a sort operation that is accounts for about 84% of the query cost. Note, I have no order by, so it must be sorting when inserting from select. I’ll post back w/query… THANKS!

Is insert is within the same server or between the servers? Try using nolock hint…sometimes it improve peformance if you not worried reading dirty data…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Without seeing the query and som emore information like indices, triggers… it is like a shot in the dark. Hard to be on target. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

CODE BELOW! Thx! Await reply…..
CREATE PROCEDURE [dbo].[pDisposition_DAUXTRNCH_INCOME_IAT_Insert]
@tranId int
AS
BEGIN DELETE FROM dbo.DAUXTRNCH_INCOME_IAT WHERE TRANSACTION_ID = [email protected] INSERT INTO DAUXTRNCH_INCOME_IAT
(TRANSACTION_ID, INC_TRANSACTION_ID, RELATION_ID, TRANCHE_ID, SECURITY_ID, PARTNER_ID, LOSSPAID_IAT, NETALLOC_IAT, DIST_IAT,
[DISPOSITION%_IAT], [TRANCHE%_IAT], [WEIGHTEDDISPOSITION%_IAT], LOSSPAID_DISPOSED_IAT, NETALLOC_DISPOSED_IAT,
DIST_DISPOSED_IAT, INCOME_DATE_IAT, TRANSACTION_DATE_IAT, UPDATE_DATE_IAT)
SELECT TDISPOSITION_FTD.TRANSACTION_ID, INCOME_PII.TRANSACTION_ID AS INC_TRANSACTION_ID, INCOME_PII.RELATION_ID,
INCOME_FTI.TRANCHE_ID, INCOME_FTI.SECURITY_ID, INCOME_PII.PARTNER_ID, dbo.fn_g_LOSSPAID_PII(INCOME_PII.RELATION_ID,
INCOME_PII.TRANSACTION_ID, TDISPOSITION_FTD.TRANSACTION_DATE_FTD) AS LOSSPAID_IAT, dbo.fn_g_NetAlloc_PII(INCOME_PII.RELATION_ID,
INCOME_PII.TRANSACTION_ID, TDISPOSITION_FTD.TRANSACTION_ID) AS NETALLOC_IAT, dbo.fn_g_DIST_PII(INCOME_PII.RELATION_ID,
INCOME_PII.TRANSACTION_ID, TDISPOSITION_FTD.TRANSACTION_ID) AS DIST_IAT,
TDISPOSITION_FTD.DISPOSITIONPCT_FTD AS [DISPOSITION%_IAT],
TINV.TINV_INCGEN_I * TDISPOSITION_FTD.TRANCHEPCT_FTD AS [TRANCHE%_IAT],
TINV.TINV_INCGEN_I * TDISPOSITION_FTD.TRANCHEPCT_FTD / RBASE.BASE * TDISPOSITION_FTD.DISPOSITIONPCT_FTD AS [WEIGHTEDDISPOSITION%_IAT],
dbo.fnGetLossAlloc(TDISPOSITION_FTD.TRANSACTION_ID, INCOME_PII.TRANSACTION_ID, INCOME_FTI.TRANCHE_ID, INCOME_PII.RELATION_ID)
* TDISPOSITION_FTD.DISPOSITIONPCT_FTD AS LOSSPAID_DISPOSED_IAT, dbo.fnGetIncomeAlloc(TDISPOSITION_FTD.TRANSACTION_ID,
INCOME_PII.TRANSACTION_ID, INCOME_FTI.TRANCHE_ID, INCOME_PII.RELATION_ID)
* TDISPOSITION_FTD.DISPOSITIONPCT_FTD AS NETALLOC_DISPOSED_IAT, dbo.fnGetDistAlloc(TDISPOSITION_FTD.TRANSACTION_ID,
INCOME_PII.TRANSACTION_ID, INCOME_FTI.TRANCHE_ID, INCOME_PII.RELATION_ID)
* TDISPOSITION_FTD.DISPOSITIONPCT_FTD AS DIST_DISPOSED_IAT, INCOME_FTI.TRANSACTION_DATE_FTI AS INCOME_DATE_IAT,
TDISPOSITION_FTD.TRANSACTION_DATE_FTD AS TRANSACTION_DATE_IAT, GETDATE() AS UPDATE_DATE_IAT
FROM INCOME_FTI INNER JOIN
TDISPOSITION_FTD ON INCOME_FTI.TRANCHE_ID = TDISPOSITION_FTD.TRANCHE_ID INNER JOIN
INCOME_PII ON INCOME_FTI.TRANSACTION_ID = INCOME_PII.TRANSACTION_ID INNER JOIN
TINV ON TDISPOSITION_FTD.TRANCHE_ID = TINV.TRANCHE_ID INNER JOIN
RBASE ON INCOME_FTI.TRANSACTION_ID = RBASE.TRANSACTION_ID
WHERE (TDISPOSITION_FTD.TRANSACTION_ID = 3830) AND (INCOME_PII.TRANSACTION_ID < 3830)
ORDER BY TDISPOSITION_FTD.TRANSACTION_ID
END

Could you run a test where you replace all the function calls (fnGetLossAlloc etc.) with static numeric values?

When I SET STATISTICS IO ON, I get this that stands out. How can I reduce the scan count and # of logical reads? Table ‘DAUXTRNCH_INCOME_IAT’. Scan count 31291, logical reads 109880, physical reads 0, read-ahead reads 0.

Ok, I may be making progress…Wtg to hear back from the developer.
I added a composite clustered index on 4 columns (which together make a unique row) and reran the procedure: Table ‘DAUXTRNCH_INCOME_IAT’. Scan count 0, logical reads 91324, physical reads 0, read-ahead reads 0.

]]>