Re: dynamic SPs

Kristofer Andersson wrote: > Doesn't anyone have an opinion on this? Is it safe or do I risk > running into bottlenecks (such as locks on the system tables that hold > SPs) if I repeatedly create, execute and immediately drop stored > procedures? You do run into some risks of that. It is somewhat wasteful to go updating the system catalog - rather extensively - simply to run the contained SQL once, and then undo those changes to the system catalog. I don't see how doing so can be quicker than executing the SQL directly - see also notes below. > Also, why does it take so long time to create a SP? Informix doesn't > seem to do too much syntax validation when creating a SP so where is > the time spent when I execute a "create procedure"? What do you mean about 'not doing syntax validation'? It does s much as it can. If the tables it is to operate on do not exist when it is created (eg they are temporary tables), that reduces the amount of checking it can do. Otherwise, it generates the query plan it will use, and records it. There's a lot of work to be done with all that. > "Kristofer Andersson" wrote: >>I have a piece of code that generate a large batch of SQL statements. >>Because of a bug in informix/esql (if a statement in the middle of a batch >>affect 0 rows, no subsequent statements will be executed) we have to split >>it and execute one statement at a time. This slows down the execution >>enormously. I recall the bug - either you or someone else ran into it earlier this year. I'm puzzled about the 'enormously'. Are the statement all paremeterized? How many statements in your batch? What sort of network environment are you using? >>I was thinking about dynamically creating stored procedures to avoid this >>problem. I create a bunch of SPs that contain my script and then execute >>those. Is there any risks, bottlenecks etc that I may encounter if I do >>that? (Create procedure, execute procedure, drop procedure)? Or can informix >>handle a lot of create/execute/drop procedure calls from many users? The obvious risk is that dynamically creating the SPs will be a lot slower than simply executing the SQL. I'd be fascinated to hear about the environment where you think creating the SPs and executing them would be quicker than simply executing the statements. It would only seem to make sense (to me - not necessarily having thought it through accurately) if there is some incredibly large network latency - if you are connecting via satellite links, or something similar. So, this is not the approach I'd expect to take. I would expect to be using the single statements without running into major performance issues. Without knowing more about what you're up to - whether each statement in a batch is operating on a single row in the table or whether it is doing lots of rows, how big the tables are, whether you ever run update statistics (and if so, how), and so on, it is difficult to know how else to respond. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

"Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:cjtpb.4931$qh2.2672@newsread4.news.pas.earthlink.net... > > Also, why does it take so long time to create a SP? Informix doesn't > > seem to do too much syntax validation when creating a SP so where is > > the time spent when I execute a "create procedure"? > > What do you mean about 'not doing syntax validation'? It does s much > as it can. If the tables it is to operate on do not exist when it is > created (eg they are temporary tables), that reduces the amount of > checking it can do. Otherwise, it generates the query plan it will > use, and records it. There's a lot of work to be done with all that. Ok, if the execution plan is generated when the procedure is created, that explains the creation time. As for the syntax validation, I remember creating some SPs that didn't give syntax errors until they were executed. > >>I have a piece of code that generate a large batch of SQL statements. > >>Because of a bug in informix/esql (if a statement in the middle of a batch > >>affect 0 rows, no subsequent statements will be executed) we have to split > >>it and execute one statement at a time. This slows down the execution > >>enormously. > > I recall the bug - either you or someone else ran into it earlier this > year. Yup, me and a colleague (who wrote the ESQL service I am calling). > I'm puzzled about the 'enormously'. Are the statement all > paremeterized? How many statements in your batch? What sort of > network environment are you using? No, not parameterized at all. The number of statements vary depending on user selections, but I had one example where I generated some 3000 insert into...select statements (where it selects 0, 1 or many records from the same table it is inserting into). The ESQL code that receive and split the statement batch is running under Tuxedo on the same machine as Informix (so no network). We're using shared memory to connect to Informix (9.4). In that particular case, running the whole batch in DBAccess took about 20 seconds. Running it (statement by statement) from ESQL took 7 minutes. Packing it up in SPs and executing them brought it down to 1 minute. Still, I would love to get the same performance as DBAccess but don't know how we can achieve that. > >>I was thinking about dynamically creating stored procedures to avoid this > >>problem. I create a bunch of SPs that contain my script and then execute > >>those. Is there any risks, bottlenecks etc that I may encounter if I do > >>that? (Create procedure, execute procedure, drop procedure)? Or can informix > >>handle a lot of create/execute/drop procedure calls from many users? > > The obvious risk is that dynamically creating the SPs will be a lot > slower than simply executing the SQL. I'd be fascinated to hear about > the environment where you think creating the SPs and executing them > would be quicker than simply executing the statements. It would only > seem to make sense (to me - not necessarily having thought it through > accurately) if there is some incredibly large network latency - if you > are connecting via satellite links, or something similar. > > So, this is not the approach I'd expect to take. I would expect to be > using the single statements without running into major performance > issues. Without knowing more about what you're up to - whether each > statement in a batch is operating on a single row in the table or > whether it is doing lots of rows, how big the tables are, whether you > ever run update statistics (and if so, how), and so on, it is > difficult to know how else to respond. No, this was definitely not my initial approach either. But I don't know what I can do to get this thing to run quicker. The code that generate the SQL statements allow so many different input combinations so writing traditional stored procedures to replace it would end up with 64 different SProcs (maintenance nightmare). As usual, thanks a lot for replying. Your input is much appreciated.