Hello Everybody,

I've developed some EDB Functions and when invoked from scripts/Delphi, the performance is quite good. However, when I choose to use it as part of a SELECT statement it performs poorly. Here an example to help clarify:

SELECT ClientID, InvoiceTotal, fnCalculateComision(InvoiceId) AS ComisionDue
FROM Invoices

If this statement is executed against just one row (when using a key base filter) the statement returns quickly, however if the statement affects 200 or more rows the performance drops down dramatically.

If a script and cursor approach is used instead of a SELECT the performance is good.

It is as if every time a row is processed the function call is prepared. Is this possible?

Is there a workaround for this?

Thanks for your help.

Regards,
Mario

Mario


I think the answer probably lies in the code in your function which you haven't posted.

Roy Lambert [Team Elevate]

Mario

>>It is as if every time a row is processed the function call is prepared. Is this possible?

I am not 100% sure, but I think this is correct, the function has to be called for every row, as the ID only becomes available at that point. If the function is slow this will make the SELECT pretty slow.

>>Is there a workaround for this?

I suspect you have already found it ... using a cursor within a script.

Roy,

Thank you for taking the time to answer my question.

I didn't post the code of the function (which does a couple of EXECUTES IMMEDIATE's), because I didn't feel it was relevant, given that the performance is ok when invoked in a different context.

If you are willing to take a quick review of the function, I'll post it. I'm sure an experience eye could find a lot of flaws that could increase the performance no matter what..

Regards,
Mario


Roy Lambert wrote:

Mario


I think the answer probably lies in the code in your function which you haven't posted.

Roy Lambert [Team Elevate]

Adam,

Thank you too, for taking the time to review my post...

>>I am not 100% sure, but I think this is correct, the function has to be called for every row, as the ID only >>becomes available at that point. If the function is slow this will make the SELECT pretty slow.
I'm afraid the could be the case...

>>>>Is there a workaround for this?
>>
>>I suspect you have already found it ... using a cursor within a script.

Unfortunately my reporting engine (ReportBuilder)  won't allow to report from EDB scripts or stored procedures...

Regards,
Mario

Mario,
> Unfortunately my reporting engine (ReportBuilder)  won't allow to report from EDB scripts or stored procedures...

That's not correct. TEDBStoredProc is derived from TEDBDataset, so you
can use it with RB without any problems.

Please read the post "Is it possible to get name of View...".

Uli

Mario

>I didn't post the code of the function (which does a couple of EXECUTES IMMEDIATE's), because I didn't feel it was relevant, given that the performance is ok when invoked in a different context.

That gives a clue straight away. EXECUTE IMMEDIATE while very useful (I'd be stuffed without them) and pretty nippy get initialised each time. They have to because they're essentially just running a query.

If you can replace the EXECUTE IMMEDIATE's with SQL/PSM you should see a major improvement. If not then it may be possible to use Delphi and get some level of improvement.

Roy Lambert [Team Elevate]

Uli,

That's correct, RB could work with most TDataset descendants (such as TEDBDataset..), but I'm using the end user report designer that stores the backing SQL and report definition on EDB.  When using this feature I'm limited to SELECT's statements when working with EDB, even though it works just fine with SQL Server and ADO.

Regards,
Mario

Roy,


>>
>>If you can replace the EXECUTE IMMEDIATE's with SQL/PSM you should see a major improvement. If not then it >>may be possible to use Delphi and get some level of improvement.

Do you mean there's another way to invoke SQL/DDL statement from a function without using EXECUTE IMMEDIATE?

Regards,
Mario

Mario

>>>If you can replace the EXECUTE IMMEDIATE's with SQL/PSM you should see a major improvement. If not then it >>may be possible to use Delphi and get some level of improvement.
>
>Do you mean there's another way to invoke SQL/DDL statement from a function without using EXECUTE IMMEDIATE?

You're carrying out modifications to the structure in a SELECT query? That does not sound sensible to me. Hopefully you've confused DDL with DML. This link is Oracle but gives a good definition

http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands

Please post the code for fnCalculateComision.

Roy Lambert [Team Elevate]