UDF slows down reporting 4

[cdm1221]

I've created a DLL with user defined functions that I need. The record selection formula as well multiple fields in my report use two of these functions. The report runs ridculously slow (10+ min on a DB of 75000 records). Now I've read that if you use a function in the record selection, instead of just grabbing the specified records, it will run the function on every record in the db. I believe the use of these functions in the record selection is the main source of delay in my report, rather than the function running on the fields in the report. Pretty much these functions only need to be called once to get the value, and then use that value in record selection and in fields. Now, can I place formulas in the report header that call these functions to ensure that they will only be called once throughout the report, and the value the formula holds will be used throughout the report? Or is there an easier way?

 

[timky]

I think you can omit the ‘SELECT’ and ‘FROM’ from SQL Expression if you wish to call the function.

For example, calls_Assigned(1002) will do the work.


Tim

 

[cdm1221]

Thanks everyone! This can be difficult when you have to go through a DBA to do everything!

 

[troyboy]

I have read all the posts on using an Oracle Function within the SQL Expression editor and still I get an error.

I am using Crystal 8.5 and a Oracle 8 database

I have tried using both Oracle ODBC and native Oracle drivers (P2SORA7.dll)
I am using the following syntax:

DW_FETCH_INSUR("PATIENTS"."PATNT_REFNO", SYSDATE)
this gives:
Error in compliling SQL expression: .
I get the same error if I wrap the statement within brackets and also if I remove the quotes from around the table name.

I have even tried using a select statement as suggested in other posts, without success.

I am tearing my hair out,

 

[rosemaryl]

Dear CDM1221,

You didn't indicate what version of Crystal. However, have your tried the Crystal ORacle driver? In version 8.5 it is call CROralce8 v3.6

When setting up the odbc driver, you might try making sure under the advanced tab that procedure returns results is turned on (checked). When you say wrap in brackets, do you mean in parentheses... I always start my sql expressions with an open paren and end with a closed paren.

Hope that helps,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[troyboy]

Ro,
I am using Crystal 8.5 and I have tried native database driver, Oracle ODBC, Microsoft ODBC for Oracle and the CROracle8 v3.6 as you suggest.
I haven't tried the advanced tab yet so thats next to try. My only problem is that unless I'm mistaken you can't distribute the CROracle8 v3.6 driver and as this is going to a number of clients it would be nice if it worked with either the native driver and/or either the Microsoft or Oracle ODBC driver.

When I said brackets I meant '(', sorry if it lost something in translation.

Thanks for the reply
Troyboy

 

[rosemaryl]

Dear Troyboy,

Well, the driver ships with Crystal and I don't see why you couldn't distribute... however the Oracle ODBC driver does have an enable result sets setting.

Also, just to check, do you have the "PATIENTS"."PATNT_REFNO" field on the report? Sometimes I get that error message, and then if I place the field on the report and suppress it, the message goes away!

Hope that helps,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[troyboy]

Ro,

I'm still having the same problem. I've changed the Oracle ODBC driver to 8.01.07.810, which also has the enable results sets setting.
When trying to use the function in a SQL expression field I still get the error, but I can use the function if I type it in in the where clause.

Troyboy

 

[rosemaryl]

Dear Troyboy,

Hmmm it works in the where clause, but not in the SQL expression. That is interesting. It should work. Do you have access to a query tool? What I would do is copy your entire Crystal generated Query and then add the sql expression to the select portion. Does it work there? If it will not work in your Query tool then it will not work in Crystal.

Could you please copy your Database/Show SQL query here for me to see?

Also, what does your udf do exactly and how is it defined? I really do most of my work in MS SQL and do not have as much experience with Oracle. I do know that for a function to work in a Crystal SQL Expression, it has to be in a package which is comprised of the package specification and the package body or is a standalone function.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[troyboy]

Ro,

Many thanks for all your help, finanlly cracked it! Like you I do most of work with MS SQL and hence always have problems when I have to do work for one of Oracle Customers.

The function was returning a VARCHAR2 datatype, which basically was screwing things up.
In the end I resorted to putting the data in a view, with the functions in there, but Crystal wouldn't let me use the fields in formula fields even though they are defined as VARCHAR2(50).
Once I wrapped the function with a SUBSTR function all started to work! Now to work out whether VARCHAR data types are okay, cause I don't want to wrap everything with a database specific function.
Once again thanks

Troyboy

 

[rosemaryl]

Dear Troyboy,

I don't know why a varchar2 datatype wouldn't work... maybe the substring was solving a different problem.

Keep me apprised on your progress and let me know if I can be of help.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.