SQL Server Performance Forum – Threads Archive

help need for optimizing query

HI all
I have a procedure for loading data form other databases to some tables in my database using cursors
Now it is working fine with small amount of data but with large amount of data it takes a lot of time.
Can someone advice any other method to carry out the same task..
Thanks Script: CREATE PROCEDURE [uspd_ins_SVL_DATALOAD]
AS
BEGIN
SET NOCOUNT ON
–************ Declare local variables ****************************
DECLARE @Insr_Dttm SMALLDATETIME, @UPDT_DTTM SMALLDATETIME
DECLARE @TranName VARCHAR(20), @Logentry int, @Asst_ID int, @Dateofreading datetime,
@Dvsn_Ind CHAR(1), @Muni_ID int, @AR_Asst_ID int, @Insp_Type_ID int,
@Insp_Date TIMESTAMP, @Insp_Stts_ID int, @Insp_Form_ID int,
@Form_Vrsn_Nmbr smallint, @Insp_Form_Ttle varchar( 100),
@Org_Type_Ind char(1), @MS_Plat_ID int,
@Asst_Type_ID int, @Asst_Styp_ID int, @Asst_Name varchar (30),
@Asst_Nmbr varchar (100), @Locn_Desc varchar (200),
@Asst_Insp_ID int, @Load_Dttm SMALLDATETIME,
@ins_error int, @ins_error1 int,
@ins_error2 int, @ins_error3 int, @ins_error4 int,
@weather varchar(20), @weather_item_lov_valu_id int, @next_sys_gen_date datetime,
@schd_optn_id int, @Regn_ind varchar(2), @max_dttm datetime — Initialize local variables
SELECT @Load_Dttm = getdate()
SELECT @Insr_Dttm = getdate()
SELECT @Updt_Dttm = @Insr_Dttm
SELECT @TranName = ‘SVL_DATALOADING’ — The following initial values must correspond to the data set up in production
— for SV inspection (i.e. Zero Voltage test)
SELECT @Insr_Dttm = getdate()
SELECT @UPDT_DTTM = getdate()
SET @Insp_Type_ID = ‘3’
SET @Insp_Stts_ID = ‘5’
SET @Insp_Form_ID = ‘6’
SET @Form_Vrsn_Nmbr = ‘1’
SET @Insp_Form_Ttle = ‘EO-10322 (Routine Stray Voltage Test of Distribution Structures and Street Lights)’
SET @Org_Type_Ind = ‘E’
SET @Schd_optn_id = ‘1’
–***************************** Begin CURSOR *******************************************
DECLARE svl_dataload_cur INSENSITIVE CURSOR FOR
SELECT a.Dvsn_Ind, a.MS_Plat_ID, a.Muni_ID, a.Regn_Ind,
b.Asst_ID, b.Asst_Nmbr, b.Asst_Name, b.Locn_Cmnt,
c.Logentry, c.DateOfReading, c.weather,
d.Asst_Type_ID, d.Asst_Styp_ID FROM ELECTDISTINSPECTIONS_ASSETDW.ASSETDW.dbo.ADW_LOCATION_HIERARCHY a,
ELECTDISTINSPECTIONS_ASSETDW.ASSETDW.dbo.ADW_ASSET b,
MASTERLOG c,
ELECTDISTINSPECTIONS_ASSETDW.ASSETDW.dbo.ADW_ASSET_HIERARCHY d
WHERE a.Locn_Hrch_ID = b.Locn_Hrch_ID
AND b.Asst_ID = c.Asst_ID
AND b.Asst_Hrch_ID = d.Asst_Hrch_ID
ORDER BY c.Dateofreading desc
–***************************** Open CURSOR ******************************************* OPEN svl_dataload_cur FETCH NEXT FROM svl_dataload_cur
INTO @Dvsn_IND, @MS_Plat_ID, @Muni_ID, @Regn_Ind, @Asst_ID, @Asst_Nmbr, @Asst_Name, @Locn_Desc,
@Logentry, @DateofReading, @Weather, @Asst_Type_ID, @Asst_Styp_ID WHILE @@FETCH_STATUS = 0
BEGIN BEGIN TRANSACTION @TranName –Begin Transaction SVL_DATALOADING /*****************************************************************************************
Step 1:
Insert the data into EDIS_ASSET_INSPECTION table
–Inserting Stray Voltage Inspection data fetched in the cursor in the EDIS_Asset_Inspection table.
******************************************************************************************/
INSERT INTO EDIS_ASSET_INSPECTION ( AR_Regn_IND, AR_Dvsn_IND, AR_Muni_ID, AR_Asst_ID,
Insp_Type_ID, Insp_Date, Insp_Stts_ID, Insp_Form_ID, Form_Vrsn_Nmbr,
Insp_Form_Ttle, Org_Type_IND, Icmp_Ind, Stop_Ind, SV_Ind, Insr_User, Updt_user, Insr_Dttm, Updt_Dttm, Cmnt) VALUES( @Regn_Ind, @Dvsn_IND, @Muni_ID, @Asst_ID, @Insp_Type_ID, @Dateofreading,
@Insp_Stts_ID, @Insp_Form_ID, @Form_Vrsn_Nmbr, @Insp_Form_Ttle,
@Org_Type_IND, ‘N’, ‘N’, ‘N’, current_user, current_user, @Insr_Dttm, @Updt_Dttm, ‘Stray Voltage Log–‘ + CAST (@Dateofreading AS VARCHAR (20)) ) SELECT @ins_error1 = @@ERROR /**********************************************************************************************
Step 2:
Insert the data into EDIS_ASSET_INSPECTION_ITEM table.
–Inserting Stray Voltage Inspection data fetched in the cursor in the EDIS_Asset_Inspection_Item table.
*************************************************************************************************/ SELECT @Asst_Insp_ID = @@IDENTITY
FROM EDIS_ASSET_INSPECTION /************************************************************************************************
Selecting LOV_Value_ID from EDIS_LOV_VALUE corresponding to the value of the weather column
for the current row fetched from the Masterlog table and storing it in the local variable @Item_LOV_Valu_ID.
*************************************************************************************************/ — First, map the weather value in the MasterLog table to a standardized value SELECT @weather =
CASE @weather
WHEN ‘NULL’ THEN NULL
WHEN ”THEN NULL
WHEN ‘Cold’THEN ‘Cold’
WHEN ‘D’THEN NULL
WHEN ‘F’ THEN ‘Fair’
WHEN ‘Fair’THEN ‘Fair’
WHEN ‘FF’THEN ‘Fair’
WHEN ‘Fiar’THEN ‘Fair’
WHEN ‘Flood’ THEN NULL
WHEN ‘FREEE’THEN ‘Freeze’
WHEN ‘Freeze’THEN ‘Freeze’
WHEN ‘Heat’THEN ‘Hot’
WHEN ‘HIGH WIND’ THEN ‘Windy’
WHEN ‘P’THEN NULL
WHEN ‘Rain’THEN ‘Rain’
WHEN ‘SNOW’THEN ‘Snow’
WHEN ‘Sunny’ THEN ‘Hot’
WHEN ‘Thaw’THEN ‘Thaw’
WHEN ‘Warm’THEN ‘Hot’ END — Now select the LOV Value ID corresponding to the standardized weather name
SELECT @weather_item_lov_valu_id = lov_valu_id
FROM EDIS_LOV_VALUE
WHERE lov_valu_name = @weather
AND lov_id = 9 INSERT INTO EDIS_ASSET_INSPECTION_ITEM ( Asst_Insp_ID, Insp_Tmpl_Item_ID, Valu_Entr_IND, Lov_ID, Item_LOV_Valu_ID, Insr_User, Updt_user, Insr_Dttm, Updt_Dttm )
VALUES ( @Asst_Insp_ID, 6, ‘Y’, 8, 44, current_user, current_user, @Insr_Dttm, @Updt_Dttm) INSERT INTO EDIS_ASSET_INSPECTION_ITEM ( Asst_Insp_ID, Insp_Tmpl_Item_ID, Valu_Entr_IND, Lov_ID, Item_LOV_Valu_ID, Insr_User, Updt_user, Insr_Dttm, Updt_Dttm )
VALUES ( @Asst_Insp_ID, 7, ‘Y’, Null, Null, current_user, current_user, @Insr_Dttm, @Updt_Dttm) INSERT INTO EDIS_ASSET_INSPECTION_ITEM ( Asst_Insp_ID, Insp_Tmpl_Item_ID, Valu_Entr_IND, Lov_ID, Item_LOV_Valu_ID, Insr_User, Updt_user, Insr_Dttm, Updt_Dttm )
VALUES ( @Asst_Insp_ID, 8, ‘Y’, 9, @weather_item_lov_valu_id, current_user, current_user, @Insr_Dttm, @Updt_Dttm) SELECT @ins_error2 = @@ERROR /********************************************************************************************************
Step 3:
Insert the data into EDIS_ASSET_INSPECTION_ASSET table.
–Inserting Stray Voltage Inspection data fetched in the cursor in the EDIS_Asset_Inspection_Asset table.
************************************************************************************************/
INSERT INTO EDIS_ASSET_INSPECTION_ASSET ( Asst_Insp_ID, AR_MS_Plat_ID,
AR_Asst_Type_ID, AR_Asst_Styp_ID, AR_Asst_Nmbr, AR_Asst_Name, Locn_Desc,
Insr_Dttm, Updt_Dttm, Insr_User, Updt_User) VALUES ( @Asst_Insp_ID, @MS_Plat_ID, @Asst_Type_ID, @Asst_Styp_ID,
@Asst_Nmbr, @Asst_Name, @Locn_Desc, @Insr_Dttm, @Updt_Dttm,
CURRENT_USER, CURRENT_USER) SELECT @ins_error3 = @@ERROR
/*****************************************************************************************************
Step 4:
Insert the data into EDIS_INSPECTION_SCHEDULE table.
Step 4.1:- Select the most recent Inspection date from the MasterLog table for a particular asset fetched in the cursor. Step 4.2:- Logic for rescheduling all testing completed between 12/1/2005 and 11/30/2006 for 11/30/2007.
Any testing completed after 11/30/2006 is rescheduled for 11/30/2008. Step 4.3:- Loading all Inspections after 12/1/2005 in the EDIS_INSPECTION_SCHEDULE table and rescheduling
the most recent inspection done on any particular asset to the appropriate Next Inspection date. *****************************************************************************************************/
–Step 4.1
SELECT @max_dttm = MAX(dateofreading) FROM MASTERLOG
WHERE asst_id = @asst_id –Step 4.2
IF @dateofreading BETWEEN ’12/1/2005′ AND ’11/30/2006′ AND @dateofreading = @max_dttm or @max_dttm is NULL
SET @next_sys_gen_date = ’11/30/2007′ ELSE
IF @dateofreading > ’11/30/2006′ AND @dateofreading = @max_dttm or @max_dttm is NULL
SET @next_sys_gen_date = ’11/30/2008′ –Step 4.3
IF @dateofreading > ’12/1/2005′ AND @dateofreading = @max_dttm or @max_dttm is NULL
BEGIN
INSERT INTO EDIS_INSPECTION_SCHEDULE ( AR_Dvsn_Ind, AR_Muni_ID, AR_Asst_ID, Insp_Type_ID, Last_Insp_Date,
Next_Sys_schd_Date, Usr_Ovrd_Ind, Insr_Dttm, Updt_Dttm, Insr_User, Updt_User, Schd_Optn_ID, Cmnt) VALUES ( @Dvsn_IND, @Muni_ID, @Asst_ID, @Insp_Type_ID, @Dateofreading, @next_sys_gen_date, ‘N’, @Insr_Dttm, @Updt_Dttm,
CURRENT_USER, CURRENT_USER, @Schd_Optn_ID, ‘All SV testing completed between 12/1/2005 and 11/30/2006 is rescheduled for 11/30/2007.Testing completed after 11/30/2006 is rescheduled for 11/30/2008.’ )
END
SELECT @ins_error4 = @@ERROR
/*******************************************************************************************************************
Step 5:
Insert the data into EDIS_SVL_LOAD table.
Step 5.1:- If data is successfully loaded in all the above tables without any errors, load a successful entry in the EDIS_SVL_LOAD
table for each row of data loaded in the above tables indicating with a "Y" in the Load_status_indicator column. Step 5.2:- If data is successfully loaded in EDIS_ASSET_INSPECTION table but unsuccessfully loaded(with errors)
in the EDIS_ASSET_INSPECTION_ITEM table, load an unsuccessful entry in the EDIS_SVL_LOAD table for
each row of data unsuccessfully loaded in the EDIS_ASSET_INSPECTION_ITEM table,
indicating with a "I" in the Load_status_indicator column. Step 5.3:- If data is successfully loaded in EDIS_ASSET_INSPECTION table and EDIS_ASSET_INSPECTION_ITEM table
but unsuccessfully loaded(with errors)in EDIS_ASSET_INSPECTION_ASSET table,
load an unsuccessful entry in the EDIS_SVL_LOAD table for each row of data unsuccessfully loaded in the EDIS_ASSET_INSPECTION_ASSET table, indicating with a "A" in the Load_status_indicator column. Step 5.4:- If data is successfully loaded in EDIS_ASSET_INSPECTION table and EDIS_ASSET_INSPECTION_ITEM table
and EDIS_ASSET_INSPECTION_ASSET table, but unsuccessfully loaded(with errors)in EDIS_INSPECTION_SCHEDULE table,
load an unsuccessful entry in the EDIS_SVL_LOAD table for each row of data unsuccessfully loaded in the EDIS_INSPECTION_SCHEDULE table, indicating with a "S" in the Load_status_indicator column. *****************************************************************************************************************/
IF @ins_error1 = 0 AND @ins_error2 = 0 AND @ins_error3 = 0 AND @ins_error4 = 0 BEGIN
–Step 5.1
SELECT @Asst_Insp_ID = ASST_Insp_ID
FROM EDIS_ASSET_INSPECTION_ASSET INSERT INTO EDIS_SVL_LOAD ( Logentry, Asst_ID, Asst_Insp_ID, Load_Dttm, Load_Stts_IND)
VALUES ( @Logentry, @Asst_ID, @Asst_Insp_ID, @Load_Dttm, ‘Y’ )
SELECT @ins_error = @@ERROR
END
ELSE
IF @ins_error1 = 0 AND @ins_error2 <> 0
BEGIN
–Step 5.2
INSERT INTO EDIS_SVL_LOAD ( Logentry, Asst_ID, Asst_Insp_ID, Load_Dttm, Load_Stts_IND)
VALUES ( @Logentry, @Asst_ID, @Asst_Insp_ID, @Load_Dttm, ‘I’ ) SELECT @ins_error = @@ERROR
END
ELSE IF @ins_error1 = 0 AND @ins_error2 = 0 AND @ins_error3 <> 0
BEGIN
–Step 5.3
INSERT INTO EDIS_SVL_LOAD ( Logentry, Asst_ID, Asst_Insp_ID, Load_Dttm, Load_Stts_IND)
VALUES ( @Logentry, @Asst_ID, @Asst_Insp_ID, @Load_Dttm, ‘A’ ) SELECT @ins_error = @@ERROR END
ELSE IF @ins_error1 = 0 AND @ins_error2 = 0 AND @ins_error3 = 0 AND @ins_error4 <> 0
BEGIN
–Step 5.4
INSERT INTO EDIS_SVL_LOAD ( Logentry, Asst_ID, Asst_Insp_ID, Load_Dttm, Load_Stts_IND)
VALUES ( @Logentry, @Asst_ID, @Asst_Insp_ID, @Load_Dttm, ‘S’ ) SELECT @ins_error = @@ERROR
END
–***************************** COMMIT TRANSACTION *******************************************
IF @ins_error = 0 –If data successfully loaded in the EDIS_SVL_LOAD Table— COMMIT TRANSACTION SVL_DATALOADING ELSE ROLLBACK TRANSACTION SVL_DATALOADING –***************************** NEXT CURSOR FETCH ******************************************* FETCH NEXT FROM svl_dataload_cur
INTO @Dvsn_IND, @MS_Plat_ID, @Muni_ID, @Regn_Ind, @Asst_ID, @Asst_Nmbr,@Asst_Name, @Locn_Desc,
@Logentry, @Dateofreading, @Weather, @Asst_Type_ID, @Asst_Styp_ID END
CLOSE svl_dataload_cur
DEALLOCATE svl_dataload_cur
–***************************** End CURSOR *******************************************
END

You can search SSP/Google for articles on how to get rid of cursors.
using Cursors in SQL server itself is a real headache.

Drop the cursors, they’re a waste of time you have to spend writing code … Let’s say you have two source tables, source_1 and source_2. You insert the rows from source_1 into target_1. INSERT INTO target_1 (<column_list&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> SELECT <column_list> FROM source_1 You then need the values from the identity column in target_1, for inserting source_2 rows into target_2. Simple: you join source_1, source_2 and target_1 on the natural key to be able to read the identity column from target_1, like this: INSERT INTO target_2 (<FK_identity_col>, <column_list&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> SELECT target_1.<identity_col>, <source_2_column_list> FROM source_2 INNER JOIN source_1 ON source_2.<naturalkey> = source_1.<naturalkey> INNER JOIN target_1 ON target_1.<naturalkey> = source_1.<naturalkey> etc. etc.

If this is posted under 2005 section,
then in 2005, you can also access inserted, deleted virtual tables directly in insert statement like: declare @t1 table(col1 int identity,col2 int)
declare @t2 table(col1 int,col2 int,col3 varchar(100))
insert @t1
OUTPUT inserted.col1,inserted.col2,’New Records’ INTO @t2
select 1 union all
select 2 select * from @t1
select * from @t2

Ranitjain, pardon my ignorance re SQL 2005, but can you access "inserted" and "deleted" outside of triggers?

Hi adriaan,
Yes in 2005, inserted, deleted can be accessed outside triggers in SP or in any query
using OUTPUT clause.
OUTPUT with INTO is used to insert into another table and
IF only OUTPUT is used it will return those records as resultset

— Ah, this is after my 3333rd post! Would have been nice to keep that number a little longer.– Thanks for the info on SQL 2005. Sounds like it could come in handy from time to time … … but one wonders what overhead was introduced into the core engine to make this type of ‘ad-hoc trigger’ possible.

]]>