Nik's Technology Blog

Travels through programming, networks, and computers

Data Layer with Business Logic

Deciding when to move business logic to the data layer in a multi-tiered application is a tricky one and depends a lot on the RDMS you are using.
I've been looking at an ASP.NET application that creates 7 rows in a database for each "musical performance" when a button is clicked on a web form to rebuild a database.
To begin with a SQL 2005 stored procedure written in VB.NET (But could easily have been C# with the same results since all .NET languages are compiled to IL). was called from a loop in the business layer 7 times to create the 7 rows. The application was run and it took about 90 seconds to rebuild a test database in this manner.

This logic was then moved into 1 stored procedure still written in VB.NET; The result was that instead of making 7 calls to the database each time, we make 1 call to the database and move the business logic, executing the loop there, and hence at the data layer. The database rebuild took about 30 seconds this time.

Stored Procedures in Native T-SQL

To try and make the execution faster we decided to write the stored procedure in T-SQL, which is still the native language of SQL Server. This shaved another 15 seconds of the execution time! Just proving what sort of overhead writing your stored procedures in anything other than T-SQL introduces.

This exercise provides a glimpse into how the decisions you make when designing an application radically affect the responsiveness of the system.

Business Logic at the Data Layer

If you were pulling lots of data to the business layer performing some processing then discarding most of the data at that point, then it may improve performance if this operation was moved down to the data layer. It really depends on the data and the application.
One of the problems with storing too much application logic in the data layer is version control. It can become a mine field managing lots of complex stored procedures between test and live environments and keeping them in-sync with the application.

Comments are closed