T-sql Stored Procedure

I made following stored procedure;

CREATE PROCEDURE [dbo].[BR1]

@phone

varchar(50)
AS


select substring([Tele No],2,7)as 'Phone No.', [Subscription Name], [Billing Month] from BR
JOIN mn
ON BR.[Billing Month]= mn.mn
where [Tele No] like @phone
order by mn.Status ;

when i called this "BR1" stored procedure through my report project, it is taking 7 to 8 mins to retrieve records from SQL server, although when i called BR1 from Management studio it is retrieving within seconds.

Could you please help me in this matter.
LVL 10
ukerandiAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
what does @phone contain?

did you run the procedure from management studion or the sql statement?
did you run the sql  statement with a variable of  a hardcoded/literal value?

why do you join the 2 tables?
why do you order by status but not include the column in the select output?

what indexes are there on the tables ?
how large are the tables
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>where [Tele No] like @phone
Is the LIKE necessary, or can it be equals?  Any LIKE comparison will be slow.

Also, eyeball your SSMS execution, as it may be returning the first records quickly, but not the entire set.
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
How many rows are the query processing?
Which indexes exists for both tables?
What kind of values @phone can have?
Can you post here the query plan for this query?
0
 
Anoo S PillaiConnect With a Mentor Commented:
You have to look into this from two angles

1) Whether the procedure is actually taking 7-8 minutes to get completed from SQL server  OR is it a delay from reporting component. Find out the duration of this procedure from SQL profiler and verify. If duration of the procedure that you get from profiler is low, then look into reporting component.

2) It is possible that there would be a delay when procedure is called from application. A detailed discussion is available at Slow in application, Fast in SSMS . ( If you have patience to read it fully, definitely it would add something new to your knowledge base :)  )

Anoo
0
 
RandyOMConnect With a Mentor Commented:
I would suggest looking into how "parameter sniffing" works and also how to identify issues between your front-end and back-end processing.  Here's a good article to point you in the right direction. http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

likes "%blablah" cause table scans and completely disregard any indexes that exist on the table (from my understanding).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.