IDS vs GMS2

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

  • Execution Plans attached as promised...

  • 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

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

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

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

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • 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