Fine tune/Speed up my SQL stored procedure with currently take ~15-20mins
$10-30 AUD
Bezahlt bei Lieferung
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
Projekt-ID: #18793966
Über das Projekt
13 Freelancer bieten im Durchschnitt $41 für diesen Job
hi, i can fine tune and optimize your queries or store procedures and bring it down in second. i am an experienced db administrator. thanks
Hi Sir, I can improve you sql stored procedure. I can give you till monday. If you are interested let me know. Thanks, Alex
hi i read all requirement please share more detail i did similar 5 task i will provide 5 star rating work thanks less
Hi, I have 10 years extensive experience of working with Microsoft SQL Server & writing simple to very complex, high performance tsql scripts. Thanks
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
I have done several fine tuning of Stores Procedures and this should be an easy one. Consider this done.
Having 7 plus Years of SQL DBA experience would make this stored procdure tuning would more effective and ease.