Hi,

The following query is used for reporting and I would like to speed up processing time. Tried already with functions but got same results (in time).

If I could calculate part 2 and 3 from the result from 1 it would be improve the speed I think.

Any suggestions how to optimize this?


SELECT INV.InvoiceID AS Factuurnummer, INV.InvoiceDate AS Factuurdatum, CU.CustomerID AS Klantnummer, LEFT(CU.customername,50) AS Klantnaam,
- 1 -
(SELECT IF(PR.IsProductGroup=TRUE THEN (
SELECT SUM(SELECT SUM(Price) FROM CustomerProductGroupProducts
INNER JOIN Products PR1
ON Products.ProductID=CustomerProductGroupProducts.ProductID
WHERE CustomerProductGroupProducts.ProductGroupID=InvoiceLines.ProductID AND Products.SectionID=8)
FROM InvoiceLines
WHERE InvoiceLines.Invoiceid=INVLS.InvoiceID
GROUP BY Invoicelines.InvoiceID
) ELSE (Price*Quantity))
FROM InvoiceLines INVLS
INNER JOIN Products PR
ON PR.ProductID=INVLS.ProductID
WHERE INVLS.InvoiceID=INV.InvoiceID
GROUP BY INVLS.InvoiceID)
AS "Bedrag_exclusief_BTW",
- 2 -
(SELECT IF(PR.IsProductGroup=TRUE THEN (
SELECT SUM(SELECT SUM(Price) FROM CustomerProductGroupProducts
INNER JOIN Products PR1
ON Products.ProductID=CustomerProductGroupProducts.ProductID
WHERE CustomerProductGroupProducts.ProductGroupID=InvoiceLines.ProductID AND Products.SectionID=8)
FROM InvoiceLines
WHERE InvoiceLines.Invoiceid=INVLS.InvoiceID
GROUP BY Invoicelines.InvoiceID
) ELSE (Price*Quantity))
FROM InvoiceLines INVLS
INNER JOIN Products PR
ON PR.ProductID=INVLS.ProductID
WHERE INVLS.InvoiceID=INV.InvoiceID
GROUP BY INVLS.InvoiceID) /100 * 21
AS "Bedrag_aan_BTW",
- 3 -
(SELECT IF(PR.IsProductGroup=TRUE THEN (
SELECT SUM(SELECT SUM(Price) FROM CustomerProductGroupProducts
INNER JOIN Products PR1
ON Products.ProductID=CustomerProductGroupProducts.ProductID
WHERE CustomerProductGroupProducts.ProductGroupID=InvoiceLines.ProductID AND Products.SectionID=8)
FROM InvoiceLines
WHERE InvoiceLines.Invoiceid=INVLS.InvoiceID
GROUP BY Invoicelines.InvoiceID
) ELSE (Price*Quantity))
FROM InvoiceLines INVLS
INNER JOIN Products PR
ON PR.ProductID=INVLS.ProductID
WHERE INVLS.InvoiceID=INV.InvoiceID
GROUP BY INVLS.InvoiceID) /100*(100+21)
AS "Bedrag_inclusief_BTW"
--
FROM Invoices INV
INNER JOIN Customers CU
ON CU.CustomerID=INV.CustomerID
WHERE ((CAST(SUBSTRING(CAST(INV.InvoiceDate AS VARCHAR),1,10) AS DATE) >= CAST('2019-01-01' AS DATE) AND
CAST(SUBSTRING(CAST(INV.InvoiceDate AS VARCHAR),1,10) AS DATE) <= CAST('2019-01-21' AS DATE)))

Regards,

Mike

Mike,

It's impossible to optimize a query when we don't know the structure of the tables and their indexes.
Not knowing how they are, I can still say that the WHERE clauses don't look good at all.
Those CAST and SUBSTRING calls don't look good, but I have to know the tables and indexes to be sure.

--
Fernando Dias
[Team Elevate]

Hi Fernando,

I have decided to write another query and this gives a much better result.

Thanks for your comments!

Regards,

Mike

Mike


Glad you've found a better solution. I support Fernando about needing more information to analyse things. There is a process that you should adopt for future occasions.

1. Test the whole procedure - if its fast enough stop
2. Break the query into individual sql statements that can be sensibly reported on by EDBManager - make sure each part is optimised - mainly get indices that are needed in place
3. Try and avoid subselects and use JOINs where possible
4. Think about producing temporary tables rather than staying with a "single" query
5. As Fernando says the CASTs and SUBSTRs in the WHERE clause don't look good. If you use parameters that can all be done outside the query

Something that can be difficult to estimate is when an index scan is used and when a row scan and when a calculation (or CAST etc) has to be carried out for each row. Even when individual chunks of the sql are optimised putting them together can screw it all.

Roy Lambert

Hi Roy,

Thanks for the tips.

Regards,

Mike

Mike,

I would second Roy's statement about breaking things up *first*, and then assembling them into something larger once you have the individual SELECT statements working in an optimized fashion (EDB query execution plans will be very useful at this stage).

Tim Young
Elevate Software
www.elevatesoft.com

Mike


I did miss one thing which wouldn't apply here - if you've asked for a sensitive result set check to see if you actually got one and haven't introduced something which makes it insensitive.

Roy Lambert