2 questions: Exists and "Index Hint"

[ermagarden]

I have 2 questions:

1. Is EXISTS faster than Inner Join?
I have a query that's slow and i'm trying to redo it.
Select .... from TB1 Inner Join TB2 ON f1 = f2
inner join libray.TB3 ON f2 = f3
TB3 is in another library. I just want to see if rows are there in TB3. I dont want to necessarily match rows to get data back. I want to execute the query when TB3 as rows that match f2 field in TB2.

2. SQL Server has "index hint" where I can force my query to use a particular index. My query sometimes uses an index I created, sometimes it uses a Logical that my manager created. I want it to use my index.
I see this when I turn on iSeries Navigator's query plan to see the execution of the query.

[arrow483]

Re: 2 questions: Exists and "Index Hint"

1. Is EXISTS faster than Inner Join?

I thnk that would depend on local database specifics.

2. SQL Server has "index hint"

You don't have "hints" on the iseries, and you can't "Force" it to use an index it doesn't see as the best. However, there are several things you can do to improve performance. Three of the biggest are:
1. Don't write the SQL over a LF.
2. Make sure the QAQQINI file has ignore derived index value set on.
3. Run the SQL thru Navigator's Visual Explain and see what it suggests for indexes, then build these indexes.
The idea is to get the query to use the SQE engine over the CQE engine.

[ermagarden]

Re: 2 questions: Exists and "Index Hint"

I'll look into #2.

Not sure what u mean by #1. I'm not an AS400-er. My manager and other developers have already created the LF. I created a SQL Index to see how my query behaves. Looking at the Visual Explain, I saw that couple of times... the query used my SQL index and other times, it used an LF.

[arrow483]

Re: 2 questions: Exists and "Index Hint"

A LF is like an indexed SQL view, except that the index is kept up to date dynamically. The SQL optimiizer will use the LF if the index is what it needs. But you want to write the query to select from the PF (maybe you are already doing that). The optimizer will determine the "best" access method. The QAQQINI file must be in library QUSRSYS. If ANY LF is biult over the PF you are querying, and that LF has a select/omit statement, you will be forced to use the older CQE engine, which runs MUCH slower, unless the QAQQINI in library QUSRSYS has the proper IGNORE statment.

[ermagarden]

Re: 2 questions: Exists and "Index Hint"

But you want to write the query to select from the PF (maybe you are already doing that). Yes, I select from the physical file.

If ANY LF is biult over the PF you are querying, and that LF has a select/omit statement each file/table has lots of LFs. The two tables/files I'm selecting from each have maybe 8 or 9 LFs. All selects.

Vendor has done the same in SQl Server and I know this design is not correct in sql server and my query is not fast in sql server either.

I dont think I can do much about the LFs. We're giving my .net app to clients and I dont want to play around with their AS400 especially that I'm not that familiar with it.

Your explanation makes sense.

[arrow483]

Re: 2 questions: Exists and "Index Hint"

The two tables/files I'm selecting from each have maybe 8 or 9 LFs. All selects.

So what happens is this: The optimizer passes control to the SQE engine. It looks at all the LF's, and find one with a select/omit. SQE rejects the query (it can't use a select/omit LF), and the optimizer then resends your query back to the older CQE engine. CQE looks at LF's again, and tries to optimize. CQE doesn't always see all the indexes, so even a perfect one may not get used.

Now, if you put the IGNORE option into QAQQINI file, the SQE simply ignores any LF's with select/omit, and processes based on remaining indexes, LF's, statistics, etc. Does it help? Yeah - ther is about a 15% overhead wit the SQE/reject/CQE cycle, but the real difference is SQE is a lot smarter.

Example: I had a 17 million record file that created a DTS download to SQL server. Took 60 - 90 minutes. After I put the IGNORE in, it takes about 12 minues. (It reads al 17 million records, but doesn't upload all on them).

Example: I had a 45 file join that took 52 minutes to query. Added IGNORE and the query now runs in under 2 minutes.

Do this:
1. Check if QAQQINI exists in QUSRSYS. If not use CRTDUPOBJ QSYS/QAQQINI QUSRSYS/QAQQINI with data, triggers, contraints all *YES.

2. Check if this fie contains the IGNORE_DERIVED_INDEX statemnt. If so, make sure the "value" is *YES. If not, add it.

While "your mileage may vary" this nearly always helps, and I have never seen a case where I don't want it. Its minimum interference with the AS400, and can be easily reversed, if you decide you don't want it.

[ermagarden]

Re: 2 questions: Exists and "Index Hint"

Thanks, i will try this on Monday.

Been at this query for couple of months now. Hope this speeds it up.

Will post back.

[ermagarden]

Re: 2 questions: Exists and "Index Hint"

This is what I did:

Thru green screen, I got to QAQQINI. I displayed file attributes, didnt see Ignore_Derived_Index.

I found this article which explains what you've said to see how I can add that property:

http://www.centerfieldtechnology.com/publications/archive/December%202005%20Newsletter.pdf

It's an insert statement. Tried to insert it, I got this error:
Character conversion between CCSID 65535 and CCSID 13488 not valid.

Googled that error msg and wasnt much help.

-----Next, I did this: select * from qusrsYS/QAQQINI

I got one row back that has :

Parameter

IGNORE_DERIVED_INDEX
******** End of data ********

BUT, i dont know if this value is YES or NO. How can I check that. It just displays the name.

[BenThurley]

Re: 2 questions: Exists and "Index Hint"

There is some more detail in this thread

http://www.code400.com/forum/showthread.php?t=4142

[ermagarden]

Re: 2 questions: Exists and "Index Hint"

Silly me, had to shift to the right to see the value and it's *YES.

[Mercury]

Re: 2 questions: Exists and "Index Hint"

I've been trapped too...