Slow Stored Procedure Response

[byrne1]

When I execute the following SQL command from within the Query Analyzer it takes one second to return 84 rows:

SELECT * FROM LOANS WHERE SHORT_NAME LIKE 'BYRNE%'

When I execute this same command through a stored procedure from within the Query Analyzer it takes 1 1/2 minutes to return the same 84 rows. Can someone help me understand why this happens and what I can do to speed up my stored procedure? Thanks in advance for your help.

QUERY ANALYZER COMMAND:
sp_GetLoansName byrne


STORED PROCEDURE:
CREATE PROCEDURE sp_GetLoansName
@ShortName varchar(20)
as
select * from loans where short_name like left(@ShortName,len(@shortname)) + '%'

 

[tlbroadbent]

When you execute the query Query Analyzer, the criteria has been resolved - LIKE 'BYRNE%'.

In the stored procedure SQL must reevaluate the function for every row in the table - LIKE (@ShortName,len(@shortname)) + '%'.

Change the SP as folows and let me know if it helps.

CREATE PROCEDURE sp_GetLoansName
@ShortName varchar(20)
as
Set @shortname=left(@ShortName,len(@shortname)) + '%'
select * from loans where short_name like @ShortName Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[byrne1]

YES!!!!

That worked! Thank you very much for your help! You have relieved me of a lot of stress! Thanks again!