Re: limit on query length in Informix IDS 11.50

On Wed, Sep 29, 2010 at 9:21 PM, Gentian Hila <genti.tech@gmail.com> wrote: > I will try dbaccess. > > No it's not a lot of tables joined, it's just a whole lot of > calculations that cognos does not do internally but sends them over to > database and apparently the version 8.4 we just got cannot be forced > to do them internally as 8.2 used to do > There are long case ... when and nested select statements > > It's really complex too read it - as I said below : almost 50,000 > characters in a query. > > On Wed, Sep 29, 2010 at 3:49 PM, Superboer <superboer7@t-online.de> wrote: > > Should be 64 KB.at least V7 etc had that limit.. > > > > why not test it; eq put it in dbaccess and set explain on!! > > will also tell you hopefully what is going on; > > may be you need to set optimization low; > > i suspect a large nr of tables are joined??? causing the optimizer to > > go crazy > > or work....??? > > > > > > Superboer. > > > > > > On 29 sep, 20:19, Gentian Hila <genti.t...@gmail.com> wrote: > >> Is there a limit on query length in Informix IDS 11.50? > >> > >> If there is one, can it be increased? > >> > >> Also can anyone suggest some information on how to optimize IDS 11.50 > >> for longer queries to make them run faster? > >> > >> We have some Cognos reports that send very long queries to IDS 11.50 > >> (about 50,000 characters including space). > >> > >> We use Informix Client SDK 3.50. > >> > >> Cognos support insists that the query is too long for Informix and it > >> might be even though we're not getting an error that the query is > >> longer than necessary, it simply never stops running. > >> > >> Thank you > >> > >> Genti > > > 50K is less than the normal limit which someone already stated is 64K. So, the problem is not that the engine is "cutting" the query or something like that (instead you should receive an error). Naturally a 50K query is hard to read, optimize and understand. But you must do it... You should get a query plan and check the execution of the query. You can use SQLTRACE, EXPLAIN ON, onstat -g pqs etc. Of course, if you need help you could open a PMR, but the job of technical support is not do the DBA job for the customer. What I mean is that ideally the first analysis should be made by you and then, if you suspect that the engine is making the wrong choices you could ask for help. Saying that it's too long for Informix is a simplistic and unproven statement. Specially when some Cognos versions use IDS as their repository. There is no such thing as a query too long. What exist are bugs which we solve and wrong data models for the querys being run, which our customer should solve (with our help if needed). So you could check for sequential scans, try to recreate statistics on the tables used etc. Finaly, the 64K limit is a limitation of the SQLi protocol (native/traditional Informix client/server protocol). Your version, and in fact since 11.10, support DRDA, the open (although created by IBM) RDBMS client/server protocol. This protocol has a maximum limit of 2MB (to the best of my knowledge) and I was able to run queries with that that failed with SQLi. What I mean is that if your queries get even largers, and given that you're getting closer to the limit of SQLi, you may need to consider in the future creating another engine listener using DRDA protocol. Regards. > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...