Sql Server script examples Query tuning and optimization best practices Databases, Tables, Stored procedures,functions, SSIS, SSRS Interview questions and answers explanation, Errors and solutions, Data 3G tariff recharge
Mixed Database Layers application code best practices
It is related to application design. There is not
any direct relation with database tuning.
Some application has not any separate layer for database. All the
database queries are embedded with application code. There could be many types that an application code mixed with database queries. For examples, in-line
queries, using some framework which auto generates SQL queries with application
code, using language integrated query (LINQ)
in C# or VB.Net etc. There are some basic problems in the mixed database layer architecture: 1. In general task of query tuning is SQL
developers. So it is very difficult get the queries of the application and
modify accordingly. 2. Most probably those code will not be written by database developer. Most of time they are very code in coding but not database. They have not sufficient knowledge when to use JOIN, CROSS APPLY, Table valued functions, stored procedure, CTE etc. This lead to inefficient code.
3. SQL server provides tools like query profiler,
extended events, DMVs etc, by using them a SQL developer can get the all the
queries. But it is not sufficient because business logic are very much mixed with application code. SQL
developer can get kind of idea but it is difficult understand the flow of the
queries. For example, which queries can be joined etc? Also SQL developers
cannot modify the script independently. So a best practice recommends there should be separate layer
for database work. Stored procedure is one of the best alternative.