Make my stored procedure more efficient

Question

I have already created a stored procedure for Select Query and it is working Fine. But I need some thing more efficient to make my SP Query. Have you any suggestion for that.

Create Procedure usp_SelectUserProfile
    @UserId int
As
Begin
     Select <column name> from DB where UserId = @UserId
End

Thnx

Do you actually have a problem with speed? There's not much you can do to improve that query. If UserId is the PK then it should be a simple index seek. — Blorgbeard, Jan 04 '12 at 11:34

score 5 · Accepted Answer · answered Jan 04 '12 at 11:28

5

The only thing i can imagine here is:

Create Procedure usp_SelectUserProfile
    @UserId int
As
Begin
     SET NOCOUNT ON;
     Select <column name> from DB where UserId = @UserId
End

Suppresses sending the amount of affected rows to client, on small procedures with many calls can save you up to 17% of call time

answered Jan 04 '12 at 11:28

Oleg Dok

21,109
4
45
54

Suppresses sending the amount of affected rows to client, on small procedures with many calls it can save you up to 17% of call time – Oleg Dok Jan 04 '12 at 11:33
17% is oddly specific - I'd be interested to read any reference you have for that – Blorgbeard Jan 04 '12 at 11:36
@Blorgbeard - read it in the book, now I'll try to find a reference – Oleg Dok Jan 04 '12 at 11:39
Thanks! Don't take too much trouble if you can't find it, just an idle curiosity :) – Blorgbeard Jan 04 '12 at 11:50
2

@Blorgbeard - actually - found it! Citation: Best Practice With every returned record set, SQL Server will, by default, also send a message stating the number of rows affected or returned. Not only is this a nuisance, but I have found in my informal testing that it can slow a query by up to 17 percent depending on the query’s complexity. Therefore, get into the habit of beginning every stored procedure with the following code: CREATE PROC MyProc AS SET NOCOUNT ON; Citation ends (Paul Nielsen, Mike White, Uttam Parui - Microsoft Sql Server Bible, p.622 ISBN 978-0-470-25704-3 – Oleg Dok Jan 04 '12 at 11:52
@OlegDok: See my question here for lots more: http://stackoverflow.com/questions/1483732/set-nocount-on-usage – gbn Jan 04 '12 at 12:12
@gbn - great post, thanks, I knew only about problems with linq2sql and editing the rows directly from ssms when NOCOUNT is globally OFF with startup option – Oleg Dok Jan 04 '12 at 12:16

score 0 · Answer 2 · answered Jan 04 '12 at 11:30

0

you can use SET NOCOUNT ON

Create Procedure usp_SelectUserProfile
@UserId int
As
Begin
 SET NOCOUNT ON
 Select <column name> from DB where UserId = @UserId
End

read this for more details http://msdn.microsoft.com/en-us/library/ms189837.aspx

answered Jan 04 '12 at 11:30

reshma k

544
1
3
11

score 0 · Answer 3 · edited May 23 '17 at 11:48

If UserID is not the Primary key, then create a new index on (UserID) INCLUDE (<column name>)
Qualify all object references with schema Select <column name> from dbo.MyTable where UserId = @UserId. Also use this on the stored procedure too. Without this, you'll prevent plan re-use
Ensure datatype and length of column UserId and @UserId are identical to avoid datatype precedence and implicit conversions
Add SET NOCOUNT ON as mentioned on other answers: see SET NOCOUNT ON usage

Make my stored procedure more efficient

3 Answers3