Best way to write stored procedures

Question

Post reply

Best way to write stored procedures

Sana4u

SSC-Addicted

Points: 464
More actions
June 26, 2014 at 11:30 am

#396968

Hi,
What is the best way to write stored procedures. Any tips on that also, I need to know how can I optimize stored procedures

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

performance is really a broad quesiton, and it's not just procedure codign style you need to consider;

indexing and SARG-ability of the queries are probably the two biggest things i would review.

here's my quick check list:

· Is a cursor being used? or a looping construct like a WHILE for FOR Each?

· How much Data is being returned? are there millions of rows, or columns with huge varbinary/image/varchar maxes being returned?·

· Are there clustered indexes on all the tables involved?

· Are there indexes to support the query?

· Is There a WHERE statement being used? if not...that's a table scan of all the data.

· Are there columns that are being selected that are not used (ie a VIEW of 50 columns, but you need only three columns)

· Are the statistics up to date?

· Are you using catch all queries?

· Are the WHERE statement parameters SARG-able?

· Are any functions being used in the WHERE statement?

· Are there any inline SELECT statements to get a a single or correlated value (i.e. ColumnName = (SELECT...)

· Could the Indexes benefit from INCLUDE columns?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sana4u SSC-Addicted Points: 464 More actions · Answer 2

HI

Is there a better way to write the following ?? Please view the below comments I have received in one of the stored procedure

Plenty wrong. For starters, this

where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@YearStartDate,110) as datetime) and cast(convert(varchar(10),@YearEndDate,110) as datetime)

Change that so that you have no functions of any form on the columns. Hint, just drop the casts and converts.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Sana4u (6/28/2014)
HI
Is there a better way to write the following ?? Please view the below comments I have received in one of the stored procedure
Plenty wrong. For starters, this
where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@YearStartDate,110) as datetime) and cast(convert(varchar(10),@YearEndDate,110) as datetime)
Change that so that you have no functions of any form on the columns. Hint, just drop the casts and converts.

A simple change would be this:

WHERE

PostingDate between @YearStartDate and @YearEndDate

Without looking at the code, sample data, expected results, table definitions not much more. I will say that even this isn't how I would write this particular filter, but I would need the information I just mentioned previously to write it accurately.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Lynn Pettis (6/28/2014)
Sana4u (6/28/2014)
HI
Is there a better way to write the following ?? Please view the below comments I have received in one of the stored procedure
Plenty wrong. For starters, this
where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@YearStartDate,110) as datetime) and cast(convert(varchar(10),@YearEndDate,110) as datetime)
Change that so that you have no functions of any form on the columns. Hint, just drop the casts and converts.
A simple change would be this:
WHERE
PostingDate between @YearStartDate and @YearEndDate
Without looking at the code, sample data, expected results, table definitions not much more. I will say that even this isn't how I would write this particular filter, but I would need the information I just mentioned previously to write it accurately.

Since the datatype of that column CAN contain a time, I'd recommend bullet-proofing the WHERE clause so that there's no chance of missing most of the day associated with the @YearEndDate as follows...

WHERE PostingDate >= @YearStartDate

AND PostingDate < DATEADD(dd,1,@YearEndDate)

;

To be honest, I'd never use BETWEEN on such temporal criteria even if the datatype were the DATE datatype because you just can't tell when someone will decide that the time of day is equally important and change the datatype to accommodate it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Jeff Moden (6/28/2014)
Lynn Pettis (6/28/2014)
Sana4u (6/28/2014)
HI
Is there a better way to write the following ?? Please view the below comments I have received in one of the stored procedure
Plenty wrong. For starters, this
where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@YearStartDate,110) as datetime) and cast(convert(varchar(10),@YearEndDate,110) as datetime)
Change that so that you have no functions of any form on the columns. Hint, just drop the casts and converts.
A simple change would be this:
WHERE
PostingDate between @YearStartDate and @YearEndDate
Without looking at the code, sample data, expected results, table definitions not much more. I will say that even this isn't how I would write this particular filter, but I would need the information I just mentioned previously to write it accurately.
Since the datatype of that column CAN contain a time, I'd recommend bullet-proofing the WHERE clause so that there's no chance of missing most of the day associated with the @YearEndDate as follows...
WHERE PostingDate >= @YearStartDate
AND PostingDate < DATEADD(dd,1,@YearEndDate)
;
To be honest, I'd never use BETWEEN on such temporal criteria even if the datatype were the DATE datatype because you just can't tell when someone will decide that the time of day is equally important and change the datatype to accommodate it.

I fully agree with you, Jeff. I would rather use the >= and < when working with dates. All I was trying to do was show the OP how easy it was to strip the functions from the column and variables.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Sana4u (6/26/2014)
Hi,
What is the best way to write stored procedures. Any tips on that also, I need to know how can I optimize stored procedures

To be honest, this is a subject that's way too big for a forum question. You could spend two solid 7 day weeks, 8-10 hours per day in a course by the best SQL teacher in the world and still come up real short.

That, notwithstanding, every journey begins with a first step. Lookup CREATE PROCEDURE, SELECT, JOINs, and INDEXES in Books Online and study them until you're sick of it all. Then, you'll be qualified to start learning about things like the mythical "best way" (mythical because it changes depending on the problem at hand), optimization, and the ever true "It Depends".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Lynn Pettis (6/28/2014)
Jeff Moden (6/28/2014)
Lynn Pettis (6/28/2014)
Sana4u (6/28/2014)
HI
Is there a better way to write the following ?? Please view the below comments I have received in one of the stored procedure
Plenty wrong. For starters, this
where cast(convert(varchar(10),PostingDate,110) as datetime) between cast(convert(varchar(10),@YearStartDate,110) as datetime) and cast(convert(varchar(10),@YearEndDate,110) as datetime)
Change that so that you have no functions of any form on the columns. Hint, just drop the casts and converts.
A simple change would be this:
WHERE
PostingDate between @YearStartDate and @YearEndDate
Without looking at the code, sample data, expected results, table definitions not much more. I will say that even this isn't how I would write this particular filter, but I would need the information I just mentioned previously to write it accurately.
Since the datatype of that column CAN contain a time, I'd recommend bullet-proofing the WHERE clause so that there's no chance of missing most of the day associated with the @YearEndDate as follows...
WHERE PostingDate >= @YearStartDate
AND PostingDate < DATEADD(dd,1,@YearEndDate)
;
To be honest, I'd never use BETWEEN on such temporal criteria even if the datatype were the DATE datatype because you just can't tell when someone will decide that the time of day is equally important and change the datatype to accommodate it.
I fully agree with you, Jeff. I would rather use the >= and < when working with dates. All I was trying to do was show the OP how easy it was to strip the functions from the column and variables.

No problem and I knew you were probably just taking a shortcut. I just wanted to make sure the OP knew that BETWEEN can really be a problem when it comes to temporal criteria.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)