(RADIATOR) Suggestions for FindQuery and AllocateQuery in AddressAllocatorSQL

[Andy M]

Hi,

We are running Radiator 3.8 on a Linux server, using AuthBy DYNADDRESS and
AddressAllocator SQL in order to allocate IP addresses from the RADPOOL
table held in an Oracle 9i DB.

The current logic of the address allocation process seems to be:

1. Select an address (using FindQuery)
2. Update it (using AllocateQuery)
3. If the update failed due to another process getting there first, goto #1
(maximum of 20 times)

We are seeing an increasing number of cases of repeated lookups - to the
point where it is impacting performance; to get around this we've written a
stored PL/SQL function, get_ip(), which will both choose an IP address and
update it (in such a way that it isn't kept waiting for locks from other
updates to be released). It is called by means of a FindQuery like:

   select get_ip(pool) from dual;

The problems here are:

1. We need to define an AllocateQuery function, even though we don't want to
run anything. We could run something like the following (which does
nothing), but it is still an unnecessary DB round trip:
       AllocateQuery DECLARE p_ret_allocate integer; BEGIN p_ret_allocate :=
1; END;
2. At the stage that FindQuery is run, the expiry time (which is required by
the update statement) is not known and so isn't accessible as a bind
variable

... so could I make a couple of suggestions:

1. Don't run AllocateQuery if it is defined as aen empty statement
2. The expiry time is calculated once, before the main loop, and made
accessible as a bind variable to FindQuery

Thanks in advance,

Andy

--
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.