Stored Procedure performance issue - PLEASE HELP!
in [DB2]
|
From: credondo on 1 Feb 2010 16:45 On 31 ene, 20:01, "Hardy" <wyh...(a)gmail.com> wrote: > "Mark A" <no...(a)nowhere.com> дÈëÏûÏ¢news:hk4dd8$dt7$1(a)news.eternal-september.org... > > > > > > > "Serge Rielau" <srie...(a)ca.ibm.com> wrote in message > >news:7skho3FgomU1(a)mid.individual.net... > >> in addition there is indeed an extra cost. > >> First you execute the CALL statement which then executes the stored > >> procedure which needs some basic initialization which then opens the > >> cursor. > > >> In trivial procedures this can be a "feel-able" overhead. > >> For TPC-C we do not use any stored procedures. > >> Instead we use inline functions for encapsulation since they are macro > >> expanded. > > >> Cheers > >> Serge > >> -- > >> Serge Rielau > >> SQL Architect DB2 for LUW > >> IBM Toronto Lab > > > Would IBM be willing to publish specific examples of "inline functions for > > encapsulation" as used in TPC-C? > > try this article by Serge, > > http://www.ibm.com/developerworks/db2/library/techarticle/dm-0411riel... Hi guys, I found the root of the issue with my Stored Procedures. It appears that DB2 is sensitive to numeric conversions or it just not use indexes when arguments types do not match with column types, performance issue was cause by an argument parameter declared as Double while its table.column match was a Numeric(8,0). As soon as I changed my argument to Numeric(8,0) time response was reduced to 123 milliseconds. Conclusion: Declare arguments with the same datatype of columns. Thanks for your help!
From: Tonkuma on 1 Feb 2010 19:57
Is it necessary to use UNION? If no duplicated rows were not expected, you can use UNION ALL to avoid sort. |