Crystal Report Slow in VB

[Varco]

I have a complex report created in V8.0 that still executes reasonably quickly from the designer, the parameter entry is instant, the preview window is displayed in under 2 seconds. This same report when compiled into VB V6.0 is displayed quickly from within VB. However if the distribution files are created and the program installed on another system, it takes almost a full minute before the Crystal Parameter Entry is displayed, and the report takes several minutes to execute. Any thoughts or suggestions? Jim Varco
Varco Consulting
varco@compuserve.com

 

[MalcolmW]

What database structure are you building your report on, Jim? Could you paste a copy of the SQL statement that Crystal is using, from Database|Show SQL statement (assuming that you are using ODBC, query files or SQL - otherwise this option is greyed out)?

Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[Varco]

Thanks for your response Malcolm. This report will run against either a SQL or Visual FoxPro database using ODBC. Following is the SQL statement you asked for, however the bulk of the real processing is done in subreports, of which there are many. Testing shows the report executes nicely in the Crystal Designer, from within the VB environment, from the distribution files when installed on the machine it was developed on (Crystal and VB installed) and the same distribution files when installed on a system that does not have Crystal or VB runs extremely slowly. I am now trying to determine exactly which DLL's I need to make sure are included with the distribution package. I have a list of the ones the report is using from the About box in Crystal, however many of themn are system and or part of the Crystal Designer.


SELECT
jhead."jhhist", jhead."jhjob", jhead."jhrqdt", jhead."jhpart", jhead."jhrev", jhead."jhdesc", jhead."jhpqty", jhead."jhssdt", jhead."jhsddt", jhead."jhplan", jhead."jhschd", jhead."jhrelf", jhead."jhpcmp", jhead."jhcmpd", jhead."jhclsd", jhead."jhqtys", jhead."jhqtyi", jhead."jhqtyc", jhead."jhquot", jhead."jhqtln",
jjasm."jyjob", jjasm."jylevl", jjasm."jypart", jjasm."jyrev", jjasm."jyum", jjasm."jydesc", jjasm."jyextd", jjasm."jypqty", jjasm."jynote",
rcshp."rscust", rcshp."rsname",
qdqty."qsseq", qdqty."qsqty", qdqty."qsscrp",
rslsp."rrname"
FROM
{ oj ((("jhead" jhead INNER JOIN "JJASM" jjasm ON
jhead."jhjob" = jjasm."jyjob&quot
LEFT OUTER JOIN "qdqty" qdqty ON
jhead."jhquot" = qdqty."qsquot" AND
jhead."jhqtln" = qdqty."qsline&quot
LEFT OUTER JOIN "RCSHP" rcshp ON
jhead."jhcust" = rcshp."rscust&quot
LEFT OUTER JOIN "rslsp" rslsp ON
rcshp."rsslsp" = rslsp."rrslsp"}
ORDER BY
jhead."jhjob" ASC,
jjasm."jylevl" ASC Jim Varco
Varco Consulting
varco@compuserve.com

 

[MalcolmW]

Regarding dll's for distribution, the most elegant solution is a free MS utility called Dependency Walker, available from

http://www.dependencywalker.com.

Regarding your performance problem - actually, the report's performance problem, Jim. I would be tempted to troubleshoot it by
1) developing a very simple report using just one database type, and creating a distribution package - checking it with dependency walker.
2) add the option of a second database type, distribute and test
3) add a parameter, distribute and test
4) add a subreport, distribute and test
5) substitute the real report, but with most of it suppressed so that it is quick
6) start unsuppressing parts of the real report, and see when the slowdown occurs.
Try to find the bottleneck - I certainly don't see anything obvious, so I would get something simple to work well first, and then move forward towards what you have now.
Sorry for the slow response - real life events have been more engaging than internet based ones for the last few days.
Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[Varco]

Malcolm

Thanks again for the help. I have Dependency Walker, I will try it on this report. I copied a few of the dll's in the Crystal shared folder to the test machine (no VB or Crystal) and performance improved significantly. I would like to identify just what dll's are the culprit and am going to work on that. Jim Varco
Varco Consulting
varco@compuserve.com

 

[MalcolmW]

I'd like to hear of your analysis of the problem and solution - sounds like you have it well in hand now though, although that could be my hopeless optimism showing. Usually when dll's are missing/outdated/wrong location, the report will refuse to run altogether. This makes the problem easier to fix in some ways. Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net