Re: Massive Stored Procedure
- Hi all,
very good summary in post #42096.
I am adding some additional comments (widening the view) to this topic.
> Big Boys ... have thrown the relational model outconsider also type of application:
-heavy data processing, e.g. collecting data from an industrial technology/assembly line etc. (many concurrent data changes): relational DB wins (including heavy use of DB procedures)
-reporting/monitoring/data-mining systems (using DB replica, mostly read-only, analytical queries.., almost no write transactions): relational DB replicas win
-LDAP/DNS/HLR/telecom applications: mostly in-memory distributed systems- deployed on large amount of computers (many concurrent read access, low amount of write access):
-web-servers/portals: probably this did you mention by Big Boys...: is there not required mostly read access with small amount of data changes?
Which type of application use the "Big Boys"?
> lock yourself into the platform for which they compileconsider audience of the software:
-single system in one company --or--
-many system installations (e.g.bugZilla, supporting several DBMS, spread all over the world)
How many times did you see change of DBMS? Why did it occur?
Consider also costs of getting knowledge by your personel (time, experience knowing behaviour nuances)
Consider also in heavy write-transactional system different client programming approach for DBMS based on multi-versioning and DBMS based on locking (e.g. Oracle and DB2: handling high write-transaction-rate concurrency: setting isolation levels, conflict resolutions, error handling differs a lot!): this means the change of DBMS does not affect only "the bottom layer", but also bussiness logic utilizing it.
> stored procedures for me are just another programming tool/language, like Java"just another":
Consider this is quite different paradigm:
let a pure "java" guy optimize the dbms layer performance
or let the pure "DBMS" guy optimize java performance. Will you be satisfied with results?
I think not really:
relational paradigm: "DBMS" guy thinks in defining sets of data to affect (relational DBMS is good at handling related sets of data) in less procedural steps having in mind I/O penalty of his work. But is unable to handle network-related bussiness logic (remote calls, sending messages to other systems etc.) down there (in DBMS).
oo/procedural paradigm: "java" guy works more procedurally and is shielded from low-level I/O impact of "persistency" (of course if he has no previous database optimization experience). A good example is design of entity beans in EJB by company which created java (SUN): they are more-or-less unusable from performance point of view.
Simply said: java is strong where DBMS is weak and DBMS is strong where java is weak.
> quick advice is DON'T USE THEMwhy?
your personel may be full of DB-gurus who can make changes much more efficiently than team of OO-gurus...
what about performance tunning when you are not aware about "the bottom layer" (i.e. the DB)
> performance and scalability ... the costs would go up - Oracle Enterprise with clustering support is insanely expensiveexpensive:
- does the system not bring enough bussiness value to pay it's core technology component? (this means: what is bussiness loss for an hour your system's downtime? Compare it with price of Oracle Enterprise clustering support and decide)
- are there no cheaper database solutions providing scalability? (e.g. Ingres, MySQL has both GPL and bussiness version of the same software with clustering support: just depends on knowledge of your team whether it can afford to do full support for the product)
- are there no options to split the system to smaller cooperating systems?
- my experience: for the same reason in a project JBoss was chosen for bussiness logic instead of buying database clustering support (lower price for clustering support): but no knowledge of putting it to life, no consultant support bought. Result: no cluster until now, lot money invested to port bussiness logic. This means: your personel technology knowledge is the key for success, not only the base product's price.
> harder to test, harder to develop, harder to refactortrue (in case having no TestDrivenDevelopment- TDD).
TDD was introduced in object-oriented world:
but I hope it can be used in DB development as well.
Has anybody a success story in introducing TDD to DB development?