Correlated Subqueries
We are using Business Objects for user generated adhoc queries against a data warehouse of student information. Business Objects often generates SQL which uses correlated subqueries. On large tables these queries run unacceptably long. Since I don't have much control over what Business Objects generates and I don't have much control over the request that our customers make is there anything I can do to make Informix handle these queries better? Thanks for your help. Dale Forrey, DBA email: forrey@wsu.edu Information Technology FAX: (509) 335-0540 Washington State University Phone: (509) 335-7098 PO Box 641222 Pullman, WA 99164-1222 ADABAS 6.2.1 on OS/390 Natural 2.2.8 Predict 3.4.1 Informix 7.30 UC3-1 on AIX 4.3.1
Try setting the environmental variable NO_SUBQF=1 before starting the engine. > > We are using Business Objects for user generated adhoc queries against a >data warehouse of student information. Business Objects often generates >SQL which uses correlated subqueries. On large tables these queries run >unacceptably long. Since I don't have much control over what Business >Objects generates and I don't have much control over the request that our >customers make is there anything I can do to make Informix handle these >queries better? Thanks for your help. > >Dale Forrey, DBA email: forrey@wsu.edu >Information Technology FAX: (509) 335-0540 >Washington State University Phone: (509) 335-7098 >PO Box 641222 >Pullman, WA 99164-1222 > >ADABAS 6.2.1 on OS/390 >Natural 2.2.8 >Predict 3.4.1 > >Informix 7.30 UC3-1 on AIX 4.3.1 > > > > Madison Pruet
Setting PDQPRIORITY for the session will almost certainly help. Also, you might try setting OPTCOMPIND to 2 for that session (not for the entire engine, though, as this will hurt OLTP performance). If you're typically running the same queries over and over, you might also try running with SET EXPLAIN ON (if possible in your tool) and creating some custom indexes to improve performance. The last resort is, if the tool you're using doesn't do what you need it to do in a reasonable amount of time, then it's not meeting your needs, and you might consider a different tool. Dale Forrey wrote in message <785ma5$5vv$1@news.xmission.com>... > > We are using Business Objects for user generated adhoc queries against a >data warehouse of student information. Business Objects often generates >SQL which uses correlated subqueries. On large tables these queries run >unacceptably long. Since I don't have much control over what Business >Objects generates and I don't have much control over the request that our >customers make is there anything I can do to make Informix handle these >queries better? Thanks for your help. > >Dale Forrey, DBA email: forrey@wsu.edu >Information Technology FAX: (509) 335-0540 >Washington State University Phone: (509) 335-7098 >PO Box 641222 >Pullman, WA 99164-1222 > >ADABAS 6.2.1 on OS/390 >Natural 2.2.8 >Predict 3.4.1 > >Informix 7.30 UC3-1 on AIX 4.3.1