Performance issues with SQL

[mgallot]

We have many ver 8.5 reports that have SQL inserted into the report. They are not linked to stored procedures, they are just raw sql inserted into the SQL that Crystal creates. The developer who created these is no longer here, and I dont understand why they were created in this fashion.

We are using ODBC connections to an AS400 DB2 database. The problem is that the reports are so slow that they time out, or take hours to run. Unfortunately, the users have become so used to the reports taking literally 4 hours, that if it finishes in 'only 3 hours' they are happy. I have recreated some reports using Crystal features, and the performance time went down to just a couple of minutes.

I am trying to figure out exactly why the inserted sql just doesnt perform in an acceptable manner. There is so much of it, it would not be feasible to post here, but does anyone have a good understanding of the the layer structure in Crystal and where sql typically fails?

I'm looking for a more general opinion of development methods and efficiency.

Thanks so much.

 

[Luvsql]

When you say "raw sql inserted" what does this mean ie are they using a repository file with complex sql syntax within it or just sql tables? If you go to database > show sql query, does this syntax run fast outside of crystal?

I currenly use tables, sql views or stored procedures and a complex report has never taken more than 1 minute, accessing 5Gb of data.

What version of Crystal are you using? I'm only familiar with MS Sql server.

 

[mgallot]

It is SQL that is written in a separate program and the report points to it. So, you cannot click on 'show sql' and view it, the sql is contained in a separate file. The tables are DB2. The SQL is not efficient outside of the report if you run it standalone or in another application either. The developer of this SQL convinced management that the only way to write this complex report was to write this SQL outside of the report. I don't buy that, and have proved it by rewriting some reports. The method that was previously used to write these reports seems to be in conflict with the reason Crystal Reports exists. I cant pinpoint exactly how the concept is inefficient in the way it accesses data (even if the sql was good), but I know that one or more of the layers does not like this SQL. I guess its the whole theory that is in question.

 

[synapsevampire]

It doesn't sound like you understan how the process works, so I'd lean towards the existing developers solution.

Having SQL in "another program" probably translates to a recordset is created in by code and passed to a report.

It obviously isn't required, and replacing the process is relatively simple if you can obtain the SQL and creaate Views or SP's from each.

Part of what they were also resolving was the viewing issue, without the code you'd have to have some means for users to view the reports, either using the distributable EXE method, which isn't supported in subsequent versions, use a 3rd party viewer, or go with Crystal Enterprise.

-k

 

[mgallot]

Any Crystal report that takes between 4 and 8 hours to execute, or simply times out is not a good solution to lean toward. Just because something pre-exists doesnt mean that it is a good solution. The problem is that the developer did not understand Crystal Reports, (or efficient use of SQL) that is why I can re-write the reports and change the run time to just minutes. The developer was a consultant, and was paid by the hour.

I have seen the explanation as to why SQL takes longer to complete than the SQL that Crystal writes in this forum before. I just cant find it today. One of the frequent users was the person who gave the explanation, and it was a Crystal trainer.

 

[mgallot]

I found out that the original SQL was written using the Seagate Query client. However, the developer simply write his own code into the .qry file and did not use the editor. Then the Crystal .rpt was created using the .qry file. This was originally written in version 7.0.

 

[mgallot]

I found a couple of answers to the problem. These are from the Seagate technical database.

- Passing log on information through Crystal Queries or Dictionaries may lead to complex connectivity issues. Saving a query file with no data (in the SQL Designer under the 'File' menu, the 'Save Data with Query' item is not selected) may lead to problems with passing log on information to the server database. If you are refreshing a report to the server database every time it is run, then a Crystal Query or Dictionary will add an extra layer for passing log on information to the server database. This also effects how quickly records are read from the server database.

· Crystal Queries or Dictionaries cannot be used with subreports when log on information is required.

· You cannot convert a report's database driver when reporting off a Crystal Query or Dictionary. For example, a report originally created off a QRY file cannot be changed to report directly off an Oracle table or view. The report must be recreated.


--------------------------------------------------------------------------------

Category:
Web

 

[synapsevampire]

Seagte hasn't been involved with Crystal for years.

The Crystal query designer isn't even suggested by Busines Objects (current owner) these days, and creating Views or Stored Procedures is what I always suggest.

I had misread the post thinking that the report was running in minutes, you're correct in rewriting the reports, but offload the processing to the database wherever possible.

-k

 

[fredp1]

It sounds like your after the following FAQ
Optimizing SQL Pass Through using the Record Selection Formula
faq767-3825 by synapsevampire

Fred

 

[fredp1]

How did you get with this problem.

I also use the AS/400 but donot have any performance problems.

Cheers