SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Twenty tips to write a good stored procedure


Twenty tips to write a good stored procedure

Author
Message
arup chakraborty
arup chakraborty
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1222 Visits: 365
Comments posted to this topic are about the item Twenty tips to write a good stored procedure
amirzainab
amirzainab
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 120
Sorry Arup, this article deserves a 5. I rated it one by mistake. My apologies.
Kaborka
Kaborka
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 79
Don't you mean to advise "set nocount on"?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (296K reputation)SSC Guru (296K reputation)SSC Guru (296K reputation)SSC Guru (296K reputation)SSC Guru (296K reputation)SSC Guru (296K reputation)SSC Guru (296K reputation)SSC Guru (296K reputation)

Group: General Forum Members
Points: 296125 Visits: 46686
Sorry, but I'm going to greatly disagree with a number of these 'tips'

8. sp_executeSQL and the KEEPFIXED PLAN options - Both sp_executesql and the KEEPFIXED PLAN option avoid the recompilation of a stored procedure.


Well, maybe. There are still things that will force a recompile. Recompiles aren't always bad, sometimes they are, sometimes running the proc with an outdated and inefficient plan is far, far worse.

11. More WHERE clause hints - Avoid unnecessary conditions in the WHERE Clause. You can easily use the first example instead of the second one. The second example uses one extra OR condition which can be avoided using the first example.
SELECT emp_name FROM table_name WHERE LOWER(emp_name) = 'edu'


SELECT emp_name FROM table_name WHERE emp_name = 'EDU' OR emp_name = 'edu'



Completely the wrong way around. The first, with a function on the column, cannot use an index seek. The best it can run with is a full index scan which won't be cheap on a large table. The second can use an index seek and, in fact, will do two seeks against the table. Unless the table is very small, the second will run way faster than the first.

General rule. Never use a function on a column in the where clause unless there's absolutely no way around. It prevents SQL doing index seeks, forcing full index scans or even table scans.

Also, try to avoid IN. While checking the existence of some values, then use EXISTS instead of IN. Because IN counts the NULL values also, hence slower than EXISTS. Since EXISTS returns Boolean(Yes/No) but IN returns all values hence result set for IN is heavier than EXISTS.
SELECT * FROM employee WHERE emp_no NOT IN (SELECT emp_no from emp_detail)


SELECT * FROM employee WHERE NOT EXISTS (SELECT emp_no FROM emp_detail)



Those two queries are not equivalent, hence they won't be running the same speed.

IN checks for matches, so the first query will return all records in employee where there isn't a match in emp_detail. Exists returns true if there are any rows at all in the subquery, it doesn't care about the values returned in the select. Hence that second query will only return results if there are no rows at all in emp_detail and, if that's the case, it will return all the rows in the employee table.

The equivalent query using exists is this:
SELECT * FROM employee WHERE NOT EXISTS (SELECT 1 FROM emp_detail where employee.emp_no = emp_detail.emp_no)


That will probably run much the same speed as the IN (haven't tested)

EDIT: The queries are only equivalent when the subquery returns no NULLs. If it does, NOT IN and NOT EXISTS return different results.

12. CAST and CONVERT - Try to use CAST instead of CONVERT. CAST is ANSI-92 standard but CONVERT works in MS SQL server only. Also, Convert may be deprecated in future MS SQL releases.


Please provide a reference that states that CONVERT may be deprecated in future versions.

14. Avoid using cursors - Try to use temporary table/table variables with identity column and then iterate all the tables using WHILE loop and a looping counter, which will map with the identity column.


A while loop is no better than a cursor. It's still looping and working on rows one by one. If you're going to remove a cursor, replace it with set-based code. Properly written set-based code will be way faster than cursor or while loop for the vast majority of cases.

16.Subquery vs JOINs - But try to avoid correlated sub queries because it makes the query much slower.

Most of the time correlated subqueries run just as fast as the equivalent query with a join and most of the time have the same exec plan. The exceptions are when the correlation function is an inequality and when there's a TOP (1) ... ORDER BY in the subquery.

18. Try to use table variables instead of Temporary Tables - Temp tables can cause stored procedures to recompile. But table variables were designed specifically to guard against stored procedure recompiles during execution.

In SQL 2005 and higher, with temp table caching, use of temp tables does not always cause a recompile. Adding rows to a temp table may still cause a recompile though.
It's true that table variables don't cause recompiles, but the downside is that the optimiser estimates 1 row in the table variable. If there's a lot more, the plan that the optimise comes up with may be very bad indeed and the query run very, very slow.
Recommendation: Try both see how they behave, use the one that causes the least problems for the particular proc.

19.Index scans are much faster than table scans.

And index seeks are faster still.

But when a table returns smaller rows, then it is better to use a table scan.

I assume you mean 'smaller number of rows' rather than 'smaller rows'. Even so, that's not necessarily true. See this blog post
http://sqlblogcasts.com/blogs/simons/archive/2009/08/06/Do-you-need-to-index-very-small-table-.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Knut Boehnert
Knut Boehnert
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1458 Visits: 388
Very nice guidelines but with regards to performance point 11 doesn't make sense to me.

"11. More WHERE clause hints - Avoid unnecessary conditions in the WHERE Clause. You can easily use the first example instead of the second one. The second example uses one extra OR condition which can be avoided using the first example."

The example shows first query with an OR clause like:
--Where emp_domain = 'EDU' or emp_domain = 'edu'

against

--Where Lower(emp_domain) = 'edu'

Isn't the function in most cases slower than the OR clause? At least that's what I was taught and in most cases I tested this was the case.

[Edit] Looks Like Gail already answered this.
Chris Howarth-536003
Chris Howarth-536003
Right there with Babe
Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)

Group: General Forum Members
Points: 717 Visits: 1167
I disagree with point 7 - regarding the sp_ prefix, try the example below.

CREATE DATABASE Test
GO
USE [master]
GO
CREATE PROCEDURE dbo.sp_TempProc
AS
SELECT 'master'
GO
USE [Test]
GO
CREATE PROCEDURE dbo.sp_TempProc
AS
SELECT 'test'
GO
USE [Test]
GO
EXEC sp_TempProc
GO
USE [master]
GO
EXEC sp_TempProc
GO

Chris
GilaMonster
GilaMonster
SSC Guru
SSC Guru (296K reputation)SSC Guru (296K reputation)SSC Guru (296K reputation)SSC Guru (296K reputation)SSC Guru (296K reputation)SSC Guru (296K reputation)SSC Guru (296K reputation)SSC Guru (296K reputation)

Group: General Forum Members
Points: 296125 Visits: 46686
Chris Howarth (8/10/2009)
I disagree with point 7 - regarding the sp_ prefix, try the example below.


What he said is mostly true. It was true on SQL 2000 (if I recall). On 2005, the check is first done to the MSSQLSystemResource Database (a hidden system DB). If I recall (haven't tried it) it is still possible to get resolution to master with a proc starting with sp_ but the proc in master must be marked as a system object.

Still, if you check with profiler, a call to a proc that starts with sp_ does have SQL check DBs other than the current one first.

Easy way to show that there's some odd name resolution happening.

CREATE PROCEDURE sp_help
AS
SELECT 'My Local version of sp_help'
GO

exec sp_help



Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ta.bu.shi.da.yu
ta.bu.shi.da.yu
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1087 Visits: 494
You say that the following is about to be deprecated as it isn't SQL-92 compliant:

SELECT * FROM employee e1, employee_dtl e2
WHERE e1.id = e2.id



Where did you get this info from? I wasn't aware that implicit joins were deprecated in SQL-92!

Random Technical Stuff
gfabbri
gfabbri
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 34
sorry, it is better to put SET NOCOUNT ON, isn't it?
Ashesh
Ashesh
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1531 Visits: 197
I completely agree with the fact that stored proc with the prefix "sp_" is reserved for system stored procs. However, the 2nd part is not correct.
If a stored procedure uses same name in both the user database and a system database, the stored procedure in the user database will never get executed.

To verify this I've executed the below code, which suggest irrespective of the prefix, it’s going to execute the proc which has been referred.
T-SQL Code
use master
drop proc dbo.sp_test
go
create proc dbo.sp_test
as
print 'SP from Master DB'
go
use AdventureWorks
go
drop proc dbo.sp_test
go
create proc dbo.sp_test
as
print 'SP from AdvWork DB'
go

exec dbo.sp_test



Result

SP from AdvWork DB




Thanks,
Ashesh

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search