Dec 26, 2014

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. 

No comments: