Click here to Skip to main content
15,113,873 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
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

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
   
   
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.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900