HOW TO SPEED UP REPORT RETRIEVAL??

[jodo]

CR 8.5, VB 6, using the report designer component inside of VB.

I have a report that uses two tables from the same database to create a report. The report uses criteria passed to it from a form in VB. The data retrieved is usually a couple of days worth of data, at most a couple of months. I ran the report trying to pull a few days worth of data and the report takes about 20-25 minutes to run. Is there anyway I can speed this up?

-In the report designer, under report options, I have the 'Use Indexes Or Server For Speed' box checked.

-I have 'Perform Grouping On Server' checked under the database object.

-My SQL Join Type is Equal[=].

I'm not sure what else I can do. Any suggestions.

 

[synapsevampire]

Passing the criteria to the report does NOT assure that it's passing it to the database, and I'm fairly certain your report is not.

How the criteria is constructed decides what gets passed.

Your best performance will be using Stored Procedures, but if you're using pass through SQL for a join, check out the thread with the Subject: Optimizing Performance in the Crystal Enterprise forum.

This link may work:

thread782-262623 speaks of using parameters, but the theory is identical, you must get your SQL to pass through to the server or you'll get a large record set back and Crystal will then do the work, which is very slow.

There are many threads on this site related to this.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jodo]

Thank you for responding.

I'm passing criteria to the RecordSelectionFormula like this:

Report.RecordSelectionFormula = "{WO_History3.CREATED} in Date(" & _
Format(frmMain.cbStartDate, "yyyy,mm,dd&quot & &quot to Date(" & _
Format(frmMain.cbEndDate, "yyyy,mm,dd&quot & &quot"

This is Crystal's Syntax. In the stand-alone version you can actually view the SQL code that is sent to Oracle; however, I don't think you can view the SQL statement inside of the report designer. I've also created this report inside of the stand-alone version of CR and it works great, but when I try to import the working report into VB's report designer, it craps out and I'm bombarded with errors. I read the thread you sent me, but I don't think I'm doing the same thing. One more thing, I'm not sure how to use stored procedures. Would it be more efficient? I've essentially got most of my program completely except for this problem and I don't want to change a lot of my code, HELP!!

Thanks.

 

[synapsevampire]

This looks suspect, though it's been awhile since I used pass through from an app:

Report.RecordSelectionFormula = "{WO_History3.CREATED} in Date(" & _
Format(frmMain.cbStartDate, "yyyy,mm,dd&quot & &quot to Date(" & _
Format(frmMain.cbEndDate, "yyyy,mm,dd&quot & &quot"

Shouldn't this read:

Report.RecordSelectionFormula = "{WO_History3.CREATED} >= Date(" & _
Format(frmMain.cbStartDate, "yyyy,mm,dd&quot & &quot
or
Report.RecordSelectionFormula = &quot;{WO_History3.CREATED} <= Date(&quot; & _
Format(frmMain.cbStartDate, &quot;yyyy,mm,dd&quot & &quot

Note that I'm just checking for a greater than or less than criteria here, and there aren't any paramters being passed.

The key is to check what is actually being passed through to the database.

A Stored Procedure might have 2 parameters in it, start_date and end_date.

You would then redo the report to use the SP instead of tables, and set the parameters from your app prior to invoking the report.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kenhamady]

Are you sure that your RDC is v8.5? (ie did you have an older RDC and then upgrade to 8.5 Pro instead of developer? The fact that you can't import an external RPT tells me something is wrong with your RDC. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[jodo]

synapsevampire, the RecordSelectionFormula works because I am using the same criteria for other reports. However, the other reports are single table reports.

Ken, I am using CR v8.5. After reading your response I have since recreated the report in stand-alone CR and imported it into VB's report designer and it work. Please read the following.

I've created this two different ways now and it ALMOST works.
-The first way I tried to make the report work was to create a view of both of the tables and join them together so, in essense, my report would be looking at one single table.
-The second way I made it work was to create the report in CR 8.5 and then import it into the designer.

Both reports work passing it all criteria except for the date criteria, both reports produce the following errors.

1) ODBC error: [Oracle][ODBC Oracle Driver][Oracle OCI]ORA-1830: date format picture ends before converting entire input string.

2) Error detected by database DLL.

I researched the ORA-1830 error and most said that the date I'm passing to the format is too long, but I'm sending it the mm/dd/yyyy format, which then formats it to the yyyy,mm,dd Crystal format for the RecordSelectionCriteria.

Any suggestions, HELP!!

Thanks,
-j

 

[kenhamady]

I suggest that you capture the string that you are passing for your date criteria and compare it to a record selection formula for dates that acutally works in CR. I will bet they aren't the same. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[synapsevampire]

If you use the in {date} to {date}, I think that Crystal will convert the SQL to >= <=. I'd seen a ghost before, and thought your IN was incorrectly assembled, and I prefer >= <=.

I infer from &quot;date format picture ends before converting entire input string&quot; that the Format statement has something wrong with it.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com