Performance cost of double validation: middle tier and database

Question

Post reply

Performance cost of double validation: middle tier and database

Jesse Reich

Hall of Fame

Points: 3020
More actions
September 29, 2009 at 6:18 pm

#211595

One of the tenets of my company's development philosophy is to create modularized, "loosely coupled" software components that can be sold / used interchangably.
Specifically, they want a C# middle tier that can access / modify data in Oracle, SQL Server, etc.. On the flip side, they want a database (we have SQL Server obviously) that can be used by any middle tier language.
The issue we face involves basic validation. We end up keeping it in two places.
Example:
The Employee object has an attribute EmployeeType that only allows the enums Permanent, Temp, Offshore.
In the database, we have an Employee table. The EmployeeType column has a check constraint for the values Permanent, Temp, and Offshore.
For something simple, the only concern is duplication of business logic. But when we have requirements that are much more complex, the cost of validating the data both in the middle tier and our stored procedures becomes a performance issue.
My question: is the performance hit of double-validation something that must be accepted when creating modular software?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Doing validation in both the app and backend side seams to me the easiest way to get into troubles.

- system performance. Double validation certainly has a negative impact on overall systems performance.

- system maintenance. Double validation means "people" has to keep app and backend validation logic in sync.

I would decide for one of the two alternatives.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Lowell SSC Guru Points: 323518 More actions · Answer 2

wow..i'm leaning towards the opposite of Paul above. I vote for validation in both tiers.

I think check constraints and defaults, FkKs have a minimal impact at the database level, and by double checking in the middle layer, you get better error messages, nintead of returning whatever error came from the server.

I'd agree validation in the middle layer costs a bit of performance cycles, but i think it's worth it;

Our app tends to do both, with the database being more robust as far as the constraints go, with our application layer giving "prettier" error messages for the items we pre-check before sending to the database.

error messages like "Customer Id already exists" are much better than Unique Cosntraint UQ__something violated.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

I think that it depends. There are some validation that can be done in both places (Application and DB), but other should be done (at least in my opinion) only in the DB or only in the application. For example suppose that I have a column that stores price of a product. In the database there is a good chance that I’ll have a check constraint saying that the price has to be greater then 0. In my opinion the application can also check that the user’s input for that column was greater then 0 and if it wasn’t it shouldn’t access the database and try to insert this record. This will save unnecessary round trip between the application and the database. On the other hand, if I also have at the same table a column that is called CategoryID which is a foreign key to Categories table, I will have a foreign key in the database, but I won’t expect the application to check just before inserting the record that this category still exists in the database. An example for validation check that should be done in the application is that the input was done with the correct data types.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 4

I'd agree that you want to do it in both places. However, I'd load the validation domains, list of choices, from the db into the middle layer. Find a way to persist the data across time in the middle tier, and reload it periodically. Maybe use Query notifications or some caching to hold the data there to prevent extra cycles.

you'll hold off hitting the db too often, you'll have a central place for storage of rules, and you are flexible.