Solved

T-sql Stored Procedure

Posted on 2015-01-25
5
196 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 66

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 51

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

627 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