Fine tune/Speed up my SQL stored procedure with currently take ~15-20mins
$10-30 AUD
완료함
게시됨 약 5년 전
$10-30 AUD
제출할때 지불됩니다
I need an expert to fine tune my stored procedure which I don't think is too complex.
It tries to insert/update transactions/records in a master table only if it passes some rules.
If it fails it insert/updates the transactions/records in another table.
The procedure for 45,000 line takes around 15 mins.
It works perfectly as is, but it takes so long and I know my tables in a couple of months will be +1million records so i'm nervous how long it will take then.
--------------------------------------------------------------------
SQL SHELL EXAMPLE - Working one can be provided with test data
--------------------------------------------------------------------
USE [CHAMP_DW]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ETL_stage_evestment_performance_to_champ]
@BatchImportID UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
SET NOCOUNT ON;
/*Variable Declaration*/
/*Assign static value for whole one time process*/
/*Create staging table so we can re-run procedure instead of having to rerun complete task again and again*/
-- POPULATE TEMP TABLE
/*While loop started to get process record one by one*/
WHILE EXISTS(SELECT 1 FROM #stagingPerformance AS FHD WITH (NOLOCK))
BEGIN
BEGIN TRY
BEGIN TRANSACTION
/*assign 1 record values to respective variables*/
/*Record wise validation start*/
SELECT
@ErrorCode = NULLIF(STUFF(@ErrorCode,1,1,''),''),
@ErrorMessage = NULLIF(STUFF(@ErrorMessage,1,1,''),'')
IF @ErrorMessage IS NOT NULL
BEGIN
;THROW 50552,'VALIDATION RAISE ERROR.',1
END
/*Record wise validation end*/
-- INSERT/UPDATE FACT TABLE
--COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- ROLL BACK
-- INSERT/UPDATE LOG TABLE
--COMMIT TRANSACTION
END CATCH
-- CLEAR VARIABLES
END
DROP TABLE #stagingPerformance
END
I have written ETL type scripts previously and have extensive experience in SQL Server
I would have to understand the exact process and data what you are doing as it needs to be written simpler and rather with a bulk update instead of a while loop.
If you can explain on the error condition should the whole process fail or only the invalid records fail
Eliminating of the temp table is also probably smart as it is dependent on your temp database setup
Hi,
By seeing procedure i can straightway say that there is use of temporary table in procedure.
1. Make sure that temporary tablespace enough database space available. ( Increase tempdb space).
2. If at all if you can avoid from temporary table to permanant table then it will improve performance.
If it solves please accept the bid.
Regards
Hi,
I have been working with SQL for the last 15 years. I have good knowledge on optimization including reading execution plans and using the Query Analyzer.
Thanks
Dear Sir ,
I have 20 years of experience in BI Analytics and Data Integration . Expert level work experience in SQL and PL/SQL in various database like Oracle , SQL Server , MY SQL , Teradata etc . Good exposure in Database tuning and application tuning .
If you think I can help you then kindly let me know .
Regards ,
Goutam Sahoo