Getting Data without a table join

[koolskins]

I have a report running CR9 on an in Informix database that is showing inventory data plus issues, if any. The issue table has an outer join on it so all inventory records will display regardless of issues. I had a problem with report efficeincy when the issue table had a subordinate table connected to it simply to get the issuee's name should an issue record exist. When I removed this subordinate table the report ran using indexes and and was much quicker.

I am now trying to get the employee name info by using a formula within the report. It appears to be working, but once again, the report is running very slow and actually is taking hours to complete.

The formula I am currently running is:

whileprintingrecords;
stringvar custodian;
stringvar custodian := " ";
if custodian = " " then {in_scusiss_246.employee_nbr}={py_emptype_24.empl_id_nbr};
stringvar custodian := ({py_emptype_24.empl_fname}+" "+{py_emptype_24.empl_lname});

What can be done to simply get the employee name information on the report without sacrificing speed and efficeincy?

Thanks.
Randy

 

[fredp1]

Hi randy,

I'm a CR8.5/SQL Server user so I cannot speak with much confidence on Cr9/informix.
If you have a Inventory table left outer join to the Issue table, you'll need to have a left outer join from Issue to Employee table. If you don't, and do it in the formulae, you'll get a cartesean join, which would explain why the report is very slow.
It also depends on what is in your selection statement. can you post that so others can see?

Can you also post the SQL that CR is generating.
Some times, you can have a unoptimsed Selection statement and all the processing may get done locally rather than by the server.

BTW, do you really need to have a whileprintingrecords in your formulae?

Fred

 

[koolskins]

Thanks, it turned out to be a combination of attempting two outer joins from the same table with the employee table attempting to outer join to one of those plus an extra condition in the select that was unnecessary due to the joins. Your thoughts definitely pointed me in the right direction.

Randy