Performance issue with big CSV files as data source

[Former Member]

Hi,

We are creating crystal reports for a large banking corporation with CSV files as data source. For some reports, we need join 2 csv files. The problem we met now is that when the 2 csv files are very large (both >200M), the performance is very bad and it takes an hour or so to refresh the data in Crystal Reports designer. The same case for either CR 11.5 or CR 2008.

And my question is, is there any way to improve performance in such situations? For example, can we create index on the csv files? If you have ever created reports connecting to CSV, your suggestions will be highly appreciated.

Thanks,

Ray

[Former Member · Accepted solution]

Ray,

Short answer is no.

A CSV file is really nothing more than an organized text file. The reason your performance is so bad... You are taking 2 HUGE text files and using a workstation PC & CR to to compare them. The PC isn't designed for that and neither is CR.

If you want to improve performance. Download a copy SQL Server Express and load the CSV files into SQL Server tables. Let SQL Server do the number crunching... Even if you do the whole thing, including SQL Server, on a PC you will experience DRAMATIC increases is speed... In fact it will probably take longer to load than it would to process.

You can also try using MS Access for similar results.

HTH,

Jason

[Former Member · Accepted solution]

Hi Jason,

Thanks for the reply. Yes, loading so much data from two so large files into memory and join them in memory is too heavy a task for CR designer.

The CSV files are extracted from the customer's enterprise database and placed in a certain place for Crystal Report's access.

Importing data in the CSV files into another database or MS Access again may solve this problem, and we're also trying to persuade the customer to adopt this way out. The problem with this solution is that the project is to go production, and this solution has to change the entire architect.

We are also trying to ask the user to view the report from InfoView instead of CR Viewer to take advantage of schedule ability. In the meantime, trying to find some method to decrease the CSV file size.

Thanks,

Ray

[Former Member · Accepted solution]

Ray,

Is there a good reason that the files are being generated at all, as opposed to connecting CR directly to the database?

Jason

[Former Member · Accepted solution]

For example, security. The customer don't want any vendor to touch their enterprise database, either in test or in production env.

-Ray

[Former Member · Accepted solution]

Certainly a reasonable concern...

The question at this point is, How are the reports going to be used and deployed once they are in production?

I'd look at it from that direction.

For example... They may be able to dump the data directly to another database on a separate server that would insulate the main enterprise server. This would allow the main server to run the necessary queries during off peak hours and would isolate any reporting activity to a "reporting database".

This would also keep the data secure and encrypted (it would continue to enjoy the security provided by an RDBMS). Text & csv files can be copied, emailed, altered & deleted by anyone who sees them. Placing them in encrypted .zip folders prevents them from being read by external applications.

<Hope you liked the sales pitch I wrote for you to give to the client... =^)

If all else fails and you're stuck using the csv files, at least see if they can get it all on one file. Joining the 2 files is killing you performance wise... More so than using 1 massive file.

Jason

[Former Member · Accepted solution]

In production env the reports are deployed to a BOE system. The users cannot access InfoView directly - instead, they access their portal and views a report via OpenDocument.

I see 2 approaches from your reply. One is to dump the data from the enterprise db to another one during off peak hours and connect to this db from Crystal Reports, the other is to join the two large CSV files into one. Are they?

Well, it seems impossible to persuade the customer to install another db and re-create all reports as the architect will be changed in this way. We are now looking into how to join the two CSV files before CR, and how to split the CSV files into smaller ones.

Thanks,

Ray

[Former Member · Accepted solution]

Ray,

You are correct about the 2 different approaches... A 3rd approach that shouldn't be overlooked either... Do nothing. If the users are simply accessing cached copies of an executed report, and not executing the report itself, just schedule the thing to run at night. Who cares how long it takes to complete as long as it's available the following morning.

I'd just look at dropping the the csv files on the BOE server so that it's not having to pull data across a network while it's processing and then leave it that.

As a consultant, all you can really do is compile a list of suggestions with their cost estimates along with the associated benefits and short-comings.

Jason

[Former Member · Accepted solution]

Thanks for all your suggestions and hope to see your great insights on future topics I've marked this thread as solved and awarded points.

-Ray