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 ? Reading (cutting&pasting) directly from error -460: ----- ... The actual limit differs with different implementations, but it is always generous, in most cases up to 32,000 characters. ... ----- But I guess that isn't generous enough for your customer. ;-) > 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 Is this list static? If you can make ANY changes to the SQL, then you could pump these values in to a new table and reduce your select to something like: SELECT cust_name FROM customer WHERE cust_num IN ((SELECT cust_num FROM my_new_table)) Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com http://www.informix.com/idn |///// / //| |http://www.iiug.org +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |What year 2000 bug? year 2000 bug? |/// / ////| | Fax: +27 838250 2325 |year 2000 bug? year 2000 bug? year |// / /////| |Cell: +27 83 250 2325 |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+

"Mark D. Stock" wrote: > > 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 ? > > Reading (cutting&pasting) directly from error -460: > > ----- > ... The actual limit differs with different implementations, but it is > always generous, in most cases up to 32,000 characters. ... > ----- > > But I guess that isn't generous enough for your customer. ;-) Your customer should know that in my own testing neither Informix NOR Oracle handles IN list > 2K efficiently. I did extensive performance testing in conjunction with setting the default IN list size for my dbdelete.ec utility and 2K produces the best performance. If your client REALLY has to generate a DYNAMIC list of keys to match that is that long, I STRONGLY suggest taking Mark's advice and writing the key list out to a temp table and either using an IN or EXIST against a sub-select from the temp table or just joining to the temp table. You will be doing your customer a favor. Of course Oracle does not properly support temp tables so maybe the customer will just have to switch to using Informix EXCLUSIVELY to get optimum performance. And as Mark states if that HUGE list is STATIC it should really be a permanent table and not a mess of code that needs to be maintained. In this case even the Oracle version can take advantage of the improvement. > > 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 > > Is this list static? If you can make ANY changes to the SQL, then you > could pump these values in to a new table and reduce your select to > something like: > > SELECT cust_name > FROM customer > WHERE cust_num IN ((SELECT cust_num FROM my_new_table)) Or SELECT cust_name FROM customer, my_new_table WHERE customer.cust_num = my_new_table.cust_num; or even: SELECT cust_name FROM customer, my_temp_table WHERE customer.cust_num = my_new_table.cust_num; I think your customer will be pleasantly surprised if you do the performance testing and it shows the table join version is faster than his huge IN list. Art S. Kagel