The following query is fast (< 2 seconds):

SELECT
 ProductsID
FROM StockCounts
 WHERE DateDone =
 (SELECT Max(DateDone) FROM StockCounts)

but this is terribly slow (>100 seconds ... not sure how much more!)

SELECT
 ProductsID
FROM StockCounts
 WHERE DateDone = DateLastStockCount()

The FUNCTION DateLastStockCount() only does this:

CREATE FUNCTION "DateLastStockCount" ()
RETURNS DATE
BEGIN
 DECLARE Crsr CURSOR FOR Stmt;
 DECLARE Result DATE;
PREPARE Stmt FROM
' SELECT Max(DateDone) as MD
  FROM StockCounts ';
OPEN Crsr;
FETCH FIRST FROM Crsr('MD') INTO Result;
RETURN Result;

I would have expected the 2 versions to run at more or less the same speed. I guess that the Function is re-running for every instance of the WHERE, rather than being cached.

Is there any way around this?

(EDB 2.17b2)

On 6/8/2014 4:08 PM, Adam Brett wrote:
> The following query is fast (< 2 seconds):
> I would have expected the 2 versions to run at more or less the same speed. I guess that the Function is re-running for every instance of the WHERE, rather than being cached.

What does execution plan say ?

Since the function does not take any params i'd say EDB is not smart
enough to optimize it for the 2nd case and does runs it for every row
but it is able to optimize the 1st case with the max. Might be
worthwhile opening incident report and having Tim take a look.

Raul

Adam


I think your assumption is correct. The parser can determine that (SELECT Max(DateDone) FROM StockCounts) only need to be run once and its value substituted in the query whereas it has no idea of the internals of DateLastStockCount() so it has to run it on a per row basis.

Until such time as Tim has a flag DOTHISONCEATTHESTARTOFTHEQUERY, or he strips the sql fom the function and inlines it to the query I can't see things changing.

If you have an index on DateDone I'd guess that the execution plan would show an index scan for the first query and a row scan for the second.

Roy Lambert

Right on both Roy

I guess the behaviour do make sense. A function could give different results at different moments of a SELECT so I suppose it should be re-run.

I would actually prefer the default to be that such a simple function returns 1 result and is not re-run, otherwise result data might actually be from different periods.

No doubt Tim will inform us that this is "ISO Standard Behaviour" ...

Adam


>No doubt Tim will inform us that this is "ISO Standard Behaviour" ...

Much as I like to blame those wonderful people that make up the sql standards committee I don't think I really can in this case

Here's a nasty suggestion: you could subclass the query component, parse the sql before its run and obtain values for any of your functions that you know produce a static result and plonk the result into the functions place, or not so nasty move over to using scripts only.

Hmmm. I wonder how Tim would take to the suggestion of a new function type - FixedFunction almost identical to normal functions just that they are run once before the query executes and the result substituted for the function?

Roy Lambert

Roy

My solution was to put the whole thing in a stored procedure that returns a cursor.

Retrieve the MaxDate into a local var in the procedure, then pass this as a param into a Statement.

Should have thought of it before. I'm only now getting used to using Stored Procedures to return data. It is a powerful method as you have all the options of scripts within a mechanism that acts like a query.

I still think (in the context of my original thread) that the function should only return a single value though, rather than 1 per returned row ...

Adam