Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Embedded SQL vs Stored Procedures

Re: Embedded SQL vs Stored Procedures

From: Gama Franco <>
Date: Wed, 08 Sep 2004 09:47:19 +0100
Message-ID: <chmgit$4hv$>

Jim Kennedy wrote:

> "Gama Franco" <> wrote in message

>>Sybrand Bakker wrote:
>>>On Tue, 07 Sep 2004 17:08:24 +0100, Gama Franco <>
>>>>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
>>>Sybrand Bakker, Senior Oracle DBA
>>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
>>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
> Jim


The tables are not dropped after creation. The point is that no DBMS provides temporal tables (tables based on the temporal extension to the relational model), so we need a system that provides them. This is the main issue here. Now, I'm afraid that a stored procedure that does all the actions needed to create such tables might be slower than doing them in procedures on OCCI (code those procedures in C++ rather than in PL/SQL)

Best regards,

   Gama Franco Received on Wed Sep 08 2004 - 03:47:19 CDT

Original text of this message