Re: dynamic SPs

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