J2EE vs .NET performance with Pet Store & Stored Procedure discourage
I read an article about an implemention of Pet Store in
J2EE and .NET.
In the article they said that Sun discourage Stored Procedure usage in the Blue Prints & Patterns.
I really can't belive it!!
I participate in a development 2 years ago in which the movement of the logic from the client (in Java) to the server (TransacSQL over SQL Server 6.5) give an improvement of about 7 times!
Can any one tell me what's going on?
The address of the article is:
http://www.gotdotnet.com/team/compare/oraclerespond.aspx
Juan
[583 byte] By [
mirjua] at [2007-9-26 18:29:40]
In J2EE, the logic is moved from the client to the middle tier. The database is really treated as a "Model" or a datastore with no behavior. There are several advantages to this approach. First the stored procedures are not portable. If you decide to run your application over an Oracle database instead of a Microsoft database you will have to rewrite the procedures. Secondly the application is easier to maintain. The client is thin without much code, the database is just holding the data with no logic so all the logic is in the middle-tier. You don't have to hire people that know the stored procedure language along with others that know the application language, etc. Thirdly, if you want to cluster your middle-tier and use multiple databases, the replication is easier to deal with if the logic is encapsulated in the middle-tier.
It seem rigth about portability and mantainability, and it seems OK for n-tier functionality, but what about performance?
The article that I read said about seven times the performance among .NET and J2EE.
I may attribute a great deal of responsabilty at the SQL Performance loss due to issue a Query every time without the tunning available in the stored procedures.
Am I wrong?
I wrote an application in wich the performace difference among making a data processing in JDBC and stored procedures was 7 times.
May I be coding wrong the JDBC?
Performance for performance sake is not always the right answer. All the reasons mentioned above are correct, but vendor neutrality alone is enough of an argument for to avoid stored procs. Look at the headaches many old client/server products dealt with when opening up their app to multiple database vendors!
bRi
As long as it seem right, what about the SQL'99 Standard?
It look's like a way to standarize the SQL in areas like Stored Procedure.
And right now there is a product that support it very well (mimer 8.2: www.mimer.com) so could be a matter of time to every SQL provider goes for the standard, Isn't it?
Also, the performance is VERY IMPORTANT.
Not every company can have the last Enterprise Level Server cluster from COMPAQ or DELL.
In my country is usual that a company uses a Pentium 200 Mhz (or less) with 64 MB (or less).
In fact my development machine is Pentium 200 with 192 MB - quite a lot of memory in Venezuela's stadards!!)
When you have to compete with other products, performance is always one consideration, and if 100 users try to access data they want it right now, not in 10 seconds.
I ask my self if the ones whom writes the pattern's think that every company changes their machines every year, and that's not the case in third world country's.
I'm commited with new technologies and standard's, and specially with Java (I demostrated to several people that Java isn't as slow as every one say's, specially for bussines app's), but I become very (VERY) worry about this tendency
am I wrong?
Points are valid. There's a trade-off between perf & maintainability. Yes, a product is slower and thus potentially loose market share. However, if a product can be certified for newer and existing db upgrades, then I gains market agility. Agility = $.
If I'm a CIO, yes, I look at perf. However, and here's my point, most within IT don't see the Total Cost of Ownership (TCO) for purchasing applications, or in otherwords the business side of IT. So, If I can purchase a product, granted it be a little slower, and I can have the ability switch/upgrade databases without having to spend hundreds of MY IT hours testing my vendors product, and/or waiting for them to 'catch up with the marekt' -- then it's worth it.
We had a product from a nameless vendor that had scalibility issues with another vendors db and version. The solution was to upgrade the db. However, they we're not yet ready to upgrade, due to hundreds of stored procs, etc., that needed re-coding and testing. Basically, upgrading the db meant upgrading 1/2 the app, alog with all the testing, etc. We were stuck with these issues for 8 months. Would I purchase that product again, NO. Would I have invested an additional 10% of my $ in hardware to make up for the perf loss, yes. And my TCO would still have been lower.
Therefore, In my humble opinion, Agility = $ in the long run. Besides, the cost of faster hardware is, and will always continue, to decrease.
However, agility -vs- performance should be evaluted during the purchase, and is up the the context of the business decision behind a purchase, and thus 'value is in the eye of the beholder'...
bRi
Forgot to talk about SQL 99. If in fact stored procs can be ported, without re-writes, then i'm game for moving some db related items to the back end, as long as it's data related and not biz logic (e.g. triggers that copy a record to an archive file before deletes, etc.)bRi
Another area in performance is scalability. Clustering of operations in stored procedures involves database replication and is not as granular or performant as clustering the operations at the application server level.
About SQL'99
Around 5 years ago, I read an article that said that you should put the bussiness logic inside of the database (along side thet data, where it's belongs - just like an Object, where the data lives with the methods, TOGETHER).
If all database providers follows closelly th SQL'99 standard why not use a proven and usefull method of development, with the right tool in every case?
It's seem to me a more philosofical (and my be teological) possition not to do so (even as I can be wrong too, but's hard to understand all this to me)
About scalability:
It's clear to me that is easer to get more proccess performance at the application level with clustering, but what about the fact that if the access to the data is slow, doesn't matter how fast is the proccessing of that data.
I remember a very old statement:
The system speed is as high as the slower part of that system.
The Stored procedures in SQL 99 is called PSM (Persistent Stored Modules), and as you point out Mimer SQL has supported it for a while now. Besides Mimer, IBM DB2 supports PSM and I think it's only a matter of time before others will as well.Regards,Fredrik
My preference is to use Stored Procedures to contain common logic. I've listed a few reasons why I believe this.
First, not all database vendors support SQL to the letter. Orcale, the worlds biggest, has it's own syntax for outer joins. SQL defined outer joins DO NOT WORK in Oracle. What this translates to is that when you port from one DB to an other, you should ALWAYS verify all SQL/code works. Porting without testing is asking for trouble.
Second, it is not guarenteed (although it may be in some environments) that all connections to the database come from a java middle tier. Many places have software that was written long ago that still works. The database they use may now need to be accessed from your java app. If they've built the logic into the database already, you do not have to recode it. If you build logic into a java middle tier and then have to write an app in an other language, you may have to recode the logic.
And third, database vendors have been doing this MUCH longer than J2EE and .NET. Primary Keys, Foreign Keys, Triggers and Stored Procs are just a few objects available to enforce common logic/integrity. Since the DB is responsible for maintaining/storing the data, it is only logical that they also enforce the validity of that data.
Keep in mind, all of this relates to the product you are developing. If you have a product the relies on performance (quick results) Stored Procs are the way to go. If you develop a product that you must sell to anyone with any JDBC DB, then you will probably have to code to a common denominator (logic in middle common tier). This comes at a cost.
I think that you have to see the pet store as a nice assembly of good coding practices, which often come with a certain penalty in performance.
pet store provides good coding practices which are probably o.k. for most applications; remember that pet store is just some sort of catalogue which is not really hard to implement.
pros and cons of stored procedures are discuessed above, so I will not repeat those.
as far as the penalty regarding stored procedure goes, only you (and your customer) can really make the decision - build a prototype and see what are the costs (in performance, scalability, portability)
the problem with a general answer to your question is that there is not a general answer.
my suggestions are:
- check out carefully which way is faster; this might be different for different methods
- if you are going to use stored procedures, try to create a nice abstraction layer and document it properly. that way you can later migrate the database easier than if everything was hard wired; have a look at the data object pattern (http://java.sun.com/blueprints/patterns/j2ee_patterns/data_access_object/)
- if you are working with oracle, have a look at java stored procedures; this way you might get the "best of both worlds": code that is fast (because it's a stored procedure) and code which can at least to some extent be moved into a J2EE middle layer (in contrast to PL/SQL)
- if there are no big advantages of using stored procedures, favour implementing the logic in the middle tier.
I can get the same performance gains in Java using stored procedures over EJB...EJB is notoriously slow, relatively speaking.I can also come very close to that performance without using stored procedures... .NET itself is nothing special.
