Solved

T-sql Stored Procedure

Posted on 2015-01-25
5
185 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
ID: 40569749
>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
ID: 40569784
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 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 40570178
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
ID: 40571856
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
ID: 40572173
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

816 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

10 Experts available now in Live!

Get 1:1 Help Now