Crystal Reports and Performance issues
Fellow ARSListers, I have been trying to track down a problem with performance. We appear to have located the problem coming from our Crystal Reports server than runs multiple reports against Remedy every day. I have the option enabled to put large result buffer entries in the Thread log file. My goal was to see large queries being executed against the Remedy database. What I found was that several entries in the log file from the login used by our Crystal Reports server. Has anyone see this behavior before? It appears as if Crystal Reports is sending the same query over and over again to Remedy for processing. I have seen a single report generate 3 dozen similar entries to what I have listed below. The report also eats up over half of the CPU's processing power while this report is running. THRD /* Mon Sep 13 2010 16:29:17.2030 */ Thread Id 3100 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134939/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:20.2650 */ Thread Id 2944 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134950/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:23.3440 */ Thread Id 4836 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134962/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:26.4690 */ Thread Id 4604 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134973/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:29.5630 */ Thread Id 1256 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134987/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:32.6560 */ Thread Id 2552 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134998/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:35.7030 */ Thread Id 2616 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135010/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:38.7500 */ Thread Id 5340 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135023/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:41.8130 */ Thread Id 5904 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135034/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:44.7350 */ Thread Id 3296 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135049/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:47.6880 */ Thread Id 5492 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135060/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:50.6880 */ Thread Id 3512 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135072/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:52.9230 */ Thread Id 3668 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135083/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:54.7660 */ Thread Id 2692 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135095/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:57.7980 */ Thread Id 5252 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135106/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:30:00.8920 */ Thread Id 4908 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135117/User: entp_report/Form: HPD:Help Desk_SLA/ Any insight or assistance to resolving why Crystal is doing this would be much appreciated. I have used Crystal Reports in the past, but have never been aware of any behavior like this. However, all of my tests point to the reports being run through Crystal as the source of our performance issues. James Van Sickle Senior Remedy Developer Office: 972-308-4090 Mobile: 214-263-9340 _ This email and its attachments, if any, are intended for the personal use of the named recipient(s) and may contain confidential, privileged, or proprietary information. If you are not a named recipient, or an agent responsible for delivering it to a named recipient, you have received this email in error. In that event, please (a) immediately notify me by
Re: Crystal Reports and Performance issues
If the Crystal report in question contains sub reports then you will get multiple queries. If the main reports result set contains lots of records then you will get as many SQL calls for each of the sub reports. This is not out of the ordinary. On Sep 13, 2010, at 5:53 PM, Van Sickle, James W james.vansic...@cmc.com wrote: ** Fellow ARSListers, I have been trying to track down a problem with performance. We appear to have located the problem coming from our Crystal Reports server than runs multiple reports against Remedy every day. I have the option enabled to put large result buffer entries in the Thread log file. My goal was to see large queries being executed against the Remedy database. What I found was that several entries in the log file from the login used by our Crystal Reports server. Has anyone see this behavior before? It appears as if Crystal Reports is sending the same query over and over again to Remedy for processing. I have seen a single report generate 3 dozen similar entries to what I have listed below. The report also eats up over half of the CPU’s processing power while this report is running. THRD /* Mon Sep 13 2010 16:29:17.2030 */ Thread Id 3100 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134939/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:20.2650 */ Thread Id 2944 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134950/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:23.3440 */ Thread Id 4836 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134962/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:26.4690 */ Thread Id 4604 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134973/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:29.5630 */ Thread Id 1256 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134987/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:32.6560 */ Thread Id 2552 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134998/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:35.7030 */ Thread Id 2616 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135010/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:38.7500 */ Thread Id 5340 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135023/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:41.8130 */ Thread Id 5904 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135034/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:44.7350 */ Thread Id 3296 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135049/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:47.6880 */ Thread Id 5492 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135060/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:50.6880 */ Thread Id 3512 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135072/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:52.9230 */ Thread Id 3668 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135083/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:54.7660 */ Thread Id 2692 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135095/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:57.7980 */ Thread Id 5252 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135106/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:30:00.8920 */ Thread Id 4908 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135117/User: entp_report/Form: HPD:Help Desk_SLA/ Any insight or assistance to resolving why Crystal is doing this would be much appreciated. I have used Crystal Reports in the past, but have never been aware of any behavior like this. However, all of my tests point to the reports being run through Crystal as the source of our performance issues. James Van Sickle Senior Remedy Developer Office:
Re: Crystal Reports and Performance issues
Is it doing any tablescans? I see gfwe but what are the queries. Can you index to help some? Is it the same table? Is it possible to pin that in memory oracle wise to help with io. Just wondering.. You might have increase oracle memory space to allow oracle to breath. Or is the ms? Sent from my iPhone On Sep 13, 2010, at 6:43 PM, Frank caruso.fr...@gmail.com wrote: ** If the Crystal report in question contains sub reports then you will get multiple queries. If the main reports result set contains lots of records then you will get as many SQL calls for each of the sub reports. This is not out of the ordinary. On Sep 13, 2010, at 5:53 PM, Van Sickle, James W james.vansic...@cmc.com wrote: ** Fellow ARSListers, I have been trying to track down a problem with performance. We appear to have located the problem coming from our Crystal Reports server than runs multiple reports against Remedy every day. I have the option enabled to put large result buffer entries in the Thread log file. My goal was to see large queries being executed against the Remedy database. What I found was that several entries in the log file from the login used by our Crystal Reports server. Has anyone see this behavior before? It appears as if Crystal Reports is sending the same query over and over again to Remedy for processing. I have seen a single report generate 3 dozen similar entries to what I have listed below. The report also eats up over half of the CPU’s processing power while this report is running. THRD /* Mon Sep 13 2010 16:29:17.2030 */ Thread Id 3100 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134939/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:20.2650 */ Thread Id 2944 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134950/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:23.3440 */ Thread Id 4836 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134962/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:26.4690 */ Thread Id 4604 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134973/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:29.5630 */ Thread Id 1256 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134987/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:32.6560 */ Thread Id 2552 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 134998/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:35.7030 */ Thread Id 2616 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135010/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:38.7500 */ Thread Id 5340 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135023/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:41.8130 */ Thread Id 5904 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135034/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:44.7350 */ Thread Id 3296 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135049/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:47.6880 */ Thread Id 5492 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135060/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:50.6880 */ Thread Id 3512 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135072/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:52.9230 */ Thread Id 3668 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135083/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:54.7660 */ Thread Id 2692 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135095/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:29:57.7980 */ Thread Id 5252 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135106/User: entp_report/Form: HPD:Help Desk_SLA/ THRD /* Mon Sep 13 2010 16:30:00.8920 */ Thread Id 4908 (GLEWF) large result buffer allocation - /Length: 1505110/Entries: 10729/Client Ver: =10/RPC ID: 135117/User: entp_report/Form: HPD:Help Desk_SLA/
