Access Performance Problem (need to filter records with Qeury?)

[DaveSw]

Hello,

I'm new to CR, and am having trouble with report performance. Here's what I've got: The report is using Access DB (using direct connect, but I've tried ADO/DAO), I've got a parameter field that I'm using in the record selection formula to limit the data to the records I want.

This all works, but it's way to slow! In preview mode data refresh I can see Crystal searching through 200,000+ records for the 20 I actually want!

How do I speed this up? Basically what I want is for Crystal to use a SQL query with WHERE, GROUP BY and ORDER BY clauses, but I can't find away to do this.

Any help is appreciated!

 

[rogar]

Write your query in Access Queries and use that as your report data source. You can find the query in the CR Data Explorer under DatabaseFiles/your.mdb, where the access query will be in the same list as the database tables.

Alternatively, use the Crystal SQL Designer to create your query as a .qry file and find it in the CR Data Explorer under Metadata/Query - Crystal SQL Query Files. The Crystal Query will use ODBC so may be slower than a direct link to your Access mdb.

 

[DaveSw]

Well, I'm already using an Access query as the data source for the report. CR still filters through all of the returned records instead of just using a WHERE clause.

Should I be using an Access Parameter query instead (and if so, how)? Seems like there were some issues with doing it that way.

 

[DaveSw]

I finally got CR to do what I wanted, so I thought I would post my results.

The problem was in how I was accessing the DB. Using a direct Access connection is (apparently) really slow because Crystal filters the records itself. Maybe it works better on a table with indexes, but I am using a Query as my data source.

So the solution I ended up with was using an OLEDB connection with the MS Jet driver. I tried using an ODBC connection, but for some reason Crystal couldn't read the db when I did that. With a SQL type connection (rather than PC database, in CR language), the Show SQL Query menu option was enabled, and Crystal used a speedy SELECT with a WHERE clause like I wanted all along.

So OLEDB (or any other access layer) may be slower performance than a direct connection, but the SQL support and faster record filtering turned out to be a LOT faster for me.