Applicable Versions: 3.05 Onwards

The advantage of the multi level level (or complex) queries is that they provide extra flexibility by allowing users to combine subjects together in their queries. The downsides of them are a) that they may be too complex for business users to use as it requires joins to be made and b) the performance issues they can create.

The way the multi level queries work is that the top level (or parent) query runs first and returns a set of results. 

Then for each of the records in the results each query that is a child of the parent query runs and provides a set for results. 

This process is then repeated until all queries have been run with the final result set being built up as each query is executed.

If you have say a three level complex query that has a parent query that returns 100 records and that that has three child queries that also return 100 records and then they similarly have three child queries that also return 100 records then what you would end up with in the final results set would be 100 x 100 x 100 = 1,000,000 so a million records.

So even though each query returns a small dataset, when they are combined it really multiplies up. In this example there will be 1201 small query's ran against the database. Although they will be quick (say 1 second per query) that will still overall take 20 minutes to complete the whole thing on the AppServer and then further time to transport the data from the AppServer to the client.

However the only thing that travels across the network is the final result set. Each child query is run within the same Progress .p (datapa.p). So the network traffic you are seeing must purely be from the large result sets.

Most customers who are using very large dataset's tend to us business logic subjects as it gives them complete control over how to access the data and gives them options to cache some of that data to a database or disk.