I have the same problem as Mark described. The only difference is that I can use a dynamic SQL on the application side and fetch the 100 result back. The question is: 1 how much the performance difference between using db procedure with 100 output parameters vs using a dynamic SQL and fetch it at client side? 2 I have asked this before, but did not get any answers. Does SAPDB support array as input/output parameter?
Thanks a lot for your input, Jenny On Thursday 21 February 2002 01:29 am, you wrote: > Mark Wong wrote: > > Aside from problems with repmcli dumping core when a store > > procedure is > > too long, I have come across some other issues with using a ODBC > > application to load a stored procedure. > > > > As much as I would like to post the stored procedure in full, but it's > > been rejected twice so I'l briefly reproduce it. :) : > > > > CREATE DBPROC order_display(IN c_uname VARCHAR(20), IN c_passwd > > VARCHAR(20), > > OUT items FIXED(2), > > OUT o_id FIXED(10), OUT c_fname VARCHAR(15), OUT c_lname VARCHAR(15), > > OUT c_phone VARCHAR(16), OUT c_email VARCHAR(50), OUT o_date > > TIMESTAMP, > > OUT o_sub_total FIXED(17, 2), OUT o_tax FIXED(17, 2), OUT o_total > > FIXED(17, 2), > > OUT o_ship_type VARCHAR(10), OUT o_ship_date TIMESTAMP, > > OUT o_status VARCHAR(15), > > OUT bill_addr_street1 VARCHAR(40), OUT bill_addr_street2 VARCHAR(40), > > OUT bill_addr_city VARCHAR(30), OUT bill_addr_state VARCHAR(20), > > OUT bill_addr_zip VARCHAR(10), OUT bill_co_name VARCHAR(50), > > OUT ship_addr_street1 VARCHAR(40), OUT ship_addr_street2 VARCHAR(40), > > OUT ship_addr_city VARCHAR(30), OUT ship_addr_state VARCHAR(20), > > OUT ship_addr_zip VARCHAR(10), OUT ship_co_name VARCHAR(50), > > OUT i_id1 FIXED(10), OUT i_title1 VARCHAR(60), > > OUT i_publisher1 VARCHAR(60), OUT i_cost1 FIXED(17, 2), > > OUT ol_qty1 FIXED(3), OUT ol_discount1 FIXED(17, 2), > > OUT ol_comments1 VARCHAR(100), > > > > ... > > > > OUT i_id100 FIXED(10), OUT i_title100 VARCHAR(60), > > OUT i_publisher100 VARCHAR(60), OUT i_cost100 FIXED(17, 2), > > OUT ol_qty100 FIXED(3), OUT ol_discount100 FIXED(17, 2), > > OUT ol_comments100 VARCHAR(100), > > OUT cx_type VARCHAR(10), OUT cx_auth_id CHAR(15)) AS > > VAR c_id FIXED(10); o_bill_addr_id FIXED(10); > > o_ship_addr_id FIXED(10); > > BEGIN > > SET i_id1 = 0; > > SET i_title1 = ''; > > SET i_publisher1 = ''; > > SET i_cost1 = 0; > > SET ol_qty1 = 0; > > SET ol_discount1 = 0; > > SET ol_comments1 = ''; > > > > ... > > > > SET i_id100 = 0; > > SET i_title100 = ''; > > SET i_publisher100 = ''; > > SET i_cost100 = 0; > > SET ol_qty100 = 0; > > SET ol_discount100 = 0; > > SET ol_comments100 = ''; > > SELECT c_id, c_fname, c_lname, c_phone, c_email > > FROM tpcw.customer > > WHERE c_uname = :c_uname > > AND c_passwd = :c_passwd; > > IF $rc = 0 THEN > > BEGIN > > FETCH INTO :c_id, :c_fname, :c_lname, :c_phone, :c_email; > > SELECT o_id, o_date, o_sub_total, o_tax, o_total, o_ship_type, > > o_ship_date, > > o_status, o_bill_addr_id, o_ship_addr_id > > FROM tpcw.orders, tpcw.customer > > WHERE o_c_id = :c_id > > ORDER BY o_date DESC; > > IF $rc = 0 THEN > > BEGIN > > FETCH INTO :o_id, :o_date, :o_sub_total, :o_tax, :o_total, > > > > :o_ship_type, > > : > > :o_ship_date, :o_status, :o_bill_addr_id, > > : > > :o_ship_addr_id; > > > > SELECT cx_type, cx_auth_id > > INTO :cx_type, :cx_auth_id > > FROM tpcw.cc_xacts > > WHERE cx_o_id = :o_id; > > SELECT addr_street1, addr_street2, addr_city, addr_state, addr_zip, > > co_name > > INTO :bill_addr_street1, :bill_addr_street2, :bill_addr_city, > > > > :bill_addr_state, :bill_addr_zip, :bill_co_name > > > > FROM tpcw.address, tpcw.country > > WHERE addr_id = :o_bill_addr_id > > AND addr_co_id = co_id; > > SELECT addr_street1, addr_street2, addr_city, addr_state, addr_zip, > > co_name > > INTO :ship_addr_street1, :ship_addr_street2, :ship_addr_city, > > > > :ship_addr_state, :ship_addr_zip, :ship_co_name > > > > FROM tpcw.address, tpcw.country > > WHERE addr_id = :o_ship_addr_id > > AND addr_co_id = co_id; > > SELECT ol_i_id, i_title, i_publisher, i_cost, ol_qty, ol_discount, > > ol_comments > > FROM tpcw.item, tpcw.order_line > > WHERE ol_o_id = :o_id > > AND ol_i_id = i_id; > > SET items = 0; > > IF $rc = 0 THEN > > BEGIN > > FETCH INTO :i_id1, :i_title1, :i_publisher1, :i_cost1, :ol_qty1, > > > > :ol_discount1, :ol_comments1; > > > > SET items = items + 1; > > END; > > > > ... > > > > IF $rc = 0 THEN > > BEGIN > > FETCH INTO :i_id100, :i_title100, :i_publisher100, :i_cost100, > > > > :ol_qty100, :ol_discount100, :ol_comments100; > > > > SET items = items + 1; > > END; > > END; > > > > Here is the only resulting SQL error: > > > > SQLSTATE S1000 > > [SAP AG][LIBSQLOD SO][SAP DB]General error;-1110 POS(1) View > > definition > > too long. > > > > The full description is: > > > > -1107: Too complicated SQL statement (too many internal commands) > > Explanation: The SQL statement is so complicated that more internal > > statements must be generated for it than can be administered > > for an SQL > > statement. User Action: Simplify the SQL statement or split it into > > several SQL statements. > > > > > > I get the impression that SAP DB cannot handle a stored procedure this > > large, even when increasing _PACKE_SIZE to the max of 131072 bytes and > > _MAXTASK_STACK to 2048 KB. The file size of my stored procedure is > > 56762 bytes. Any help on how to reduce my procedure size, > > short of not > > supporting 100 items (resulting in over 600 parameters), would be > > greatly appreciated. > > Mhm, as you can imagine, we tried your dbprocedure by ourselves in the > meantime. > We found that error, too. > We have to change some coding to allow such length of dbprocs. > Therefore: even the next kernel version (7.3.0.21) will not be able to > handle > your special problem. Sorry, therefore we thought for a workaround, too. > > 1. to increase _PACKET_SIZE is fine, at least 64KB are ok for your dbproc, > if you like 131072, ok > 2. increasing _MAXTASK_STACK to 2048KB is not that fine. > it will not help in your case (your case is quite different to that one > of > Cataldo out of this list. And even he needed a _MAXSTACK_SIZE > of around 360-380KB, not 2048KB). > I would ask you to decrease it ! Set it to the default-value. That will > be enough and save memory (especially if MAXUSERTASKS will be more > than a handful) > 3) As far as I understand your dbproc, you input 2 values, prepare several > intermediate results, whose values are returned from this dbproc. > With the last select: > > SELECT ol_i_id, i_title, i_publisher, i_cost, ol_qty, ol_discount, > ol_comments > FROM tpcw.item, tpcw.order_line > WHERE ol_o_id = :o_id > AND ol_i_id = i_id; > > you create a resultset with not more than 100 results (or the first 100 > results > are enough for you). Their values are returned, increase the number of > output-Variable > to this enormous number and make the dbproc that long. > > What do you think to create a (TMP) table inside or outside the dbproc > to hold the results of the last select, empty that table in the beginning > of the stored proc, and do an > > INSERT mytmptable > SELECT ol_i_id, i_title, i_publisher, i_cost, ol_qty, ol_discount, > ol_comments > FROM tpcw.item, tpcw.order_line > WHERE ol_o_id = :o_id > AND ol_i_id = i_id; > Then you can select from that table and fetch the resultrows one by one. > > The output-parameter of your dbproc would just be those intermediate > results, > the initialisation and the fetches of some hundred values could be avoided > (BTW: all output-parameter are initialized with NULL internally.) > but your way of selecting (those 4-5 selects in the dbproc) can be hidden > from the user. > > We are working to make such kind of dbproc a possible one, but I do not > know if you have time to wait for that release which will fix everything > (remember, it will be NOT fixed in 7.3.0.21, the next relase to come). > > Elke > SAP Labs Berlin > _______________________________________________ > sapdb.general mailing list > [EMAIL PROTECTED] > http://listserv.sap.com/mailman/listinfo/sapdb.general _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
