Slow stored procedures after migration 2008 to 2016 SQL server

  • alexandermkd

    SSC Enthusiast

    Points: 137

    Dear,

    i have recently migrated database from SQL 2008R2 SP3 (WS 200R2 )to SQL2016 SP2 CU2 (WS 2012R2). Migration was made with backup restore method.

    Now we are facing with really slow performance of one stored procedure. New server is far more powerful that old one but sp is x3 slower that the old server.

    - Execution plan on both servers is same

    - I have left compatibility level to 100 and also made change to 130 but without success.

    - I have exec sp with recompile again no success with

    - Index and update statistics are up to date

    - Exec store procedure with recompile

    - I have add some trace flags which where recommend but without success

    - Changed cardinality estimator :

    ALTER  DATABASE  SCOPED CONFIGURATION SET  LEGACY_CARDINALITY_ESTIMATION = ON;
    GO

    ALTER  DATABASE  SCOPED CONFIGURATION SET  QUERY_OPTIMIZER_HOTFIXES = ON;
    GO

    ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING =OFF ;GO

    Thank you

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 18988

    alexandermkd - Tuesday, January 8, 2019 5:00 AM

    Dear,

    i have recently migrated database from SQL 2008R2 SP3 (WS 200R2 )to SQL2016 SP2 CU2 (WS 2012R2). Migration was made with backup restore method.

    Now we are facing with really slow performance of one stored procedure. New server is far more powerful that old one but sp is x3 slower that the old server.

    - Execution plan on both servers is same

    - I have left compatibility level to 100 and also made change to 130 but without success.

    - I have exec sp with recompile again no success with

    - Index and update statistics are up to date

    - Exec store procedure with recompile

    - I have add some trace flags which where recommend but without success

    - Changed cardinality estimator :

    ALTER  DATABASE  SCOPED CONFIGURATION SET  LEGACY_CARDINALITY_ESTIMATION = ON;
    GO

    ALTER  DATABASE  SCOPED CONFIGURATION SET  QUERY_OPTIMIZER_HOTFIXES = ON;
    GO

    ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING =OFF ;GO

    Thank you

    Can you share the execution plan with us to get some idea.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • alexandermkd

    SSC Enthusiast

    Points: 137

    Hi tnx for the fast response:

    old server:

    https://www.brentozar.com/pastetheplan/?id=Skq8MMzfV

    new server:

    https://www.brentozar.com/pastetheplan/?id=SkDhGGzf4

  • Jeff Moden

    SSC Guru

    Points: 994866

    It's probably the same thing we went through.  There's a different cardinality estimator that came into play in 2014 (IIRC).  There's also a Trace Flag you can set to continue to use the old one although the number escapes me just now.  Google for it.

    Also, understand that, someday, to old estimator will be deprecated and destroyed so, someday, you're going to have to fix code to use the new one.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • frederico_fonseca

    SSChampion

    Points: 14186

    do you have the explain plan of the proc that is being executed inside the code of the plans you supplied? TARIFF_SIM_MATRIX_3M is being executed on a loop and 
    both from new and old server. The plans you posted don't really give us useful info and are not the ones that make the process slow.

    As for compatibility...
    either leave at compatibility 100 or put it at 130 and use "ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;" 
    one or the other should be enough.
    Regarding server...

    what are the specs of both old and new server? cores, ram, max mem, maxdop, cost threshold.
    and are both physical or virtual, and if virtual what virtualization used (vmware, hyper-v)

  • alexandermkd

    SSC Enthusiast

    Points: 137

    Hi federico, i cannot reproduce the  actual exec plan because sp is almost endless  (it is generating tons of multiple execution query plans, also on the old server if i enable actual execution plan stored procedure is extreme slow :O 🙁 ). My main problem is the company which compiled this stored procedures (company is dead 🙂 ) so we dosent have any support.

  • frederico_fonseca

    SSChampion

    Points: 14186

    you can still do it although manually.

    look at table XVBS_LOG_3M and identify the offer_id that took the longest (you need to compare each record with the previous one to identify it)

    then do the explain plan (real, not estimate) for a single execution of
    exec TARIFF_SIM_MATRIX_3M 'offer_id'

    and I assume this is not an external sp and that you can get the source code for it - would most likely be useful if you could post that here also.

  • alexandermkd

    SSC Enthusiast

    Points: 137

    Hi federico,
    i have executed manualy EXEC TARIFF_SIM_MATRIX_3M 1; (with parameter 1) and both executions plan are same.
    https://drive.google.com/file/d/1QEcBqr0RoQmLUp9GNeR3zFPgYm9GKPVW/view?usp=sharing.

    execution for stored procedure is :
    old server 1 min and 30 sec new server 12 min.

    Bellow i are my database settings:

  • Jeff Moden

    SSC Guru

    Points: 994866

    alexandermkd - Thursday, January 10, 2019 6:32 AM

    Hi federico,
    i have executed manualy EXEC TARIFF_SIM_MATRIX_3M 1; (with parameter 1) and both executions plan are same.
    https://drive.google.com/file/d/1QEcBqr0RoQmLUp9GNeR3zFPgYm9GKPVW/view?usp=sharing.

    execution for stored procedure is :
    old server 1 min and 30 sec new server 12 min.

    Bellow i are my database settings:

    You're not listening. 😉  Problems like these are known issues during an upgrade because of the new cardinality estimator.  At least give the Trace Flag that I spoke of a couple of posts ago a shot.  That will also tell you more about what to look for to fix the code if you want to turn the trace flag off.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Johan Bijnens

    SSC Guru

    Points: 134265

    did you perform full database maintenance after the upgrade to the new engine ?

    You've seen the "missing index", right ?

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • frederico_fonseca

    SSChampion

    Points: 14186

    @jeff - the OP has already tried the some trace flags and also had the legacy configuration turned on .
    ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON is the same as enabling TF 9481 or keeping the db on compatibility mode < 120 (it is set to 100 on this case)
    This can also be seen on the plan CardinalityEstimationModelVersion="70"
    so the issue may not be related to that, at least on this particular DB. But if dbo.elast udf is referring to another db it may be due to it also)
    I assume the above TF is the one you were referring to.

    @alexandermkd can you give us the code for both this proc and udf dbo.elast - according to the plan either this udf (or another one not visible) are the culprits of the slowness. This is mentioned at least 23 times on the code (less executions)
    UDF cpu time on new server was 93% of the overall time.

    overall proc TARIFF_SIM_MATRIX_3M should be rewritten - would not pass on my standards (and on those of many here I presume)

  • alexandermkd

    SSC Enthusiast

    Points: 137

    ALZDBA - Thursday, January 10, 2019 8:26 AM

    did you perform full database maintenance after the upgrade to the new engine ?

    You've seen the "missing index", right ?

    yes, tried with missing index too (table has 96 rows so index dosent help much)

  • alexandermkd

    SSC Enthusiast

    Points: 137

    frederico_fonseca - Thursday, January 10, 2019 10:59 AM

    @jeff - the OP has already tried the some trace flags and also had the legacy configuration turned on .
    ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON is the same as enabling TF 9481 or keeping the db on compatibility mode < 120 (it is set to 100 on this case)
    This can also be seen on the plan CardinalityEstimationModelVersion="70"
    so the issue may not be related to that, at least on this particular DB. But if dbo.elast udf is referring to another db it may be due to it also)
    I assume the above TF is the one you were referring to.

    @alexandermkd can you give us the code for both this proc and udf dbo.elast - according to the plan either this udf (or another one not visible) are the culprits of the slowness. This is mentioned at least 23 times on the code (less executions)
    UDF cpu time on new server was 93% of the overall time.

    overall proc TARIFF_SIM_MATRIX_3M should be rewritten - would not pass on my standards (and on those of many here I presume)

     USE [vbs]
    GO
    /****** Object: StoredProcedure [dbo].[XVBS4_SIMULATOR_3M]  Script Date: 11.01.2019 10:00:07 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[XVBS4_SIMULATOR_3M] AS
    INSERT INTO XVBS_LOG_3M VALUES (getdate (),'XVBS_SIMULATOR_3M ','XVBS_SIMULATOR_3M - proc_start'+ case when (select elasticity from XVBS_elasticity_INPUT e where e.id=1) = 1 then ' - ELASTICITY-ON' else ' - ELASTICITY-OFF' end )        --***LOG***
    DECLARE @script varchar (255)
    DECLARE @intFlag INT
    SET @intFlag = 1

    --create empty table for inserting, if already exist, recreate
    IF object_id('[vbs].[dbo].[XVBS_SIMULATION_3M_2]') is not null
      BEGIN
            DROP TABLE XVBS_SIMULATION_3M_2
            CREATE TABLE XVBS_SIMULATION_3M_2
                (
                Subscription_id int,
                month_key int,
                msisdn varchar (255),
                account_type varchar (255),
                RPLN varchar (255),
                RPLN_name varchar (max),
                RPLN_desc varchar (max),
                ELIGIBILITY_GROUP varchar (255),
                DEFAULT_ELIGIBILITY varchar (255),
                RPLN_account_type varchar (255),
                offer_ID int,
                tariff_ID varchar (255),
                promo_ID_1 varchar (255),
                promo_ID_2 varchar (255),
                promo_ID_3 varchar (255),
                promo_ID_4 varchar (255),
                promo_ID_5 varchar (255),
                ARPU_WO_IC decimal (15,4),
                CM decimal (15,4),
                MONTHLY_FEE decimal (15,4),
                HW_FLAG int,
                REG_SUBSIDY decimal (15,4),
    --            ADDITIONAL_SUBSIDY decimal (15,4),
                HW_SUGGESTION varchar (255),
                SMS_PAID_PACK int,
                DATA_PAID_PACK int,
                DATA_DATETIME datetime
                )
        END
    ELSE
      CREATE TABLE XVBS_SIMULATION_3M_2
                (
                Subscription_id int,
                month_key int,
                msisdn varchar (255),
                account_type varchar (255),
                RPLN varchar (255),
                RPLN_name varchar (max),
                RPLN_desc varchar (max),
                ELIGIBILITY_GROUP varchar (255),
                DEFAULT_ELIGIBILITY varchar (255),
                RPLN_account_type varchar (255),
                offer_ID int,
                tariff_ID varchar (255),
                promo_ID_1 varchar (255),
                promo_ID_2 varchar (255),
                promo_ID_3 varchar (255),
                promo_ID_4 varchar (255),
                promo_ID_5 varchar (255),
                ARPU_WO_IC decimal (15,4),
                CM decimal (15,4),
                MONTHLY_FEE decimal (15,4),
                HW_FLAG int,
                REG_SUBSIDY decimal (15,4),
    --            ADDITIONAL_SUBSIDY decimal (15,4),
                HW_SUGGESTION varchar (255),
                SMS_PAID_PACK int,
                DATA_PAID_PACK int,
                DATA_DATETIME datetime
                )

    --loads and runs simulation on every offer from list of the offers (XVBS_RPLN_TARIFF_MAPPING_3M) where run_simulation=1 and STATUS='ACTIVE'
    WHILE (@intFlag <=(select count (*) from XVBS_RPLN_TARIFF_MAPPING_3M where run_simulation=1 and STATUS='ACTIVE') )
    BEGIN
        SET @script='exec TARIFF_SIM_MATRIX_3M '''+convert(varchar,(select offer_id from (select ROW_NUMBER () OVER (ORDER BY offer_id) as ROWNUM, x.* from ( select * from XVBS_RPLN_TARIFF_MAPPING_3M where run_simulation=1 and STATUS='ACTIVE') x) a where a.ROWNUM=@intFlag) )
                    +''''
        SET @intFlag = @intFlag + 1
        EXEC (@script)
        --PRINT (@script)
    INSERT INTO XVBS_LOG_3M VALUES (getdate (),'TARIFF_SIM_MATRIX_3M '+convert(varchar,(select offer_id from (select ROW_NUMBER () OVER (ORDER BY offer_id) as ROWNUM, x.* from ( select * from XVBS_RPLN_TARIFF_MAPPING_3M where run_simulation=1 and STATUS='ACTIVE') x) a where a.ROWNUM=@intFlag-1) ), (select rpln from (select ROW_NUMBER () OVER (ORDER BY offer_id) as ROWNUM, x.* from ( select * from XVBS_RPLN_TARIFF_MAPPING_3M where run_simulation=1 and STATUS='ACTIVE') x) a where a.ROWNUM=@intFlag-1)+' - offer_calculated' )        --***LOG***
    END

    create index XVBS_SIMULATION_3M_2_idx1 on XVBS_SIMULATION_3M_2 (subscription_id);   --1min
    create index XVBS_SIMULATION_3M_2_idx2 on XVBS_SIMULATION_3M_2 (msisdn);
    INSERT INTO XVBS_LOG_3M VALUES (getdate (),'XVBS4_SIMULATOR_3M ','XVBS4_SIMULATOR_3M - proc_end' )        --***LOG***

    USE [vbs]
    GO
    /****** Object: StoredProcedure [dbo].[TARIFF_SIM_MATRIX_3M]  Script Date: 11.01.2019 10:03:37 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[TARIFF_SIM_MATRIX_3M] @OID int AS
    DECLARE @VOICE_IC_COST decimal (15,4) DECLARE @SMS_IC_COST decimal (15,4) DECLARE @RPLNGRP nvarchar(25) DECLARE @ACCOUNT_TYPE varchar (15) DECLARE @DISC_MF decimal (15,4)
    DECLARE @SQL_CODE varchar (max) DECLARE @PROC_COMMAND varchar (max) DECLARE @MF_TOTAL decimal (15,4) DECLARE @MF_RPLN decimal (15,4) DECLARE @HW_FLAG int DECLARE @HW_SUGGESTION nvarchar (255) DECLARE @SMS_PAID_PACK int DECLARE @DATA_PAID_PACK int
    DECLARE @ONNET_STAR_AFT nvarchar(25) DECLARE @OFFNET_STAR_AFT nvarchar(25) DECLARE @INTERNAT_STAR_AFT nvarchar(25) DECLARE @SMS_STAR_AFT nvarchar(25) DECLARE @DATA_STAR_AFT nvarchar(25)
    SET @VOICE_IC_COST = (select VOICE_IC_COST from XVBS_IC_cost_INPUT)        SET    @SMS_IC_COST =    (select SMS_IC_COST from XVBS_IC_cost_INPUT)
    SET @RPLNGRP = (select ELAST_RPLN_GRP from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )            --expected_tariff_group: 'FREEP' Free++, Free+ | 'SMART' - smart '90,'00 | 'SMARTXLS' - smart xs-xl | 'FREE' - Free stare
    SET @ACCOUNT_TYPE = (select ACCOUNT_TYPE from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
    SET @DISC_MF = (select MF_Discount from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @SQL_CODE = (select SQL_CODE from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @MF_TOTAL = (select MF_TOTAL_inclVAT from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @MF_RPLN = (select MF_RPLN_inclVAT from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @HW_FLAG = (select HW_FLAG from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @HW_SUGGESTION = (select HW_SUGGESTION from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @SMS_PAID_PACK = (select SMS_PAID_PACK from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @DATA_PAID_PACK = (select DATA_PAID_PACK from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @ONNET_STAR_AFT= (select case when isnull(t.onnet_stars,0)>=isnull(p1.onnet_stars,0) and isnull(t.onnet_stars,0) >=isnull(p2.onnet_stars,0) and isnull(t.onnet_stars,0) >=isnull(p3.onnet_stars,0) and isnull(t.onnet_stars,0) >=isnull(p4.onnet_stars,0) and isnull(t.onnet_stars,0)>=isnull(p5.onnet_stars,0) then isnull(t.onnet_stars,0)
                            when isnull(p1.onnet_stars,0) >=isnull(p2.onnet_stars,0) and isnull(p1.onnet_stars,0) >=isnull(p3.onnet_stars,0) and isnull(p1.onnet_stars,0) >=isnull(p4.onnet_stars,0) and isnull(p1.onnet_stars,0)>=isnull(p5.onnet_stars,0) then isnull(p1.onnet_stars,0)
                            when isnull(p2.onnet_stars,0) >=isnull(p3.onnet_stars,0) and isnull(p2.onnet_stars,0) >=isnull(p4.onnet_stars,0) and isnull(p2.onnet_stars,0)>=isnull(p5.onnet_stars,0) then isnull(p2.onnet_stars,0)
                            when isnull(p3.onnet_stars,0) >=isnull(p4.onnet_stars,0) and isnull(p3.onnet_stars,0)>=isnull(p5.onnet_stars,0) then isnull(p3.onnet_stars,0)
                            when isnull(p4.onnet_stars,0)>=isnull(p5.onnet_stars,0) then isnull(p4.onnet_stars,0)
                            else isnull(p4.onnet_stars,0) end
                from epi_tariffs t
                     left outer join epi_promotions p1 on p1.id = (select PROMO_ID_1 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p2 on p2.id = (select PROMO_ID_2 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p3 on p3.id = (select PROMO_ID_3 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p4 on p4.id = (select PROMO_ID_4 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p5 on p5.id = (select PROMO_ID_5 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                where t.TARIFF_CODE = (select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID ) )
    SET @OFFNET_STAR_AFT= (select case when isnull(t.offnet_stars,0)>=isnull(p1.offnet_stars,0) and isnull(t.offnet_stars,0) >=isnull(p2.offnet_stars,0) and isnull(t.offnet_stars,0) >=isnull(p3.offnet_stars,0) and isnull(t.offnet_stars,0) >=isnull(p4.offnet_stars,0) and isnull(t.offnet_stars,0)>=isnull(p5.offnet_stars,0) then isnull(t.offnet_stars,0)
                            when isnull(p1.offnet_stars,0) >=isnull(p2.offnet_stars,0) and isnull(p1.offnet_stars,0) >=isnull(p3.offnet_stars,0) and isnull(p1.offnet_stars,0) >=isnull(p4.offnet_stars,0) and isnull(p1.offnet_stars,0)>=isnull(p5.offnet_stars,0) then isnull(p1.offnet_stars,0)
                            when isnull(p2.offnet_stars,0) >=isnull(p3.offnet_stars,0) and isnull(p2.offnet_stars,0) >=isnull(p4.offnet_stars,0) and isnull(p2.offnet_stars,0)>=isnull(p5.offnet_stars,0) then isnull(p2.offnet_stars,0)
                            when isnull(p3.offnet_stars,0) >=isnull(p4.offnet_stars,0) and isnull(p3.offnet_stars,0)>=isnull(p5.offnet_stars,0) then isnull(p3.offnet_stars,0)
                            when isnull(p4.offnet_stars,0)>=isnull(p5.offnet_stars,0) then isnull(p4.offnet_stars,0)
                            else isnull(p4.offnet_stars,0) end
                from epi_tariffs t
                     left outer join epi_promotions p1 on p1.id = (select PROMO_ID_1 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p2 on p2.id = (select PROMO_ID_2 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p3 on p3.id = (select PROMO_ID_3 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p4 on p4.id = (select PROMO_ID_4 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p5 on p5.id = (select PROMO_ID_5 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                where t.TARIFF_CODE = (select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID ) )
    SET @INTERNAT_STAR_AFT= (select case when isnull(t.internat_stars,0)>=isnull(p1.internat_stars,0) and isnull(t.internat_stars,0) >=isnull(p2.internat_stars,0) and isnull(t.internat_stars,0) >=isnull(p3.internat_stars,0) and isnull(t.internat_stars,0) >=isnull(p4.internat_stars,0) and isnull(t.internat_stars,0)>=isnull(p5.internat_stars,0) then isnull(t.internat_stars,0)
                            when isnull(p1.internat_stars,0) >=isnull(p2.internat_stars,0) and isnull(p1.internat_stars,0) >=isnull(p3.internat_stars,0) and isnull(p1.internat_stars,0) >=isnull(p4.internat_stars,0) and isnull(p1.internat_stars,0)>=isnull(p5.internat_stars,0) then isnull(p1.internat_stars,0)
                            when isnull(p2.internat_stars,0) >=isnull(p3.internat_stars,0) and isnull(p2.internat_stars,0) >=isnull(p4.internat_stars,0) and isnull(p2.internat_stars,0)>=isnull(p5.internat_stars,0) then isnull(p2.internat_stars,0)
                            when isnull(p3.internat_stars,0) >=isnull(p4.internat_stars,0) and isnull(p3.internat_stars,0)>=isnull(p5.internat_stars,0) then isnull(p3.internat_stars,0)
                            when isnull(p4.internat_stars,0)>=isnull(p5.internat_stars,0) then isnull(p4.internat_stars,0)
                            else isnull(p4.internat_stars,0) end
                from epi_tariffs t
                     left outer join epi_promotions p1 on p1.id = (select PROMO_ID_1 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p2 on p2.id = (select PROMO_ID_2 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p3 on p3.id = (select PROMO_ID_3 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p4 on p4.id = (select PROMO_ID_4 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p5 on p5.id = (select PROMO_ID_5 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                where t.TARIFF_CODE = (select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID ) )
    SET @SMS_STAR_AFT= (select case when isnull(t.sms_stars,0)>=isnull(p1.sms_stars,0) and isnull(t.sms_stars,0) >=isnull(p2.sms_stars,0) and isnull(t.sms_stars,0) >=isnull(p3.sms_stars,0) and isnull(t.sms_stars,0) >=isnull(p4.sms_stars,0) and isnull(t.sms_stars,0)>=isnull(p5.sms_stars,0) then isnull(t.sms_stars,0)
                            when isnull(p1.sms_stars,0) >=isnull(p2.sms_stars,0) and isnull(p1.sms_stars,0) >=isnull(p3.sms_stars,0) and isnull(p1.sms_stars,0) >=isnull(p4.sms_stars,0) and isnull(p1.sms_stars,0)>=isnull(p5.sms_stars,0) then isnull(p1.sms_stars,0)
                            when isnull(p2.sms_stars,0) >=isnull(p3.sms_stars,0) and isnull(p2.sms_stars,0) >=isnull(p4.sms_stars,0) and isnull(p2.sms_stars,0)>=isnull(p5.sms_stars,0) then isnull(p2.sms_stars,0)
                            when isnull(p3.sms_stars,0) >=isnull(p4.sms_stars,0) and isnull(p3.sms_stars,0)>=isnull(p5.sms_stars,0) then isnull(p3.sms_stars,0)
                            when isnull(p4.sms_stars,0)>=isnull(p5.sms_stars,0) then isnull(p4.sms_stars,0)
                            else isnull(p4.sms_stars,0) end
                from epi_tariffs t
                     left outer join epi_promotions p1 on p1.id = (select PROMO_ID_1 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p2 on p2.id = (select PROMO_ID_2 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p3 on p3.id = (select PROMO_ID_3 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p4 on p4.id = (select PROMO_ID_4 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p5 on p5.id = (select PROMO_ID_5 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                where t.TARIFF_CODE = (select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID ) )
    SET @DATA_STAR_AFT= (select case when isnull(t.data_stars,0)>=isnull(p1.data_stars,0) and isnull(t.data_stars,0) >=isnull(p2.data_stars,0) and isnull(t.data_stars,0) >=isnull(p3.data_stars,0) and isnull(t.data_stars,0) >=isnull(p4.data_stars,0) and isnull(t.data_stars,0)>=isnull(p5.data_stars,0) then isnull(t.data_stars,0)
                            when isnull(p1.data_stars,0) >=isnull(p2.data_stars,0) and isnull(p1.data_stars,0) >=isnull(p3.data_stars,0) and isnull(p1.data_stars,0) >=isnull(p4.data_stars,0) and isnull(p1.data_stars,0)>=isnull(p5.data_stars,0) then isnull(p1.data_stars,0)
                            when isnull(p2.data_stars,0) >=isnull(p3.data_stars,0) and isnull(p2.data_stars,0) >=isnull(p4.data_stars,0) and isnull(p2.data_stars,0)>=isnull(p5.data_stars,0) then isnull(p2.data_stars,0)
                            when isnull(p3.data_stars,0) >=isnull(p4.data_stars,0) and isnull(p3.data_stars,0)>=isnull(p5.data_stars,0) then isnull(p3.data_stars,0)
                            when isnull(p4.data_stars,0)>=isnull(p5.data_stars,0) then isnull(p4.data_stars,0)
                            else isnull(p4.data_stars,0) end
                from epi_tariffs t
                     left outer join epi_promotions p1 on p1.id = (select PROMO_ID_1 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p2 on p2.id = (select PROMO_ID_2 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p3 on p3.id = (select PROMO_ID_3 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p4 on p4.id = (select PROMO_ID_4 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p5 on p5.id = (select PROMO_ID_5 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                where t.TARIFF_CODE = (select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID ) )

    SET @PROC_COMMAND='
    DECLARE @RPLNGRP nvarchar(25)
    DECLARE @ONNET_A nvarchar(25) DECLARE @OFFNET_A nvarchar(25) DECLARE @INTERNAT_A nvarchar(25) DECLARE @SMS_A nvarchar(25) DECLARE @DATA_A nvarchar(25)
    DECLARE @ADDITIONAL_SUBSIDY decimal (15,4)
    SET @RPLNGRP = (select ELAST_RPLN_GRP from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' )
    SET @ONNET_A = '+@ONNET_STAR_AFT+'
    SET @OFFNET_A = '+@OFFNET_STAR_AFT+'
    SET @INTERNAT_A = '+@INTERNAT_STAR_AFT+'
    SET @SMS_A = '+@SMS_STAR_AFT+'
    SET @DATA_A = '+@DATA_STAR_AFT+'
    SET @ADDITIONAL_SUBSIDY= (select isnull(ADDITIONAL_SUBSIDY,0) from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' )
    INSERT INTO XVBS_SIMULATION_3M_2
    SELECT
    Subscription_id, month_key, msisdn, account_type,
    (select RPLN from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) RPLN,
    (select RPLN_name from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) RPLN_name,
    (select RPLN_desc from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) RPLN_desc,
    (select ELIGIBILITY_GROUP from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) ELIGIBILITY_GROUP,
    (select DEFAULT_ELIGIBILITY from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) DEFAULT_ELIGIBILITY,
    (select ACCOUNT_TYPE from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) RPLN_ACCOUNT_TYPE,
    (select offer_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) offer_ID,
    (select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) tariff_ID,
    (select promo_ID_1 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) promo_ID_1,
    (select promo_ID_2 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) promo_ID_2,
    (select promo_ID_3 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) promo_ID_3,
    (select promo_ID_4 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) promo_ID_4,
    (select promo_ID_5 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) promo_ID_5,
    '+@SQL_CODE+'-Round (('+convert (varchar, @DISC_MF)+')*'+convert (varchar, (@MF_RPLN/1.18))+',2) as ARPU_WO_IC,                    -- ARPU_wo_IC - discount on MF_RPLN
    '+@SQL_CODE+'-Round (('+convert (varchar, @DISC_MF)+')*'+convert (varchar, (@MF_RPLN/1.18))+',2) +
    (IC_revenue - IC_cost - (a.MOU_RND_OFFNET*(dbo.elast2(''OFFNET'',a.OFFNET_STARS,@OFFNET_A)-1)*'+convert (varchar, @VOICE_IC_COST)+') -
    (a.CNT_SMS_OFFNET*(dbo.elast2(''SMS'',a.SMS_STARS,@SMS_A)-1)*'+convert (varchar, @SMS_IC_COST)+')     ) -
    (case when '+convert (varchar(1), @HW_FLAG)+'=1
         then ISNULL((select SUBSIDY from epi_tariffs where tariff_code=(select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' )),0)/24 + @ADDITIONAL_SUBSIDY/24                    --pocet mesiacov viazanosti
         else 0 end ) as CM,                                                        -- CM
    '+convert (varchar, round (@MF_TOTAL/1.18,2))+' MONTHLY_FEE,                                                                                                        -- Monthly_fee
    '+convert (varchar(1), @HW_FLAG)+' HW_FLAG,
    ISNULL((select SUBSIDY from epi_tariffs where tariff_code=(select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' )),0) REG_SUBSIDY,
    '''+convert (nvarchar(255), isnull(@HW_SUGGESTION,'/'))+''' HW_SUGGESTION,
    '+convert (varchar(1), @SMS_PAID_PACK)+' SMS_PAID_PACK,
    '+convert (varchar(1), @DATA_PAID_PACK)+' DATA_PAID_PACK,
    DATA_DATETIME                                                                                                                            -- HW_FLAG
    from XVBS_SIMULATION_3M_1 a
    where 1=1
        AND 1=case when upper('''+@ACCOUNT_TYPE+''') in (''R'',''RES'',''RESIDENT'',''RESIDENTIAL'') and a.ACCOUNT_TYPE=''ENTERPRISE'' then 0
                   when upper('''+@ACCOUNT_TYPE+''') in (''B'',''BUS'',''BUSINESS'',''E'',''ENTERPRISE'') and a.ACCOUNT_TYPE=''RESIDENTIAL'' then 0
                  else 1 end
    '
    EXEC (@proc_command)
    --select (@proc_command)
    --print (@proc_command)

    USE [vbs]

    GO
    /****** Object: UserDefinedFunction [dbo].[elast]  Script Date: 11.01.2019 10:04:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER FUNCTION [dbo].[elast] (@TYPE nvarchar(2), @tariff_act nvarchar(25), @tariff_exp nvarchar(25))
    RETURNS decimal(15,4)
    WITH EXECUTE AS CALLER
    AS
    BEGIN
    declare @Return decimal(15,4)
    DECLARE @SMART_FREEP_ON decimal (15,4)
    DECLARE @SMART_SMART_ON decimal (15,4)
    DECLARE @SMART_SMARTXLS_ON decimal (15,4)
    DECLARE @SMART_FREE_ON decimal (15,4)
    DECLARE @SMART_FREEP_OF decimal (15,4)
    DECLARE @SMART_SMART_OF decimal (15,4)
    DECLARE @SMART_SMARTXLS_OF decimal (15,4)
    DECLARE @SMART_FREE_OF decimal (15,4)
    DECLARE @SMART_FREEP_SM decimal (15,4)
    DECLARE @SMART_SMART_SM decimal (15,4)
    DECLARE @SMART_SMARTXLS_SM decimal (15,4)
    DECLARE @SMART_FREE_SM decimal (15,4)

    SET        @SMART_FREEP_ON =1                --elasticity for voice ONnet
    SET        @SMART_SMART_ON =1                    --elasticity for voice ONnet
    SET        @SMART_SMARTXLS_ON=1                --elasticity for voice ONnet
    SET        @SMART_FREE_ON =1                    --elasticity for voice ONnet
    SET        @SMART_FREEP_OF =1                --elasticity for voice OFFnet
    SET        @SMART_SMART_OF =1                --elasticity for voice OFFnet
    SET        @SMART_SMARTXLS_OF=1                --elasticity for voice OFFnet
    SET        @SMART_FREE_OF =1                    --elasticity for voice OFFnet
    SET        @SMART_FREEP_SM =1                --elasticity for voice SMS
    SET        @SMART_SMART_SM =1                    --elasticity for voice SMS
    SET        @SMART_SMARTXLS_SM=1                --elasticity for voice SMS
    SET        @SMART_FREE_SM =1                    --elasticity for voice SMS

    select @return = case when lower(@tariff_act) like ('vip smart%') then ( case lower(@tariff_exp)when 'freep' then (case lower(@TYPE) when 'on' then @SMART_FREEP_ON when 'of' then @SMART_FREEP_OF when 'sm' then @SMART_FREEP_SM else 1 end )
                                                                                                    when 'smart' then (case lower(@TYPE) when 'on' then @SMART_SMART_ON when 'of' then @SMART_SMART_OF when 'sm' then @SMART_SMART_SM else 1 end )
                                                                                                    when 'smartxls' then (case lower(@TYPE) when 'on' then @SMART_SMARTXLS_ON when 'of' then @SMART_SMARTXLS_OF when 'sm' then @SMART_SMARTXLS_SM else 1 end )
                                                                                                    when 'free' then (case lower(@TYPE) when 'on' then @SMART_FREE_ON when 'of' then @SMART_FREE_OF when 'sm' then @SMART_FREE_SM else 1 end )
                                                                         else 1 end
                                                                        )
                                             else 1 end
    return @Return
    END

    USE [vbs]
    GO
    /****** Object: UserDefinedFunction [dbo].[elast2]  Script Date: 11.01.2019 10:05:14 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER FUNCTION [dbo].[elast2] ( @SERVICE nvarchar(25), @BEF_STARS nvarchar (25), @AFT_STARS nvarchar (25) )
    RETURNS decimal(15,4)
    WITH EXECUTE AS CALLER
    AS
    BEGIN
    declare @Return decimal(15,4)
    SET @RETURN=1
    SET @RETURN= case when (select elasticity from XVBS_elasticity_INPUT e where e.id=1) = 1
                     then (select elasticity from XVBS_elasticity_INPUT e where e.service=@service and e.bef=isnull(@BEF_STARS,0) and e.aft=isnull(@AFT_STARS,0) )
                     else 1 end

    return @RETURN
    END

    @frederico_fonesca big tnx for the support

  • frederico_fonseca

    SSChampion

    Points: 14186

    @alexandermkd 
    the code for proc TARIFF_SIM_MATRIX_3M does not match that of the code being executed as per the explain plans you supplied.

    just as a snippet from the explain plans

      , (select promo_ID_4 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_ID = 1) promo_id_4

       , (select promo_ID_5 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_ID = 1) promo_id_5
       , round(
        (
        case when (a.MOU_RND_ONNET
          * dbo.elast('ON', Tariff_Model, @Rplngrp) - isnull(a.MOU_Friend1, 0)
          * dbo.elast('ON', Tariff_Model, @Rplngrp) + a.MOU_RND_OFFNET
          * dbo.elast('OF', Tariff_Model, @Rplngrp) + (case
         when a.MOU_RND_Fix <= 10
           then 0
         else a.MOU_RND_Fix
         end) + a.MOU_RND_USSD <= 100)
         then 0
        else (a.MOU_RND_ONNET
          * dbo.elast('ON', Tariff_Model, @Rplngrp) - isnull(a.MOU_Friend1, 0)
          * dbo.elast('ON', Tariff_Model, @Rplngrp) + a.MOU_RND_OFFNET
          * dbo.elast('OF', Tariff_Model, @Rplngrp) + (case
        when a.MOU_RND_Fix <= 10
         then 0
        else a.MOU_RND_Fix
        end) + a.MOU_RND_USSD - 100) * 7.9
        end +   --100min+10add  --price allnet

    so if the proc code is wrong I also have to query if the code for the 2 functions is the one being executed.
    Please go back and ensure you get the correct code for both proc and functions.

  • alexandermkd

    SSC Enthusiast

    Points: 137

    frederico_fonseca - Saturday, January 12, 2019 3:31 AM

    @alexandermkd 
    the code for proc TARIFF_SIM_MATRIX_3M does not match that of the code being executed as per the explain plans you supplied.

    just as a snippet from the explain plans

      , (select promo_ID_4 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_ID = 1) promo_id_4

       , (select promo_ID_5 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_ID = 1) promo_id_5
       , round(
        (
        case when (a.MOU_RND_ONNET
          * dbo.elast('ON', Tariff_Model, @Rplngrp) - isnull(a.MOU_Friend1, 0)
          * dbo.elast('ON', Tariff_Model, @Rplngrp) + a.MOU_RND_OFFNET
          * dbo.elast('OF', Tariff_Model, @Rplngrp) + (case
         when a.MOU_RND_Fix <= 10
           then 0
         else a.MOU_RND_Fix
         end) + a.MOU_RND_USSD <= 100)
         then 0
        else (a.MOU_RND_ONNET
          * dbo.elast('ON', Tariff_Model, @Rplngrp) - isnull(a.MOU_Friend1, 0)
          * dbo.elast('ON', Tariff_Model, @Rplngrp) + a.MOU_RND_OFFNET
          * dbo.elast('OF', Tariff_Model, @Rplngrp) + (case
        when a.MOU_RND_Fix <= 10
         then 0
        else a.MOU_RND_Fix
        end) + a.MOU_RND_USSD - 100) * 7.9
        end +   --100min+10add  --price allnet

    so if the proc code is wrong I also have to query if the code for the 2 functions is the one being executed.
    Please go back and ensure you get the correct code for both proc and functions.

    USE [vbs]
    GO
    /****** Object: StoredProcedure [dbo].[TARIFF_SIM_MATRIX_3M]  Script Date: 12.01.2019 18:14:00 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[TARIFF_SIM_MATRIX_3M] @OID int AS
    DECLARE @VOICE_IC_COST decimal (15,4) DECLARE @SMS_IC_COST decimal (15,4) DECLARE @RPLNGRP nvarchar(25) DECLARE @ACCOUNT_TYPE varchar (15) DECLARE @DISC_MF decimal (15,4)
    DECLARE @SQL_CODE varchar (max) DECLARE @PROC_COMMAND varchar (max) DECLARE @MF_TOTAL decimal (15,4) DECLARE @MF_RPLN decimal (15,4) DECLARE @HW_FLAG int DECLARE @HW_SUGGESTION nvarchar (255) DECLARE @SMS_PAID_PACK int DECLARE @DATA_PAID_PACK int
    DECLARE @ONNET_STAR_AFT nvarchar(25) DECLARE @OFFNET_STAR_AFT nvarchar(25) DECLARE @INTERNAT_STAR_AFT nvarchar(25) DECLARE @SMS_STAR_AFT nvarchar(25) DECLARE @DATA_STAR_AFT nvarchar(25)
    SET @VOICE_IC_COST = (select VOICE_IC_COST from XVBS_IC_cost_INPUT)        SET    @SMS_IC_COST =    (select SMS_IC_COST from XVBS_IC_cost_INPUT)
    SET @RPLNGRP = (select ELAST_RPLN_GRP from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )            --expected_tariff_group: 'FREEP' Free++, Free+ | 'SMART' - smart '90,'00 | 'SMARTXLS' - smart xs-xl | 'FREE' - Free stare
    SET @ACCOUNT_TYPE = (select ACCOUNT_TYPE from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
    SET @DISC_MF = (select MF_Discount from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @SQL_CODE = (select SQL_CODE from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @MF_TOTAL = (select MF_TOTAL_inclVAT from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @MF_RPLN = (select MF_RPLN_inclVAT from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @HW_FLAG = (select HW_FLAG from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @HW_SUGGESTION = (select HW_SUGGESTION from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @SMS_PAID_PACK = (select SMS_PAID_PACK from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @DATA_PAID_PACK = (select DATA_PAID_PACK from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID)
    SET @ONNET_STAR_AFT= (select case when isnull(t.onnet_stars,0)>=isnull(p1.onnet_stars,0) and isnull(t.onnet_stars,0) >=isnull(p2.onnet_stars,0) and isnull(t.onnet_stars,0) >=isnull(p3.onnet_stars,0) and isnull(t.onnet_stars,0) >=isnull(p4.onnet_stars,0) and isnull(t.onnet_stars,0)>=isnull(p5.onnet_stars,0) then isnull(t.onnet_stars,0)
                            when isnull(p1.onnet_stars,0) >=isnull(p2.onnet_stars,0) and isnull(p1.onnet_stars,0) >=isnull(p3.onnet_stars,0) and isnull(p1.onnet_stars,0) >=isnull(p4.onnet_stars,0) and isnull(p1.onnet_stars,0)>=isnull(p5.onnet_stars,0) then isnull(p1.onnet_stars,0)
                            when isnull(p2.onnet_stars,0) >=isnull(p3.onnet_stars,0) and isnull(p2.onnet_stars,0) >=isnull(p4.onnet_stars,0) and isnull(p2.onnet_stars,0)>=isnull(p5.onnet_stars,0) then isnull(p2.onnet_stars,0)
                            when isnull(p3.onnet_stars,0) >=isnull(p4.onnet_stars,0) and isnull(p3.onnet_stars,0)>=isnull(p5.onnet_stars,0) then isnull(p3.onnet_stars,0)
                            when isnull(p4.onnet_stars,0)>=isnull(p5.onnet_stars,0) then isnull(p4.onnet_stars,0)
                            else isnull(p4.onnet_stars,0) end
                from epi_tariffs t
                     left outer join epi_promotions p1 on p1.id = (select PROMO_ID_1 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p2 on p2.id = (select PROMO_ID_2 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p3 on p3.id = (select PROMO_ID_3 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p4 on p4.id = (select PROMO_ID_4 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p5 on p5.id = (select PROMO_ID_5 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                where t.TARIFF_CODE = (select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID ) )
    SET @OFFNET_STAR_AFT= (select case when isnull(t.offnet_stars,0)>=isnull(p1.offnet_stars,0) and isnull(t.offnet_stars,0) >=isnull(p2.offnet_stars,0) and isnull(t.offnet_stars,0) >=isnull(p3.offnet_stars,0) and isnull(t.offnet_stars,0) >=isnull(p4.offnet_stars,0) and isnull(t.offnet_stars,0)>=isnull(p5.offnet_stars,0) then isnull(t.offnet_stars,0)
                            when isnull(p1.offnet_stars,0) >=isnull(p2.offnet_stars,0) and isnull(p1.offnet_stars,0) >=isnull(p3.offnet_stars,0) and isnull(p1.offnet_stars,0) >=isnull(p4.offnet_stars,0) and isnull(p1.offnet_stars,0)>=isnull(p5.offnet_stars,0) then isnull(p1.offnet_stars,0)
                            when isnull(p2.offnet_stars,0) >=isnull(p3.offnet_stars,0) and isnull(p2.offnet_stars,0) >=isnull(p4.offnet_stars,0) and isnull(p2.offnet_stars,0)>=isnull(p5.offnet_stars,0) then isnull(p2.offnet_stars,0)
                            when isnull(p3.offnet_stars,0) >=isnull(p4.offnet_stars,0) and isnull(p3.offnet_stars,0)>=isnull(p5.offnet_stars,0) then isnull(p3.offnet_stars,0)
                            when isnull(p4.offnet_stars,0)>=isnull(p5.offnet_stars,0) then isnull(p4.offnet_stars,0)
                            else isnull(p4.offnet_stars,0) end
                from epi_tariffs t
                     left outer join epi_promotions p1 on p1.id = (select PROMO_ID_1 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p2 on p2.id = (select PROMO_ID_2 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p3 on p3.id = (select PROMO_ID_3 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p4 on p4.id = (select PROMO_ID_4 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p5 on p5.id = (select PROMO_ID_5 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                where t.TARIFF_CODE = (select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID ) )
    SET @INTERNAT_STAR_AFT= (select case when isnull(t.internat_stars,0)>=isnull(p1.internat_stars,0) and isnull(t.internat_stars,0) >=isnull(p2.internat_stars,0) and isnull(t.internat_stars,0) >=isnull(p3.internat_stars,0) and isnull(t.internat_stars,0) >=isnull(p4.internat_stars,0) and isnull(t.internat_stars,0)>=isnull(p5.internat_stars,0) then isnull(t.internat_stars,0)
                            when isnull(p1.internat_stars,0) >=isnull(p2.internat_stars,0) and isnull(p1.internat_stars,0) >=isnull(p3.internat_stars,0) and isnull(p1.internat_stars,0) >=isnull(p4.internat_stars,0) and isnull(p1.internat_stars,0)>=isnull(p5.internat_stars,0) then isnull(p1.internat_stars,0)
                            when isnull(p2.internat_stars,0) >=isnull(p3.internat_stars,0) and isnull(p2.internat_stars,0) >=isnull(p4.internat_stars,0) and isnull(p2.internat_stars,0)>=isnull(p5.internat_stars,0) then isnull(p2.internat_stars,0)
                            when isnull(p3.internat_stars,0) >=isnull(p4.internat_stars,0) and isnull(p3.internat_stars,0)>=isnull(p5.internat_stars,0) then isnull(p3.internat_stars,0)
                            when isnull(p4.internat_stars,0)>=isnull(p5.internat_stars,0) then isnull(p4.internat_stars,0)
                            else isnull(p4.internat_stars,0) end
                from epi_tariffs t
                     left outer join epi_promotions p1 on p1.id = (select PROMO_ID_1 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p2 on p2.id = (select PROMO_ID_2 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p3 on p3.id = (select PROMO_ID_3 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p4 on p4.id = (select PROMO_ID_4 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p5 on p5.id = (select PROMO_ID_5 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                where t.TARIFF_CODE = (select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID ) )
    SET @SMS_STAR_AFT= (select case when isnull(t.sms_stars,0)>=isnull(p1.sms_stars,0) and isnull(t.sms_stars,0) >=isnull(p2.sms_stars,0) and isnull(t.sms_stars,0) >=isnull(p3.sms_stars,0) and isnull(t.sms_stars,0) >=isnull(p4.sms_stars,0) and isnull(t.sms_stars,0)>=isnull(p5.sms_stars,0) then isnull(t.sms_stars,0)
                            when isnull(p1.sms_stars,0) >=isnull(p2.sms_stars,0) and isnull(p1.sms_stars,0) >=isnull(p3.sms_stars,0) and isnull(p1.sms_stars,0) >=isnull(p4.sms_stars,0) and isnull(p1.sms_stars,0)>=isnull(p5.sms_stars,0) then isnull(p1.sms_stars,0)
                            when isnull(p2.sms_stars,0) >=isnull(p3.sms_stars,0) and isnull(p2.sms_stars,0) >=isnull(p4.sms_stars,0) and isnull(p2.sms_stars,0)>=isnull(p5.sms_stars,0) then isnull(p2.sms_stars,0)
                            when isnull(p3.sms_stars,0) >=isnull(p4.sms_stars,0) and isnull(p3.sms_stars,0)>=isnull(p5.sms_stars,0) then isnull(p3.sms_stars,0)
                            when isnull(p4.sms_stars,0)>=isnull(p5.sms_stars,0) then isnull(p4.sms_stars,0)
                            else isnull(p4.sms_stars,0) end
                from epi_tariffs t
                     left outer join epi_promotions p1 on p1.id = (select PROMO_ID_1 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p2 on p2.id = (select PROMO_ID_2 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p3 on p3.id = (select PROMO_ID_3 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p4 on p4.id = (select PROMO_ID_4 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p5 on p5.id = (select PROMO_ID_5 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                where t.TARIFF_CODE = (select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID ) )
    SET @DATA_STAR_AFT= (select case when isnull(t.data_stars,0)>=isnull(p1.data_stars,0) and isnull(t.data_stars,0) >=isnull(p2.data_stars,0) and isnull(t.data_stars,0) >=isnull(p3.data_stars,0) and isnull(t.data_stars,0) >=isnull(p4.data_stars,0) and isnull(t.data_stars,0)>=isnull(p5.data_stars,0) then isnull(t.data_stars,0)
                            when isnull(p1.data_stars,0) >=isnull(p2.data_stars,0) and isnull(p1.data_stars,0) >=isnull(p3.data_stars,0) and isnull(p1.data_stars,0) >=isnull(p4.data_stars,0) and isnull(p1.data_stars,0)>=isnull(p5.data_stars,0) then isnull(p1.data_stars,0)
                            when isnull(p2.data_stars,0) >=isnull(p3.data_stars,0) and isnull(p2.data_stars,0) >=isnull(p4.data_stars,0) and isnull(p2.data_stars,0)>=isnull(p5.data_stars,0) then isnull(p2.data_stars,0)
                            when isnull(p3.data_stars,0) >=isnull(p4.data_stars,0) and isnull(p3.data_stars,0)>=isnull(p5.data_stars,0) then isnull(p3.data_stars,0)
                            when isnull(p4.data_stars,0)>=isnull(p5.data_stars,0) then isnull(p4.data_stars,0)
                            else isnull(p4.data_stars,0) end
                from epi_tariffs t
                     left outer join epi_promotions p1 on p1.id = (select PROMO_ID_1 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p2 on p2.id = (select PROMO_ID_2 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p3 on p3.id = (select PROMO_ID_3 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p4 on p4.id = (select PROMO_ID_4 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                     left outer join epi_promotions p5 on p5.id = (select PROMO_ID_5 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID )
                where t.TARIFF_CODE = (select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = @OID ) )

    SET @PROC_COMMAND='
    DECLARE @RPLNGRP nvarchar(25)
    DECLARE @ONNET_A nvarchar(25) DECLARE @OFFNET_A nvarchar(25) DECLARE @INTERNAT_A nvarchar(25) DECLARE @SMS_A nvarchar(25) DECLARE @DATA_A nvarchar(25)
    DECLARE @ADDITIONAL_SUBSIDY decimal (15,4)
    SET @RPLNGRP = (select ELAST_RPLN_GRP from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' )
    SET @ONNET_A = '+@ONNET_STAR_AFT+'
    SET @OFFNET_A = '+@OFFNET_STAR_AFT+'
    SET @INTERNAT_A = '+@INTERNAT_STAR_AFT+'
    SET @SMS_A = '+@SMS_STAR_AFT+'
    SET @DATA_A = '+@DATA_STAR_AFT+'
    SET @ADDITIONAL_SUBSIDY= (select isnull(ADDITIONAL_SUBSIDY,0) from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' )
    INSERT INTO XVBS_SIMULATION_3M_2
    SELECT
    Subscription_id, month_key, msisdn, account_type,
    (select RPLN from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) RPLN,
    (select RPLN_name from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) RPLN_name,
    (select RPLN_desc from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) RPLN_desc,
    (select ELIGIBILITY_GROUP from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) ELIGIBILITY_GROUP,
    (select DEFAULT_ELIGIBILITY from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) DEFAULT_ELIGIBILITY,
    (select ACCOUNT_TYPE from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) RPLN_ACCOUNT_TYPE,
    (select offer_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) offer_ID,
    (select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) tariff_ID,
    (select promo_ID_1 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) promo_ID_1,
    (select promo_ID_2 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) promo_ID_2,
    (select promo_ID_3 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) promo_ID_3,
    (select promo_ID_4 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) promo_ID_4,
    (select promo_ID_5 from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' ) promo_ID_5,
    '+@SQL_CODE+'-Round (('+convert (varchar, @DISC_MF)+')*'+convert (varchar, (@MF_RPLN/1.18))+',2) as ARPU_WO_IC,                    -- ARPU_wo_IC - discount on MF_RPLN
    '+@SQL_CODE+'-Round (('+convert (varchar, @DISC_MF)+')*'+convert (varchar, (@MF_RPLN/1.18))+',2) +
    (IC_revenue - IC_cost - (a.MOU_RND_OFFNET*(dbo.elast2(''OFFNET'',a.OFFNET_STARS,@OFFNET_A)-1)*'+convert (varchar, @VOICE_IC_COST)+') -
    (a.CNT_SMS_OFFNET*(dbo.elast2(''SMS'',a.SMS_STARS,@SMS_A)-1)*'+convert (varchar, @SMS_IC_COST)+')     ) -
    (case when '+convert (varchar(1), @HW_FLAG)+'=1
         then ISNULL((select SUBSIDY from epi_tariffs where tariff_code=(select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' )),0)/24 + @ADDITIONAL_SUBSIDY/24                    --pocet mesiacov viazanosti
         else 0 end ) as CM,                                                        -- CM
    '+convert (varchar, round (@MF_TOTAL/1.18,2))+' MONTHLY_FEE,                                                                                                        -- Monthly_fee
    '+convert (varchar(1), @HW_FLAG)+' HW_FLAG,
    ISNULL((select SUBSIDY from epi_tariffs where tariff_code=(select tariff_ID from XVBS_RPLN_TARIFF_MAPPING_3M where offer_id = '+convert(varchar,@OID)+' )),0) REG_SUBSIDY,
    '''+convert (nvarchar(255), isnull(@HW_SUGGESTION,'/'))+''' HW_SUGGESTION,
    '+convert (varchar(1), @SMS_PAID_PACK)+' SMS_PAID_PACK,
    '+convert (varchar(1), @DATA_PAID_PACK)+' DATA_PAID_PACK,
    DATA_DATETIME                                                                                                                            -- HW_FLAG
    from XVBS_SIMULATION_3M_1 a
    where 1=1
        AND 1=case when upper('''+@ACCOUNT_TYPE+''') in (''R'',''RES'',''RESIDENT'',''RESIDENTIAL'') and a.ACCOUNT_TYPE=''ENTERPRISE'' then 0
                   when upper('''+@ACCOUNT_TYPE+''') in (''B'',''BUS'',''BUSINESS'',''E'',''ENTERPRISE'') and a.ACCOUNT_TYPE=''RESIDENTIAL'' then 0
                  else 1 end
    '
    EXEC (@proc_command)
    --select (@proc_command)
    --print (@proc_command)

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply