Join BSVW strategies for great big tables in a UBE

I have a custom table w/9,076+K rows in it.
I have it joined to F4211 which has 578+K rows.
I have the custom table as the "left" side F4211 is the "right" side.
They are joined by KCOO, DOCO, DCTO.
The custom table has addl keys beyond this.

Data selection in the UBE is a series of
"where ((F55COL = value) and (F4211 MCU = value1))
OR (F55COL = value) and (F4211 MCU = value2))...
These are set in the INIT er for the section using the bsvw.

When the ube reaches the point of processing this sql
it, understandably, grinds to a near stop.

My FIRST fix will be to clean up the custom table,
reducing the number of rows to as few as possible.
Even after I do this, this table will be large.

Is there anything I can do either in the join,
or when I'm setting the data selection to improve this sql?

TYVM

Gene

What type of database server are you running the request on? These are not "BIG" numbers for most of the servers I have seen around. I have seen places with >1,000,000 transactions a month in the F4211 alone.

Let the database server give you hints about indexes.

One way to do it is to run the report in debug mode to extract the SELECT statement. You can use this statement as-is in a tool like SQL Query Analyser if you are running NT/SQL. The Query Analyser has an option "Query/Show execution plan".

If you are on the AS/400, let me know. There is a way to do it as well.

Gene,

Make sure you have matching indexes in your custom table - you don't
want the select to force a table scan on all matching rows.

(db)

Gene,

Make sure you have matching indexes in your custom table - you don't
want the select to force a table scan on all matching rows.

(db)

Sebastion & Daniel - thanks both for replying.

I asked the CNCer where the ube will run, and it's being moved from the 400
to an NT server, (for now at least).

I asked about query analyzer, and he suggested
400 green screen sql statements
which I can do (once I come up for air).

As far as "matching indexes", the F55 has 3 indexes. They are:

A: KCOO, DOCO, DCTO, AN8, ITM, TXID, LDLN
2: AN8, TXID, LDLN
III: ITM, TXID, LDLN

(The custom table is a "text notes" table used
for orders, ab#s, and items). What am I matching to?

As this UBE is looking for ORDERS ONLY, would the SQL
improve if I added "and AN8=0 and ITM=0"?

Thanks again

Gene

Before you execute the SQL statements on the 400, make the following call:

strsrvjob
strdbg

When issuing the strdbg (start debug), hit F4 and make sure UPDATE PRODUCTION TABLES is activated.

Then go in SQL and execute the statement.

Once this is done, issue

dspjob + Display job log

The 400 query optimiser should have left entries in the log. If you require any additional index, THE LOG WILL TELL YOU.

When done, issue:
enddbg (end debug)
endsrvjob (End service job)

Merci, gracias, gracii, dzienkuje, THANKS!
I will give it a whirl.