Slow stored procedures after migration 2008 to 2016 SQL server
-
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;
GOALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;
GOALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING =OFF ;GO
Thank you
- alexandermkd - Tuesday, January 8, 2019 5:00 AM
Can you share the execution plan with us to get some idea.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/ -
Hi tnx for the fast response:
old server:
https://www.brentozar.com/pastetheplan/?id=Skq8MMzfV
new server:
-
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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) -
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.
-
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.
-
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: - alexandermkd - Thursday, January 10, 2019 6:32 AMHi 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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
did you perform full database maintenance after the upgrade to the new engine ?
You've seen the "missing index", right ?
Johan
Learn to play, play to learn !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 but most of the time this is me
-
@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)
- ALZDBA - Thursday, January 10, 2019 8:26 AM
yes, tried with missing index too (table has 96 rows so index dosent help much)
- frederico_fonseca - Thursday, January 10, 2019 10:59 AM
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
GOALTER 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***
ENDcreate 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 SMSselect @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
ENDUSE [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 endreturn @RETURN
END@frederico_fonesca big tnx for the support
-
@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 allnetso 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. - frederico_fonseca - Saturday, January 12, 2019 3:31 AM
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)
Bellow i are my database settings:Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply