Make Stored Procedure Cleaner and Faster

Can someone assist me with making working stored procedure faster and cleaner below

ALTER PROCEDURE [dbo].[ProcessCustomerHours]

@customerNumber varchar(50),

@startDate datetime,

@endDate datetime,

@firstBatch int,

@secondBatch int,

@hours decimal(6,2) output

DECLARE @ResultValue decimal(6,2)

BEGIN

SELECT CustomerNumber

,SUM(CASE WHEN PayType = 1 THEN [Hours] ELSE 0 END) Hours

,SUM(CASE WHEN PayType <> 1 THEN [Hours] ELSE 0 END) PTO

,MIN(StartDate) StartDate

,MAX(EndDate) EndDate

,(Select Sum ([Hours]) PHours

FROM CustomerSheetHdr h

JOIN CustomerSheetDtl d on h.CustomerSheetHdrId=d.CustomerSheetHdrId

Where StartDate >= convert(datetime, @startDate)

And EndDate <= convert(datetime, @endDate)

And [CustomerNumber] = @customerNumber

And CustomerSheetBatchId in (@firstBatch, @secondBatch)

And PayType = 1

Group By CustomerNumber

Having Sum([Hours]) > 0) PreviousHours

INTO #TempAggregatedTable

FROM CustomerSheetHdr h

JOIN CustomerSheetDtl d on h.CustomerSheetHdrId=d.CustomerSheetHdrId

Where StartDate >= (Select Distinct MIN(StartDate)

FROM CustomerSheetHdr h

JOIN CustomerSheetDtl d on h.CustomerSheetHdrId=d.CustomerSheetHdrId

Where CustomerSheetBatchId in (@firstBatch, @secondBatch)

Group By CustomerNumber)

And EndDate <= (Select Distinct MAX(EndDate)

FROM CustomerSheetHdr h

JOIN CustomerSheetDtl d on h.CustomerSheetHdrId=d.CustomerSheetHdrId

Where CustomerSheetBatchId in (@firstBatch, @secondBatch)

Group By CustomerNumber)

And [CustomerNumber] = @customerNumber

And CustomerSheetBatchId in (@firstBatch, @secondBatch)

Group By CustomerNumber

Having Sum ([Hours]) > 80

Hi David,

There are lots of articles out there which will help you do this.

I am not a good writer but i can try to give you some advice.

1. Try to declare variables in a line at a place becoz all variables will get allocated when procedure runs.

2. Try to use proper indenting ie.

IF(@VAR1 > 1)

BEGIN

PRINT 'YES'

END

Above will help you find codes / bugs easily.

3. Use Top clause when you are checking existance with ids or any search criteria.

4. Maximize use of where clause to minimize duplicate data.

5. Use index (clustered / Non-Clustered) on tables.

6. Select only columns which are required in any select statement.

7. Try to make functions for checking ie.

If you are checking Existance of any user put it in a function and call it in a procedure.

8. Assign variables before accessing it (not stored procedure parameters).

9. user SET NOCOUNT ON; in the beginning of each procedure which will stop counting number of rows affected by the statement being executed which will lower the execution time.

10. Minimize use of joins (Use when compulsory)

These are basic steps for any good stored procedure.

hope it helps.

Thank you.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Vishal Jadav10y ago
Hi David,

There are lots of articles out there which will help you do this.

I am not a good writer but i can try to give you some advice.

1. Try to declare variables in a line at a place becoz all variables will get allocated when procedure runs.
2. Try to use proper indenting ie.

IF(@VAR1 > 1)
BEGIN
PRINT 'YES'
END
Above will help you find codes / bugs easily.

3. Use Top clause when you are checking existance with ids or any search criteria.
4. Maximize use of where clause to minimize duplicate data.
5. Use index (clustered / Non-Clustered) on tables.
6. Select only columns which are required in any select statement.
7. Try to make functions for checking ie.
If you are checking Existance of any user put it in a function and call it in a procedure.
8. Assign variables before accessing it (not stored procedure parameters).
9. user SET NOCOUNT ON; in the beginning of each procedure which will stop counting number of rows affected by the statement being executed which will lower the execution time.
10. Minimize use of joins (Use when compulsory)

These are basic steps for any good stored procedure.

hope it helps.
Thank you.