Beermann, Albert wrote: > > 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 >
Your function needs several communications between client and db-kernel. Depending where your client is situated (same machine or not) and depending on the net between both machines, communication is not the fastest part of the command handling and should be avoided if possible. The dbproc runs in the db-kernel, thus needing just one communication for the whole call. I do not have any idea how big yout table "BWPROGI"."AUTONUM" is. Usually (if no function-based index is used) no qualification like your Upper (suchbegriff) = upper (:searchstring) can be used for a good search-strategy, thus meaning that the whole table has to be scanned. You do it twice, once for the select, once for the update. If we are talking about a huge table, such a function-based index may help or the usage of Create xxx cursor for select <as it is now> FOR UPDATE Fetch xxx into :lastnumber Update "BWPROGI"."AUTONUM" set wert = wert + 1 where current of xxx Close xxx There are more statements which need more time than yours, but less table scan. Depending on the size of "BWPROGI"."AUTONUM" my idea has a positive or negative effect on performance. Your argument with the special number intervals I do not understand. Do you mean, not just 1 more or 9 times 1 more and the 10th time 20 more or what is the problem with sequences? In your update example we do not see that problem, just add 1. Hope you checked sequences very well because your private 'sequence' costs performance compared to those of the database. Elke SAP Labs Berlin > Best Regards > Albert > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
