Re: LENGTH OF SQL STATEMENT.

Vikas kapare wrote: > > Hi, > > I have a unique problem with one of my customer. > He has some badly written application which is capable > of working on Informix, Oracle and SQL Server. > > His application produces one SQL statement of 56 wordpad pages(A4). > One SQL statement with 56 pages. This SQL statement refuses to > run on Informix. It gives -460 error. Statement too long. > This application runs fine with > Oracle. Customer is under threat to change the engine to Oracle. > > 1. What is the limit on SQL statement in Informix ? > > 2. Can the above problem be resolved or some work around other than > optimising the SQL statement. > > This statement is like > > Select cust_name from customer where cust_num in ( big big list )> > This "big big list" is of 56 pages. > > IDS 7.X The X in 7.X matters - the answer is probably different for each of 1x, 2x and 3x. However, once upon a time the answer was, as Mr Obnoxio said, 32KB. That was subsequently increased to 64 KB, and is now reputedly in the 200+ KB range. So, in principle, you should be OK even with ridiculously large statements. However, as Mr Kagel pointed out, you will probably benefit very substantially from converting the IN(value-list) into IN (SELECT value FROM table) notation, or even going for a join. You have to use the IN notation if you are doing an UPDATE or DELETE. If you are selecting, you can use either. Once upon a long time ago (circa 1990), I was doing benchmarking on querying names from a database with over half a million individuals in it, and we found that for small lists (less than say a dozen items), the IN notation was faster than a join, but for larger lists (say over a hundred items), the join was faster than the IN notation. The system would handle ridiculous queries (such as A B C DE F as a series of initials and find the one person with a matching name - in less than a second). This was the most dynamic of dynamic SQL; we worked out how to ask the database the question based on the data in the query, with orders of magnitude difference in performance depending on which sequence was chosen. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>