Re: Embedded SQL vs Stored Procedures

"Gama Franco" <tiago_at_cern.ch> wrote in message news:2q6r9hFr7qesU1_at_uni-berlin.de...
> Sybrand Bakker wrote:
> > On Tue, 07 Sep 2004 17:08:24 +0100, Gama Franco <gama_franco_at_clix.pt>
> > wrote:
> >
> >
> >>Unfortunatelly I really have to.
> >
> >
> > You only think so. Also no real DBA would accept procedures are
> > creating tables on the fly. Allowing this would mean the db is out of
> > control.
> >
> >
> > --
> > Sybrand Bakker, Senior Oracle DBA
>
> Hi,
>
> Ok, here it goes.
>
> I'm programing a database based on a temporal extension to the
> relational model. That means that I need to simulate the DBMS, and every
> table created by the user will be a temporal table (dealing with time
> intervals). The database will have tables to manage all the meta info,
> and temporal tables will be created on demand (just like the way that
> you create a table when you need in Oracle, but these one are temporal).
> This means that the program will need to manage and create new tables,
> and OCCI is a major requirement. It's CERN's official programming
> language...
> Because the system will be used by a large number of scientists, there
> will be no DBA. To manage all this chaos, an hierarchical model will
> handle the location of the tables (we simulate something like a file
> system on the top of the hierarchical model, and every table will be
> located at a leaf of the tree).
>
> This is just a brief introduction, and I would like to know if someone
> already made the performance tests on Embedded SQL vs Stored Procedures.
> If this is not the case, I will have to make the tests but I was looking
> for a shortcut.
>
> I'm not crazy or stupid. This system is already running using MySQL (I
> now what you're going to say) as the repository. It is already
> collecting data and the database is somewhat big. We need to create an
> implementation in Oracle and make some performance tests.
> If you have a lot of scientist running their own experiments with
> specific use cases, you need to figure out a clever way to give them
> freedom without throwing them into chaos.
>
> Best regards,
> Gama Franco
>

If you mean you are creating these tables permanently then it might be okay, but if you are creating and dropping them on the fly then it is a real bad idea. I am guessing that mysql creates a variety of locks and that's why people create temp tables on the fly. In Oracle it is rare to need to do that. Stored procedures are nice because you control a lot of stuff and can redefine something's without changing code on the client. Your biggest gains will be to read the application developer's guide, use asktom.oracle.com and USE BIND VARIABLES. Jim Received on Tue Sep 07 2004 - 20:37:23 CDT