I have the following SQL which runs in less than 0.5 seconds:

SELECT
  ProductsID as ID
  FROM StockCounts
  WHERE DateDone = (SELECT MAX(DateDone) FROM StockCounts)

I have to retrieve the "MAX(DateDone) FROM StockCounts" pretty regularly, so I wrote a FUNCTION to return it:

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

SO I can replace the SQL Above with:

SELECT
  ProductsID as ID
  FROM StockCounts
  WHERE DateDone = SC_MaxDate()

--

However using the FUNCTION the query runs endlessly without returning a result.

The FUNCTION works when executed separately, and the SQL Prepares ...

What am I doing wrong?

Adam,

I think you will find that it is 'working'.  If you add a log message before the OPEN Crsr, you will see that the function is executed for each row.  So it is going to be slow.

SET LOG MESSAGE TO CAST(CURRENT_TIMESTAMP() AS VARCHAR(30));
OPEN Crsr;            
FETCH FIRST FROM Crsr('DateDone') INTO Result;
RETURN Result;
END

You may be able to speed up your original SQL by creating an index on the column DateDone so it can easily find the MAX(DateDone)

Richard

Adam,

As Richard already said, the function is working and you will notice a dramatic change in speed if you create an index on DataDone.

The other way to increase the speed, with or without the index, is to do it with 2 steps:
1. Get the value of MAX(DateDone) by calling the function SC_MaxDate() once
2. Use that value as a constant or parameter for your main query SELECT ProductsID as ID FROM StockCounts WHERE DateDone = ?

Another thing that you can improve is the function itself:

CREATE FUNCTION "SC_MaxDate" ()
RETURNS DATE
BEGIN
  DECLARE Result DATE;
  EXECUTE IMMEDIATE 'SELECT MAX(DateDone) INTO Result FROM StockCounts';
   RETURN Result;
END

However, this wont give you more speed, just a shorter function.

--
Fernando Dias
[Team Elevate]

Adam


Out of interest can you post the execution plan for both instances?

Just to ramble on a bit more and extend what Richard and Fernando said - sub selects and user functions can be disastrous.

I'm guessing that ElevateDB can work out that the sub select you apply only needs to be done once so can optimise the query. With a UDF ElevateDB knows nothing about what's going on inside the UDF and has to run it for each row.

Properly used they are brilliant. In general terms I've come to the conclusion that their proper place is in extracting data for a given cell. Sub selects may have to be used (eg where the old join syntax can't be used for a DELETE operation), but where you can use JOIN.

A little example

SELECT _ID, _Name, _xGlyph FROM QSC
WHERE _ID IN (SELECT _fkQSCdetail FROM QSCxRef WHERE _fkQSCmaster = :xref)

This worked fine when I was testing on very small datasets (say c10 rows) but when I moved to 200k rows (I left an extra 0 in my fill with garbage routine it was taking c8 seconds

This

SELECT _ID, _Name, _xGlyph
FROM QSC
JOIN QSCxref ON (_ID = _fkQSCdetail) AND (_fkQSCmaster = :xref)

with the same indices was pretty much instantaneous

The execution plan gave the clue (I've reduced my test data back down to 20k rows):

Filtering
---------

The following filter condition was applied to the QSC table:

"_ID" IN (SELECT ALL "_fkQSCdetail" AS "_fkQSCdetail" FROM "QSCxRef" WHERE
"_fkQSCmaster" = 0

Index scan (QSCxref.PK): 0 keys, 8KB estimated cost ORDER BY "_fkQSCdetail")

Row scan (QSC): 20000 rows, 9MB estimated cost


When you see "Row scan" that's a clue that things can be speeded up.


Roy Lambert

Roy,

It's a PSM function

--
Fernando Dias
[Team Elevate]

Fernando

>It's a PSM function

I'm not sure what point you're trying to make here. I know the programming language is the inbuilt one rather than Delphi and its not an external function which is why I used the terms "user functions" and "UDF"

The point I was making is that when the SQL is executed all that can be done is go "hey I've got a function here which return a string - I'll run it and check for each row" whereas with the code in line it can go "hmmm I need that value - its not going to change as I run down the table I'll just get it this once"

Roy

Roy,

<<I'm not sure what point you're trying to make here. >>

Neither do I
You are right.

--
Fernando Dias
[Team Elevate]

Fernando

I am very pleased I had made a cuppa but hadn't started drinking it yet.

Roy Lambert

Roy,

Wooo, It's 37C here today... it has to be water or beer, never tea.
I don't know what I was thinking when I wrote that, really.

--
Fernando Dias

Roy

Wow.

I got an execution plan & you were right, there was no Index on DateDone.

Just adding this Index reduced the run-time for the function-based SQL from infinity to 0.016 seconds!

Thanks.