CODEWITHSUNDEEP
sql-server
user2432269
user2432269

Reputation:

How to decerase execution time while executing stored procedure in sql server

I have a stored procedure like this:

alter procedure [dbo].[fetch]
@locid integer 

as 
begin
SET NOCOUNT on
  select * from transaction_tbl where locid=@locid
end

While executing this i am getting around 1500 records..that is taking around 35 seconds, is there any way to decrees this time

Upvotes: 0

Views: 92

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

Obviously, the way to speed up the query is to add an index on transaction_tbl(loc_id). I want to suggest that such a simple stored procedure should really be implemented as an inline table valued function:

create function udf_fetch (@LocId)
    @LocId int
)
returns table
return(select *
       from transaction_tbl 
       where LocId = @Locid;
      );

You can then call it as:

select *
from dbo.udf_fetch(@LocId)

So you can use the results in combination with other tables in a from clause.

Upvotes: 1

sgeddes
sgeddes

Reputation: 62861

There probably isn't a one answer works here, but usually when this is the case, you need to add an index to your your table.

Make sure you have an index on the locid field.

CREATE UNIQUE NONCLUSTERED INDEX (indexname)
ON transaction_tbl (locid)

http://msdn.microsoft.com/en-us/library/ms188783.aspx

Upvotes: 0

Related Questions