July 7, 2003 at 6:40 am
Can I ask somebody to give me some advices regarding a joined stored proc.
When can we used TOP 1(field) or Max(field),
Can I use derived table in this proc ?
Thanks in advance.
/*
nbr OF records :
tables : - hrate : 14360 records- secp:798487
- sact:15079- bookorder : 47- aoduser : 6327- ebook : 1678 - aofficer : 111-ipflabel : 10- ccy : 43 - pmtype : 2 - sbal : 61972 - secmain : 8990- ctry : 254
normally, the order to fields respect index fields.
*/
if exists (select 'X' from dbo.sysobjects where id = object_id(N'[dbo].[InqSec]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InqSec]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROC InqSec(@user_code char(8) ,
@inq_date datetime ,
@sec_number integer ,
@aom_ccy char(3) )
AS
BEGIN
SET NOCOUNT ON
DECLARE @current_user nvarchar(10)
DECLARE @cmd nvarchar(100)
Declare @aom_rate decimal(18,10)
Declare @aom_ccy_cts tinyint
Declare @ccy_cty char(3)
Declare @ccy_desc char(15)
Declare @sec_name char(40)
Declare @filename varchar(30)
Declare @sec_nr_str varchar(10)
declare @test_index bit
select @test_index = 0
-- start create table @TEMPCCY like a temporary table.
-- **** WORK ONLY ON SQL 2000 *****
declare @TEMPCCY table ( HCR_CODE CHAR(3) Primary key, HCR_DATE DATETIME, HCR_BMIDDLE DECIMAL (18,10),
HCR_CCY_CTS TINYINT, HCR_CCY_DESC CHAR(15), CCY_CTY CHAR(3) )
-- end
Select @aom_rate = 0,
@ccy_cty = " ",
@ccy_desc = " ",
@sec_name = " "
Set @filename = RTrim(user_name() + '.position')
Set @sec_nr_str = Convert(varchar(10), @sec_number)
-- store the last ccy price base on the inq. date
INSERT INTO @TEMPCCY
SELECT rate.hcr_code HCR_CODE, rate.hcr_date HCR_DATE, rate.hcr_bmiddle HCR_BMIDDLE,
ccy.ccy_centimes HCR_CCY_CTS, ccy.ccy_desc_int HCR_CCY_DESC, ccy.ccy_country CCY_CTY
FROM hrate rate (NOLOCK)
INNER JOIN ccy AS ccy (NOLOCK) on ccy.ccy_code = rate.hcr_code
WHERE rate.hcr_date = ( SELECT TOP 1(hcr_date) FROM hrate h (NOLOCK)
WHERE h.hcr_code = rate.hcr_code
AND h.hcr_date <= @inq_date ORDER BY h.hcr_code, h.hcr_date DESC )
CREATE TABLE #temp_aom
(val_line tinyint NOT NULL,
val_type tinyint NOT NULL,
status char(2) NOT NULL,
ao_code smallint NOT NULL,
ao_name char(8) NOT NULL,
am_code smallint NOT NULL,
am_name char(8) NOT NULL,
ip_code smallint NOT NULL,
ip_desc char(30) NOT NULL,
mt_code smallint NOT NULL,
mt_desc char(30) NOT NULL,
bp_code integer NOT NULL,
customer int NOT NULL,
pfolio tinyint NOT NULL,
atp_code char(4) NOT NULL,
ccy_code char(3) NOT NULL,
acc_seq tinyint NOT NULL,
deal_code char(4) NOT NULL,
deal_number int NOT NULL,
deal_seq smallint NOT NULL,
sec_number int NOT NULL,
atp_desc char(10) NOT NULL,
atp_attach1 tinyint NOT NULL,
atp_attach2 tinyint NOT NULL,
ccy_desc char(15) NOT NULL,
sec_name char(40) NOT NULL,
balance DECIMAL (18,4) NOT NULL,
value datetime NOT NULL,
sec_mk_price decimal(20, 8) NOT NULL,
sec_mk_price_nom decimal(14, 6) NOT NULL,
sec_mk_price_ccy char(3) NOT NULL,
sec_mk_price_rate decimal(18, 10) NOT NULL,
sec_mk_price_ccy_cts tinyint NOT NULL,
sec_mk_price_date datetime NOT NULL,
sec_pp_price decimal(20, 8) NOT NULL,
sec_pp_price_nom decimal(14, 6) NOT NULL,
sec_pp_ccy_rate decimal(18, 10) NOT NULL,
sec_soe_order int NOT NULL,
fxd_ccy2 char(3) NOT NULL,
fxd_crossrate decimal(18,10) NOT NULL,
fxd_crosscode char(1) NOT NULL,
fxd_crossmul smallint NOT NULL,
fxd_ccy2_rate decimal(18,10) NOT NULL,
fxd_ccy2_cts tinyint NOT NULL,
int_rate decimal(14, 8) NOT NULL,
int_basis tinyint NOT NULL,
int_amount DECIMAL (18,4) NOT NULL,
int_exday smallint NOT NULL,
start_date datetime NOT NULL,
int_date datetime NOT NULL,
end_date datetime NULL,
mat_date datetime NULL,
sexcode char(3) NOT NULL,
cty_code char(3) NULL,
pfs_code char(3) NULL,
nat_code char(3) NOT NULL,
cgs_code char(3) NOT NULL,
sis_code integer NOT NULL,
esa_code char(3) NOT NULL,
src_code char(3) NOT NULL,
deal_rate decimal(18, 10) NOT NULL,
deal_ccy_cts tinyint NOT NULL,
aom_ccy char(3) NOT NULL,
aom_rate decimal(18, 10) NOT NULL,
aom_ccy_cts tinyint NOT NULL,
line_date datetime NOT NULL,
view_order tinyint NOT NULL,
drv_position int NULL,
hedged_traded char(1) NULL,
zero_show Char(1) NULL,
in_money varchar(3) NULL,
contingent_amt DECIMAL(18, 4) NULL,
mk_lb_ssp_price decimal(20, 8) NULL,
market_value decimal(20, 8) NULL,
purchase_value decimal(20, 8) NULL,
dvd_option_future char(1) NULL,
drv_tick_value Decimal(14,5) NULL,
drv_tick_den Smallint NULL,
drv_price_fluct decimal(15, 8) NULL,
balance_indicator varchar(5) NULL,
security_desc Char(30) NULL,
dvd_strike Decimal(18,8) NULL,
det_ccy_balance Money NULL,
drv_prem_paid_ind Char(1) NULL
)
-- *4 start -----------------------------
CREATE NONCLUSTERED
INDEX temp_aom_p_sec ON [dbo].[#temp_aom] (sec_number) --, [val_type])
WITH FILLFACTOR = 80
CREATE NONCLUSTERED
INDEX [temp_aom_f_val_type] ON [dbo].[#temp_aom] ([val_type], [sec_number])
-- *4 end -----------------------------
-- create a temporary table to store the extracted customers
SELECT sca_customer customer, sca_security sec_number,
sca_pfolio pfolio, sca_acttype atp_code,
sca_ccy ccy_code, sca_mngt_type mt_code,
sca_mngt_prof ip_code, sca_agent bp_code,
sca_status status, sca_acc_off ao_code,
sca_acc_mngr am_code
INTO #temp
FROM sact (NOLOCK), aoduser (NOLOCK), aofficer (NOLOCK)
WHERE sca_security = @sec_number
AND sca_acc_off = aod_number
AND aou_code = aod_code
AND aou_uid = @user_code
AND aou_access = "Y"
-- If @@rowcount < 1 Return -- *2
-- read the information on the basis shedule ccy
SELECT @aom_rate = HCR_BMIDDLE, @aom_ccy_cts = HCR_CCY_CTS
FROM @TEMPCCY
WHERE HCR_CODE = @aom_ccy
-- create the temporary table and insert it the secmain positions
INSERT INTO #temp_aom
SELECT 1, 0, #temp.status, #temp.ao_code, "", #temp.am_code, "",
#temp.ip_code, "", #temp.mt_code, "", #temp.bp_code,
scb.scb_customer, scb.scb_pfolio, scb.scb_acttype,
scb.scb_ccy, 1, "", 0, 0, scb.scb_security, "", 0, 0,
"", "", scb.scb_td_nominal, scb.scb_date, scb.scb_td_pp_aver,
0, scb.scb_ccy, 0, 0, GetDate(), scb.scb_td_pp_aver, 0, 0,
0, " ", 0, " ", 0, 0, 0, 0, 0, 0, 0, getdate(), getdate(),
getdate(), getdate(), "", NULL, NULL, "..", "..", 0,
"..", "..", 0, 0, @aom_ccy, @aom_rate,
@aom_ccy_cts, @inq_date, 0, Null drv_position, Null hedged_traded, Null zero_show,
Null in_money, Null contingent_amt, Null mk_lb_ssp_price, Null market_value,
Null purchase_value, Null dvd_option_future, Null drv_tick_value, Null drv_tick_den,
Null drv_price_fluct, Null balance_indicator, Null security_desc, Null dvd_strike,
Null det_ccy_balance, NULL drv_prem_paid_ind
FROM sbal scb (NOLOCK), #temp (NOLOCK)
WHERE #temp.customer = scb.scb_customer
AND #temp.sec_number = scb.scb_security
AND #temp.pfolio = scb.scb_pfolio
AND #temp.atp_code = scb.scb_acttype
AND #temp.ccy_code = scb.scb_ccy
AND scb.scb_type > 1 -- >= 2 *4
AND scb.scb_date = (SELECT Max(scb_date) FROM sbal (NOLOCK)
WHERE scb_customer = #temp.customer
AND scb_security = #temp.sec_number
AND scb_pfolio = #temp.pfolio
AND scb_acttype = #temp.atp_code
AND scb_ccy = #temp.ccy_code
AND scb_date <= @inq_date
AND scb_type > 1 ) -- >= 2 *4
-- delete the position to zero
DELETE #temp_aom
WHERE val_type = 0
AND sec_number = @sec_number
AND balance = 0
-- Update the possim table with the security information
UPDATE #temp_aom
SET sec_name = secmain.sdt_rpt_desc ,
start_date = isnull(sdt_issued, getdate()) ,
int_date = isnull(sdt_cp_last, getdate()) ,
end_date = isnull(sdt_maturity, getdate()) ,
mat_date = sdt_maturity ,
int_rate = sdt_cp_rate ,
int_basis = sdt_cp_basis ,
int_exday = secmain.sdt_cp_exday ,
sexcode = secmain.sdt_sexcode ,
pfs_code = secmain.sdt_pfsector ,
cty_code = secmain.sdt_cty_risk ,
sec_pp_price_nom = secmain.sdt_nominal ,
sis_code = secmain.sdt_issuer ,
esa_code = secmain.sdt_ecosector ,
src_code = secmain.sdt_rating ,
nat_code = secmain.sdt_nature ,
cgs_code = ctry.cty_market
FROM secmain (NOLOCK), ctry (NOLOCK)
WHERE val_type = 0 -- *4
AND #temp_aom.sec_number = @sec_number -- *4
AND secmain.sdt_number = @sec_number
AND secmain.sdt_number = #temp_aom.sec_number
AND secmain.sdt_cty_risk = ctry.cty_code
-- AND val_type = 0 -- *4
-- retreive the price of the security
UPDATE #temp_aom
SET sec_mk_price = secp.ssp_price ,
sec_mk_price_nom = secp.ssp_nominal ,
sec_mk_price_ccy = secp.ssp_ccy ,
sec_mk_price_date = secp.ssp_date ,
sec_mk_price_ccy_cts = gccy.HCR_CCY_CTS,
sec_mk_price_rate = gccy.HCR_BMIDDLE
FROM secp (NOLOCK), @TEMPCCY AS gccy
WHERE val_type = 0 -- *4
AND sec_number = @sec_number -- *4
AND ssp_sexcode = #temp_aom.sexcode -- *4
-- AND ssp_number = @sec_number -- *4
-- AND ssp_number = #temp_aom.sec_number -- *4
AND ssp_date = ( SELECT TOP 1(s.ssp_date) -- MAx(ssp_date) *4
FROM secp s (NOLOCK)
WHERE s.ssp_number = #temp_aom.sec_number
And s.ssp_sexcode = #temp_aom.sexcode
And s.ssp_date <= @inq_date ORDER BY s.ssp_number, s.ssp_date desc) -- *4
AND ssp_number = @sec_number
AND ssp_number = #temp_aom.sec_number
AND ssp_ccy = gccy.HCR_CODE
-- AND val_type = 0 -- *4
-- set the correct date of coupons
UPDATE #temp_aom
SET sec_soe_order = 1
FROM bookorder (NOLOCK)
WHERE val_type = 0 -- *4
AND orb_sec_number = @sec_number -- *4
AND orb_sec_number = #temp_aom.sec_number -- *4
AND orb_cst_init_number = #temp_aom.customer
AND sec_number = @sec_number
AND orb_status <> "R" -- *D3
-- AND val_type = 0 -- *4
UPDATE #temp_aom
SET sec_soe_order = 1
FROM ebook (NOLOCK)
WHERE val_type = 0 -- *4
AND ore_sec_number = #temp_aom.sec_number -- *4
AND ore_sec_number = @sec_number -- *D4 -- *4
AND ore_cst_init_number = #temp_aom.customer
-- load the management information from the temporary table
UPDATE #temp_aom
SET ao_name = aod1.aod_code ,
am_name = aod2.aod_code
FROM aofficer aod1 (NOLOCK), aofficer aod2 (NOLOCK)
WHERE val_type = 0 -- *4
AND #temp_aom.sec_number = @sec_number -- *4
AND aod1.aod_number = #temp_aom.ao_code
AND aod2.aod_number = #temp_aom.am_code
-- AND val_type = 0 -- *4
-- insert the invest. and profile description
UPDATE #temp_aom
SET #temp_aom.mt_desc = pmt_desc_int ,
#temp_aom.ip_desc = ipl_desc_int
FROM ipflabel (NOLOCK), pmtype (NOLOCK)
WHERE val_type = 0 -- *4
AND sec_number = @sec_number
AND ipl_code = #temp_aom.ip_code
AND pmt_code = #temp_aom.mt_code
-- insert the acttype description
UPDATE #temp_aom
SET atp_desc = atype.atp_desc_int ,
atp_attach1 = atype.atp_attach1 ,
atp_attach2 = atype.atp_attach2 ,
view_order = atype.atp_inq_order
FROM atype (NOLOCK)
WHERE atype.atp_code = #temp_aom.atp_code -- *4
AND sec_number = @sec_number -- *4
AND val_type = 0
-- insert the ccy data
UPDATE #temp_aom
SET ccy_desc = gccy.HCR_CCY_DESC,
deal_rate = gccy.HCR_BMIDDLE,
deal_ccy_cts = gccy.HCR_CCY_CTS
FROM @TEMPCCY as gccy
WHERE gccy.HCR_CODE = #temp_aom.ccy_code -- *4
AND #temp_aom.sec_number = @sec_number
AND val_type = 0
if exists (select 'X' from sysobjects where name = 'position' and uid= user_id(user_name()))
-- table exists --> delete / insert
Begin
Select @cmd = N'DELETE ' + @filename + N' WHERE val_type= 0 AND sec_number = ' + @sec_nr_str
EXECUTE sp_executesql @cmd
End
Else
-- table doesn't exist --> create
Begin
EXECUTE (" CREATE TABLE position
(val_line tinyint NOT NULL,
val_type tinyint NOT NULL,
status char(2) NOT NULL,
ao_code smallint NOT NULL,
ao_name char(8) NOT NULL,
am_code smallint NOT NULL,
am_name char(8) NOT NULL,
ip_code smallint NOT NULL,
ip_desc char(30) NOT NULL,
mt_code smallint NOT NULL,
mt_desc char(30) NOT NULL,
bp_code integer NOT NULL,
customer int NOT NULL,
pfolio tinyint NOT NULL,
atp_code char(4) NOT NULL,
ccy_code char(3) NOT NULL,
acc_seq tinyint NOT NULL,
deal_code char(4) NOT NULL,
deal_number int NOT NULL,
deal_seq smallint NOT NULL,
sec_number int NOT NULL,
atp_desc char(10) NOT NULL,
atp_attach1 tinyint NOT NULL,
atp_attach2 tinyint NOT NULL,
ccy_desc char(15) NOT NULL,
sec_name char(40) NOT NULL,
balance DECIMAL (18,4) NOT NULL,
value datetime NOT NULL,
sec_mk_price decimal(20, 8) NOT NULL,
sec_mk_price_nom decimal(14, 6) NOT NULL,
sec_mk_price_ccy char(3) NOT NULL,
sec_mk_price_rate decimal(18, 10) NOT NULL,
sec_mk_price_ccy_cts tinyint NOT NULL,
sec_mk_price_date datetime NOT NULL,
sec_pp_price decimal(20, 8) NOT NULL,
sec_pp_price_nom decimal(14, 6) NOT NULL,
sec_pp_ccy_rate decimal(18, 10) NOT NULL,
sec_soe_order int NOT NULL,
fxd_ccy2 char(3) NOT NULL,
fxd_crossrate decimal(18,10) NOT NULL,
fxd_crosscode char(1) NOT NULL,
fxd_crossmul smallint NOT NULL,
fxd_ccy2_rate decimal(18,10) NOT NULL,
fxd_ccy2_cts tinyint NOT NULL,
int_rate decimal(14, 8) NOT NULL,
int_basis tinyint NOT NULL,
int_amount DECIMAL (18,4) NOT NULL,
int_exday smallint NOT NULL,
start_date datetime NOT NULL,
int_date datetime NOT NULL,
end_date datetime NULL,
mat_date datetime NULL,
sexcode char(3) NOT NULL,
cty_code char(3) NULL,
pfs_code char(3) NULL,
nat_code char(3) NOT NULL,
cgs_code char(3) NOT NULL,
sis_code integer NOT NULL,
esa_code char(3) NOT NULL,
src_code char(3) NOT NULL,
deal_rate decimal(18, 10) NOT NULL,
deal_ccy_cts tinyint NOT NULL,
aom_ccy char(3) NOT NULL,
aom_rate decimal(18, 10) NOT NULL,
aom_ccy_cts tinyint NOT NULL,
line_date datetime NOT NULL,
view_order tinyint NOT NULL,
drv_position int NULL,
hedged_traded char(1) NULL,
zero_show Char(1) NULL,
in_money varchar(3) NULL,
contingent_amt DECIMAL(18, 4) NULL,
mk_lb_ssp_price decimal(20, 8) NULL,
market_value decimal(20, 8) NULL,
purchase_value decimal(20, 8) NULL,
dvd_option_future char(1) NULL,
drv_tick_value Decimal(14,5) NULL,
drv_tick_den Smallint NULL,
drv_price_fluct decimal(15, 8) NULL,
balance_indicator varchar(5) NULL,
security_desc Char(30) NULL,
dvd_strike Decimal(18,8) NULL,
det_ccy_balance Money NULL,
drv_prem_paid_ind Char (1) NULL
) ")
select @test_index = 1
End
-- *4 start
SET @cmd = N'INSERT INTO ' + @filename + N' SELECT * FROM #temp_aom'
EXECUTE sp_executesql @cmd
-- *4 end
IF @test_index = 1
Begin
-- *4 start
SET @cmd = N'CREATE INDEX myindex ON ' + @filename + ' (ip_code) '
EXECUTE sp_executesql @cmd
-- *4 end
End
END
GO
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
GRANT EXECUTE ON [dbo].[InqSec] TO [GRP]
GO
July 7, 2003 at 7:10 am
There are many, many things that you can do to improve this procedure, and I'm sure you'll get a bunch of comments about all those things; however, the first thing you need to do is divide this procedure into separate stored procedures that define each task you are trying to accomplish. Generally, it's not a great idea to try to do everything in one giant procedure: you give yourself no chance of actually having an optimal execution plan. Your best bet in this case is to separate out the different pieces so you can optimize them separately. You can always have a single controlling procedure that manages the execution of the sub-procedures, but at least the sub-procedures will be optimized...
Oh, and use modern join syntax; it will greatly improve your chances of picking up those nasty performance bugs where you join one table to another using a cross join because, say, you forgot to put the join condition in the WHERE clause.
HTH,
jay
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply