Solved

T-sql Stored Procedure

Posted on 2015-01-25
5
182 Views
Last Modified: 2015-02-02
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.
0
Comment
Question by:ukerandi
5 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 125 total points
Comment Utility
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
Comment Utility
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
 
LVL 7

Assisted Solution

by:Anoo S Pillai
Anoo S Pillai earned 125 total points
Comment Utility
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
 

Assisted Solution

by:RandyOM
RandyOM earned 125 total points
Comment Utility
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

How to increase the row limit in Jasper Server.
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now