query performance

[perry]

Hi All,
A customer is having some performance issue and Azure DB caught few expensive queries.

I have search across all stored procedures and custom plugin and none of them contains "HAVING SUM(IN_SOH.QuantityLeft) > 0"

It also doesn't look like a query from report to me.

Anyone has suggestion on which part of Jiwa uses below query?

Code: Select all

SELECT IN_SOH.InventoryID
FROM IN_SOH WITH (NOLOCK)
LEFT JOIN SO_Lines WITH (NOLOCK) ON SO_Lines.InventoryID = IN_SOH.InventoryID
LEFT JOIN SO_History WITH (NOLOCK) ON SO_History.InvoiceHistoryID = SO_Lines.InvoiceHistoryID
LEFT JOIN SO_Main WITH (NOLOCK) ON SO_Main.InvoiceID = SO_History.InvoiceID
WHERE (SO_Lines.InvoiceLineID IS NULL OR ((SO_Main.Status=1 OR SO_Main.Status=2) OR SO_History.HistoryNo <> SO_Main.CurrentHistoryNo))
AND (SO_Lines.InvoiceLineID IS NULL OR CreditNote = 0)
AND SO_Lines.InvoiceLineID IS NULL
GROUP BY IN_SOH.InventoryID
HAVING SUM(IN_SOH.QuantityLeft) > 0

[Mike.Sheen]

Anyone has suggestion on which part of Jiwa uses below query?

It's not the Jiwa application - I searched all the Jiwa source code for each of the following and got no results:

• "HAVING SUM(IN_SOH.QuantityLeft) > 0"
• "HAVING SUM("
• "GROUP BY IN_SOH.InventoryID"

If the query you show hasn't been beautified, then it's not a query from a Crystal Report, either - Crystal has a certain way of joining tables and it's not as pretty as what we see here.

Have you looked in triggers?

[DannyC]

Have you looked through all views?

Try this SQL in a query window

Code: Select all

SELECT OBJECT_NAME( object_id ), *
  FROM sys.sql_modules
WHERE [definition] LIKE '%HAVING SUM(IN_SOH.QuantityLeft) > 0%';

[perry]

Thanks both,
not in triggers nor views...

I may check with the web guy, it looks like getting list of item was never invoiced.