Re: dynamic SPs

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