2006/3/23, Beermann, Albert <[EMAIL PROTECTED]>: > 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
I guess your DBPROC is faster because it does not use dynamic SQL. Personally I'd use sequences because they are built to provide safe concurrent access. What you basically do is, you implement a number of counters via a table. You could as well use your search string to access a sequence. Even better, directly use n sequences. My 0.02... robert -- Have a look: http://www.flickr.com/photos/fussel-foto/ -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
