November 19, 2015 at 10:28 am
I have two almost identical queries.
IDS INSERT_DML_FRAGMENTS.txt and GMS2 INSERT_DML_FRAGMENTS.txt
The only difference is in WHERE clause (few places).
IDS INSERT: ON ( CLIENT_SHARED.source_code = 'IDS' . . .
GMS2 INSERT: ON ( CLIENT_SHARED.source_code = 'GMS2' . . .
IDS INSERT runs 2 minutes
GMS2 INSERT runs 40 minutes!
After some testing I found out that the real slow down occurs
during second INSERT INTO Temp table.
--40 min!
SELECT DISTINCT CLIENT_SHARED.source_code,
CLIENT_SHARED.customer_number
INTO #common_i_ss_party_all_systems_current_valid_main_party
I anticipate questions about CROSS joins like:
JOIN fatca_stg.r_enum_ref ACCT_LGL_ENT
ON ( ACCT_LGL_ENT.set_name = 'LEGAL ENTITY'
AND ACCT_LGL_ENT.description IN (SELECT [filter_string_value]
FROM
It is ugly. Agree.
But still why IDS INSERT runs 2 minutes vs 40 minutes for GMS2?
Probably the root cause in not in CROSS join.
Unfortunately changing the code is almost impossible...
It is being generated dynamically.
Too risky to change the code..
My option at this time is modifying indexes.
Biggest tables in that second INSERT block are:
fatca_stg.s_client_shared (80 million)
fatca_stg.s_client_account_association_shared (83 million)
fatca_stg.s_account_shared (82 million)
DDL attached.
Query attached (changed extension to .TXT)
Execution Plans will be attached in the next Post .
November 19, 2015 at 10:30 am
Execution Plans attached as promised...
November 19, 2015 at 2:40 pm
Have you tried adding the index on S_CLIENT_ACCOUNT_ASSOCIATION_SHARED that SQL suggests? The query is currently doing a clustered index scan against that 80M row table. I would start there in your development environment and compare run times and IO with and without the suggested index.
USE [FATCA]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [FATCA_STG].[S_CLIENT_ACCOUNT_ASSOCIATION_SHARED] ([SOURCE_CODE],[BUS_PROC_DT])
INCLUDE ([CUSTOMER_NUMBER],[ACCOUNT_IDENTIFIER])
GO
November 19, 2015 at 8:32 pm
What a surprise!
As soon as I remove DISTINCT from GMS2 INSERT (second insert into temp table)
it runs 10 seconds !
And it inserts 20,955 rows.
How DISTINCT can increase execution time from 10 seconds to 40 minutes !?
Same positive effect when I use table variable instead of regular temp table.
With table variable it runs 9 seconds!
So is it a problem with DISTINCT statement or temp table?
If I don't do INSERT INTO #temp, just SELECT DISTINCT . .
It runs 39 seconds.
I think lot of things lead to temp table issues..
Am I correct ?
November 19, 2015 at 8:43 pm
Missing Index is only suggested in IDS INSERT plan.
It runs fast anyway. Problem is with GMS2 INSERT query.
There are no suggestions in GMS2 INSERT plan.
i tried this index before. No impact. I removed it.
November 20, 2015 at 9:42 am
is Fc_hashbytes_sha1 a User defined Scalar function?
Also, do you need this?
WITH (INDEX(idx_unique_t_etl_xml_xref_uuid))
Also, this in a join condition cannot be good.
[FATCA_TDS].[Fc_hashbytes_sha1](UPPER(ISNULL(LTRIM(RTRIM(PARTY.source_code)), 'UNKNOWN') + '|' + ISNULL(LTRIM(RTRIM(PARTY.customer_number))
Is your collation such that you need to do upper case?
Maybe it is better to clean the data on the table instead of in the query.
November 20, 2015 at 12:59 pm
MadAdmin,
First of all, I updated attached scripts.
Made them smaller to narrow down the problem.
I agree with all your concerns.
Join on a function, TRIM, etc. - bad ideas.
The problem is.
My hands are tight.
The code is generated dynamically at runtime
from SP_EXEC_WORKFLOW recursive stor proc.
SQL is stored in a table TRANSFORM_DML in columns like
(WITH, SELECT,FROM,WHERE).
It is very hard to find out what exactly record needs to be changed.
This ETL will be discontinued in 4 months.
Management does not want to touch the code and
make modifications.
So, long story short.
Treat it as a black box and you can only
try to improve performance using indexes, RAM, CPU,
TempDB configuration, etc.
If nothing helps , this will be escalated
and if i get approval, i will change the code.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply