Prepared ESQL/C vs. Stored Procedures

IDS 7.31 HP-UX 11.0 DSS configured environment. Has anyone benchmarked the performance of Prepared ESQL/C inserts vs. Passing the values of a record to be inserted by a Stored Procedure? This is what we are trying to do, we are inserting 25 rows/second (the rows are rather large 1006 bytes) and each time we insert a row into the database we summarize the data and populate up to 20 other tables with the summarized data (for faster reporting against the summary tables). What we would like to do is pass the parameters of the new row to a stored procedure and then within that stored procedure populate the master table and update/insert into the summary tables, does anyone know if this will be a lot less efficient than doing it withing ESQL/C, more efficient, the same? If you need any more information let me know, thanks for any help that anyone can offer. Andrew

In the environment I tested under (v7.31/9.20, HP10.20), Prepared ESQL was about twice as fast as SPs. The tests were various and involved combinations of inserts, updates & selects on a, more or less, 2:1:1 basis with row sizes varying from 256 bytes to 32k, char & text. Note that the various statements involved were prepared just once each. This is, as your probably know, crucial to use of prepared statements. You may want to explore a third option, given that row by row processing is inherently slower than a bulk load situation. If its possible, you could try to bulk load your inserts, updating/inserting your summary tables using triggers. If you want a more details on the benchmark, I'd be happy to provide them off-line. And, if you test these three options in your environment (recommended), I would like to see your numbers. Rudy Andrew Ford wrote: > IDS 7.31 > HP-UX 11.0 > DSS configured environment. > > Has anyone benchmarked the performance of Prepared ESQL/C inserts vs. > Passing the values of a record to be inserted by a Stored Procedure? > > This is what we are trying to do, we are inserting 25 rows/second (the rows > are rather large 1006 bytes) and each time we insert a row into the > database we summarize the data and populate up to 20 other tables with the > summarized data (for faster reporting against the summary tables). > > What we would like to do is pass the parameters of the new row to a stored > procedure and then within that stored procedure populate the master table > and update/insert into the summary tables, does anyone know if this will be > a lot less efficient than doing it withing ESQL/C, more efficient, the > same? > > If you need any more information let me know, thanks for any help that > anyone can offer. > > Andrew

Another thing to bear in mind, but I guess you've already been there, is that the esql solution will only tweak the other tables if that esql code is used to insert the data. Inserting the row through dbaccess/isql or anything else won't do it... Linking your SP to an insert trigger would ensure the 'manipulation' is done every time, however it's done. If your data is only put in one way, then it's an easier decision. If it can be put in by a variety of routes, then it may not be as simple as finding the fastest way, it may be you have to choose the route that gives the best compromise between data integrity and speed. In article <393EA0B2.B7725610@americasm01.nt.com>, Rudy Fernandes <rferdy@americasm01.nt.com> writes >In the environment I tested under (v7.31/9.20, HP10.20), Prepared ESQL was >about twice as fast as SPs. The tests were various and involved combinations >of inserts, updates & selects on a, more or less, 2:1:1 basis with row sizes >varying from 256 bytes to 32k, char & text. > >Note that the various statements involved were prepared just once each. This >is, as your probably know, crucial to use of prepared statements. > >You may want to explore a third option, given that row by row processing is >inherently slower than a bulk load situation. If its possible, you could try >to bulk load your inserts, updating/inserting your summary tables using >triggers. > >If you want a more details on the benchmark, I'd be happy to provide them >off-line. And, if you test these three options in your environment >(recommended), I would like to see your numbers. > >Rudy > >Andrew Ford wrote: > >> IDS 7.31 >> HP-UX 11.0 >> DSS configured environment. >> >> Has anyone benchmarked the performance of Prepared ESQL/C inserts vs. >> Passing the values of a record to be inserted by a Stored Procedure? >> >> This is what we are trying to do, we are inserting 25 rows/second (the rows >> are rather large 1006 bytes) and each time we insert a row into the >> database we summarize the data and populate up to 20 other tables with the >> summarized data (for faster reporting against the summary tables). >> >> What we would like to do is pass the parameters of the new row to a stored >> procedure and then within that stored procedure populate the master table >> and update/insert into the summary tables, does anyone know if this will be >> a lot less efficient than doing it withing ESQL/C, more efficient, the >> same? >> >> If you need any more information let me know, thanks for any help that >> anyone can offer. >> >> Andrew > Andrew Lennard andy@kontron.demon.co.uk