I have a StoredProc declared like this:

CREATE PROCEDURE "FF_ListFields" (IN "aFarmersID" INTEGER)
BEGIN
 DECLARE Crsr CURSOR WITH RETURN FOR Stmt;
PREPARE Stmt FROM
' SELECT
 FarmersID,
 COUNT(*) as FieldCount,
 CAST(LIST(CAST(YieldEstimate as VARCHAR)) as VARCHAR) as YieldEstimates,
 CAST(LIST(CAST(AcreageTotal as VARCHAR)) as VARCHAR) as FieldSizes
 FROM FarmFields WHERE FarmersID = ? ';
OPEN Crsr USING aFarmersID;
END

I want to write a select like this (or similar):

SELECT
 F.Name,
 FF.FieldCount,
 FF.YieldEstimates
FROM Farmers F,
           FF_ListFields(F.FarmersID) FF

I think it is impossible, i.e. I can only use a StoredProc result-set  within a StoredProc, not with reference to other database objects.

Is this the case?

Adam


Never having tried it I suspect that impossible is right. Did you try it and if so what result did you get?

Think of it in Delphi terms. You declare a procedure and expect to be able to access its internal variables outside of the procedure. It may be possible with a function - again I don't know never having tried it.

In my case I do have a number of scripts that produce temporary in-memory tables but those use CREATE TABLE so I can access it.

Whilst there may be a bit of a performance penalty you could use subselects

SELECT
 F.Name,
(SELECT COUNT(*) as FieldCount FROM Farmers WHERE Farmers.FarmersID = F.FarmersID) as FieldCount,
(SELECT CAST(LIST(CAST(YieldEstimate as VARCHAR)) as VARCHAR) FROM Farmers WHERE Farmers.FarmersID = F.FarmersID) as YieldEstimates
FROM Farmers F


Totally untested.

The other approach would be to use a function eg

SELECT
 F.Name,
 GetFieldCount(F.FarmersID) as FieldCount,
GetYieldEstimates(F.FarmersID) as YieldEstimates
FROM Farmers F


CREATE FUNCTION GetFieldCount((IN "aFarmersID" INTEGER,)
RETURNS VARCHAR COLLATE "ANSI_CI"
BEGIN
 DECLARE Crsr CURSOR WITH RETURN FOR Stmt;
DECLARE OUTPUT VARCHAR;
PREPARE Stmt FROM
' SELECT
 COUNT(*) as FieldCount
 FROM FarmFields WHERE FarmersID = ? ';
OPEN Crsr USING aFarmersID;
FETCH FROM Crsr ('FieldCount') INTO OUTPUT';
Close Crsr;
RETURN OUTPUT;
END

CREATE FUNCTION GetYieldEstimates((IN "aFarmersID" INTEGER,)
RETURNS CLOB
BEGIN
 DECLARE Crsr CURSOR WITH RETURN FOR Stmt;
DECLARE OUTPUT CLOB
PREPARE Stmt FROM
' SELECT
CAST(LIST(CAST(YieldEstimate as VARCHAR)) as VARCHAR) as YieldEstimates
 FROM FarmFields WHERE FarmersID = ? ';
OPEN Crsr USING aFarmersID;
FETCH FROM Crsr ('YieldEstimatest') INTO OUTPUT';
Close Crsr;
RETURN OUTPUT;
END


again totally untested.

Finally you may be able to use a view in conjunction with a JOIN on FarmersID. I'd need some data to play with to figure that one out - I'm hopeless at doing that in my head. I need to test to see if it will work. If it will I think it would be your best option.


Roy Lambert

Roy

I am definitely at the stage of owing you a number of beers. If you ever make it to London we will have to find a way of sorting this out.

I tried using a sub-select, also creating VIEWS, but the problem is that my Farmers table is massive (500k rows) and the FarmFields table is c 1.5m rows, so both these routes are a bit slow.

However the in-line sub select and FUNCTION can be fast, as it uses the Index on Farmer, so it returns in 0.016 seconds ...

I hadn't thought of doing it that way. Thanks.

BTW: I think in some other DB's it is possible to treat SPs a bit like VIEWS. I have heard people talking about it anyway.

Adam


<< If you ever make it to London>>

Shudder - my lungs have just about cleared out the black gunky stuff they acquired from going into London when I worked there - and that was over 20 years ago!

>
>I tried using a sub-select, also creating VIEWS, but the problem is that my Farmers table is massive (500k rows) and the FarmFields table is c 1.5m rows, so both these routes are a bit slow.

If its non-unicode is there a way you can let me have a copy of the table? Preferably without confidential data. Or a small program to create it with junk? I'd like to see just how fast it is.

It depends on how many places the analysis is used - it might be worth taking a hit on system start up to give speed later.

>However the in-line sub select and FUNCTION can be fast, as it uses the Index on Farmer, so it returns in 0.016 seconds ...

With subselects working out the best approach can be a bit hit and miss. My starting point is generally to run the subselect as a stand alone query - if its slow there the main query will be slower. As a stand alone you can at least check for indices etc. Avoid them as much as possible in the WHERE clause though - JOINs are much better.


>BTW: I think in some other DB's it is possible to treat SPs a bit like VIEWS. I have heard people talking about it anyway.

I think I have as well.

Roy

Adam,

It seems to me that you are making it harder than what it is, unless i'm not understanding it right.
You don't need procedures functions or views, just a simple join, as long as you have the right indexes it will be fast.
Something like this:

SELECT
  F.Name,
  COUNT(*) as FieldCount,
  CAST(LIST(CAST(YieldEstimate as VARCHAR)) as VARCHAR) as YieldEstimates,
  CAST(LIST(CAST(AcreageTotal as VARCHAR)) as VARCHAR) as FieldSizes
FROM
  FarmFields FF
  LEFT OUTER JOIN Farmers F ON F.FarmerID = FF.FarmerID
GROUP BY
  FarmerID

--
Fernando Dias
[Team Elevate]

Fernando


Certainly looks as though it should work - I keep forgetting about OUTER JOIN. I'll be interested to hear back from Adam about it. If it produces the right data it should be faster than the subselects.

Roy Lambert

Roy,

It wouldn't be much different, and I suspect that the speed would be almost the same:

SELECT
  (SELECT Name FROM Farmers F WHERE F.FarmerID = FF.FarmerID) AS Name,
  COUNT(*) as FieldCount,
  CAST(LIST(CAST(YieldEstimate as VARCHAR)) as VARCHAR) as YieldEstimates,
  CAST(LIST(CAST(AcreageTotal as VARCHAR)) as VARCHAR) as FieldSizes
FROM
  FarmFields FF
GROUP BY
  FarmerID

--
Fernando Dias
[Team Elevate]

Fernando & Roy

Thanks for the help. Once I had Roy's solution and it was fast, I didn't delve too much into the exact why of this case.

I did try Fernando's way, he is right it is the most obvious. I am not 100% sure why, but it was very slow. I do have indexes on the tables, but perhaps I didn't have them perfectly right or something. The tables are fairly large (>2m rows) and the result was taking about 10  seconds, way too much.

Moving to Roy's in-line SELECT took the return time down to about 0.02 seconds. I am not 100% sure of the reasons for the large difference, my feeling is that the use of the LIST() function might be what is slowing things down for the version with the JOIN. I know that including functions in EDB (especially user-defined functions) can have a dramatic effect on speed in some cases.

Adam


>I did try Fernando's way, he is right it is the most obvious. I am not 100% sure why, but it was very slow. I do have indexes on the tables, but perhaps I didn't have them perfectly right or something. The tables are fairly large (>2m rows) and the result was taking about 10 seconds, way too much.
>
>Moving to Roy's in-line SELECT took the return time down to about 0.02 seconds. I am not 100% sure of the reasons for the large difference, my feeling is that the use of the LIST() function might be what is slowing things down for the version with the JOIN. I know that including functions in EDB (especially user-defined functions) can have a dramatic effect on speed in some cases.

I would guess that its creating a non-sensitive result set so having to write to disk as well. The in-line selects should allow a sensitive result set so much faster since no need to create the disk table.

Roy Lambert

Adam,

<< Moving to Roy's in-line SELECT took the return time down to about 0.02 seconds. I am not 100% sure of the reasons for the large difference, my feeling is that the use of the LIST() function might be what is slowing things down for the version with the JOIN. >>

No, it's the fact that Roy's version can return a sensitive (live) result set, so it doesn't need to visit all of the rows in the driver table, like the LOJ version.

Tim Young
Elevate Software
www.elevatesoft.com