Joined: 18 Aug 2010
Online Status: Offline
Posts: 28
Topic: report is running slow Posted: 03 Mar 2011 at 1:45pm
My report is not complex though involves lots of views.
There is no page count currently so that could nt be a problem
The joins are on indexed columns.
the selection criteria checks for string from a column of type clob.
I am not sure if this is causing the problem.Can someone suggest what to look for and how to optimize my report.
The report was running from last five minutes ir brought about 20 records and read around 5000 records.I had to stop the process in between as it was reading all the records.
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Posted: 04 Mar 2011 at 3:52am
The problem is that it's looking at a CLOB column in the selection criteria - Crystal will do that locally. If you know how to write SQL, instead of using the views as tables and setting the selection criteria in the Select Expert, I would use a command where you create the entire SQL needed for the report including the selection criteria (if you're using parameters, delete the params from the main report and recreate them in the Command Editor - use quotes around any string parameter when you add it to the SQL.) This will put all of the processing on the database. It's likely it will still be somewhat slow as searching through CLOB fields takes some time, but it won't be as slow as doing it in Crystal itself.
Joined: 18 Aug 2010
Online Status: Offline
Posts: 28
Posted: 05 Mar 2011 at 7:28am
Thank You.
I am following your approach now and i copied the sql from 'Show SQL Query' in the database option on the command and removed all the exisiting views.
While doing this i observed one thing the sql does nt contain the complete selection criteria when copied from show sql .It left out the statement where i was picking from clob field and then took statements after that.
In the end there was or clause in the selection criteria which again did not show up in the sql.Is it not strange.
Should i addd those statements manually in the sql which i got from show sql query option. what could be the reason of not selcting those lines from criteria in the shoq sql.
Thanks again for your help.I really hope this solves the problem.
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Posted: 07 Mar 2011 at 6:54am
When the condition doesn't appear in the SQL it means that Crystal is processing that condition internally instead of having the database process it. This is what was causing the issues with your report.
Oh and there is no way to force crystal to process the sql on database end but to write a command.I did add a command and the performance has improved. I have to do this for next 20 reports so is this is the only possible option.
I was building couple of reports and thanks a lot for your suggestion and it works well.
I have one question now , till now i build up command for all non prompt reports.
Recently i had to do it for one report which had prompt but i am using it to display the row.
SO if the prompt value matches with one of the departments then show group 2 and this is in the section expert.
if matches with section then display group 3 and this formula is in the section expert of group 3.
In this case do i need to create a paramter in the command as i dont know where to put it in the script. Or will it work if i create on the report and use it in the show group formula.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
