Performance of CR-Oracle vs Access-Oracle

[SonOfZeus]

I've found that in running the same selection routine in Access to Oracle and CR to Oracle, Access performs the work in 1/2 the time. Is there some setting in CR that improves the performance. Currently I had both simply displaying all details, so the comparison was fair:

Crystal Reports:

SELECT
TAMSEXPENSE."CIRCUITID", TAMSEXPENSE."PERIOD", TAMSEXPENSE."AMOUNT"
FROM
"NTO1"."TAMSEXPENSE" TAMSEXPENSE
WHERE
TAMSEXPENSE."PERIOD" = 200303
ORDER BY
TAMSEXPENSE."CIRCUITID" ASC,
TAMSEXPENSE."AMOUNT" ASC

Access:

SELECT NTO1_TAMSEXPENSE.CIRCUITID, NTO1_TAMSEXPENSE.AMOUNT
FROM NTO1_TAMSEXPENSE
WHERE (((NTO1_TAMSEXPENSE.PERIOD)=200303))
ORDER BY NTO1_TAMSEXPENSE.CIRCUITID, NTO1_TAMSEXPENSE.AMOUNT;

 

[synapsevampire]

What version of Crystal? Newer versions are generally faster.

What type of connectivity to Oracle? A direct connection is faster.

And are you comparing the full data load time, or the time it takes to initially display data? Are you creating a report in Access, or just comparing a linked table return time?

I wouldn't be surprised if Access is faster, but I wouldn't expect half the time.

-k

 

[SonOfZeus]

I'm using Crystal 8.5 and Access 97

 

[SonOfZeus]

Sorry, I'm using basic ODBC connection to Oracle. I will look at direct, but thought this was a good benchmark using the same for each. No reporting, simply displaying query results, am going to end in access. Both are returning approxiamately 1 million records. Just tried the group on server and no effect, simply sorting both ASC for this test.

 

[synapsevampire]

Group on server won't do anything if you're just dumping 1 mil rows.

Your comparison isn't really fair unless you test against an Access report.

Crystal is by far the superior report writer, and Access is by far the superior programming language, plus it has a database engine, so it does many things faster.

Querying a database isn't one of them, the database server dictates everything. Access will display linked data much faster than Crystal can present data in a report, but keep in mind that Crystal is doing a whole lot more - formatting, pages, etc.

I use Access to poke around in disparate databases, but I would never use it as a report writer, generally I'm hired to convert them over to CR.

If you want real performance from your database, use Stored Procedures.

-k

 

[IdoMillet]

SOZ,

This is an interesting report and since you are using the same connection method, I would turn on the ODBC trace option in your ODBC setup and inspect the log file for differences in the handshaking going on.

Please let us know what you find.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

Ido: I don't know why you'd think this interesting, the comparison is ill conceived.

Crystal does a multi-pass on data because it allows for more sophisticated functionality, not just querying data. The results should be pointless in any real systems analysis because you're comparing what a report takes vs. what a single query does.

And a 1 mil row query is kinda silly, not a real world example of a useful data set.

I went through a similar analysis at HP 2 years ago. The person advocating Access lost their contract about a month after I joined, as I had to redo all of the spaghetti Access reporting into something scalable and maintainable.

I suggest that if you have Oracle, use SP's to summarize data (or a data warehouse), and use the most flexible report generation tool available, which arguably at this point is Crystal.

-k

 

[IdoMillet]

Your points are well taken, but finding out where exactly is the time diference coming from (is it due to a different hand shaking during the data retrieval or is it due to formatting time) is still interesting. Or maybe I'm just terminally curious...

Cheers,
- Ido



CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[SonOfZeus]

Thanks for the feedback, was greatly appreciated.

I simply used this simply query to get a baseline reference, knowing ODBC isn't the fastest connection or utilizing the Oracle system. I do have to find another way of connecting Crystal Reports directly to Oracle, already use a BDE for Delphi in another project.

As for access being the wrong package, can't say I disagree, but the product was purchased years ago and imports various clients external files into the database. Currently there are better than 30 million records in the one table alone.

The user having me investigate doesn't have access to write stored procedures, or the knowledge. I will be using Crystal to report if for no other reason the drilldown routines. Access will still be used for the purposes it was created, importing files (including parsing) and basic reporting which has been built in.