SPEED? Can I build a report, copy its SQL code then run with Command?

[urbanhim]

I have reports which take 2-3 hours to run, they are reading through an Oracle table which has over 30m records in it, each with 40 fields.

I am only bringing back certain criteria, but as I understand it, Crystal will read all records first, and then bring back what it needs.

I am led to believe that SQL Commands are quicker as they only retrieve what is required straight away, rather than reading everything, I do not have any SQL experience or knowledge, so i'm not sure.

My question is this... Is it possible to build the report in the usual way, and then before running it, choosing the "Show SQL" from Menu, then copying this and running it straight from a Database Command within Crystal.

Will this make it quicker, if not, is there something I can type to the SQL to speed it up?

Any guidance would be appreciated.

Many thanks

UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[lbass]

The command might be quicker (why not just try it?), but I think you should review your query to make sure all selection criteria are passing to it first. Go to report->performance info and see what that indicates. Then go to report->selection formula->record and make sure all criteria are in your query (database->show SQL query).

There are also some methods within a command that can increase speed.

-LB

 

[CrystalLion]

I have done what you suggest. However, the results were less than stellar. In fact, the sql I copied from Show SQL into a command failed.

When I stripped out all but the main table (the one that is huge) that was causing it to fail, the performance was great when running the report against just the command table. However, when I tried to add another table and link it to the command table, it reverted to slug mode.

I am still experimenting myself, but hope this helps.

Good luck. FYI - LBass is usually dead-on with his/her suggestions.

 

[lbass]

CrystalLion--Can I assume you are not also Urbanhim? You should not be linking a command to a table, as this will cause the linking to occur locally, and dramatically slow your report.

If I am trying to set up a complicated command, I will first create a report by adding tables, linking them, adding criteria and fields, etc., and then copy the SQL query into a new report in the command area. I then modify that as necessary. That makes it much simpler.

I can't comment on why your attempt failed. Did you get an error message?

-LB

 

[urbanhim]

Thanks to both of you, I have tried copying the SQL from Show SQL and pasting into a new command. Although this does work without errors, the report doesnt appear to run that much quicker. The time from pasting SQL and clicking [OK] to the time it allows me to click [Next] is on a par with when I just "Refresh" my report proper.

My Oracle DB guys are adament that SQL commands would be quicker than Crystal itself, they have built SQL commands which run through Oracle direct, but now all our reports are to be done through Crystal - Fine where theres no changes because I copy the Oracle SQL and paste into Crystal command, but where changes or new criteria are needed, I have to build the report through Crystal itself as I cannot write SQL code.

Here's an example of Oracle SQL, and then my same report build through Crystal, and then using Show SQL, these are different but the results are the same. The Oracle SQL runs in seconds, my Crystal SQL takes ages!!

ORACLE SQL
select 
universe_timestamp,account_id,SITE_IDENTIFIER,update_type,business_area,
site_type,failed_check from order_history
where universe_timestamp between trunc(sysdate - 7) and trunc(sysdate)
and channel = 'MOBILE'

CRYSTAL SHOW SQL
SELECT "ORDER_HISTORY"."UNIVERSE_TIMESTAMP", 
"ORDER_HISTORY"."SITE_IDENTIFIER", "ORDER_HISTORY"."UPDATE_TYPE", 
"ORDER_HISTORY"."BUSINESS_AREA", "ORDER_HISTORY"."SITE_TYPE", 
"ORDER_HISTORY"."FAILED_CHECK", "ORDER_HISTORY"."CHANNEL", 
"ORDER_HISTORY"."ACCOUNT_ID"
 FROM   "CONTENT"."ORDER_HISTORY" "ORDER_HISTORY"
 WHERE  "ORDER_HISTORY"."CHANNEL"='MOBILE' AND 
("ORDER_HISTORY"."UNIVERSE_TIMESTAMP">=TO_DATE ('28-02-2008 00:00:00', 
'DD-MM-YYYY HH24:MI:SS') AND "ORDER_HISTORY"."UNIVERSE_TIMESTAMP"<TO_DATE 
('06-03-2008 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))

Oh, and I can confirm that I am not CrystalLion!! ;-)

Thanks guys



UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]