The following SQL has yet to complete and I have left it running for 3+ hours (so do not have a plan as yet). Can anyone please give me some
pointers as to how to optimise. There are indexes on ProductCode LocationNo  Field1 & JJWLocNo on all relevant tables. I have verified
ProductDay.

Product has 14500 Records
ProductDetail has 750,000 Records
ProductDay has 3.5million Records
CstPrc has 13500 Records
JJWLocation has 51 Records

Any pointers will be much appreciated.

Select D.JJWName, D1.Field1, D2.Description, Sum(D3.SalesQty) as Quantity, D4.RetailPrice1, Sum(D3.SalesValue) as TotVal, D1.Field2
From Product D2
JOIN ProductDetail D4 ON D2.ProductCode = D4.ProductCode
JOIN ProductDay D3 ON D4.ProductCode = D3.ProductCode AND D3.LocationNo = D4.LocationNo
JOIN JJWLocation D ON D3.LocationNo = D.JJWLocNo
JOIN CstPrc D1 ON D4.FreeText1 = D1.Field1
Where D1.Field1 LIKE 'I01%'
GROUP BY D.JJWName, D1.Field1, D2.Description, D4.RetailPrice1, D1.Field2
ORDER BY D.JJWName

Regards
Malcolm

Malcolm

Try adding NOJOINOPTIMIZE at the end of SQL statement and see if it helps.

Eduardo

Malcolm

You don't mention indices on these

JOIN CstPrc D1 ON D4.FreeText1 = D1.Field1
Where D1.Field1 LIKE 'I01%'
GROUP BY D.JJWName, D1.Field1, D2.Description, D4.RetailPrice1, D1.Field2
ORDER BY D.JJWName

also if you do have an index on D4.FreeText1 and D1.Field1 and they are case insensitive make sure to wrap them with UPPER.

I'd also suggest trying without the LIKE because I think that would be generating a row scan use an IN test for a few values just to see.


Roy Lambert [Team Elevate]

Malcolm,

<< The following SQL has yet to complete and I have left it running for 3+
hours (so do not have a plan as yet). Can anyone please give me some
pointers as to how to optimise. There are indexes on ProductCode LocationNo
Field1 & JJWLocNo on all relevant tables. I have verified ProductDay. >>

Do a reverse-engineer in DBSYS on the relevant tables, and then post the SQL
code here.  That will allow us to see what the situation is.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi All and may thanks for your responses.
I had missed an index on the FreeText1 field. I went back to basics and built up the SQL a bit at a time and now have it running in 8 to 10
minutes, which is a vast improvement.
With the record counts I gave are these timeings what you would expect?
I notice in the plan (below) that the The expression:

D3.ProductCode = D1.ProductCode AND D3.LocationNo = D1.LocationNo

is PARTIALLY-OPTIMIZED and is estimated to cost 32650 bytes per candidate row
What does this imply? can it be improved?

Many Thanks
Malcolm
================================================================================
SQL statement (Executed with 4.26 Build 3)
================================================================================

select
D2.JJWLOcNo,
D4.Field1,
D.Description,
Sum(D3.SalesQTY)as Quantity,
D1.RetailPrice1,
Sum(D3.SalesValue)As TotVal,
D4.Field2
FROM JJWLocation D2 INNER JOIN ProductDay D3 ON D2.JJWLocNo = D3.LocationNo
INNER JOIN Product D ON D3.ProductCode = D.ProductCode
INNER JOIN ProductDetail D1 ON D3.ProductCode = D1.ProductCode AND
D3.LocationNo = D1.LocationNo
INNER Join CstPrc D4 ON D1.FreeText1 = D4.Field1
WHERE

D.ProductCode LIKE 'I01%'
GROUP BY D2.JJWLocNo, D4.Field1, D.Description
ORDER BY JJWLocNo, D4.Field1
JOINOPTIMIZECOSTS

Tables Involved
---------------

JJWLocation (D2) table opened shared, has 51 rows
ProductDay (D3) table opened shared, has 3272361 rows
Product (D) table opened shared, has 14351 rows
ProductDetail (D1) table opened shared, has 731901 rows
CstPrc (D4) table opened shared, has 13472 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more grouped rows

Result set will be grouped by the following column(s) using a temporary index:

JJWLOcNo
Field1
Description

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

JJWLOcNo ASC
Field1 ASC

WHERE Clause Execution
----------------------

The expression:

D.ProductCode LIKE 'I01%'

is OPTIMIZED, covers 75 rows or index keys, costs 2100 bytes, and will be
applied to the Product table (D) before any joins

Join Ordering
-------------

The driver table is the JJWLocation table (D2)

The JJWLocation table (D2) is joined to the ProductDay table (D3) with the
INNER JOIN expression:

D2.JJWLocNo = D3.LocationNo

The ProductDay table (D3) is joined to the Product table (D) with the INNER
JOIN expression:

D3.ProductCode = D.ProductCode

The ProductDay table (D3) is joined to the ProductDetail table (D1) with the
INNER JOIN expression:

D3.ProductCode = D1.ProductCode AND D3.LocationNo = D1.LocationNo

The ProductDetail table (D1) is joined to the CstPrc table (D4) with the INNER
JOIN expression:

D1.FreeText1 = D4.Field1

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

The joins are already in optimal order and cannot be optimized any further

Join Execution
--------------

Costs ARE being taken into account when executing this join
Remove the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force
the optimizer to stop considering costs when optimizing this join

The expression:

D2.JJWLocNo = D3.LocationNo

is OPTIMIZED and is estimated to cost 1330137 bytes per candidate row

The expression:

D3.ProductCode = D.ProductCode

is OPTIMIZED and is estimated to cost 28 bytes per candidate row

The expression:

D3.ProductCode = D1.ProductCode AND D3.LocationNo = D1.LocationNo

is PARTIALLY-OPTIMIZED and is estimated to cost 32650 bytes per candidate row

The expression:

D1.FreeText1 = D4.Field1

is OPTIMIZED and is estimated to cost 0 bytes per candidate row

================================================================================
>>>>> 1598 rows affected in 634.516 seconds
================================================================================

Malcolm

Have you tested the SQL statement with NOJOINOPTIMIZE ?

Eduardo

Malcolm,

<< I had missed an index on the FreeText1 field. I went back to basics and
built up the SQL a bit at a time and now have it running in 8 to 10 minutes,
which is a vast improvement.
With the record counts I gave are these timeings what you would expect? >>

It's really hard to judge just by the record counts.  The organization of
the records in the table and all sorts of other issues can affect the
timings in various ways.

<< I notice in the plan (below) that the The expression:

D3.ProductCode = D1.ProductCode AND D3.LocationNo = D1.LocationNo

> is PARTIALLY-OPTIMIZED and is estimated to cost 32650 bytes per candidate
> row

What does this imply? can it be improved? >>

That could simply be from the JOINOPTIMIZECOSTS clause.  I would try the
query with and without that clause to see what the results are.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim and Many thanks again

I was just wondering if the timings were in the right ball park or were way out, As I said 8 to 10 Mins is a great improvement and I can live with it
but always looking to improve

Re the Partialy optmised. I tried with and without the JOINOPTMISEDCOSTS and come down in fovour of using it by about 1.5 - 2 minutes.

Appreciate all your imputs

Regards
Malcolm