Get started Bring yourself up to speed with our introductory content.

Speed up SQL subselects

Here you'll find an easy way to speed up your SQL subselects.

An SQL subselect picks records based on whether a value appears in another select -- a simple example would be: select * from orders where partno in (select partno from outofstock). My master file has two keys so to do a subselect I have to to combine them. Key1 is 9s0, key2 is 3s0 so I have been selecting like this: select * from master where key1*1000+key2 in (select k1*1000+k2 from billfile)

This is very slow, compared to an inner join. I recently found that if I converted the combined key to a string, response is as fast as an inner join (apparently the iSeries is much better with characters than math): select * from master where cast(key1 as char(9))||cast(key2 as char(3))in (select cast(k1 as char(9))||cast(k2 as char(3)) from billfile) The || is string concatenation in SQL; cast() transforms one data type to another.


The Best Web Links: tips, tutorials and more.

Visit the ITKnowledge Exchange and get answers to your developing questions fast.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.

Dig Deeper on iSeries SQL commands and statements

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.