Why is PERVASIVE ODBC performance so slow with Crystal Reports..?

[MJRBIM]

I am using CR-10 (PRO) and Pervasive ODBC Engine interface 9.50.77.02

Simple report requests take forever to process, and the w3dbsmgr.exe process runs up to 96% of my CPU while they are processing.

Which leaves me with 3 questions...

1.) Why is PERVASIVE ODBC performance so slow with Crystal Reports..?

2.) Is this normal for PERVASIVE ODBC performance...?

3.) Any tricks or work-arounds to improve performance...?

Thanks in advance!

 

[mirtheil]

1.) Why is PERVASIVE ODBC performance so slow with Crystal Reports..?

Short answer is that it depends. It could be a poorly designed query. It could also be an incorrect definition in the DDF files causing the engine to read all of the records instead of an index.

2.) Is this normal for PERVASIVE ODBC performance...?

Normally, no. It is possible and depends a lot on your database.

3.) Any tricks or work-arounds to improve performance...?

Make sure your DDFs are consistent (run a DB Check).
Make sure your SQL is optimized. Run it in the PCC. Is it slow there too? Use the Query Plan Viewer to determine if the query is using an index.

You would need to post more information about the problem before we could give anything more specific. For example, we'd need to know how big the database is, whether it's local or remote, what OS is being used, what kind of CPU/Disk subsystem is used, the actual SQL in use, etc.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[MarkatLMFJ]

I'm not working on crystal reports yet but I'm having dreadful query performance through ODBC for what should be a simple query (and I've not put the first join in it yet either) but since the schemas of the database are a 2NF at best and attempted 3NF showing a sequence of development over years (and really no attention to proper data types) theres not a lot I should expect but where do I find the Query Plan Viewer. I use the equivalent tool heavily in MS-SQL especially for tuning indexes and optimizing queries but I've not found the tool on my machine (or my server).

currently we are on Pervasive 8.5, I have the 8.7 client on my machine, I'm upgrading to 9.5 on Tuesday morning.

--Mark

 

[mirtheil]

The first thing I would suggest would be to run a database consistency check (right click the database in the PCC and select Tasks then Check Database). That's for 8.x. For 9.x, you'll need to download the consistency checker from the Component ZOne (

http://www.pervasive.com/componentzone).

The Query Plan Viewer is kind of hidden. Here's a link to a PDF Pervasive sent me a while back:

http://test.mirtheil.com/files/qpv.zip.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[MarkatLMFJ]

Mirtheil, will running check database potentially harm anything?

 

[mirtheil]

No. The check database does not modify the database at all.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[MarkatLMFJ]

Thanks, I'm reading the query analyzer docs you sent the link for.......initial thoughts are making me think I need to find a new job

 

[mirtheil]

It's not as bad as it seems.. Post here.. We'll help..

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[pwesson]

I've been using Crystal for years and personally I think most of the problems are with Crystal. It just sucks at running queries on large dbs.

What we've had to do to make it not so unpleasant is use our application, in our case Delphi, to do all the querying and write the results out to a CSV file, from which we then use Crystal to report from.

Works much faster when Crystal just have to display the data...

Paul Wesson, Programmer/Analyst

http://www.ipaw.net

 

[MarkatLMFJ]

> It just sucks at running queries on large dbs.

Anything will work faster when it just has to display data; but that doesn't do much good when you need dynamic reports and everything else in near real time without a lot of steps or needing a programmer/analyst involvement.

I've used crystal on very large DBs (and when I say large DBs, I mean databases over 20gb which span multiple raid volumes on clustered servers -- granted not with pervasive) and the performance of the report is limited by how fast the data can actually be joined together, how many sub queries and lookups are necessary. You have to start carefully analyzing indexes and how your tables are being joined, are you doing row scans or index scans, are you using nested loop joins other types of joins, do you have views which have the capability of their own indexes, do you build indexes where their only purpose serves to enhance a certain query (or part of a query's) performance. Crystal is very very powerful, I'm not the best at it in just laying things out, I've worked with the reports and done the query tuning; a sloppy schema and a sloppily written query can make all the difference in the world; if those are running on top of a DBMS which has "issues" with complex queries you can expect slow crystal performance (even just to run the queries through a straight up query tool even).

ML