Which is best way to write sql query?

Posted by Vinoths09 under .NET Framework on 8/29/2015 | Points: 10 | Views : 1631 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

using sql query to select some data or mainpulate may i use query in codebehind or else in stored procedure and which is best place to write query and why?

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Shreedar on: 8/29/2015 [Member] Starter | Points: 25

0	Hi Depending up on your requirement you can write stored procedures only if the select statement is retrieving less number of records. Performance of application degrades with stored procedure having more record's. One advantage is you can reduce the code in code behind, if you make use of stored procedures. Regards Sridhar Thota. www.dotnet-sridhar.blogspot.com Vinoths09, if this helps please login to Mark As Answer. \| Alert Moderator

Hi

Depending up on your requirement you can write stored procedures only if the select statement is retrieving less number of records.
Performance of application degrades with stored procedure having more record's.

One advantage is you can reduce the code in code behind, if you make use of stored procedures.

Regards

Sridhar Thota.
www.dotnet-sridhar.blogspot.com

Vinoths09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sheonarayan on: 8/29/2015 [Administrator] HonoraryPlatinum | Points: 25

0	The best practices says that all your database related code should not be in the code behind or application layer. So all the SQL related code should be in the database and naturally inside the stored procedure. However, sometimes if you have a very simple sql statement to execute and it is only static in nature you may write it in the C# (not in the code behind page but in different database layer - read this http://www.dotnetfunda.com/articles/show/2708/3-tier-architecture-in-aspnet-a-complete-article). If any of your C# method is executing more number of SQL statements from the database, preferably put all of them into stored procedure and execute once so that it executes fast. The benefit of using stored procedure is that the execution plan of all the sql statement written there is prepared at the time of saving the stored procedure, the sql syntax is checked. So this makes the sql statement execution faster in comparison of executing them from the C# code. The simple answer of your question is keep all sql statements in the database (stored procedure, udfs etc.). Thanks Regards, Sheo Narayan http://www.dotnetfunda.com Vinoths09, if this helps please login to Mark As Answer. \| Alert Moderator

The best practices says that all your database related code should not be in the code behind or application layer. So all the SQL related code should be in the database and naturally inside the stored procedure.

However, sometimes if you have a very simple sql statement to execute and it is only static in nature you may write it in the C# (not in the code behind page but in different database layer - read this http://www.dotnetfunda.com/articles/show/2708/3-tier-architecture-in-aspnet-a-complete-article).

If any of your C# method is executing more number of SQL statements from the database, preferably put all of them into stored procedure and execute once so that it executes fast.

The benefit of using stored procedure is that the execution plan of all the sql statement written there is prepared at the time of saving the stored procedure, the sql syntax is checked. So this makes the sql statement execution faster in comparison of executing them from the C# code.

The simple answer of your question is keep all sql statements in the database (stored procedure, udfs etc.).

Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Vinoths09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajesh123 on: 8/31/2015 [Member] Starter | Points: 25

0	HI Refer this link http://www.developer.com/db/10-ways-to-improve-sql-query-performance.html rajesh.plsql123@gmail.com Vinoths09, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts