BOE XI - Crystal Report with Synonym runs fast in CR but very slow BOE

[crogers111]

BOE XI
CR XI
SQL OLE

I have a Crystal Report that pulls data from 2 databases on the same server. For one database, I have a Synonmym setup for the one table it pulls data from, and it only pulls 1 or 2 rows from this table.

The report runs and displays in CR in about 5 seconds. When I set this report (and another similar report) up in BOE with the same criteria, it runs and takes approx 12 mins to complete.

The Synonym is used to pull in only 1 or 2 rows . I have selection critieria to filter to 1 specific row of data from the Synonym. Of the 1 row of data from the synonym there are only a few data fields used which are mostly in the Selection Criteria. (e.g. date ranges)

When I remove the Synonym and input its data manually the report runs in BOE in a matter of seconds as it does in CR WITH the Synonym...thus it def seems the synonym is slowing it down. In BOE, it seems to be behaving not like a synonym, but like an actual 2nd connection to another database in the report.

- I've tried creating a couple different versions of this report with the same database and synonym setup as a test, but all run slow in BOE(10-14 mins)

- I've tried setting BOE to output the file to both email recipient and to a specific location on the network to see if maybe the delivery was the issue but both were 10+ mins.

I'm stumped as to why CR on my desktop can display the report in 5 seconds, but it's taking BOE 12 mins to run the same report.

Any thoughts or ideas are appreciated

 

[RTag]

Do you have subreports ?

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[crogers111]

There is 1 subreport in the RH to pull in just the client name from a View in the same DB. (allows the client name to display if 0 records are returned in the main report)

I had run the report in BOE With this Subreport but Without the Synonym and it took just a few seconds.

I went ahead and removed this subreport and Kept the synonym and ran it in BOE just to see. It still takes 10+ mins...so the 1 subreport is def not affecting anything.

 

[hilfy]

What type of connection are you using to connect to the database? Is the database client version the same on the server as it is on your workstation? If it's ODBC, are the connections set up exactly[/] the same in both places? You could try temporarily installing Crystal on the BO server and running the report from there in Crystal to rule out database client/ODBC config issues.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[crogers111]

Connection is OLE for SQL (ADO).

BO Server had an older version of the SQL Server driver and didn't have the SQL Native Client. SQL Client was installed but didn't change anything.

IT director is reluctant to install CR on the BOE server as he doesn't want to 'break' anything with BOE...so I can't test.

I've been poking aroun the CMC to see if stumble on any settings that may make a difference but no luck yet.

 

[hilfy]

Crystal and BO play quite well together. If it helps, installing Crystal on the report server is also one of the things that tech support will ask you to do if you call them.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Turkbear]

Does the account that the BO system uses to run (especially the report and job servers)and the user/pass combo it uses have the same access rights to the database and its location as the one used by CR?



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