I have a string of queries all of which end with the following WHERE

WHERE DateDone IN
(
 SELECT MAX(DateDone) FROM Dispatches
)

... There is an index on DateDone in Dispatches, so this is reasonably quick.

Is there any advantage in replacing the above with a call to a FUNCTION in terms of speed / efficiency, i.e. is Elevate clever enough not to re-run the FUNCTION, or will both methods be identical?

Adam

>I have a string of queries all of which end with the following WHERE
>
>WHERE DateDone IN
>(
> SELECT MAX(DateDone) FROM Dispatches
>)
>
>.. There is an index on DateDone in Dispatches, so this is reasonably quick.
>
>Is there any advantage in replacing the above with a call to a FUNCTION in terms of speed / efficiency, i.e. is Elevate clever enough not to re-run the FUNCTION, or will both methods be identical?


My guess is the function would be slower. Why not try it and see? However you could also write the above as a JOIN (I think) which would be faster, or supply the data to be tested as a parameter to the query which would be faster still.

From what Tim's told me where ever possible JOINs for selection sub selects for data (ie as a column in the SELECT clause)

Roy Lambert [Team Elevate]

Adam,

<< I have a string of queries all of which end with the following WHERE

WHERE DateDone IN
(
 SELECT MAX(DateDone) FROM Dispatches
)

.. There is an index on DateDone in Dispatches, so this is reasonably
quick.

Is there any advantage in replacing the above with a call to a FUNCTION in
terms of speed / efficiency, i.e. is Elevate clever enough not to re-run the
FUNCTION, or will both methods be identical? >>

If you do the following in the function:

CREATE FUNCTION MaxDispatchDate()
RETURNS DATE
BEGIN
  DECLARE ResultStmt STATEMENT;
  DECLARE Result DATE;

  PREPARE ResultStmt FROM 'SELECT MAX(DateDone) INTO ? FROM Dispatches';
  EXECUTE ResultStmt USING Result;

  RETURN Result;
END

then EDB will keep the internal ResultStmt prepared as long as any other SQL
statements referencing the MaxDispatchDate function are prepared.  So, it
will execute the ResultStmt every time, but not re-prepare it, which is
exactly what you want in case the data changes in the Dispatches table.

Of course, with EDB you can also just use this:

WHERE DateDone=(SELECT MAX(DateDone) FROM Dispatches)

so you don't really need an IN clause anymore.

--
Tim Young
Elevate Software
www.elevatesoft.com

Dear Both,

Thanks for this. Sorry if I seem a bit of a leach at the moment Roy ... you are right that I _could_ do some extra testing myself before I post to the NGs (... the problem is that the answers I get back are so good & point out features of EDB (like not needing IN) which I really haven't got my head around yet, so it is useful to ask some pretty basic questions.

Hopefully I'll start answering some soon, so its a bit less 1 way.

Adam


No criticism was intended.

I have no problem with people asking questions - I do enough of it, and I have asked about which is more efficient. The problem is that all to often efficiency / speed depends on the data so you end up having to test for your circumstances anyway. The other problem is that sometimes some approaches work fine on "my" data but just won't work on on "your" data.

Keep on asking!

Roy Lambert [Team Elevate]