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?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
UDF slows down reporting 4
- Thread starter cdm1221
- Start date
synapsevampire
Programmer
You can test that theory...
Another means might be to place your formula in the report, then pass the value in a shared variable to a subreport that uses an Add Command, but this is all version dependent, and some of this you might offload to the database, but again, that's version dependent.
It sounds like whatever the UDF is doing might have been better accomplished by the database.
-k
Another means might be to place your formula in the report, then pass the value in a shared variable to a subreport that uses an Add Command, but this is all version dependent, and some of this you might offload to the database, but again, that's version dependent.
It sounds like whatever the UDF is doing might have been better accomplished by the database.
-k
- Thread starter
- #3
That is true, but I wasn't sure how to do that. I created the UDF in Visual Basic to be used in CR 8.5. The UDF functions go into the database and select a single value from the database. Is it possible to add this UDF DLL to the database, if so how? And, how would I access the functions in CR?
-
4
- #4
Dear CDM1221,
You can call a User Defined Function (not ufl but UDF).
I am assuming SQL Server database.
Create a User Defined Function that does what you want. Make sure that you grant execute to Public so that users won't have an issue running
In a crystal SQL Expression:
(select dbo.db_dhms(Table.field))
You can also do something like:
(select dbo.db_dhms(datediff(d,Field1,Field2)))
So you call the User Defined function
(Select owner.UDFNAME())
Pass any parameters the field takes, which must be fields in the report, a calculation using fields in the report, or a straight number or string, or a select statement that retrieves information for the udf to operate upon.
You may not declare variables, you may not use other sql expressions or formulas within a sql expression.
Basically, you can do anything in a SQL expression which you could do in a correlated or non-correlated sql subquery.
Regards,
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
You can call a User Defined Function (not ufl but UDF).
I am assuming SQL Server database.
Create a User Defined Function that does what you want. Make sure that you grant execute to Public so that users won't have an issue running
In a crystal SQL Expression:
(select dbo.db_dhms(Table.field))
You can also do something like:
(select dbo.db_dhms(datediff(d,Field1,Field2)))
So you call the User Defined function
(Select owner.UDFNAME())
Pass any parameters the field takes, which must be fields in the report, a calculation using fields in the report, or a straight number or string, or a select statement that retrieves information for the udf to operate upon.
You may not declare variables, you may not use other sql expressions or formulas within a sql expression.
Basically, you can do anything in a SQL expression which you could do in a correlated or non-correlated sql subquery.
Regards,
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Thread starter
- #5
I'm using Oracle, and CR8.5..I was under the impression that the sql SELECT statements were not valid in SQL Expressions. So, you're saying that with the Oracle UDF (created with VB), the UDF will show up in the SQL Expression list of functions, and I will be able to use the select statement. Correct?
Dear CDM1221,
No, that is not correct.
I am not as familiar with Oracle. But, I believe that Oracle allows you to create functions.
When you create a User Defined Function in SQL, using TSQL, that function can be called, just like a Stored Procedure can be called.
When, you call that UDF from within a SQL Expression, it will execute using the supplied parameters.
This is different from writing a UFL (User Function Library)with an application like vb or C++ , which creates a dll for use of an application as in Crystal to provide additonal functions for it's use.
I believe, if you were writing a query in SQL Plus, you could do the same thing. Call a function in a subquery.
What I am saying that you need to do, is convert (rewrite) what your function written in VB and intended for use within Crystal, to an Oracle Function that accomplishes the same thing when parameter(s) are passed to it.
If you can do that, then there should be no reason that it wouldn't work in a SQL Expression as long as the syntax is correct.
The functions that appear in the list in SQL Expressions are directly based upon the functions that are exposed via the ODBC driver used to connect to the database. Because those functions are fairly limited, most people writing SQL Expressions ignore them and use pure SQL code syntactically correct for their database.
Does that make it clearer?
Regards,
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
No, that is not correct.
I am not as familiar with Oracle. But, I believe that Oracle allows you to create functions.
When you create a User Defined Function in SQL, using TSQL, that function can be called, just like a Stored Procedure can be called.
When, you call that UDF from within a SQL Expression, it will execute using the supplied parameters.
This is different from writing a UFL (User Function Library)with an application like vb or C++ , which creates a dll for use of an application as in Crystal to provide additonal functions for it's use.
I believe, if you were writing a query in SQL Plus, you could do the same thing. Call a function in a subquery.
What I am saying that you need to do, is convert (rewrite) what your function written in VB and intended for use within Crystal, to an Oracle Function that accomplishes the same thing when parameter(s) are passed to it.
If you can do that, then there should be no reason that it wouldn't work in a SQL Expression as long as the syntax is correct.
The functions that appear in the list in SQL Expressions are directly based upon the functions that are exposed via the ODBC driver used to connect to the database. Because those functions are fairly limited, most people writing SQL Expressions ignore them and use pure SQL code syntactically correct for their database.
Does that make it clearer?
Regards,
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Thread starter
- #7
Yes, I understand. However, apparently there are already functions in my database. But I don't see them in CR8.5. I tried calling them using some sql syntax like "SELECT dbo.functionname(parameters)", but CR still threw an invalid charater at the start of the dbo object. Does the version matter? Am I calling the function wrong, or is it possible that not all functions in the database are visible in CR?
Dear CDM1221,
An addendum. It is incorrect to state that SQL select statements cannot be used in SQL expressions. I use them all the time. Both correlated and non-correlated are allowed, you just have to follow the rules for Subquery for your database.
A correlated subquery would reference fields from the view or table in your report and in effect becomes a row by row operation as each row is returned.
A non-correlated subquery does not reference any fields from the view or table in your report and simply returns the value requested.
Here, is a SQL expression that I wrote just today to get a view name from a table. The column I was looking at in the report (Column_Name) referenced the View Name and Column name as their table and column IDs and I wanted to print the actual values not 24.9, which represents the Table ID, and then the column within that table.
(CASE WHEN
(CASE WHEN (CHARINDEX('.', COLUMN_NAME) - 1) <> - 1
THEN CAST(SUBSTRING(COLUMN_NAME, 1, (CHARINDEX('.',COLUMN_NAME) - 1)) AS int)
ELSE 0 END)
<> 0
THEN
(SELECT Distinct [View_Name]
FROM [dbo].SMSYSDBINFO
WHERE (SUBSTRING(COLUMN_NAME, 1, (CHARINDEX('.', COLUMN_NAME) - 1)) = [dbo].SMSYSDBINFO.[Internal_TBL_ID]))
else '' end)
Does that help?
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
An addendum. It is incorrect to state that SQL select statements cannot be used in SQL expressions. I use them all the time. Both correlated and non-correlated are allowed, you just have to follow the rules for Subquery for your database.
A correlated subquery would reference fields from the view or table in your report and in effect becomes a row by row operation as each row is returned.
A non-correlated subquery does not reference any fields from the view or table in your report and simply returns the value requested.
Here, is a SQL expression that I wrote just today to get a view name from a table. The column I was looking at in the report (Column_Name) referenced the View Name and Column name as their table and column IDs and I wanted to print the actual values not 24.9, which represents the Table ID, and then the column within that table.
(CASE WHEN
(CASE WHEN (CHARINDEX('.', COLUMN_NAME) - 1) <> - 1
THEN CAST(SUBSTRING(COLUMN_NAME, 1, (CHARINDEX('.',COLUMN_NAME) - 1)) AS int)
ELSE 0 END)
<> 0
THEN
(SELECT Distinct [View_Name]
FROM [dbo].SMSYSDBINFO
WHERE (SUBSTRING(COLUMN_NAME, 1, (CHARINDEX('.', COLUMN_NAME) - 1)) = [dbo].SMSYSDBINFO.[Internal_TBL_ID]))
else '' end)
Does that help?
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
Dear CDM1221,
What I would do is get the syntax correct in SQL Plus.
Did you put those " " in the when you did the select?
I always wrap everything I do in a SQL Expression in open and closed parentheses.
Try:
(SELECT dbo.functionname(parameters))
Also, you won't see the functions in CR 8.5, they will simply be executed. You have to choose a function to run that makes sense. It can only return one value.
I have called Oracle Functions in a SQL Expression before, but don't currently have an Oracle database up and running. Let me see what I can find.
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
What I would do is get the syntax correct in SQL Plus.
Did you put those " " in the when you did the select?
I always wrap everything I do in a SQL Expression in open and closed parentheses.
Try:
(SELECT dbo.functionname(parameters))
Also, you won't see the functions in CR 8.5, they will simply be executed. You have to choose a function to run that makes sense. It can only return one value.
I have called Oracle Functions in a SQL Expression before, but don't currently have an Oracle database up and running. Let me see what I can find.
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Thread starter
- #10
synapsevampire
Programmer
Also make sure that you're using either the Native connectivity in Crystal, or Crystal's ODBC driver as the Oracle supplied ODBC driver will cause problems.
Excellent write up, *Ro!
Where's the FAQ?
<greedy lil grin>
-k
Excellent write up, *Ro!
Where's the FAQ?
<greedy lil grin>
-k
Dear SV,
Thanks for the compliment.
Yeah, I have been meaning to one on SQL Expresssions, because now that I have explored their capabilities, they are a lifesaver. Especially, when the app your report hs to run in won't let you add reports based upon on Stored Procedures!
Hope you are doing well.
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
Thanks for the compliment.
Yeah, I have been meaning to one on SQL Expresssions, because now that I have explored their capabilities, they are a lifesaver. Especially, when the app your report hs to run in won't let you add reports based upon on Stored Procedures!
Hope you are doing well.
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Thread starter
- #13
Dear CDM1221,
In SQL, I would run a command in Query Analyzer, Grant Execute on ProcedureName to Public thereby giving all my users permissions to run the procedure.
If you speak to your Oracle dba, he/she can advise you on the permissions that your function would need to have to allow a report writer/runner to execute the procedure with their permissions.
This is so that users with simple Select permissions won't have problems getting the report to run.
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
In SQL, I would run a command in Query Analyzer, Grant Execute on ProcedureName to Public thereby giving all my users permissions to run the procedure.
If you speak to your Oracle dba, he/she can advise you on the permissions that your function would need to have to allow a report writer/runner to execute the procedure with their permissions.
This is so that users with simple Select permissions won't have problems getting the report to run.
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Thread starter
- #15
Dear CDM1221,
Can you please post the statement you are trying to execute.
It is hard to troubleshoot without knowing exactly what you are doing.
Thanks,
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
Can you please post the statement you are trying to execute.
It is hard to troubleshoot without knowing exactly what you are doing.
Thanks,
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Thread starter
- #17
Dear CDM1221,
I just brought up an Oracle DB to test this and called a function. The purpose of this function is to get the number of calls currently assigned to a staff person. the supplied parameter is the Sequence of their record in the Staff Table.
This is what worked!
(SELECT calls_Assigned(1002) FROM Dual)
So, I think you should try:
(Select TESTFCN(ExpectedParameterValue) from Dual) and see what happens.
You have to pass the parameter, whether a field in the current report (correllated) or a value you supply hard-coded (non-correlated).
Just a note the dbo and smdba in my examples were the MS SQL Server object's owners and so wouldn't exist in your Oracle database!
Also, the function can only return 1 value.
I hope this helps!
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
I just brought up an Oracle DB to test this and called a function. The purpose of this function is to get the number of calls currently assigned to a staff person. the supplied parameter is the Sequence of their record in the Staff Table.
This is what worked!
(SELECT calls_Assigned(1002) FROM Dual)
So, I think you should try:
(Select TESTFCN(ExpectedParameterValue) from Dual) and see what happens.
You have to pass the parameter, whether a field in the current report (correllated) or a value you supply hard-coded (non-correlated).
Just a note the dbo and smdba in my examples were the MS SQL Server object's owners and so wouldn't exist in your Oracle database!
Also, the function can only return 1 value.
I hope this helps!
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
Just to explain the "FROM Dual" portion:
Oracle requires a FROM in Select statements so, for that purpose, there's a "dummy" 1-row table called "dual" to allow you to specify a FROM clause when you are not accessing a real table.
SQL Server doesn't require a FROM clause in SELECT statements, so it's not an issue there.
Cheers,
- Ido
CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
Oracle requires a FROM in Select statements so, for that purpose, there's a "dummy" 1-row table called "dual" to allow you to specify a FROM clause when you are not accessing a real table.
SQL Server doesn't require a FROM clause in SELECT statements, so it's not an issue there.
Cheers,
- Ido
CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
Dear Ido,
Thank you for explaining that, as I neglected to so.
Best Regards,
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
Thank you for explaining that, as I neglected to so.
Best Regards,
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question