Performance Improvement of stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi All,

I have one stored procedure. I have around 10K records I want to select all of them.
I dont have any where condition? How should I improve performance of the stored procedure?

Please help me.

thanks,
Sushil

Posted 26-Jun-11 20:22pm

sushil_gupta

Add a Solution

Comments

^That's Aragon 27-Jun-11 2:44am

I am not sure for your requirement. But can you tell us, why you need such big data in only one call ? Are you doing any further process ? Elaborate it for more clarity.

sushil_gupta 27-Jun-11 2:55am

I was asked this question during Interview.So Requirement was only limited to that much which I have already told.Can you think something now?

^That's Aragon 27-Jun-11 3:45am

Ok, then you should take care of some points like
1. Apply primary key and index on tables if more than 1 table is involve in the query
2. SET NOCOUNT ON ( You can find more details on MSDN)
3. Avoid functions if used. It will greatly increase performance.
4. NO LOCK to optimize table access
5. Avoid temporary tables if used
6. Use minimum JOINS(as required) and check whether proper JOIN is used or not

[no name] 27-Jun-11 9:14am

good, you could post this as an answer

2 solutions

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)

^That's **Aragon** · Answer 1 · 2011-06-28T03:26:00

Ok, then you should take care of some points like
1. Apply primary key and index on tables if more than 1 table is involve in the query
2. SET NOCOUNT ON ( You can find more details on MSDN)
3. Avoid functions if used. It will greatly increase performance.
4. NO LOCK to optimize table access
5. Avoid temporary tables if used
6. Use minimum JOINS(as required) and check whether proper JOIN is used or not

Prerak Patel · Answer 2 · 2011-06-26T20:29:00

Solution 1

No one can give you exact solution. You have to check and try what suits you.
http://www.google.com/search?q=Performance%20tuning%20stored%20procedure[^]
http://www.sqlservercentral.com/articles/Performance+Tuning/performancetuningstoredprocedures/2505/[^]

Posted 26-Jun-11 20:29pm

Prerak Patel

Comments

fjdiewornncalwe 28-Jun-11 10:20am

Good links. I would advise the OP to revisit the requirement of pulling 10K rows at a given time. It is under exceptional circumstances that I have ever not found a way around pulling that much data at one time. He should try to find a way to optimize his processes so that the datasets are smaller.