Hi Shinelal!

We need a lot more information to properly help you. First, you do not
say which table all fields belong to. Rewrite the query aliasing every
field in your join and where clauses and then report back your new
select clause and the plan the optimizer suggests. Tell us about the
fields in the indexes of the plan, including how close to unique every
field in the index is (a field with only two separate values would be
hopeless to index, whereas a field for which every record had different
values would often (not always) be brilliant.

Though it is possible to see that certain things aren't ideal in your
query. Due to your OR (the 2=0 should be redundant, I can't see how it
can use an index anyway), the outermost table (PRODUCTMASTER) will never
use an index and your query has to look up every individual record in
this table. Moreover, you use LEFT [outer] JOIN rather than a simple
[inner] JOIN. If there may be records for which there are no match in
the LEFT JOINed tables, then you may have to use LEFT JOIN, but if there
are values for every row, then remove LEFT OUTER.

In general, being that the query is asking for 'either this specific
match or no match whatsoever', I think a stored procedure is the only
way to produce decent performance ('either use an index or go through
every record to ascertain there is no match', doesn't produce a decent
plan), but I'll have a guess at what you may want to try (guessing which
fields belongs to which table, that you've used LEFT JOIN by accident
and that there is maximum one row in stockmaster for each combination of
productcode, outletcode and branchcode):

SELECT P.PRODUCTCODE
, P.PRODUCTNAME
, P.PRODUCTCOSTLOCALCURRENCY
, P.PRODUCTCOSTORGCURRENCY
, P.PRODUCTISBN
, P.PRODUCTBARCODE
, P.PRODUCTSTOCKUPDATE
, C.CURRENCYNAME
, P.CURRENCYCODE
, T.TAXDESCRIPTION
, T.TAXPERCENTAGE
, P.PRODUCTPUBLISHINGYEAR
, S.SUPPLIERNAME
, C.CURRENCYCONVERTRATE
, C.CURRENCYLOCALASREF
FROM PRODUCTMASTER P
JOIN CURRENCYMASTER C ON C.CURRENCYCODE = P.CURRENCYCODE
JOIN TAXMASTER T ON T.TAXCODE = P.TAXCODE
JOIN SUPPLIERMASTER S ON S.SUPPLIERCODE=P.PUBLISHERCODE
LEFT JOIN STOCKMASTER SM ON SM.PRODUCTCODE = P.PRODUCTCODE
WHERE P.PRODUCTSTOCKUPDATE = 'Y'
AND ((SM.OUTLETCODE=?OUTLET
AND SM.BRANCHCODE=?BRANCH)
OR SM.PRODUCTCODE IS NULL)

The left join means that stockmaster has to be the last table in your
plan, and then I find it obvious that whichever table the optimizer
chooses to put first in its plan has to be accessed using a NATURAL
plan. If your tables are big (millions of rows), then this will of
course take some time.

HTH,
Set


flashjobs wrote:

> Hello all
>
> I have a problem that the query is very slow. Can anybody help?
>
> Thanks
>
> Shinelal
>
> SELECT PRODUCTCODE
> , PRODUCTNAME
> , PRODUCTCOSTLOCALCURRENCY
> , PRODUCTCOSTORGCURRENCY
> , PRODUCTISBN
> , PRODUCTBARCODE
> , PRODUCTSTOCKUPDATE
> , CURRENCYNAME
> , P.CURRENCYCODE
> , TAXDESCRIPTION
> , TAXPERCENTAGE
> , PRODUCTPUBLISHINGYEAR
> , SUPPLIERNAME
> , CURRENCYCONVERTRATE
> , CURRENCYLOCALASREF
> FROM (((PRODUCTMASTER P
> LEFT OUTER JOIN CURRENCYMASTER C ON C.CURRENCYCODE = P.CURRENCYCODE)
> LEFT OUTER JOIN TAXMASTER T ON T.TAXCODE = P.TAXCODE)
> LEFT OUTER JOIN SUPPLIERMASTER S ON S.SUPPLIERCODE=P.PUBLISHERCODE)
> WHERE PRODUCTSTOCKUPDATE = 'Y' AND
> ((PRODUCTCODE IN (SELECT PRODUCTCODE FROM STOCKMASTER WHERE
> OUTLETCODE=?OUTLET
> AND BRANCHCODE=?BRANCH )or 2=0) OR
> PRODUCTCODE NOT IN(SELECT PRODUCTCODE FROM STOCKMASTER))