Hello Everybody
My application communicates with a maxdb via odbc.
I have about 500 concurrent users.
For automatic numbering (orderno,customerno,invoiceno,...) we use this
function.
(we can't use sequences because we work with special number intervals in
some tables)
Function p_autozaehler
************************************************************************
************
* Getting the next number for a searchvalue
************************************************************************
************
parameters searchstring,newnumber
l_sqlcmd = "SELECT wert as lastnumber FROM autonum " +;
"where suchbegriff = '" + UPPER(searchstring) +"' WITH LOCK
EXCLUSIVE "
= SQLEXEC(p_verbindungsnr,l_sqlcmd)
newnumber = lastnumber + 1
l_sqlcmd ="UPDATE autonum set wert=?newnumber where suchbegriff = '"+
UPPER(searchstring) +"'"
= SQLEXEC(p_verbindungsnr,l_sqlcmd)
l_sqlcmd = "commit"
= SQLEXEC(p_verbindungsnr,l_sqlcmd)
return(newnumber)
ENDFUNC
Now we are thinking about replacing this function with a dbproc
call.(our first dbproc !!)
We create a dbproc:
CREATE DBPROC dbproc_autonum (IN searchstring CHAR(30),OUT newnumber
FIXED(10,0)) as
begin
select wert into :lastnumber FROM "BWPROGI"."AUTONUM" WHERE
upper(suchbegriff) = upper(:searchstring) ;
update "BWPROGI"."AUTONUM" set wert = wert + 1 WHERE upper(suchbegriff)
= upper(:searchstring) ;
SET newnumber = :lastnumber + 1;
end;
In our application we do:
l_cmd = 'CALL dbproc_autonumneu(?l_searchstring,[EMAIL PROTECTED]) WITH
COMMIT'
= sqlexec(p_verbindungsnr,l_cmd)
We tried with massive batch input (20000 calls)
The version with using the dbproc is more then 150 % faster !!??
But ???
Does the dbproc the same job ??? Is there a better way to do this with
maxdb?
We want to avoid double numbers with massiv parallel use.
(20 Users do a batch invoice creation(100 new invoices each) at the same
time!?)
Do we need WITH LOCK EXCLUSIVE in our dbproc also ??
Any help welcomed
Best Regards
Albert
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
