Running report initiates update stats

[beardenb]

On a few occasions I have run reports which have initiated update statistics on a table referenced in the query CR has created. This most recently happened this morning when my coworker clicked refresh on a report and update statistics was started on one of the tables contained in the report query. The table is about 150 million rows so we'll be waiting a while for it to complete.

We are using CR9 with fairly recent (2 months old) hotfixes applied and the database is Informix 9.4. The report in question is a CR8.5 report, which may or may not have anything to do with it. We checked with the original developer of the report and she confirmed that she definitely had nothing along the lines of an update stats contained in the report. Maybe we're missing a setting somewhere in CR9. Our DBA says that the database itself wouldn't start update stats without someone or something issuing the command to tell it to.

We contacted Crystal support and they wanted to see the query. We tried to Show SQL Query... to retrieve the SQL, but this resulted in another session launching, also running update stats on the same table! They had nothing to offer at this point and we're pretty much in wait mode until the update completes, probably around 24 hours from now.

Any insights into how this might be happening would be appreciated.

--Bearden

 

[vidru]

Open the report in CR 8.5 if you can, and on the Database menu, make sure that 'Verify on Every Print' isn't checked. I don't have CR 9 here, but I believe it's still there if you don't have CR 8.5.

-dave

 

[ChuckVanT]

I work with Bearden and was the one to fire off this "report". I checked, and on the Database menu in CR9, there is no 'Verify on Every Print'. However, there is, on the File->Options->Database tab a 'Verify when Database Driver Upgraded'. I am using a different DSN than the original developer used and I suspect that this may be the issue.

While on the phone with Business Objects, I tried to view the query, twice. Each time, I had to select the DSN and another Update Statistics was initiated.

Thanks, in advance, for all help!

Chuck

 

[vidru]

Also, try unchecking 'Verify on First Refresh', which does the following according the the CR 9 help files:

The Verify on First Refresh command triggers the Verify Database command the first time you refresh your report data per session.

-dave

 

[beardenb]

Thanks for your input Dave. What you say points to the verify database action as the source of our problem, but my understanding is that verify simply compares the table/column structures stored in CR to what's actually in the database. Why/how could that cause update stats to kick off?
--Bearden

 

[vidru]

Well, not knowing anything about Informix is hindering my possible input... I'm just trying to think of anything that would be looking at the table structure from within the report itself, not that it should be trying to run an update stats.

Has Business Objects been helpful?

-dave