Missing Indexes when running reports..

[Former Member]

I wrote a CR Viewer using the CR for VS components a few years back for an ERP system. We have noticed recently that reports are running slower than they should. After looking into the issue and testing a few things, the reports all seem to have correct indexes but when I run the reports and specify to save a copy of the report, when I open the copy of the report in CR all the indexes are gone. I believe the reports are not actually using indexes when they are running. Any idea why the Viewer wouldn't be able to read indexes in the reports? If I run the reports directly from the CR designer, they run fine, just slow and no indexes when using the CR for VS components. The database is Btrieve.

[former_member11696 · Accepted solution]

Hi James,

I have never heard of that before.... What has changed recently? Updated OS, Pervasive Server/Group engine or ???

How are you connecting to the DB, Native or ODBC or OLE DB?

Do the DDF's exist in the same location as the Data files?

Are you changing location at runtime?

Is it possible to get the report, DDF's and data files to do a quick test here?

Don

[Former Member · Accepted solution]

Don,

Thanks for the reply. Nothing has changed that I can pinpoint as an issue. I have noticed this just responding to support calls over the last 6 months where someone would indicate a report was running slow, I would open the report in Crystal 2011 and notice the indexes were missing. Tables and links were their but no index indicators. I would use the set datasource location to replace the tables and the indexes would reappear.

This is using Native Btrieve to connect and computers are running CR for VS V13.0.5. Pervasive is V12.

DDF's are not in the same location as the data and we are changing the location at runtime.

What I have noticed recently is that my reports have the indexes but when look at the copy created by the viewer at runtime it is missing the indexes. The reason for looking into this in the first place was users reporting that reports are really slow. I was assuming that the missing indexes in the copy of the report might indicate that the reports themselves are not using indexes when they are running but after testing yesterday, I have a new theory.

The reports seem to be slow in loading, not accessing the data. In other words I can take the same report and have it read one record or have it read thousands of records and the time to load and display the report is nearly the same. The issue is that depending on the reports, they seem to take between 30 -60 seconds to load for an average report. That a long time when you are just trying to display one sales order or one customer record.

I did another test and created as simple a report as possible. Single table, reading one record with 3 detail fields on the report. No headers, formulas, sorts or anything and that report takes about 8 seconds to load.

I've researched similar issues on the forum and checked the default printer setting and also tested to see if it is just slow the first time and it is the same speed all the time. Even if I have a report open in another viewer (assuming that has all the CR modules loaded)

Just not sure how I can speed up the reporting process.

Jim

[former_member11696 · Accepted solution]

Hi Jim,

CR for VS is up to SP 15 now, you may want to update also, we did a few updates to the Btrieve drivers.

Ah, you said the ddf's are not in the same location. CR assumes they will be or the location of the data files is specified in the ddf's.

If they are not then when setting the connection info you should be doing this:

CrystalDecisions.Shared.TableLogOnInfo tLogonInfo;

foreach (CrystalDecisions.CrystalReports.Engine.Table rptTable in rpt.Database.Tables)

{

    tLogonInfo = rptTable.LogOnInfo;

    tLogonInfo.ConnectionInfo.DatabaseName = @"C:\ProgramData\Pervasive Software\PSQL\file.ddf";

    tLogonInfo.ConnectionInfo.ServerName = @"C:\ProgramData\Pervasive Software\PSQL\";

    tLogonInfo.TableName = rptTable.Name;

The location of the ddf's is the DatabaseName and the location of the data files is ServerName

So why this changed I have no idea... may have something to do with Pervasive 12

The reason it is slow is only because CR can't find the index.ddf.

Don

[Former Member · Accepted solution]

Don,

When I specify the DatabaseName, do I have to specify he DDF name or should this just be the path to the directory where the DFF's are located? Strange thing is that I have never specified a server only a Database Name and the Database name was my table location (directory) not the DDF location. The DDF location was setup in the report and wouldn't change, so I only specified the data location via the DatabaseName. Not sure how my way would have worked if above is how it should be.

Jim

[former_member11696 · Accepted solution]

There have been changes in the driver and SDK's.

Did you use my code to see if it works for you?

Don