Reading Records Data 2

[jbarbato]

I am using Crystal XI, with Oracle 10g DB.

I am trying to tune up the performance of a report. According to the performance information and the notes that appear at the bottom of Crystal while my report is running, the access of the database and the amount of time it takes to run my query are not the problem. (Also I copied the query in the "View SQL Query" - it is very fast).

However, the report runs slow as it is "Reading Records" I have linked views in the database expert and used the selection formula to build my filtering criteria.

Is there a way to see what is going on in the background of "Reading Records"? In my Performance Information, it gives quite a high number for page generation.

Thanks ~
Jayme

 

[Turkbear]

Hi,
( be sure you are using Oracle Native Connectivity and not ODBC)

When running the Sql that the report generates directly in Oracle ( from SqlPLus), does it return all the records you expect and does it run faster than the report?

Are you doing any post-retrieval processing on each page?
Are subreports involved?

Please explain further what you mean by:

In my Performance Information, it gives quite a high number for page generation.

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jbarbato]

In the toolbar, I click on "Report" and "Performance Information" is one of the menu options. If I click on "Performance Timing" it tells me how long everything takes.

Both reports are EXACTLY the same - I had started the report by doing a SQL command with no parameters in it, then putting filtering criteria in the Select Expert. This took 20 minutes to run the report, and I realized that this is not how crystal wants to handle data...

So I decided to try two different ways of getting my report data. Report A has a SQL command with all parameters contained in the SQL query. Report B has 2 views that are linked together, and all parameters conained in the Select Expert.

When running the SQL that the report generates directly in Oracle ( from SqlPLus), it returns all the records I expect and it runs in less than 5 seconds for both reports. Both reports seem to get through the "Access Database" and "Retreiving Records" quite quickly (around 6-8 seconds). Report A gets through "Reading Records" in 1 second. Report B takes over 5 minutes to get through "Reading Records".

But... I can't just use Report A, because the parameters in the SQL command cannot be multi-select, and that is a user requirement. Plus it is buggy to develop the report this way (I already have patches installed - It still crashes)

There is a lot of processing going on. There are 8 levels of grouping, charts and graphs, and formulas.

So I am trying to figure out why Report B goes so much slower than the other... Especially since Report B seems to be the way that Crystal prefers you handle parameters.

 

[synapsevampire]

Crystal doesn't prefer that you use the GUI, it is simply the only way to use multi-value parameters.

As for the difference in timing, what does "When running the SQL that the report generates directly in Oracle ( from SqlPLus), it returns all the records I expect and it runs in less than 5 seconds for both reports." mean in technical terms?

Report A doesn't generate SQL, you're creating it. Report B does generate SQL, but it sounds as though the WHERE clause isn't being properly passed, except for the above statement, but since 1/2 of the statement doesn't make sense, I'll ignore it.

The trick is to make sure that whatever you enter in the Report->Select Formula->Record is reflected in the Database->Show SQL Query.

So post that information.

-k

 

[jbarbato]

As for the difference in timing, what does "When running the SQL that the report generates directly in Oracle ( from SqlPLus), it returns all the records I expect and it runs in less than 5 seconds for both reports." mean in technical terms?"

It means that I take the SQL from "Show SQL Query" and run it in TOAD. Queries from Report A & Report B both take less than 5 seconds to run, and the data that is retuned is correct. It leads me to believe that the problem is not with the SQL query itself, but how Crystal is processing the information after the data is obtained.

I had to put in a single value for each parameter so that the parameters would appear in "Show SQL Query." Most of the parameters are set up so that a user can enter either a 0 or a blank space to retrieve all values for that parameter. When they do that, the parameter does not show up in the SQL query. But I have tested this, and it does not affect performance.

Which is why I would like to know how to view what is going on behind the scenes while Crystal Reports is "Reading Records". When you run a report, in the bottom left hand corner, I see two different little messages: Accessing Database, and Reading Records. For both reports, Accessing Database goes by very quickly (less than 5 seconds). For Report A, Reading Records happens in about 1 second. For Report B, Reading Records takes around 5 minutes (When there are many records returned - keep in mind I had to put at least one value for each parameter in order to get them to show up in the SQL query.)


Here are the queries:

Report B - In the Database Expert, I pulled over Views and linked them with Crystal, and then put all of my parameter information in the Selection Formula for Records

SELECT "ProjectInformation"."PROJECT_NUMBER",
"ProjectInformation"."PROJECT_DESCRIPTION",
"ProjectInformation"."CLIENT_NAME",
"ProjectInformation"."PROJECT_NUMBER_DISPLAY",
"MeasurementInfo"."DISPGROUP_DESCRIPTION",
"MeasurementInfo"."PERSPECTIVE",
"MeasurementInfo"."SUMMARY_VALUE1",
"MeasurementInfo"."SUMMARY_VALUE2",
"MeasurementInfo"."LOCATION",
"MeasurementInfo"."DELIVERABLE_TYPE",
"MeasurementInfo"."DESCRIPTION",
"MeasurementInfo"."DISCIPLINE",
"MeasurementInfo"."SCH_PERCENT_COMPLETE",
"MeasurementInfo"."FST_PERCENT_COMPLETE",
"MeasurementInfo"."EARN_PERCENT_COMPLETE",
"MeasurementInfo"."CPI",
"MeasurementInfo"."SPI",
"MeasurementInfo"."FINISH_DATE",
"MeasurementInfo"."PROJECTED_SCHEDULE_AMOUNT",
"MeasurementInfo"."PROJECTED_FORECAST_AMOUNT",
"MeasurementInfo"."SUMMARY_TYPE",
"MeasurementInfo"."EXPENSE_TYPE",
"MeasurementInfo"."DISCIPLINE_ID",
"MeasurementInfo"."DISCIPLINE_GROUP_ID",
"MeasurementInfo"."APP_LOCATION_ID",
"CurrentCalc"."FINISH_DATE",
"MeasurementInfo"."ACWP",
"MeasurementInfo"."EXPENDED_PERCENT_COMPLETE",
"MeasurementInfo"."SUMMARY_DESCRIPTION",
"MeasurementInfo"."CURRENCY",
"ProjectInformation"."CLIENT_IMAGE"

FROM ("PTS"."PROJECT_INFO_VC" "ProjectInformation"
LEFT OUTER JOIN "PTS"."RPT_PSDMEASSUM" "MeasurementInfo"
ON "ProjectInformation"."PROJECT_NUMBER"="MeasurementInfo"."PROJECT_NUMBER")
LEFT OUTER JOIN "PTS"."CURRENT_CALC_VC" "CurrentCalc"
ON "MeasurementInfo"."PROJECT_NUMBER"="CurrentCalc"."PROJECT_NUMBER"
WHERE "ProjectInformation"."PROJECT_NUMBER"=28765
AND "MeasurementInfo"."SUMMARY_TYPE"='PROJ'
AND "MeasurementInfo"."EXPENSE_TYPE"='01'
AND "MeasurementInfo"."PERSPECTIVE"='HRS'
AND ("MeasurementInfo"."FINISH_DATE">=TO_DATE ('31-03-2006 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
AND "MeasurementInfo"."FINISH_DATE"<TO_DATE ('27-10-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
AND "MeasurementInfo"."CURRENCY"='USD'
AND "MeasurementInfo"."DISCIPLINE_ID"=32395
AND "MeasurementInfo"."DELIVERABLE_TYPE"='ALL'
AND "MeasurementInfo"."DISCIPLINE_GROUP_ID"=1
AND "MeasurementInfo"."APP_LOCATION_ID"=4
ORDER BY
"MeasurementInfo"."SUMMARY_VALUE1",
"MeasurementInfo"."SUMMARY_VALUE2",
"MeasurementInfo"."LOCATION",
"MeasurementInfo"."DELIVERABLE_TYPE",
"MeasurementInfo"."DESCRIPTION",
"MeasurementInfo"."PERSPECTIVE",
"MeasurementInfo"."DISPGROUP_DESCRIPTION",
"MeasurementInfo"."DISCIPLINE",
"MeasurementInfo"."FINISH_DATE"


Report A - In the Database Expert, I wrote my own SQL command, and put all of my parameter information in the SQL Command itself - This one runs way faster, but I can't really use it since our parameters need to be multi-select
select distinct
project_number,
summary_type,
summary_value1,
summary_value2,
get_summary_description(project_number,summary_type,summary_value1, summary_value2) summary_description,
app_location_id, location || ', ' || state_province location,
deliverable_type,
expense_type,
aet.description,
currency,
perspective,
discipline_group_id,
adg.description dispgroup_description,
discipline_id, discipline || ' - ' || d.description,
finish_date,
report_perspective_management,
report_perspective_client,
report_perspective_additional1, report_perspective_additional2,
curve_sch_percent_complete sch_percent_complete, curve_fst_percent_complete fst_percent_complete, expended_percent_complete,
acwp,
curve_earn_percent_complete earn_percent_complete,
cpi,
spi,
projected_schedule_amount,
projected_forecast_amount

from summarized_measurements sm
inner join measurement_periods mp using(measurement_period_id,project_number)

inner join app_expense_types aet using(expense_type, perspective)

inner join app_locations aloc using (app_location_id)

inner join disciplines d using (discipline_id,project_number)

inner join app_discipline_groups adg using (discipline_group_id)

where project_number = to_number('{?project}')
and
sm.summary_type = '{?Summary_type}'

and ('{?Expense_type}' = ' ' or expense_type ='{?Expense_type}')
and ( '{?Perspective}' = '' or perspective = '{?Perspective}')

and ('{?Delv_type}' = ' ' or sm.deliverable_type = '{?Delv_type}')

and (to_number('{?location}')= 0 or app_location_id = to_number('{?location}'))

and (to_number('{?discipline}')= 0 or discipline_id = to_number('{?discipline}'))

and (to_number('{?discipline_group}')= 0 or discipline_group_id = to_number('{?discipline_group}'))
and sm.currency = '{?Currency}'
and sm.current_calculation_switch = 'H' and
finish_date >= to_date('{?start_date}','DD-MON-YY')
and finish_date <= to_date('{?end_date}','DD-MON-YY')
and ('{?Report_Perspective}' = 'N'
or ('{?Report_Perspective}' = 'M' and report_perspective_management = 'Y')
or ('{?Report_Perspective}' = 'C' and report_perspective_client = 'Y')
or ('{?Report_Perspective}' = 'A' and report_perspective_additional1 = 'Y')
or ('{?Report_Perspective}' = 'B' and report_perspective_additional2 = 'Y') )

order by finish_date

select distinct sm.project_number,
last_value(finish_date) over (partition by sm.project_number order by finish_date rows between unbounded preceding and unbounded following) finish_date

from summarized_measurements sm, measurement_periods mp

where sm.project_number = mp.project_number
and sm.measurement_period_id = mp.measurement_period_id

and sm.current_calculation_switch = 'C'

select proj.project_number,
proj.project_number_display,
proj.description project_description,
proj.start_date project_start_date,
proj.finish_date project_finish_date,

ac.client_name,

aip.image project_image,
aic.image client_image

from projects proj, app_images aip, app_images aic, app_clients ac

where proj.client_id = ac.client_id (+)
and ac.image_id = aic.image_id (+)
and proj.image_id = aip.image_id(+)

 

[Turkbear]

Hi,
Accessing Database = Making the connection and verifying rights - should alweays be very fast.

Reading Records = getting each returned record from the query ( will very depending on # of Records, etc..) - that is what the counter on the right is showing...


By the way, those 2 sql queries are not comparable to each other ( A uses Inner, B uses Outer as the Join type - makes a big difference) - are the record counts the same??



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I've always interpreted "Accessing database" to mean the work that is done at the server level and "reading records" to show the record count as records are being returned to the local client, so that a well-designed report might take longer accessing the database (with a slow server like ours), but return records quickly. Again, with a slow server, a report that instantly starts returning records probably means the records are being processed primarily at the local level. Ideally, of course, both phases would be speedy.

-LB

 

[Turkbear]

Hi lbass,
That is probably a more complete description of the
'accessing database' phase, but I am not sure that the 'return speed' ( as opposed to the display speed of the report) is affected by Crystal's need ( if any) to process the requested data, so I have always interpreted the 'reading records' phase as the returning of the query's output ( record by record) as it is produced at the Database server..

There is probably a course or docs on CR's internals that would be more specific...




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jbarbato]

Both queries take the same amount of time to run in TOAD... So I don't understand why they would take a different amount of time during Reading Records...

The inner joins that you see are actually done behind the scenes in the view that I created, RPT_PSDMEASSUM. I renamed RPT_PSDMEASSUM in the database expert to "Measurement Info" so that it had a little meaning to other developers who might want to maintain my report. So both queries had inner joins, you just couldnt see them in Report B Both queries are doing the same thing, and returning the same results.

Here is what I did to double check: I put select * from RPT_PSDMEASSUM (alias MeasurementInfo) in Report A, which is equivilent to pulling the entire view over as I do in Report B. I am still having a huge difference in performance times.

I really don't think it has to do with WHAT the SQL statement is that results, but rather HOW the query is put together, and what crystal does with the data as a result of HOW it was put together... Does that make sense? Is it possible that Report B is trying to filter again?

So now "Show SQL Query" for Report A looks like this:

select * from RPT_PSDMEASSUM

where project_number = to_number('{?project}')and
summary_type = '{?Summary_type}'
and ('{?Expense_type}' = ' ' or expense_type ='{?Expense_type}')
and ( '{?Perspective}' = '' or perspective = '{?Perspective}')
and ('{?Delv_type}' = ' ' or deliverable_type = '{?Delv_type}')
and (to_number('{?location}')= 0 or app_location_id = to_number('{?location}'))
and (to_number('{?discipline}')= 0 or discipline_id = to_number('{?discipline}'))
and (to_number('{?discipline_group}')= 0 or discipline_group_id = to_number('{?discipline_group}'))
and currency = '{?Currency}'
and
finish_date >= to_date('{?start_date}','DD-MON-YY')
and finish_date <= to_date('{?end_date}','DD-MON-YY')

order by finish_date

--- This table only has 3 records in it anyways - it really doesnt matter ---
select distinct sm.project_number,
last_value(finish_date) over (partition by sm.project_number order by finish_date rows between unbounded preceding and unbounded following) finish_date

from summarized_measurements sm, measurement_periods mp

where sm.project_number = mp.project_number
and sm.measurement_period_id = mp.measurement_period_id

and sm.current_calculation_switch = 'C'

--- And this table only has about 20 records, so it doesn't really matter much either---
select proj.project_number,
proj.project_number_display,
proj.description project_description,
proj.start_date project_start_date,
proj.finish_date project_finish_date,

ac.client_name,

aip.image project_image,
aic.image client_image


from projects proj, app_images aip, app_images aic, app_clients ac

where proj.client_id = ac.client_id (+)
and ac.image_id = aic.image_id (+)
and proj.image_id = aip.image_id(+)

 

[lbass]

Your results confirm my experience that using a command as datasource is faster than the same report developed using the CR GUI. Sometimes I develop quick reports using CR linking and filtering, etc., and then copy the resulting SQL into a command--and the resulting report is faster. The inability to use multi-value parameters is a limitation, of course.

-LB

 

[Turkbear]

Hi,
You can pass a multiple set of values to a Stored Procedure
for fast results - here is an example if using Oracle:
First build a Function to create a table-type on the fly:

Function InList 
 ( p_string in varchar2 ) return StdTableType
as
 l_string        long default p_string || ',';
 l_data         StdTableType := StdTableType();
 n               number;
 begin
    loop
        exit when l_string is null;
        n := instr( l_string, ',' );
        l_data.extend;
        l_data(l_data.count) :=
                 ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
         l_string := substr( l_string, n+1 );
    end loop;
    return l_data;
end;

The create the SP:

(
 p_auditid IN VARCHAR2,
  p_return_cur OUT SYS_REFCURSOR,
  p_startDate IN  VARCHAR2,
  p_endDate IN VARCHAR2
 )
IS
BEGIN
  OPEN p_return_cur FOR
  SELECT "EATS_AGREEMENTS"."VENDOR_NAME", 
"EATS_AGREEMENTS"."AGREEMENT", "EATS_AGREEMENTS"."WORK_ORDER", 
"EATS_AGREEMENTS"."MASTER_AGR_YN", "AUDITOR_LIST"."EMPL_NM", 
"EATS_AUDITOR_WORK_VIEW"."CHANGING_EMP_ID_FK", 
"EATS_AUDITOR_WORK_VIEW"."DATE_CHANGED", 
"EATS_AUDITOR_WORK_VIEW"."STATUS_FROM", 
"EATS_AUDITOR_WORK_VIEW"."STATUS_TO"
FROM   "EATS"."EATS_AGREEMENTS" "EATS_AGREEMENTS", 
"EATS"."EATS_AUDITOR_WORK_VIEW" "EATS_AUDITOR_WORK_VIEW", 
 "EATS"."AUDITOR_LIST" "AUDITOR_LIST"
 WHERE  
 ("EATS_AGREEMENTS"."RELATING_ID"="EATS_AUDITOR_WORK_VIEW"."RELATING_ID_PK" (+)) 
 AND ("EATS_AUDITOR_WORK_VIEW"."CHANGING_EMP_ID_FK"="AUDITOR_LIST"."EMPL_NBR" (+)) 
 AND
"EATS_AUDITOR_WORK_VIEW"."CHANGING_EMP_ID_FK" IN
 [COLOR=red](select * from THE ( select cast(eats.in_list(p_auditid) as eats.StdTableType ) from dual)) [/color] AND 
 ("EATS_AUDITOR_WORK_VIEW"."DATE_CHANGED" >= to_date(p_startDate,'MM/DD/YYYY') ) AND 
 ("EATS_AUDITOR_WORK_VIEW"."DATE_CHANGED"< to_date(p_endDate,'MM/DD/YYYY') ) AND 
 "EATS_AGREEMENTS"."MASTER_AGR_YN"='N'
 ORDER BY "AUDITOR_LIST"."EMPL_NM", 
"EATS_AUDITOR_WORK_VIEW"."DATE_CHANGED";
END;

The text is where a multi-value parameter is passed to the InList function and then it is used to limit the results.

Not pretty to look at, but it does work..( It is based on code at AskTom.com - a very good Oracle source)



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kenhamady]

TurkBear,

Thanks for the code example. If I tried to create a report against this SP would the report automatically have a multi-value parameter? Or would I need to follow the steps in this KB article to set up the report?

I also tried to look at the site you mentioned and all I saw was sponsored links to other sites. Not one word of content. Are you sure this is the correct site?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[jbarbato]

Thanks for the suggestion about stored procedures... Hearing that someone else has slow reports using the Crystal GUI vs. writing a SQL command confirms that I am on the right track here...

I am dissapointed - I didn't realize there was a performance issue until it was too late - I have 50 reports that run slow as a snail and we are not far from pushing to production.

I am frustrated because when I first started building reports, we had a conference with Crystal on Best Practices. They convinced me to pull tables & views with the Database Expert and use the Select Expert with the GUI to build parameters. All of my reports are set up that way, and they were just fine when there was a small amount of data. Now they take several minutes...

I would like to try the stored procedures, but unfortunately I dont have the time. We have an application that acts as a front end to the reports and passes in all parameters... That would take a lot of time to rework as well if I change it up... I am going to write commands with parameters written directly into the SQL, and take away the user's ability to multi select...

I am curious to hear the answers to Ken's questions above. While I am reformatting my reports, I am going to experiment with the stored procedures and possibly offer the multi select parameters in a future relase...

 

[synapsevampire]

It's a little more complex than what I've read here.

When using Views you may be experiencing full table scans, you should get a competent dba involved in this. As for LB's statement of a Command Object outrunning the Crystal GUI, it depends on the database, indexes, statistics, etc...you can certainly write the query from Hell and make a command object less efficient, but if all things are the same, you shouldn't see much difference.

Place select * from table in a command object.

Try a select from the same table using the Crystal gui. Do you see minutes of difference? Crystal isn't the bottleneck here, it's how the query is being processed by the server.

That said, the Command Object allows for a lot more flexibility, and using them might make sense if you are unable to create real database objects, but don't get caught up thinking that a command object is more efficient than SPs or Views, or for that matter the SQL generated by Crystal, it's all in how the database processes the query, and it shouldn't be guessed at, use the tools available to trace and optimize.

-k

 

[jbarbato]

I used Crystal Reports and pulled over tables in the Database Expert, then linked them. Then I added filter criteria to the Select Expert Formula. I ran my report. (Report B)

It took over 5 minutes.

I took the query generated above by Crystal Reports in the Show SQL Query. I placed it in a SQL Command with all of the parameters in it, and ran the report. (Report A)

It took 5 seconds.

How is that not Crystal's fault?

I would love to use tools available to trace and optimize. What are they? I looked under "Performance Information" and noticed that "Page Generation" is taking a very long time in Report B, also Reading Records takes a long time. My original post asked about how I could see what was going on behind the scenes during "Reading Records".

Please tell me about the tools available

Thanks