Suoritettu

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

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

Taidot: Tietokannan hallinto, Tietokantakehitys, Microsoft SQL Server, SQL

Näytä lisää: sql stored procedure return table, sql stored procedure check expiry date, convert sql stored procedure oracle stored procedure, mysql large table optimization, how to speed up mysql select query, mysql millions of rows performance, stored procedure good or bad, alternatives to stored procedures, improve mysql performance large database, speed up mysql queries large tables, speed up insert into sql server, sql stored procedure return true, convert sql stored procedure oracle stored procedures, sql stored procedure usps api, sql stored procedure copy data, sql stored procedure zipcode distance, sql stored procedure tutorial, SQL STORED PROCEDURE, convert sql stored procedure, sql stored procedure table input

Tietoa työnantajasta:
( 1 arvostelu ) GLEN IRIS, Australia

Projektin tunnus: #18793966

Myönnetty käyttäjälle:

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 Lisää

$25 AUD 1 päivässä
(0 Arvostelua)
0.0

15 freelanceria on tarjonnut keskimäärin %project_bid_stats_avg_sub_26% %project_currencyDetails_sign_sub_27% tähän työhön

dhanuitdevendra

Hello, Hope you are doing well! We can help you with our Database Developer as a Service (DEVaaS). Please visit [login to view URL] for details. Please have a look at our existing customer Lisää

$200 AUD 5 päivässä
(5 arvostelua)
4.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 1 päivässä
(6 arvostelua)
3.2
bbbssolutions

Hi, i am professional sql server developer having vast knowledges in DDL DMlL command and specialist in Store Procedure.I am working for last 10 years. I can solve your issue...i need to first diagnosis this...lets ch Lisää

$100 AUD 0 päivässä
(8 arvostelua)
2.9
abfreelancec

Please message me to start your work. Please review my profile. https://www.freelancer.com/u/vw7590795vw.html I have 9.3 years experience in MS SQL Server and 6.5 years experience in SSRS, SSIS (Import / Export). Pl Lisää

$14 AUD 1 päivässä
(3 arvostelua)
2.3
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 1 päivässä
(5 arvostelua)
2.5
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 1 päivässä
(2 arvostelua)
2.7
$30 AUD 1 päivässä
(2 arvostelua)
1.8
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 Lisää

$30 AUD 1 päivässä
(0 arvostelua)
0.0
ashishcompwiz

Hi, I can decide rates from your side (price can be negotiated) I understand your requirement I have knowledge in this filed 5+ yr. kindly let me know in brief what kind of requirement do you have lets can show you my Lisää

$30 AUD 1 päivässä
(0 arvostelua)
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 1 päivässä
(0 arvostelua)
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 3 päivässä
(0 arvostelua)
0.0
lp002

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

$28 AUD 1 päivässä
(0 arvostelua)
0.0
mareefuddin

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

$25 AUD 1 päivässä
(0 arvostelua)
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 Lisää

$25 AUD 1 päivässä
(0 arvostelua)
0.0