facing problem in stored procedure timeout

Question

0.00/5 (No votes)

See more:

Actually i am getting user records from database through stored procedure. i am getting that records in dataset and binding to gridview. here problem is time out exception. runnin the stored procedure is taking much time. i taken that stored procedure as a separate query and executed it is taking 32 seconds to execute the query...is there any solution to over come this..

Posted 25-Jul-13 0:07am

ntitish

Add a Solution

Comments

Parviz Bazgosha 25-Jul-13 6:11am

how many you have records ...?

check your select .

remember join multi tables is faster than (in line select ) in most case

how many your proc returns records ?

ARE CHECK TOP(n) in Select ?

ntitish 26-Jul-13 5:18am

Actually sir my stored proc for getting the users from 50 Kilometers radius using Zip code.below i am pasting my code

SELECT * from Users.Userid=B.Userid
WHERE Users.UStatus='A'

and Groups.GroupID In
(
select GroupID from Groups A,
(
SELECT Zip FROM (
select Convert(varchar(max),Zip) Zip,
( 3959 * acos( cos( radians(@lat) ) *
cos( radians( Lat ) ) *
cos( radians( Long ) -
radians(@long) ) +
sin( radians(@lat) ) *
sin( radians( Lat ) ) )) as distance from cities
) A WHERE distance < 50
) B
where A.Zip=B.Zip
)

Adarsh chauhan 25-Jul-13 6:15am

Please share your code and stored proc. so that we can find out where the problem actually is.
without enough information it would be just hit and trial for us.
there may be n no. of reasons for SP running slow..

ntitish 26-Jul-13 5:20am

Sir i pasted my code in above comment can u say any thing by seeing that

[no name] 25-Jul-13 6:15am

Sure there is. Profile your query and then fix your bottlenecks.

ntitish 26-Jul-13 5:20am

Sir i pasted my code in above comment can u say any thing by seeing that

Raja Sekhar S 26-Jul-13 3:17am

Either you can increase the timeout Limit in application or u can improve the Stored Procedure Performance... Previously encountered the same issue...

ntitish 26-Jul-13 5:20am

Sir i pasted my code in above comment can u say any thing by seeing that

Raja Sekhar S 26-Jul-13 5:29am

Remove Distinct in "select Distinct GroupID From"

ntitish 26-Jul-13 5:43am

No use sir.....after removing that also it is taking 33 seconds to get 214 records is there any solution to over come this actually i updated my query above can u see that..

ntitish 26-Jul-13 5:48am

sir r u there........

Raja Sekhar S 26-Jul-13 6:11am

U can change the Set Statement to this..
Select @lat=Lat,@Long=Long from Cities where Zip=@Zip..
But there won't be too much improvement in the Query Execution... it can be due to many issues.. u have to check the Links Provided by Maciej Los in his Answer...

Raja Sekhar S 26-Jul-13 6:14am

U can Increase the timeout of your Application...

ntitish 26-Jul-13 6:17am

how to increase the time out of my application....

Raja Sekhar S 26-Jul-13 6:24am

There will be a .config file in your application files... some thing like app.exe.config.. in that there will be something like Processing time out
just increase the value... Don't Forget to take a backup of that config file before making any changes...

ntitish 26-Jul-13 6:52am

Sorry sir iam unable to find out that but in my application web.configfile is there but there no processing time out in that file...so i think so it is not

Raja Sekhar S 26-Jul-13 7:57am

In Web.config to change the timeout for one specific page:
<location path="somefile.aspx">
<system.web>
<httpruntime executiontimeout="180">

or
<httpruntime maxrequestlength="2097152" executiontimeout="600">
Check this links:
https://www.google.co.in/search?q=increase+timeout+in+web.config&oq=increase+time+out+&aqs=chrome.3.69i57j0l3.8890j0&sourceid=chrome&ie=UTF-8

ntitish 27-Jul-13 0:15am

in cs page i changed sir.....but i think it was not the good solution......sir do u have any idea about selecting the users of 50 kilometer radius using ZIPCODE as a parameter....

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-07-25T00:16:00

Solution 1

There are several reasons of this behavior...
There is set of steps to do...

Have a look here:
http://www.sqlbadpractices.com/[^]
Query Performance[^]
Improving the performance of queries using SQL Server: Part 1[^]
Improving the performance of queries using SQL Server: Part 2[^]
Using Statistics to Improve Query Performance[^]
Using Covering Indexes to Improve Query Performance[^]
7 performance tips for faster SQL queries[^]
Sql Tutorial - Improving Query Performance[^]

Posted 25-Jul-13 0:16am

Maciej Los

Comments

Adarsh chauhan 25-Jul-13 6:40am

Very useful and informative links...
I read most of links posted by you people just to find something new and I am happy that every time I access a link i learn something new... :)
Thanks to you people and codeproject.com .. :)
+5

Maciej Los 25-Jul-13 6:44am

You're welcome and Thank you ;)

Raja Sekhar S 26-Jul-13 6:11am

As usual... Nice Links Maciej Los.... +5!

Maciej Los 26-Jul-13 7:45am

Thank you, Raja ;)