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 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?

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?

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"?

"Kristofer Andersson" wrote in message news:<nkaob.94416$5n.89900@bignews5.bellsouth.net> ...
> 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 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?

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.earthl ink.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.

> > 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.

The strange thing is that DBAccess must be splitting the batch too,
since the "0 rows bug" (162088) doesn't occur when running a batch in
dbaccess. But how does DBAccess then execute the individual
statements? It seems to be a lot more efficient than our service.
Maybe we could replicate what DBAccess does from our service (no, not
by running DBAccess from the service ).

Does anyone have insight into _how_ DBAccess does this so efficiently
(splitting and executing the individual statements in a large batch)? Can it
be reproduced in ESQL?

"Kristofer Andersson" <anderssonk75@hotmail.com> wrote in message
news:99ef697d.0311040704.419491e2@posting.google.c om...
> > > 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.
>
> The strange thing is that DBAccess must be splitting the batch too,
> since the "0 rows bug" (162088) doesn't occur when running a batch in
> dbaccess. But how does DBAccess then execute the individual
> statements? It seems to be a lot more efficient than our service.
> Maybe we could replicate what DBAccess does from our service (no, not
> by running DBAccess from the service ).

Kristofer Andersson wrote:
> Does anyone have insight into _how_ DBAccess does this so
> efficiently (splitting and executing the individual statements in a
> large batch)? Can it be reproduced in ESQL?

Of course it can be reproduced in ESQL/C - DB-Access is just a
peculiar ESQL/C application. That's why I was puzzled about your
questions.

To the best of my knowledge, ignoring stored procedures (CREATE
PROCEDURE statements), DB-Access scans the batch looking for a
semi-colon which is not in a comment or a string and sends the data up
to but not including the semi-colon to the server. The statement can
be prepared and either executed or have a cursor declared, etc. There
are some conniptions to go through. Some statements (CONNECT, SET
CONNECTION, DISCONNECT, LOAD, UNLOAD, INFO, OUTPUT) are not simply
handled by the server proper but are executed on the client side.

What are you doing that your code is so slow? I find it hard to
believe that even quadratic searching behaviour limited to 64 KB
strings and with typical statements of the order of, say, several
hundred characters each could run up that big a processing bill.
Let's see: 60000 / 200 = 30 statements per batch. Even 30 full
character by character scans shouldn't show a discrepancy of 7 minutes
in ESQL/C versus 20 seconds in DB-Access.

Have you looked at SQLCMD to see what it does and compared its
performance with either your ESQL/C or DB-Access? It even has a
benchmarking facility built in (-B on the command line or 'benchmark
on' in the SQL statements), as well as a clock mechanism.

Note that DB-Access does not support placeholders and variables - nor
does SQLCMD. And you say your code is unparameterized so it does not
need placeholder or variables either.

Also note that if you are careful, the majority of statements can be
treated with EXECUTE IMMEDIATE. The exceptions are SELECT (other than
SELECT INTO TEMP) and EXECUTE PROCEDURE where the procedure returns
data - those need the cursor.

If you're working in ESQL/C, check out CREATE PROCEDURE FROM - or see
mkproc.ec from the SQLCMD code.

> "Kristofer Andersson" <anderssonk75@hotmail.com> wrote:
>
>>>>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.
>>
>> The strange thing is that DBAccess must be splitting the batch
>> too, since the "0 rows bug" (162088) doesn't occur when running a
>> batch in dbaccess. But how does DBAccess then execute the
>> individual statements? It seems to be a lot more efficient than
>> our service. Maybe we could replicate what DBAccess does from our
>> service (no, not by running DBAccess from the service ).



--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/