limit on query length in Informix IDS 11.50
Gentian Hila
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
Superboer
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.
da...@smooth1.co.uk
Maximum size of an SQL statement 64K
The maximum length of a DECLARE statement is 64 kilobytes.
Gentian Hila
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.
> _______________________________________________
> Informix-list mailing list
> Inform...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Fernando Nunes
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.
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
> Inform...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
_______________________________________________
Informix-list mailing list
Inform...@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...