SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

One size does not fill all

By Jim Youmans,

I was just reading a discussion on a popular networking site about stored procedures. In particular, one software architect thought they should not be used.  His reasoning was that the programming language was designed to handle the database access and it was easier to port his application to different database systems without them.

I was stunned.   But then I thought about it and realized that I shouldn’t be.  More and more of the developers I work with have very little understanding of how a database works.  They use coding tools to bind their application to the database schema and then just call a programing function to do what needs to be done.  Want to update a record in the database?  Call the ADD parameter of the bound schema object.  Don’t worry about what is actually being done behind the scenes.  It does not matter what the DBMS is.  It’s all good.

No, it’s not.  I have looked at some the SQL produced by these interfaces and it can be amazingly bad.  Also, since it is ad hoc SQL, it is not optimized in like stored procedures are.  Plus, if you need to optimize the SQL code, it is next to impossible because the SQL is generated and you have very little control over that.

Also, if your app is not optimized to take advantage of the DBMS it is using, then it already has a strike against it.  I have worked with a major application that advertised that it would work with any of the leading database providers (MS SQL, MySQL, Oracle), just select the correct driver when you install the application.  It did work with MS SQL and Oracle, but it had horrible performance issues and the nightly data loads from our sales system took hours to load less than 100,000 records.  This was directly due to the fact that it created all of the SQL through the application, with no stored procedures and no optimized code.  It was a nightmare.

I am not saying that you should never let the application handle the data access, it can work depending on the size and complexity of the project.  What I am saying is that you have to understand that it can be a major issue and that you have to make an educated decision about how and when you use these tools, and don’t just blindly allow the application to handle all the “low level” database calls.  If you do, don’t be surprised when you are stuck with a slow and frustrating  “one size fits all” product.

Total article views: 164 | Views in the last 30 days: 1
Related Articles

Optimize Your Applications

This article demonstrates that one way to optimize the data access portion of your app is to do the ...


Database Optimization Task Disconnect

Database connection lost during optimization task.


How to handle UTC?

Handling UTC in multi-tier applications


Upgrading the GIS application and database.

In this article I will mention a specific issue that I experienced during my last GIS application an...


Database Create from Application (VB & C#)

Creating Database from an Front End Application