Generated SQL for oracle.

[João Paulo Ribeiro]

Hello.

We are using DODS 7.04 at work. I have generated the data layer for an
oracle database and found two functions: create_oid and
create_oid_per_table.
Later I found that the two functions were not used in the Java class
generated by dods.
It's ok to me because i think that this approach complicate something
already simple and working.
If the allocation oid code go into the database as stored procedures,
this will force dods to have similar stored  procedures for every
database or it will have to detect which database it is working with and
behave accordingly.
As i said, i think this will only complicate with no really benefit
because the store procedure should be faster but this application code
code should be executed very very rarely. If not the data base
configuration of your application need to be tuned (see
ObjectId/CacheSize).

But may be I'm not seeing the big picture and the stored procedure have
really a big benefit in this particular case.
Assuming that I'm not aware of the benefits and the dods teams think
that this is the way, i just want call your attention for the fact that
the stored procedure is only allocating 1 objectid per invocation and
this a bottleneck performance for the database inserts: the stored
procedure will have to be executed for every insert to get the objectid.
If this stored procedures will be used in the future, i think they
should receive the increment as a parameter to allow the application to
control how much oids it want to allocate and to remove the allocation
bottleneck from database/application. For example, with an increment of
10000, instead of executing the stored procedure for every insert it
will be executed only 1 time for every 10000 inserts. This is really a
small change to the stored procedures.

Can someone of the development team clarify this situation?

Best regards.
João Paulo Ribeiro

--
João Paulo Ribeiro | Senior Software Engineer
jp@...

PHONE: + 351 253 305 250
FAX  : + 351 253 305 250
www.mobicomp.com

________________________________________________________________

About Solutions | Wireless World

CONFIDENTIALITY NOTICE: This message, as well as existing attached files, is confidential and intended exclusively for the individual(s) named as addressees. If you are not the intended recipient, you are kindly requested not to make any use whatsoever of its contents and to proceed to the destruction of the message, thereby notifying the sender.
DISCLAIMER: The sender of this message can not ensure the security of its electronic transmission and consequently does not accept liability for any fact which may interfere with the integrity of its content.




--
You receive this message as a subscriber of the enhydra@... mailing list.
To unsubscribe: mailto:enhydra-unsubscribe@...
For general help: mailto:sympa@...?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

[João Paulo Ribeiro]

Hi.

Just to clarify my opinion. I now that stored procedure is faster than
the application doing two queries: one select for getting the value and
one update to update the value.
But normally this code is executed vary rarely and the performance
benefits will be very small at the cost of a more complicated approach.
In the other hand, the actual stored procedure (DODS 7.04) have no
benefit performance because they only allocate 1 oid by execution.
In the dods framework, every time a new DO object is created an oid is
allocated. The new object don't ever need to be saved to spent the oid.
It only need to be created.
I think that if this stored procedure approach is the way, the stored
procedures will have to allocate a range of oid using an increment
passed as parameter or we are going to have big bottleneck in our
applications.

I hope i made myself more clear this time :).

Best regards.
João Paulo Ribeiro


João Paulo Ribeiro wrote:

> Hello.
>
> We are using DODS 7.04 at work. I have generated the data layer for an
> oracle database and found two functions: create_oid and
> create_oid_per_table.
> Later I found that the two functions were not used in the Java class
> generated by dods.
> It's ok to me because i think that this approach complicate something
> already simple and working.
> If the allocation oid code go into the database as stored procedures,
> this will force dods to have similar stored  procedures for every
> database or it will have to detect which database it is working with and
> behave accordingly.
> As i said, i think this will only complicate with no really benefit
> because the store procedure should be faster but this application code
> code should be executed very very rarely. If not the data base
> configuration of your application need to be tuned (see
> ObjectId/CacheSize).
>
> But may be I'm not seeing the big picture and the stored procedure have
> really a big benefit in this particular case.
> Assuming that I'm not aware of the benefits and the dods teams think
> that this is the way, i just want call your attention for the fact that
> the stored procedure is only allocating 1 objectid per invocation and
> this a bottleneck performance for the database inserts: the stored
> procedure will have to be executed for every insert to get the objectid.
> If this stored procedures will be used in the future, i think they
> should receive the increment as a parameter to allow the application to
> control how much oids it want to allocate and to remove the allocation
> bottleneck from database/application. For example, with an increment of
> 10000, instead of executing the stored procedure for every insert it
> will be executed only 1 time for every 10000 inserts. This is really a
> small change to the stored procedures.
>
> Can someone of the development team clarify this situation?
>
> Best regards.
> João Paulo Ribeiro
>
>  

--
João Paulo Ribeiro | Senior Software Engineer
jp@...

PHONE: + 351 253 305 250
FAX  : + 351 253 305 250
www.mobicomp.com

________________________________________________________________

About Solutions | Wireless World

CONFIDENTIALITY NOTICE: This message, as well as existing attached files, is confidential and intended exclusively for the individual(s) named as addressees. If you are not the intended recipient, you are kindly requested not to make any use whatsoever of its contents and to proceed to the destruction of the message, thereby notifying the sender.
DISCLAIMER: The sender of this message can not ensure the security of its electronic transmission and consequently does not accept liability for any fact which may interfere with the integrity of its content.




--
You receive this message as a subscriber of the enhydra@... mailing list.
To unsubscribe: mailto:enhydra-unsubscribe@...
For general help: mailto:sympa@...?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

[Vladimir Puškaš-2]

Hello João Paulo

Stored procedures aren't there to be used from DODS at run-time. We had
in mind completely different usage, so you're safe if you skip them
completely.

Idea for these stored procedures came from necessity. Although it
doesn't happen to often, we sometimes have to _manually_ insert some
rows into database. In SQL console you cannot access Java implementation
of ObjectIdAllocator :-) hence stored procedures allocate 1 OId, they
also do it in way compatible with Java implementation...

In document "Using DODS" you'll find section "Stored procedures used as
an oid allocator" that clarifies things.

hth

you wrote:

> Hi.
>
> Just to clarify my opinion. I now that stored procedure is faster than
> the application doing two queries: one select for getting the value and
> one update to update the value.
> But normally this code is executed vary rarely and the performance
> benefits will be very small at the cost of a more complicated approach.
> In the other hand, the actual stored procedure (DODS 7.04) have no
> benefit performance because they only allocate 1 oid by execution.
> In the dods framework, every time a new DO object is created an oid is
> allocated. The new object don't ever need to be saved to spent the oid.
> It only need to be created.
> I think that if this stored procedure approach is the way, the stored
> procedures will have to allocate a range of oid using an increment
> passed as parameter or we are going to have big bottleneck in our
> applications.
>
> I hope i made myself more clear this time :).
>
> Best regards.
> Jo�o Paulo Ribeiro
>
>
> Jo�o Paulo Ribeiro wrote:
>> Hello.
>>
>> We are using DODS 7.04 at work. I have generated the data layer for an
>> oracle database and found two functions: create_oid and
>> create_oid_per_table.
>> Later I found that the two functions were not used in the Java class
>> generated by dods.
>> It's ok to me because i think that this approach complicate something
>> already simple and working.
>> If the allocation oid code go into the database as stored procedures,
>> this will force dods to have similar stored  procedures for every
>> database or it will have to detect which database it is working with and
>> behave accordingly.
>> As i said, i think this will only complicate with no really benefit
>> because the store procedure should be faster but this application code
>> code should be executed very very rarely. If not the data base
>> configuration of your application need to be tuned (see
>> ObjectId/CacheSize).
>>
>> But may be I'm not seeing the big picture and the stored procedure have
>> really a big benefit in this particular case.
>> Assuming that I'm not aware of the benefits and the dods teams think
>> that this is the way, i just want call your attention for the fact that
>> the stored procedure is only allocating 1 objectid per invocation and
>> this a bottleneck performance for the database inserts: the stored
>> procedure will have to be executed for every insert to get the objectid.
>> If this stored procedures will be used in the future, i think they
>> should receive the increment as a parameter to allow the application to
>> control how much oids it want to allocate and to remove the allocation
>> bottleneck from database/application. For example, with an increment of
>> 10000, instead of executing the stored procedure for every insert it
>> will be executed only 1 time for every 10000 inserts. This is really a
>> small change to the stored procedures.
>>
>> Can someone of the development team clarify this situation?
>>
>> Best regards.
>> Jo�o Paulo Ribeiro

regards
--
Vlada

"Any fool can steer a ship, sir. It's just knowing where to take it."




--
You receive this message as a subscriber of the enhydra@... mailing list.
To unsubscribe: mailto:enhydra-unsubscribe@...
For general help: mailto:sympa@...?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

[João Paulo Ribeiro]

Hi, Vladimir.

Thanks for your feedback. It really make more sense this way. :)
I will take a look to this in the documentation. I don't read the DODS
documentation since version 6.5. I think i need an update.
Thanks again.

Best regards.
João Paulo Ribeiro

Vladimir Puškaš wrote:

> Hello João Paulo
>
> Stored procedures aren't there to be used from DODS at run-time. We
> had in mind completely different usage, so you're safe if you skip
> them completely.
>
> Idea for these stored procedures came from necessity. Although it
> doesn't happen to often, we sometimes have to _manually_ insert some
> rows into database. In SQL console you cannot access Java
> implementation of ObjectIdAllocator :-) hence stored procedures
> allocate 1 OId, they also do it in way compatible with Java
> implementation...
>
> In document "Using DODS" you'll find section "Stored procedures used
> as an oid allocator" that clarifies things.
>
> hth
>
> you wrote:
>> Hi.
>>
>> Just to clarify my opinion. I now that stored procedure is faster than
>> the application doing two queries: one select for getting the value and
>> one update to update the value.
>> But normally this code is executed vary rarely and the performance
>> benefits will be very small at the cost of a more complicated approach.
>> In the other hand, the actual stored procedure (DODS 7.04) have no
>> benefit performance because they only allocate 1 oid by execution.
>> In the dods framework, every time a new DO object is created an oid is
>> allocated. The new object don't ever need to be saved to spent the oid.
>> It only need to be created.
>> I think that if this stored procedure approach is the way, the stored
>> procedures will have to allocate a range of oid using an increment
>> passed as parameter or we are going to have big bottleneck in our
>> applications.
>>
>> I hope i made myself more clear this time :).
>>
>> Best regards.
>> Jo�o Paulo Ribeiro
>>
>>
>> Jo�o Paulo Ribeiro wrote:
>>> Hello.
>>>
>>> We are using DODS 7.04 at work. I have generated the data layer for an
>>> oracle database and found two functions: create_oid and
>>> create_oid_per_table.
>>> Later I found that the two functions were not used in the Java class
>>> generated by dods.
>>> It's ok to me because i think that this approach complicate something
>>> already simple and working.
>>> If the allocation oid code go into the database as stored procedures,
>>> this will force dods to have similar stored  procedures for every
>>> database or it will have to detect which database it is working with
>>> and
>>> behave accordingly.
>>> As i said, i think this will only complicate with no really benefit
>>> because the store procedure should be faster but this application code
>>> code should be executed very very rarely. If not the data base
>>> configuration of your application need to be tuned (see
>>> ObjectId/CacheSize).
>>>
>>> But may be I'm not seeing the big picture and the stored procedure have
>>> really a big benefit in this particular case.
>>> Assuming that I'm not aware of the benefits and the dods teams think
>>> that this is the way, i just want call your attention for the fact that
>>> the stored procedure is only allocating 1 objectid per invocation and
>>> this a bottleneck performance for the database inserts: the stored
>>> procedure will have to be executed for every insert to get the
>>> objectid.
>>> If this stored procedures will be used in the future, i think they
>>> should receive the increment as a parameter to allow the application to
>>> control how much oids it want to allocate and to remove the allocation
>>> bottleneck from database/application. For example, with an increment of
>>> 10000, instead of executing the stored procedure for every insert it
>>> will be executed only 1 time for every 10000 inserts. This is really a
>>> small change to the stored procedures.
>>>
>>> Can someone of the development team clarify this situation?
>>>
>>> Best regards.
>>> Jo�o Paulo Ribeiro
>
> regards

--
João Paulo Ribeiro | Senior Software Engineer
jp@...

PHONE: + 351 253 305 250
FAX  : + 351 253 305 250
www.mobicomp.com

________________________________________________________________

About Solutions | Wireless World

CONFIDENTIALITY NOTICE: This message, as well as existing attached files, is confidential and intended exclusively for the individual(s) named as addressees. If you are not the intended recipient, you are kindly requested not to make any use whatsoever of its contents and to proceed to the destruction of the message, thereby notifying the sender.
DISCLAIMER: The sender of this message can not ensure the security of its electronic transmission and consequently does not accept liability for any fact which may interfere with the integrity of its content.




--
You receive this message as a subscriber of the enhydra@... mailing list.
To unsubscribe: mailto:enhydra-unsubscribe@...
For general help: mailto:sympa@...?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws