MySQL Forums
Forum List  »  Stored Procedures

Query times out when I add extra Where Condition
Posted by: John Noble
Date: January 23, 2020 04:42AM

I have a query in a stored Proc.

-- Payments WIth Balances
SELECT salesledger.customerID as customerID,
salesledger.countryID as countryID,
salesledger.invoiceDate as InvoiceDate,
salesledger.dueDate as dueDate,
salesledger.invNum as invNum,
if (p_sterling = true, "GBP", salesledger.invoiceCurrency) as invoiceCurrency,
salesledger.CCmanagerID as CCmanagerID,
if (p_sterling = true, round(salesledger.payable / salesledger.conversionRate, 2), salesledger.payable) as payable,
salesledger.paidToDate as paidToDate,
if (p_sterling = true, round(salesledger.net / salesledger.conversionRate, 2), salesledger.net) as net,
if (p_sterling = true, round(salesledger.vat / salesledger.conversionRate, 2), salesledger.vat) as vat,
if (p_sterling = true, ifnull(round((salesledger.payable + sum(receipts.allocationAmount)) / salesledger.conversionRate, 2), round(salesledger.payable / salesledger.conversionRate, 2)),
ifnull(salesledger.payable + sum(receipts.allocationAmount) , salesledger.payable)) as balance,
salesLedger.freshFrozen as freshFrozen,
customers.name AS cname,
customers.telephone as ctelephone,
ifnull(customers.groupID, "") as cgroupID,
ifnull(groups.name, "") as groupName,
ifnull(users.name, "") as username,
countries.name as countryName,
p_date as selectedDate
FROM salesledger
LEFT JOIN receipts ON
salesledger.paymentNo = receipts.paymentNo
LEFT JOIN customers ON
salesledger.customerID = customers.customerID
LEFT JOIN groups ON
customers.groupID = groups.ID
LEFT JOIN users ON
salesledger.CCmanagerID = users.userID
LEFT JOIN countries ON
salesledger.countryID = countries.countryID
WHERE
salesledger.docType = "PAY"
and salesledger.invoiceDate <= p_date
and (salesledger.paidInFullDate = '0001-01-01' or salesledger.paidInFullDate > p_date)
and salesLedger.freshFrozen <> p_freshFrozen
GROUP BY
customerID, invNum, invoiceDate, payable


This works fine and is reasonably quick.

When I add the condition 'and receipts.invoiceDate <= p_date' to the Where statement the it times out. I have even added an index on receipts.invoiceDate but it has made no difference.

Any ideas on how to solve this ?

John

Options: ReplyQuote


Subject
Views
Written By
Posted
Query times out when I add extra Where Condition
1006
January 23, 2020 04:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.