• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

T-sql Stored Procedure

I made following stored procedure;




select substring([Tele No],2,7)as 'Phone No.', [Subscription Name], [Billing Month] from BR
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.
4 Solutions
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.
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
Vitor MontalvãoMSSQL 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?
Anoo S PillaiCommented:
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 :)  )

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).
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now