Fine tune/Speed up my SQL stored procedure with currently take ~15-20mins

Končano Objavljeno pred 5 letoma/leti Plačilo ob prevzemu
Končano Plačilo ob prevzemu

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

Administracija podatkovnih baz Razvoj podatkovne baze Microsoft SQL strežnik SQL

ID projekta: #18793966

Več o projektu

13 predlogov Oddaljen projekt Aktiven pred 5 letoma/leti

Dodeljeno:

freegid

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 bul Več

$25 AUD v 1 dnevu
(1 Ocena)
0.6

13 freelancerjev ponuja v povprečju za $41 na tem delu

adeelpirzada

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

$15 AUD v 1 dnevu
(6 ocen)
4.9
iosif2455

Hi Sir, I can improve you sql stored procedure. I can give you till monday. If you are interested let me know. Thanks, Alex

$30 AUD v 1 dnevu
(5 ocen)
3.3
mukesh30march

hi i read all requirement please share more detail i did similar 5 task i will provide 5 star rating work thanks less

$50 AUD v 1 dnevu
(6 ocen)
3.2
gladrich

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 Več

$30 AUD v 1 dnevu
(0 ocen)
0.0
deepji

Hi, I have 10 years extensive experience of working with Microsoft SQL Server & writing simple to very complex, high performance tsql scripts. Thanks

$25 AUD v 1 dnevu
(0 ocen)
0.0
SiriusZN

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

$25 AUD v 3 dneh
(0 ocen)
0.0
lp002

I have done several fine tuning of Stores Procedures and this should be an easy one. Consider this done.

$28 AUD v 1 dnevu
(0 ocen)
0.0
mareefuddin

Having 7 plus Years of SQL DBA experience would make this stored procdure tuning would more effective and ease.

$25 AUD v 1 dnevu
(0 ocen)
0.0
gsahoo001

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 i Več

$25 AUD v 1 dnevu
(0 ocen)
0.0