Forum OpenACS Development: Re: acs_object_id_seq creation speed

Collapse
2: Re: acs_object_id_seq creation speed (response to 1)
Posted by Vinod Kurup on 01/19/04 09:53 PM
Hey Jade,

Yup, the view/sequence hack was initially used to allow Postgres and Oracle code to have the same syntax: 'select acs_object_id_seq.nextval from dual'. But PG changed over time, so that multiple calls to the view in 1 query only incremented the sequence once, instead of multiple times. So the hack is no longer useful. All TCL code should call db_nextval and SQL code should use the actual sequence (t_acs_object_id_seq), not the view.

Collapse
4: Re: acs_object_id_seq creation speed (response to 2)
Posted by Dirk Gomez on 01/19/04 10:26 PM
I simply ran this code:

set startclicks [clock clicks -milliseconds]

for {set x 0} {$x<1000} {incr x} {
    set item_id [db_nextval acs_object_id_seq]
}

set endclicks [clock clicks -milliseconds]
[expr $endclicks - $startclicks]

And setting cache to 20 or to 100 didn't matter.

During the AIESEC - Oracle 8i - migration I found out that retrieving an id from acs_objects_id was the most expensive operation, so I thought it might be similar with PG. Looks like it isn't.

Collapse
5: Re: acs_object_id_seq creation speed (response to 4)
Posted by Jeff Davis on 01/20/04 10:15 AM
getting 10,000 id's one at a time from the view took 2.51s (.25ms per id) and getting 10,000 from the sequence directly one at a time took 2.10s (.21ms per id); I would say it's pretty fast either way and definitely not something thats worth worrying about much.

The view hack is just that, a hack, and using the sequence directly is preferable but from a performance standpoint fixing it is a non-issue.

Collapse
6: Re: acs_object_id_seq creation speed (response to 5)
Posted by Dirk Gomez on 01/20/04 11:08 AM
Hmm, with above code snippet I had about 14 seconds with the view and about 7 seconds with the sequence - so for my setup the difference was quite big. I ran it against PG 7.3 - which version of PG did you use?
Collapse
7: Re: acs_object_id_seq creation speed (response to 6)
Posted by Jeff Davis on 01/20/04 01:05 PM
I did it in psql with output going to /dev/null. 
time {db_nextval acs_object_id_seq} 1000
versus 
time {db_nextval t_acs_object_id_seq} 1000
shows db_nextval is 6.6ms per id for the sequence versus 13.4ms for the view but that's because it tries the sequence first and then if it does not exist it tries the view (i.e. 2 trips to the db). It should probably just cache which ones it needs to use the view for (then it would be about the same speed).
Collapse
12: Re: Re: acs_object_id_seq creation speed (response to 2)
Posted by Malte Sussdorff on 06/28/06 04:16 PM
Do I understand this correctly that this hack can cause a situation where calling db_nextval acs_object_id_seq returns the same value?