which is best for good performance nested query on page or stored procedure ?

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)

@nuraGGupta@ · Answer 1 · 2011-03-08T01:10:00

Solution 1

Stored Procedure : Its reusable and pre-complied. So, will save compilation time each time when being called.

Posted 8-Mar-11 1:10am

@nuraGGupta@

Saral S Stalin · Answer 2 · 2011-03-18T04:48:00

Solution 3

Always use stored procedures except........

When you are implementing a multi-level search functionality/data extraction which may need to hit more tables if the user drills down to finer details. In this case building the query at run time using the code and executing it would give the best results. If you write stored procedure you may need to join additional tables which may not be relevant based on the user input or use dynamic sql inside sp. Both are inefficient ways. Parameter sniffing issues can also occur.

SQL server DOES NOT PRECOMPILE stored procedures from SQL Server 7.0 onwards. Execution plans are created at run time and cached for later use. It does the same for normal select queries as well.

Posted 18-Mar-11 4:48am

Saral S Stalin

Comments

mhwasim 18-May-11 12:19pm

sps are always pre-compiled...its the code which is pre-compiled....for Execution plan if u use some statements like EXEC it will then always create execution plan every time when called...

Saral S Stalin 9-Mar-12 2:43am

Dude you seem to have a wrong understanding..Check in msdn. SQL Server does not precompile stored procedures. At the first call of the sp the execution plan will be created and cached. Later call will use this cached plan. Plans can be flushed out of memory/gets invalidated due to several reasons. If a valid plan is not there in cache, a cache miss event occurs and execution plan is recreated. For execution plan caching SQL server does not differentiate a SP or a normal T-SQL statement. But cache priority of stored procedure execution plans tends to be higher and are retained for longer time in memory

mhwasim 9-Mar-12 6:58am

Please read the following article
http://msdn.microsoft.com/en-us/library/ms190439.aspx

Ramalinga Koushik · Answer 3 · 2011-03-08T01:11:00

Solution 2

Nested Query will be fast when compared to Stored Procedure.

Link1[^],Link2[^] and Link3[^] might help you

Posted 8-Mar-11 1:11am

Ramalinga Koushik

Comments

@nuraGGupta@ 8-Mar-11 7:18am

I didn't got anything out of those links. Could you please provide some light on what you understood after reading those as I am sure you must have read them before posting. :-)
Asking just to gain some knowledge.