Performance Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Performance Issue

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 01:40:46

We have a process where there is a daily insert/update from a staging database to a relation database and the number of records to be processed are 450004.Now this has to be processed emp_number by emp_number basically one by one and if one emp_number fails we rollback the entire transaction for that emp_number and move to next one.The errored emp_number will be inserted into a error log table if its not a fatal error.

Now a master stored procedure is being called by the cursor to pass each emp_number which will call more than 50 stored procedures to insert/update into the database for each emp_number :

Now as soon as the process starts hitting 73125 emp_numbers its slowing down.Is there any way we can slove this Or should be do in small bulks to resolve this issue.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-25 : 01:50:28

I guess you have your solution made so the only thing I can suggest is to cut the load into smaller batches since each record is independent of each other

--------------------
keeping it simple...

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 02:05:52

How do I do that ?. Should I first process 1000 records and then take the next 1000 records.Will it be a better solution

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-25 : 02:13:31

in your select, i assume there is some sort of order by or clustered index that you can use?

you can even use a temporary table to store those records' pks so you don't need to requery

lots of ways, just try and verify which one will suit you best...
you can post the tables' ddl and queries involved if you want a detailed solution

--------------------
keeping it simple...

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 02:22:59

In the current system I Initially load this 450004 records to a single table with status='NEW' and then in the cursor I pass this emp_numbers which are new in this table.Right now Iam using a cursor to call the master stored procedure which calls other 50 stored procedure to complete one emp_number.

Now I am thinking instead of loading the entire set of 450000 records in NEW status I will load 1000 records in NEW and rest of them in Hold and after the 1000 records are processed I will update another 1000 records to new and pass it to the cursor.Will this be a good solution ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-25 : 03:19:25

Anything that not involves a CURSOR for importing 450k rows will be a good idea.
Can you add a new identity column for the staging table and do a WHILE loop instead?

Peter Larsson
Helsingborg, Sweden

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 03:30:43

I can get the total record count and do a while loop.Will that work ?

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 13:45:00

Can I set the @@rowcount to 1000 and then execute the stored procedure

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-25 : 13:49:32

can you post some code?

Let's see the 2 sprocs

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 14:27:12

CREATE PROCEDURE [dbo].[usp_dd_collection_refresh_dal]

@latest_fd_refresh_dares_download_id INT
AS

DECLARE @error_int INT
DECLARE @sys_error_msg VARCHAR(500)
DECLARE @module_name VARCHAR(100)
DECLARE @executed_sp_name VARCHAR(500)
DECLARE @executed_sp_input_parameters VARCHAR(500)
DECLARE @executed_sp_ref_table_name VARCHAR(100)
DECLARE @executed_action VARCHAR(10)
DECLARE @error_notes VARCHAR(500)
DECLARE @computer_name VARCHAR(40)

DECLARE @main_sp_name VARCHAR(100)

DECLARE @nested_sp_name VARCHAR(500)
DECLARE @nested_sp_input_parameters VARCHAR(500)
DECLARE @nested_sp_ref_table_name VARCHAR(100)
DECLARE @nested_executed_action VARCHAR(10)

DECLARE @process_id INT
DECLARE @status VARCHAR(50)
DECLARE @comment VARCHAR(3000)
DECLARE @error_log VARCHAR(2000)
DECLARE @user_name VARCHAR(40)
DECLARE @latest_dd_coll_refresh_dares_download_id INT

/* Stored procedure usp_dd_dares_download_log_i inserts the dares download process log informations to the
tbl_dares_download_log table of dares_download database as a part of the daily Dares refresh.
*/

SET @main_sp_name='usp_dd_collection_refresh_dal'
PRINT 'Stored Procedure Executed - ' + @main_sp_name

SET @process_id=2 -- This is for Fidelity-Collection Refresh
SET @status='NEW'
SET @comment='The dares_download_id = ' + CONVERT(VARCHAR,@latest_fd_refresh_dares_download_id) +' is used to select the data from dares_download database to refresh collection database'
SET @user_name='dd_coll_refresh'

EXECUTE @error_int= usp_dd_dares_download_log_i @process_id,@status,@comment,@user_name,@latest_dd_coll_refresh_dares_download_id OUTPUT,@nested_sp_name OUTPUT,@nested_sp_input_parameters OUTPUT,@nested_sp_ref_table_name OUTPUT,@nested_executed_action OUTPUT
-- Error Handling Section
IF (@error_int <> 0 )
BEGIN
SET @module_name = 'Dares Download Log Information'
GOTO Error_Handler_Section

END
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE
@appraisal_i_refresh_id INT,@appraisal_u_refresh_id INT,
@bankruptcy_history_i_refresh_id INT,
@borrower_i_refresh_id INT,@borrower_u_refresh_id INT,
@borrower_address_i_refresh_id INT,@borrower_address_u_refresh_id INT,
@borrower_expense_i_refresh_id INT,@borrower_expense_u_refresh_id INT,
@borrower_loan_i_refresh_id INT,
@comment_i_refresh_id INT,
@corporate_advance_transaction_history_i_refresh_id INT,
@delinquency_i_refresh_id INT,@delinquency_u_refresh_id INT,
@escrow_balances_i_refresh_id INT,@escrow_balances_u_refresh_id INT,
@fees_i_refresh_id INT ,
@hazard_insurance_i_refresh_id INT,@hazard_insurance_u_refresh_id INT,
@investor_i_refresh_id INT,@investor_loan_i_refresh_id INT,
@lien_holder_i_refresh_id INT,@lien_holder_u_refresh_id INT,
@lien_holder_address_i_refresh_id INT,@lien_holder_address_u_refresh_id INT,
@lien_holder_contact_i_refresh_id INT,@lien_holder_contact_u_refresh_id INT,
@loan_detail_i_refresh_id INT,@loan_detail_u_refresh_id INT,
@loss_mitigation_history_i_refresh_id INT,
@mi_company_i_refresh_id INT,@mi_company_u_refresh_id INT,
@mi_insurance_i_refresh_id INT, @mi_insurance_u_refresh_id INT,
@multi_loan_i_refresh_id INT,
@note_i_refresh_id INT,
@payment_changes_i_refresh_id INT,@payment_changes_u_refresh_id INT,
@payment_change_details_i_refresh_id INT,@payment_change_details_u_refresh_id INT,
@payment_history_i_refresh_id INT,
@phone_contact_i_refresh_id INT,@phone_contact_u_refresh_id INT,
@repayment_plan_i_refresh_id INT,@repayment_plan_u_refresh_id INT,
@repayment_plan_details_i_refresh_id INT,@repayment_plan_details_u_refresh_id INT,
@stops_and_flags_i_refresh_id INT,@stops_and_flags_u_refresh_id INT,
@task_tracking_i_refresh_id INT, @task_tracking_u_refresh_id INT,
@tax_details_i_refresh_id INT,@tax_details_u_refresh_id INT,
@third_party_i_refresh_id INT,@third_party_u_refresh_id INT,
@third_party_address_i_refresh_id INT,@third_party_address_u_refresh_id INT,
@type_code_i_refresh_id INT

EXECUTE @error_int = usp_dd_coll_refresh_count_info_ids_i
@latest_dd_coll_refresh_dares_download_id,
@user_name,
@appraisal_i_refresh_id OUTPUT,@appraisal_u_refresh_id OUTPUT,
@bankruptcy_history_i_refresh_id OUTPUT,
@borrower_i_refresh_id OUTPUT,@borrower_u_refresh_id OUTPUT,
@borrower_address_i_refresh_id OUTPUT,@borrower_address_u_refresh_id OUTPUT,
@borrower_expense_i_refresh_id OUTPUT,@borrower_expense_u_refresh_id OUTPUT,
@borrower_loan_i_refresh_id OUTPUT,
@comment_i_refresh_id OUTPUT,
@corporate_advance_transaction_history_i_refresh_id OUTPUT,
@delinquency_i_refresh_id OUTPUT,@delinquency_u_refresh_id OUTPUT,
@escrow_balances_i_refresh_id OUTPUT,@escrow_balances_u_refresh_id OUTPUT,
@fees_i_refresh_id OUTPUT ,
@hazard_insurance_i_refresh_id OUTPUT,@hazard_insurance_u_refresh_id OUTPUT,
@investor_i_refresh_id OUTPUT,@investor_loan_i_refresh_id OUTPUT,
@lien_holder_i_refresh_id OUTPUT,@lien_holder_u_refresh_id OUTPUT,
@lien_holder_address_i_refresh_id OUTPUT,@lien_holder_address_u_refresh_id OUTPUT,
@lien_holder_contact_i_refresh_id OUTPUT,@lien_holder_contact_u_refresh_id OUTPUT,
@loan_detail_i_refresh_id OUTPUT,@loan_detail_u_refresh_id OUTPUT,
@loss_mitigation_history_i_refresh_id OUTPUT,
@mi_company_i_refresh_id OUTPUT,@mi_company_u_refresh_id OUTPUT,
@mi_insurance_i_refresh_id OUTPUT,@mi_insurance_u_refresh_id OUTPUT,
@multi_loan_i_refresh_id OUTPUT,
@note_i_refresh_id OUTPUT,
@payment_changes_i_refresh_id OUTPUT,@payment_changes_u_refresh_id OUTPUT,
@payment_change_details_i_refresh_id OUTPUT,@payment_change_details_u_refresh_id OUTPUT,
@payment_history_i_refresh_id OUTPUT,
@phone_contact_i_refresh_id OUTPUT,@phone_contact_u_refresh_id OUTPUT,
@repayment_plan_i_refresh_id OUTPUT,@repayment_plan_u_refresh_id OUTPUT,
@repayment_plan_details_i_refresh_id OUTPUT,@repayment_plan_details_u_refresh_id OUTPUT,
@stops_and_flags_i_refresh_id OUTPUT,@stops_and_flags_u_refresh_id OUTPUT,
@task_tracking_i_refresh_id OUTPUT, @task_tracking_u_refresh_id OUTPUT,
@tax_details_i_refresh_id OUTPUT,@tax_details_u_refresh_id OUTPUT,
@third_party_i_refresh_id OUTPUT,@third_party_u_refresh_id OUTPUT,
@third_party_address_i_refresh_id OUTPUT,@third_party_address_u_refresh_id OUTPUT,
@type_code_i_refresh_id OUTPUT,
@nested_sp_name OUTPUT,@nested_sp_input_parameters OUTPUT,@nested_sp_ref_table_name OUTPUT,@nested_executed_action OUTPUT

-- Error Handling Section
IF (@error_int <> 0 )
BEGIN
SET @module_name = 'Collection Refresh Count Id Information'
GOTO Error_Handler_Section
END

DECLARE @loan_number varchar(13)
DECLARE @processing_fd_dares_download_id INT
DECLARE @loop_restriction_id INT

DECLARE active_collection_loan_cur CURSOR READ_ONLY
FOR
SELECT loan_number FROM tbl_active_collection_loans WHERE status='NEW' AND dares_download_id=@latest_fd_refresh_dares_download_id

OPEN active_collection_loan_cur
FETCH NEXT FROM active_collection_loan_cur INTO @loan_number

SET @loop_restriction_id=0
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @loop_restriction_id
EXECUTE usp_dd_collection_refresh_transaction_dal
@latest_fd_refresh_dares_download_id,
@loan_number,
@appraisal_i_refresh_id,@appraisal_u_refresh_id,
@bankruptcy_history_i_refresh_id ,
@borrower_i_refresh_id,@borrower_u_refresh_id,
@borrower_address_i_refresh_id,@borrower_address_u_refresh_id,
@borrower_expense_i_refresh_id,@borrower_expense_u_refresh_id,
@borrower_loan_i_refresh_id,
@comment_i_refresh_id,
@corporate_advance_transaction_history_i_refresh_id,
@delinquency_i_refresh_id,@delinquency_u_refresh_id,
@escrow_balances_i_refresh_id,@escrow_balances_u_refresh_id,
@fees_i_refresh_id ,
@hazard_insurance_i_refresh_id,@hazard_insurance_u_refresh_id,
@investor_i_refresh_id,@investor_loan_i_refresh_id,
@lien_holder_i_refresh_id,@lien_holder_u_refresh_id,
@lien_holder_address_i_refresh_id,@lien_holder_address_u_refresh_id,
@lien_holder_contact_i_refresh_id,@lien_holder_contact_u_refresh_id,
@loan_detail_i_refresh_id,@loan_detail_u_refresh_id,
@loss_mitigation_history_i_refresh_id,
@mi_company_i_refresh_id,@mi_company_u_refresh_id,
@mi_insurance_i_refresh_id,@mi_insurance_u_refresh_id,
@multi_loan_i_refresh_id,
@note_i_refresh_id,
@payment_changes_i_refresh_id,@payment_changes_u_refresh_id,
@payment_change_details_i_refresh_id,@payment_change_details_u_refresh_id,
@payment_history_i_refresh_id,
@phone_contact_i_refresh_id,@phone_contact_u_refresh_id,
@repayment_plan_i_refresh_id,@repayment_plan_u_refresh_id,
@repayment_plan_details_i_refresh_id,@repayment_plan_details_u_refresh_id,
@stops_and_flags_i_refresh_id,@stops_and_flags_u_refresh_id,
@task_tracking_i_refresh_id,@task_tracking_u_refresh_id,
@tax_details_i_refresh_id,@tax_details_u_refresh_id,
@third_party_i_refresh_id,@third_party_u_refresh_id,
@third_party_address_i_refresh_id,@third_party_address_u_refresh_id,
@type_code_i_refresh_id,
@user_name

PRINT 'Loan Number' + '-' + @loan_number + 'processed'
FETCH NEXT FROM active_collection_loan_cur INTO @loan_number
SET @loop_restriction_id=@loop_restriction_id+1
PRINT 'No of loans processed - ' + CONVERT(VARCHAR,@loop_restriction_id)
END

CLOSE active_collection_loan_cur
DEALLOCATE active_collection_loan_cur

BEGIN
SET @status='COMPLETE'
EXECUTE @error_int= usp_dd_coll_refresh_count_info_status_u @latest_dd_coll_refresh_dares_download_id,@status,@user_name,@nested_sp_name OUTPUT,@nested_sp_input_parameters OUTPUT,@nested_sp_ref_table_name OUTPUT,@nested_executed_action OUTPUT
-- Error Handling Section
IF (@error_int <> 0 )
BEGIN
SET @module_name = 'Collection Refresh Count Information'
GOTO Error_Handler_Section
END
/* Stored procedure usp_dd_dares_download_log_u updates the dares download process log information to the tbl_dares_download_log
table of dares_download database as a part of the daily Dares refresh. Note :- Status - 'COMPLETE' on Sucess
*/
SET @error_log = 'Refreshed Successfully'
EXECUTE @error_int= usp_dd_dares_download_log_u @latest_dd_coll_refresh_dares_download_id,@status,@error_log,@user_name,@nested_sp_name OUTPUT,@nested_sp_input_parameters OUTPUT,@nested_sp_ref_table_name OUTPUT,@nested_executed_action OUTPUT
-- Error Handling Section
IF (@error_int <> 0 )
BEGIN
SET @module_name = 'Dares Download Log Information'
GOTO Error_Handler_Section
END
END

/************************************************************************Error Handling Section***********************************************************************************************/

SET @error_int = @@error

Error_Handler_Section:

IF (@error_int <> 0 )

BEGIN
SET @executed_sp_name = @main_sp_name + ' --> ' + @nested_sp_name
SET @executed_sp_input_parameters = @nested_sp_input_parameters
SET @executed_sp_ref_table_name = @nested_sp_ref_table_name
SET @executed_action = @nested_executed_action
SET @error_notes = 'Unable to ' + @executed_action + ' data in ' + @executed_sp_ref_table_name
SET @sys_error_msg =(SELECT DESCRIPTION FROM master..sysmessages WHERE error = @error_int)

EXEC dbo.usp_dd_MrErrorHandler
@ErrorNo = @error_int,
@ErrorMsg = @sys_error_msg,
@TableName = @executed_sp_ref_table_name,
@CallingSPName = @executed_sp_name,
@Parameters = @executed_sp_input_parameters,
@ModuleName = @module_name,
@ErrorNotes = @error_notes,
@ComputerNm = @computer_name
RETURN @error_int
END

ELSE

BEGIN
RETURN 0
END

/***************************************************************************************************************************************************************************************/
GO

Here "SELECT loan_number FROM tbl_active_collection_loans WHERE status='NEW' AND dares_download_id=@latest_fd_refresh_dares_download_id" is returing 450004 loans and Its being used in the cursor.

"usp_dd_coll_refresh_count_info_ids_i" is used to update the no of record processed in each table

How can make into small chunks of 1000 or best solution other than this ?

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-25 : 16:29:51

X002548 can you please help me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources