Speed up ideas

[mart1000]

I started new role yesterday and trying to run reports is nightmare. Using Crystal XI on Oracle call center system -it has about 1.5 mill records over last 7 years Had quick and mean quick review of reports design . They all run from linked tables of about 4 or 5 (generally the same ones) using left outer joins from a main 'call' table, and have some selection criteria
Below is typical of what Ive found:

SELECT DISTINCT "FSCFAU"."FSCFAU_CREATE_DATE", "FSLOCA"."FSLOCA_NAME",
"FSLOCA"."FSLOCA_ADDR_1", "FSLOCA"."FSLOCA_ADDR_2",
"FSLOCA"."FSLOCA_ADDR_3"
FROM ("OPTIM"."FSCALL" "FSCALL" LEFT OUTER JOIN "OPTIM"."FSCFAU"
"FSCFAU" ON ("FSCALL"."FSCALL_CALL_PREFIX"="FSCFAU"."FSCFAU_CALL_PREFIX")
AND ("FSCALL"."FSCALL_CALL_SUFFIX"="FSCFAU"."FSCFAU_CALL_SUFFIX")) LEFT
OUTER JOIN "OPTIM"."FSLOCA" "FSLOCA" ON
"FSCALL"."FSCALL_LOCATION"="FSLOCA"."FSLOCA_CODE"
WHERE ("FSCFAU"."FSCFAU_CREATE_DATE">=TO_DATE ('01-01-2001 00:00:00',
'DD-MM-YYYY HH24:MI:SS') AND "FSCFAU"."FSCFAU_CREATE_DATE"<TO_DATE
('31-05-2007 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))

Grouping on server is not turned on, indexes for server for speed is.



When running reports It seems to take about 30 mins while is says accessing database in bottom left corner, once it starts reading records it takes only a min or so

How can I speed things up? This is going to be a nightmare to write and test reports. My feeling is that maybe the Oracle dba should write a some views which can run daily or hourly depending on report required and I write my reports on those views. Any thoughts on speed tips are required and welcome

 

[dgillz]

Showing us only the SQL is entirely inadequate to diagnose performance. Please post the record selection formula, likely some of it is not passing thru to the SQL where clause.

Also page n of m can slowdown a report.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[synapsevampire]

As dgillz rightly points out, we don't know what should be in the SQL, right? Saying that you have some selection criteria doesn't help us know what should be generated by the SQL.

You need to post what is in your Report->Selection Formula->Record and Group

As for the Grouping on Server, that usually won't happen unless the report has the proper fields, aggregates and design.

You should also post what connectivity you are using to the Oracle database (native, ODBC, or?).

-k

 

[mart1000]

OK as I said just started yesterday, I think it will be monday before I can gather this info. Will get back with this