Pages

Dec 26, 2014

Application code best practices SQL SERVER

Application program code performance tuning SQL server

Looping is most commonly used programming element over collection of
data. Since it is very easy to think and implement a complex algorithm by
processing one row at a

time. When this collection is database objects like
table etc this row based approach could be major performance bottleneck. Since
a table may have 1 million records or even more than that. Just to say to
perform a task, if row based approach is taking four to five hours then it may
possible to accomplish same task by set based approach within 30 to 60 seconds.
Here we are going to discuss about most common faulty code which force to use
loop or recursion:

Partial Join:

Some programmers first fetch the base data from database and store either
in data table or in application cache etc. In the next step they iterate that
data table using loop or recursion and inside that loop there is will be
another child queries are being executed with passing the parameters to child
queries from the output of based query. For examples:

There is business need to pull users as well as their post information.
C# script for this:

Above script is one of the examples of partial join. It is better to
join the tbluser with tblPost in the database at the same time instead of first
fetching the user’s information and then fetching the post information of those
users one by one for in a loop.

Execution time: 1
minute 28 seconds (with same set of tables). Hence a code with partial joining
with large volume of data could be big performance bottleneck.

Data manipulation:

It is a best practice to write business logic of data in the database
side. Business logic on data changes with time. In this situation stored
procedure is best approach since modifying the application code is difficult
task. Some programmers first pull the whole the data from database then
using some loop perform business logic on data. For example:

In the below script a simple data manipulation for user type has been
done inside loop:

It is due to Transact SQL is used set based approach instead of
looping. Some time it is due to lack of knowledge of database. They think
database can do only SELECT, UPDATE, INSERT etc. They are not aware of
programming capabilities of database like IF ELSE, CASE, looping, SQL Function
etc.

Pull
minimum data from database:

Some programmers fetch all the data
from a database tables. And after some business logic calculation then apply
filter in the application layer. For example they pull 1 million records from database
then in application some filter condition is applied. After that total records reduced
to 100 (say). Which would be unnecessary cause of network traffic or may
database has appropriate indexes to perform that filter condition efficiently.

It is advisable to write business
logic of filter condition in the database side using programming capability of
database.

Minimize number of database invoke:

During a code review, I found a
very inserting piece of code. That code first pulling data from multiple tables
then after some business logic again populate data into single temporary table then
update the records in the database side. Again pull the records from temporary
table to application layer and using loop apply some different business logic
and finally populate data in the database.

It is highly recommended for single
event there should not be more than database call. Either business logical should
be implemented in application code or in database. Not partially in database
and partially in application code. As shown in below diagram:

This degrades overall performance
of an application. In this example a single stored procedure should be written to
accomplish whole business logic.

XML and JSON support:

SQL server highly supports XML and
JSON (SQL Server 2016) data format. So there is no need to first fetch data in tabular
format then convert it XML or JSON format using application code which some how use a kind of loop. For large
volume of data it decreases performance significantly. For example: