Do selects to multiple libraries affect speed?

[rpgKnight]

This is something I have not considered. We've written numerous SQL based programs were selects are made to tables that exists on different libraries. I do know that speed is affected if we don't use the correct logical and that kind of thing but I was told that speed may be a problem if a table is select from multiple libraries.

For example:

select liba/cola,libb/colb from liba/filea, libb/fileb....etc

Will it slow down the process?

[tomholden]

Re: Do selects to multiple libraries affect speed?

i highly doubt that would affect anything. as long as the tables are found (which you apparently have them qualified the resolution of the path to the file prevents a library list search which should actually improve performance but i don't recommend hard-coding them. the library list search is extremely fast). but as far as the actual data selection there should be zero performance degradation.

[rpgKnight]

Re: Do selects to multiple libraries affect speed?

i highly doubt that would affect anything. as long as the tables are found (which you apparently have them qualified the resolution of the path to the file prevents a library list search which should actually improve performance but i don't recommend hard-coding them. the library list search is extremely fast). but as far as the actual data selection there should be zero performance degradation.

That's what I thought. Thanks for the input.

[egami]

Re: Do selects to multiple libraries affect speed?

Not likely, but you could run into issues if the libraries reside in different ASP's. I know we had this issue in a previous environment I worked in, but that was also a few OS releases ago.

[B.Hauser]

Re: Do selects to multiple libraries affect speed?

Accessing files/tables that are located in different libraries/schemas within the same ASP that are used in the same SQL statement will not decrease performance.

I do know that speed is affected if we don't use the correct logical

I hope you are not specifying the logical files in your SQL statements. Specifying logical files within an SQL Statement will rather decrease performance than speed it up.

If a DDS described logical file is specified in a SQL statement, the query optimizer needs to rewrite the query based on the underlying physical files. To rewrite the quey the optimizer checks the DDS definition and only takes field selection, joins and select/omit clauses. Keys (or the access path) are not considered. After the rewrite the query optimizer does not know anymore that a logical file with a specific key was originally specified . Moreover the query optimizer scans through all access paths (Keyed logical files and SQL indexes) to find the optimal one. (That's why you need the "correct" logicals).

But it's only the old (Classic) Query Engie (CQE) that will be able to analzye a DDS described logical. That means your query must be executed by the CQE and cannot be executed by the newer SQL Query Engine (SQE) which normally performs much better.
A rerouting from SQE to CQE may decrease performance up to 15%.

Birgitta

[BenThurley]

Re: Do selects to multiple libraries affect speed?

I have seen DDS described logicals in SQL statements before. I was equally surprised but was told it would speed up performance because it already has select/omit criteria reducing the number of records that the engine needs to scan through. I was also told it would give the result set an order.

I got stung by this when I added an IGNORE_DERIVED_INDEX attribute to the QAQQINI file on production. I hadn't thought anyone would specify a select/omit in a SQL statement and all of a sudden the software started timing out. (N.B. this passed testing because the test database is much smaller). The solution was of course to hurriedly back out the change and then condition this program to not use the IGNORE_DERIVED_INDEX attribute!

Another bugbear is the process of always disconnecting from the database after running a piece of SQL and then reconnecting on the next call to that program. I am told that if the optimizer thinks a request looks similar to a previous request then it will use the same access path. I would have thought this would be a good thing but apparently the optimizer sometimes gets it wrong and performance nosedives. This disconnect, reconnect is a sort of kludge to skirt round it.

I have to say that I have always been a bit suspicious of all of this. It just doesn't feel right but without hard facts and evidence it's hard to try and convince anyone to do things differently. These are employees much more senior than I.

I'd be interested to hear you thoughts.

[egami]

Re: Do selects to multiple libraries affect speed?

I hope you are not specifying the logical files in your SQL statements. Specifying logical files within an SQL Statement will rather decrease performance than speed it up.

If a DDS described logical file is specified in a SQL statement, the query optimizer needs to rewrite the query based on the underlying physical files. To rewrite the quey the optimizer checks the DDS definition and only takes field selection, joins and select/omit clauses. Keys (or the access path) are not considered. After the rewrite the query optimizer does not know anymore that a logical file with a specific key was originally specified . Moreover the query optimizer scans through all access paths (Keyed logical files and SQL indexes) to find the optimal one. (That's why you need the "correct" logicals).

But it's only the old (Classic) Query Engie (CQE) that will be able to analzye a DDS described logical. That means your query must be executed by the CQE and cannot be executed by the newer SQL Query Engine (SQE) which normally performs much better.
A rerouting from SQE to CQE may decrease performance up to 15%.

Birgitta

This is true. And I can't count the number of shops and programmers I've ran into that have spread this nasty habit of including logicals in their SQL statements. It's rampant.

[B.Hauser]

Re: Do selects to multiple libraries affect speed?

I am told that if the optimizer thinks a request looks similar to a previous request then it will use the same access path.

It may use the same access plan, but each SQL statement gets its own access path (ODP).

The most time consuming part or the query execution is opening the data path, because (normally) a bunch of temporary objects (such as hash tables, relative record lists ...) must be built and filled with data.

When closing the connection the ODP gets deleted, and the next time a FULL OPEN (validating or building an access plan and opening the data path) must be performed.

If the connection stays open and same SQL statement will be executed several times, the ODP can be reused, that means only the data in the temporar objects get actualized.

But the actualization may take also a lot of time, if there are no optimal access path (indexes or keys in DDS described logical fils) and for accessing the data a table scan must be performed.

Birgitta

[arrow483]

Re: Do selects to multiple libraries affect speed?

got stung by this when I added an IGNORE_DERIVED_INDEX attribute to the QAQQINI file on production. I hadn't thought anyone would specify a select/omit in a SQL statement and all of a sudden the software started timing out. (N.B. this passed testing because the test database is much smaller). The solution was of course to hurriedly back out the change and then condition this program to not use the IGNORE_DERIVED_INDEX attribute!

Are you saying that seting IGNORE_DERIVED_INDEX to *YES caused the slower performance ? While this is possible (thats why default used to be *NO) - it would require that the selects in the LF are the same as the query. I changed to *YES and improved performance 400%. Of course, this is very database dependent ...

[B.Hauser]

Re: Do selects to multiple libraries affect speed?

Are you saying that seting IGNORE_DERIVED_INDEX to *YES caused the slower performance ? While this is possible (thats why default used to be *NO)

Beginning with release 6.1 the default is set to *YES!!!

Performance will be sometimes slower, because with IGNRORE_DERIVED_INDEXES *YES, all logical files with SELECT/OMIT clause are ignored.
If access path stored in these logical files were used before, they would be ignored now and perhaps a table scan instead of an index access might be performed.
Visual Explain or the permanent index advisor which was introduces with release V5R4 will show you which indexes/access path are needed.

BTW with real complex logicla files with select/omit clauses the CQE optimizer sometimes did no optimization (if analyzing the DDS seemed too timeconsuming), but used the logical file directly.

Birgitta

[BenThurley]

Re: Do selects to multiple libraries affect speed?

Yes it resulted in slower performance. It was something like a transactions file with over 10 million records. The select/omit was something like "show me the open transactions only". When the IGNORE_DERIVED_INDEX was set to *YES the SQE ignored the select/omit logical and performed a full table scan.

I would like to think this could have been resolved by building some new logicals over this file. Unfortunately, the solution we ended up with was to turn off the IGNORE_DERIVED_INDEX for this program so we'll never really know.

If we ever upgrade to 6.1 I can see us having a big disaster with programs timing out and having to turn off the IGNORE_DERIVED_INDEX attribute. As you say it is database specific and in a large enterprise system built over 20 odd years who knows what else is lurking around...

[tomholden]

Re: Do selects to multiple libraries affect speed?

Yes it resulted in slower performance. It was something like a transactions file with over 10 million records. The select/omit was something like "show me the open transactions only". When the IGNORE_DERIVED_INDEX was set to *YES the SQE ignored the select/omit logical and performed a full table scan.

I would like to think this could have been resolved by building some new logicals over this file. Unfortunately, the solution we ended up with was to turn off the IGNORE_DERIVED_INDEX for this program so we'll never really know.

If we ever upgrade to 6.1 I can see us having a big disaster with programs timing out and having to turn off the IGNORE_DERIVED_INDEX attribute. As you say it is database specific and in a large enterprise system built over 20 odd years who knows what else is lurking around...

on v6.1 just recreate the index using the WHERE clause on the CREATE INDEX statement and you're set...

[BenThurley]

Re: Do selects to multiple libraries affect speed?

on v6.1 just recreate the index using the WHERE clause on the CREATE INDEX statement and you're set...

I did look at that option for something else we are working on but it was decided that we didn't want a product which had to run on 6.1. It had to be backwards compatible.

[B.Hauser]

Re: Do selects to multiple libraries affect speed?

Yes it resulted in slower performance. It was something like a transactions file with over 10 million records. The select/omit was something like "show me the open transactions only". When the IGNORE_DERIVED_INDEX was set to *YES the SQE ignored the select/omit logical and performed a full table scan.

I would like to think this could have been resolved by building some new logicals over this file. Unfortunately, the solution we ended up with was to turn off the IGNORE_DERIVED_INDEX for this program so we'll never really know.

If we ever upgrade to 6.1 I can see us having a big disaster with programs timing out and having to turn off the IGNORE_DERIVED_INDEX attribute. As you say it is database specific and in a large enterprise system built over 20 odd years who knows what else is lurking around...

You may also solve your problem as follows (if you have nothing to do in the week end):
1. Delete the DDS desrcibed logial files with select/omit clauses
2. Create an SQL Index with the key fields specified in the DDS desrcibed logical files.
3. Recreate your DDS described logical files.

In this way the DDS described logical files can share access path with the SQL indexes (and because an SQL index has a default page size 64, while the default for DDS described logical files is 8K, the DDS described logical file gets also 64K page size which is also beneficial).

In this way you'll end up with the same number of access path, but the SQE Query Optimizer can use the SQL index, while you can access your DDS desrcibed logical files with native I/O.
To get the SQE to work you still need to set IGNORE_DERIVED_INDEX to *YES!

BTW for test szenairos you can copy the QAQQINI-File with CRTDUPOBJ in any library you want. To activate this QAQQINI in your job, you can set it with the CL-Command CHGQRYA.

If you won't delete the DDS described logical files first, you'll end up with the double number of access paths (and each access path must be updated as soon as a row in the underlying table or physical file will be changed. That means the more access path exist over a physical file (or SQL table) the worse get the performance.

Birgitta