Hi..
We are Building a datawarehouse using SQL server7.0.
Here we have severe performance problems, for instance we are trying to
load 1 lakh records into the data warehouse from the staging area. Even at the end of three hours
the DTS has loaded only 35000 records only. So could anybody suggest me to improve the performance by suggesting some tunable parameters of SQL server..
I have tried using the execution plan and it shows that a simple select
statement is taking 65% of the time which is not true.so can somebody help me to tune the sql pasted below.
************************************************** ****************************/************************************************** *
Name of the file: JSLH_LOAD.SQL
Purpose: TO POPULATE THE JSLH FACT TABLES, METADATA TABLES
Tables used: a) STG_JSLH
b) Time_DayDim
c) Cust_CustDim
d) Geo_TownDim
e) Prod_SagDim
f) STG_JSLH_REJECTS
g) Jslh_ConFact
h) Jslh_MonFact
i) ADM_METADATA
j) ADM_METADATA_HISTORY
Called from: --
Calls: --
************************************************** */


Use ldw
go

--Declaration of variables
Declare @tmp_timekey as Int,
@tmp_prodkey as Int,
@tmp_jslhkey as Int,
@tmp_jslhkey1 as Int,
@tmp_geokey as Int,
@tmp_custkey as Int,
@tmp_orderskey as Int,
@tmp_doc_num as int,
@tmp_address_num as Int,
@tmp_ship_to_address as Int,
@tmp_shipped_qty as Int,
@tmp_def_qty as Int,
@tmp_phmonth as Int,
@tmp_order_number as Int,
@tmp_jsh_inserts as Int,
@tmp_jsh_errors as Int,
@tmp_jmsf_inserts as Int,
@tmp_jmsf_errors as Int,
@tmp_jmsf_total_rows as Int,
@tmp_jcsf_inserts as Int,
@tmp_jcsf_errors as Int,
@tmp_jcsf_total_rows as Int,
@tmp_insert_errors as Int

Declare @tmp_sales as real,
@tmp_unit_cost as real,
@tmp_ext_cost as real,
@tmp_unit_price as real

Declare @tmp_gl_date as datetime

Declare @tmp_div as Varchar,
@tmp_cost_centre as Varchar(15),
@tmp_doc_type as Varchar(2),
@tmp_ext_price as Varchar(2),
@tmp_payment_term as Varchar(30),
@tmp_payment_term_code as varchar(30),
@tmp_gl_offset as Varchar(4),
@tmp_reason_code as Varchar(3),
@tmp_item_num as Varchar(25),
@tmp_order_type as Varchar(2),
@tmp_parent_line_type as Varchar(2)

set nocount on

--Setting the old records of fact table to 'N' for incremental load in Power cube of COGNOS
update jslh_confact
set JCSF_RECORD_STATUS = 'N'
update jslh_monfact
set JMSF_RECORD_STATUS = 'N'


--Deleting the previous records in stg_Jslh_rejects
truncate table stg_jslh_rejects

--Intializing variables
select @tmp_jslhkey1 = isnull(max(jsh_key)+1,1) from jslh_dim
set @tmp_jsh_inserts = 0
set @tmp_jsh_errors = 0
set @tmp_jmsf_inserts = 0
set @tmp_jmsf_errors = 0
set @tmp_jcsf_inserts = 0
set @tmp_jcsf_errors = 0
select @tmp_insert_errors = @@ERROR

--Declaration of cursor for stg_jslh to load Jslh_Dim, Jslh_ConFact and Jslh_MonFact
Declare stg_jslh_cursor Cursor Local
For
Select DOC_TYPE,DOC_NUMBER,ADDRESS_NUMBER, SHIP_TO_ADDRESS, GL_DATE, ITEM_NUMBER,
SHIPPED_QUANTITY_SOQS, DEFECTIVE_CR_NT_RQTY, NET_SALES, ORDER_TYPE, ORDER_NUMBER,
UNIT_PRICE, EXT_PRICE, UNIT_COST, EXT_COST, PAYMENT_TERM, REASON_CODE
from stg_jslh

--To load Jslh_Dim, Jslh_ConFact and Jslh_MonFact
Open stg_jslh_cursor
Fetch Next From stg_jslh_cursor into @tmp_doc_type, @tmp_doc_num, @tmp_address_num, @tmp_ship_to_address,@tmp_gl_date, @tmp_item_num,@tmp_shipped_qty,@tmp_def_qty,
@tmp_sales, @tmp_order_type, @tmp_order_number, @tmp_unit_price, @tmp_ext_price,
@tmp_unit_cost, @tmp_ext_cost, @tmp_payment_term_code, @tmp_reason_code

while @@Fetch_status = 0
Begin
select @tmp_payment_term = stg_payterm.description from stg_payterm where stg_payterm.payment_term = @tmp_payment_term_code

if(NOT EXISTS(Select jsh_key from jslh_dim where jslh_dim.jsh_doctype = @tmp_doc_type and jslh_dim.jsh_docnumber = @tmp_doc_num and jslh_dim.jsh_ordertype = @tmp_order_type and jslh_dim.jsh_ordernumber = @tmp_order_number and jslh_dim.jsh_paymentterm = @tmp_payment_term and jslh_dim.jsh_reasoncode = @tmp_reason_code))
begin
Insert into jslh_dim values(@tmp_jslhkey1, @tmp_doc_type, @tmp_doc_num, @tmp_order_type, @tmp_order_number,
@tmp_payment_term, @tmp_ship_to_address, @tmp_reason_code)
set @tmp_jslhkey1 = @tmp_jslhkey1 + 1
set @tmp_jsh_inserts = @tmp_jsh_inserts + 1
end

Select @tmp_timekey = time_daydim.tdd_key from time_daydim where time_daydim.tdd_date = @tmp_gl_date
Select @tmp_prodkey = prod_ctndim.pcd_key from prod_ctndim where prod_ctndim.pcd_ctn_code = @tmp_item_num
Select @tmp_custkey = cust_custdim.ccd_key from cust_custdim where cust_custdim.ccd_customer_code = @tmp_address_num
Select @tmp_jslhkey = jslh_dim.jsh_key from jslh_dim where jslh_dim.jsh_doctype = @tmp_doc_type and jslh_dim.jsh_docnumber = @tmp_doc_num and jslh_dim.jsh_ordertype = @tmp_order_type and jslh_dim.jsh_ordernumber = @tmp_order_number and jslh_dim.jsh_paymentterm = @tmp_payment_term and jslh_dim.jsh_reasoncode = @tmp_reason_code
Select @tmp_geokey = geo_towndim.gtd_key from geo_towndim, cust_geo_lookup where geo_towndim.gtd_town = cust_geo_lookup.cgl_town and cust_geo_lookup.cgl_customer_code = @tmp_address_num

if(@tmp_timekey <> &#39;&#39; and @tmp_prodkey <> &#39;&#39; and @tmp_custkey <> &#39;&#39; and @tmp_jslhkey <> &#39;&#39; and @tmp_geokey <> &#39;&#39
Begin
if(NOT EXISTS(select * from jslh_confact where jcsf_tdd_key = @tmp_timekey and jcsf_pcd_key = @tmp_prodkey and
jcsf_ccd_key = @tmp_custkey and jcsf_gtd_key = @tmp_geokey and jcsf_jsh_key = @tmp_jslhkey))
Begin
Insert into jslh_confact values (@tmp_timekey, @tmp_prodkey, @tmp_jslhkey, @tmp_geokey ,@tmp_custkey,
@tmp_shipped_qty, @tmp_unit_price, @tmp_ext_price, @tmp_unit_cost, @tmp_ext_cost,
@tmp_def_qty, @tmp_sales, null, &#39;Y&#39
set @tmp_jcsf_inserts = @tmp_jcsf_inserts + 1

Insert into jslh_monfact values (@tmp_timekey, @tmp_prodkey, @tmp_jslhkey, @tmp_geokey ,@tmp_custkey , @tmp_shipped_qty, @tmp_unit_price, @tmp_ext_price, @tmp_unit_cost, @tmp_ext_cost, @tmp_def_qty, @tmp_sales,null, &#39;Y&#39
set @tmp_jmsf_inserts = @tmp_jmsf_inserts + 1
End
Else
Begin
Update jslh_monfact
set jmsf_shipped_qty = jmsf_shipped_qty + @tmp_shipped_qty,
jmsf_defec_qty = jmsf_defec_qty + @tmp_def_qty,
jmsf_net_sales = jmsf_net_sales+ @tmp_sales
where jslh_monfact.jmsf_tdd_key = @tmp_timekey and jslh_monfact.jmsf_pcd_key = @tmp_prodkey and
jslh_monfact.jmsf_ccd_key = @tmp_custkey and jslh_monfact.jmsf_jsh_key = @tmp_jslhkey and
jslh_monfact.jmsf_gtd_key = @tmp_geokey

Update jslh_confact
set jcsf_shipped_qty = jcsf_shipped_qty + @tmp_shipped_qty,
jcsf_defec_qty = jcsf_defec_qty + @tmp_def_qty,
jcsf_net_sales = jcsf_net_sales + @tmp_sales
where jslh_confact.jcsf_tdd_key = @tmp_timekey and jslh_confact.jcsf_pcd_key = @tmp_prodkey and
jslh_confact.jcsf_ccd_key = @tmp_custkey and jslh_confact.jcsf_jsh_key = @tmp_jslhkey and
jslh_confact.jcsf_gtd_key = @tmp_geokey
End

End
else
Begin

set @tmp_jmsf_errors = @tmp_jmsf_errors + 1
set @tmp_jcsf_errors = @tmp_jcsf_errors + 1
insert into stg_jslh_rejects values(null,null, @tmp_doc_type, @tmp_doc_num,@tmp_address_num, @tmp_ship_to_address,@tmp_gl_date, @tmp_item_num,@tmp_shipped_qty,@tmp_def_qty,
@tmp_sales, null, @tmp_order_type, @tmp_order_number, null, @tmp_unit_price, @tmp_ext_price,
@tmp_unit_cost, @tmp_ext_cost, @tmp_payment_term_code, null, @tmp_reason_code)
End
set @tmp_timekey = null
set @tmp_prodkey = null
set @tmp_jslhkey = null
set @tmp_geokey = null
set @tmp_custkey = null

Fetch Next From stg_jslh_cursor into @tmp_doc_type, @tmp_doc_num, @tmp_address_num, @tmp_ship_to_address,@tmp_gl_date, @tmp_item_num,@tmp_shipped_qty,@tmp_def_qty,
@tmp_sales, @tmp_order_type, @tmp_order_number, @tmp_unit_price, @tmp_ext_price,
@tmp_unit_cost, @tmp_ext_cost, @tmp_payment_term_code, @tmp_reason_code
End

Close stg_jslh_cursor
Deallocate stg_jslh_cursor

--Loading of Jslh_Dim metadata into metadata tables
if(NOT EXISTS(select adm_md_table_name from adm_metadata where adm_md_table_name = &#39;Jslh_Dim&#39)
insert into adm_metadata values (&#39;Jslh_Dim&#39;,&#39;Dimension Table for Sales Order History&#39;, &#39;JSH_KEY&#39;,
@tmp_jsh_inserts, 0, @tmp_jslhkey1 - 1, getdate(), NULL,
case
when(@tmp_jsh_errors = 0) then &#39;1&#39;
else &#39;0&#39;
end)
else
update adm_metadata
set adm_md_rows_inserted = @tmp_jsh_inserts,
adm_md_last_ud_datetime = getdate(),
adm_md_last_key_value = @tmp_jslhkey1 - 1,
adm_md_flag = case
when(@tmp_jsh_errors = 0) then &#39;1&#39;
else &#39;0&#39;
end
where adm_metadata.adm_md_table_name = &#39;Jslh_Dim&#39;


insert into adm_metadata_history values (&#39;Jslh_Dim&#39;, @tmp_jsh_inserts, 0, getdate())

--sending mail with rejected records
use master
if(@tmp_jmsf_errors <> 0)
EXEC xp_sendmail @recipients = &#39;ranghu.subhash@tatainfotech.com&#39;,
@query = &#39;SELECT * FROM ldw..stg_jslh_rejects&#39;,
@subject = &#39;Rejected records from DSSJSLH file&#39;,
@message = &#39;Rejected records are in the attached text file&#39;,
@attach_results = &#39;TRUE&#39;, @width = 250

go
-- End of JSLH_LOAD.SQL