MySQL Forums
Forum List  »  Stored Procedures

Re: Query times out when I add extra Where Condition
Posted by: John Noble
Date: January 24, 2020 05:53AM

Hi Peter,

Sorry I didn't understand what you meant by bbCodes. I think I do now....


I have extended the timeout period from 30 to 120 seconds and added an index.

I tried changing the some of the joins to INNER instead of LEFT but the query still took 92.618 seconds.

But when I set the JOINS back to LEFT, it only takes 8.440 seconds. Thats probably the reason why the JOINS were set to LEFT.

Using mysql 5.5
InnoBufferPoolSize = 107M

Here is the Explain.

	
'1', 'PRIMARY', '<derived2>', 'ALL', NULL, NULL, NULL, NULL, '3192', 'Using filesort'
'2', 'DERIVED', 'salesledger', 'ALL', NULL, NULL, NULL, NULL, '409700', 'Using where; Using temporary; Using filesort'
'2', 'DERIVED', 'receipts', 'ref', 'invNum', 'invNum', '4', 'sst.salesledger.invNum', '1', ''
'2', 'DERIVED', 'customers', 'ref', 'customerID', 'customerID', '9', 'sst.salesledger.customerID', '1', ''
'2', 'DERIVED', 'groups', 'ALL', NULL, NULL, NULL, NULL, '16', ''
'2', 'DERIVED', 'users', 'ALL', NULL, NULL, NULL, NULL, '56', ''
'2', 'DERIVED', 'countries', 'ALL', NULL, NULL, NULL, NULL, '41', ''
'3', 'UNION', 'salesledger', 'ALL', NULL, NULL, NULL, NULL, '409700', 'Using where; Using temporary; Using filesort'
'3', 'UNION', 'customers', 'ref', 'customerID', 'customerID', '9', 'sst.salesledger.customerID', '1', ''
'3', 'UNION', 'groups', 'ALL', NULL, NULL, NULL, NULL, '16', ''
'3', 'UNION', 'users', 'ALL', NULL, NULL, NULL, NULL, '56', ''
'3', 'UNION', 'countries', 'ALL', NULL, NULL, NULL, NULL, '41', ''
'3', 'UNION', 'receipts', 'ALL', 'invoiceDate', NULL, NULL, NULL, '309529', 'Using where; Using join buffer'
'4', 'UNION', 'salesledger', 'ref', 'invNum', 'invNum', '4', '', '4244', 'Using where; Using temporary; Using filesort'
'4', 'UNION', 'customers', 'ref', 'customerID', 'customerID', '9', 'sst.salesledger.customerID', '1', ''
'4', 'UNION', 'groups', 'ALL', NULL, NULL, NULL, NULL, '16', ''
'4', 'UNION', 'users', 'ALL', NULL, NULL, NULL, NULL, '56', ''
'4', 'UNION', 'countries', 'ALL', NULL, NULL, NULL, NULL, '41', ''
'4', 'UNION', 'receipts', 'ALL', 'invoiceDate,receiptDate', NULL, NULL, NULL, '309529', 'Using where; Using join buffer'
NULL, 'UNION RESULT', '<union2,3,4>', 'ALL', NULL, NULL, NULL, NULL, NULL, ''


Here is the FULL sproc I am picking up 3 sets of data and merging them ...

CREATE DEFINER=`root`@`%` PROCEDURE `usp_ADCustomerIDAll`( 
   IN p_date date, 
   IN p_freshFrozen varchar(1), 
   IN p_sterling bool 
) 
BEGIN 

SELECT * FROM 
( 
-- Invoices and Credits 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, round((salesledger.payable - ifnull(sum(CASE WHEN receiptDate <= p_date THEN allocationAmount ELSE 0 END), 0)) / salesLedger.conversionRate, 2), 
salesledger.payable - ifnull(sum(CASE WHEN receiptDate <= p_date THEN allocationAmount ELSE 0 END), 0)) 
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.invnum = receipts.invnum 
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 = "Inv" or salesledger.docType = "Crd") 
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 



UNION ALL 

-- 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 receipts.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 



UNION ALL 
-- UNallocated 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, round(salesledger.payable - ifnull(SUM( CASE WHEN Masterunallocated = "F" THEN allocationAmount ELSE 0 END) / salesLedger.conversionRate, salesledger.payable / salesLedger.conversionRate), 2), 
salesledger.payable - ifnull(SUM( CASE WHEN Masterunallocated = "F" THEN allocationAmount ELSE 0 END), 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 OUTER JOIN receipts ON 
      salesledger.unallocatedNo = receipts.unallocatedNo 
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.unallocatedNo <> 0 
      and salesledger.invNum = -99999 
      and salesledger.invoiceDate <= p_date 
      and receipts.invoiceDate <= p_date 
      and receipts.receiptDate <= p_date 
      and (salesledger.paidInFullDate = '0001-01-01' or salesledger.paidInFullDate > p_date) 
      and salesLedger.freshFrozen <> p_freshFrozen 
GROUP BY 
      customerID, invNum, invoiceDate, payable 

) a 

ORDER BY 
      countryID, customerID, invNum; 
END

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Query times out when I add extra Where Condition
361
January 24, 2020 05:53AM


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.