Live reports and web reports add too much overhead to my execution time

Hi:I'm finding that my stored proc only takes 30s when run in Query Analyzer. However, with the exact same filters selected, the same query takes 55s after I run it in a live report and a whole 2 mins when I run in in a webreport. Can someone help me out?Please find attached the query, live and webreports that I'm using.

Find more posts tagged with

Comments

Tim_Hunter

I wont address the stored proc, there are many people with better tsql then me, and its been awhile.Livereport:I assume when you test in Query Analyzer you are using the same parameters as you are submitting in your Livereport? Is query analyzer on the database server or on the liverlink server?WebReport:One glaring comment, dont use the REPLACE tag for undefined results, use DEF, replace does a replace all and will try to change all occurances in every result, I imagine that should cut down the WR processing time to much closer to the LR time. Be aware the WR will appear to take longer no matter what, the client needs to render the output after the server writes it.

Kristy_Shen

Hi Tim:LivereportYes, I am using the same params in Query Analyzer and the livereport. The query analyzer is on the database server, not the livelink server.WebreportThanks for the suggestion! I will try it out and see if that help the exec time. Kristy

Kristy_Shen

Hi Tim:I tried your suggestion for the WR by using DEF:"" instead of REPLACE:?:"". However, it still took around 2 mins. (I think it only brought down the exec time by 5s or less)Do you have any other suggestions please?Thanks, Kristy

Lee_Butler

Message from Lee Butler via eLinkHi Kristy,IF statements in the row section are typically expensive on large resultsets.Could you try replacing this statement:[LL_WEBREPORT_IF "[LL_reptag_&option /]" == "report" /] [LL_REPTAG=Control_ID /][LL_WEBREPORT_ENDIF /]With a DECODE sub-tag to achieve the same? E.g:[LL_REPTAG=Control_ID /]Here the DECODE checks whether the option parameter is equal to 'report'if it is no style information is added - i.e. the link is visible. Inall other cases it is hidden.You could also try replacing:[LL_WEBREPORT_IF "[LL_reptag_&option /]" == "HTML" /] [LL_REPTAG=Control_ID /][LL_WEBREPORT_ENDIF /]With something like:[LL_REPTAG=Control_ID /]These two changes should get the performance much closer to theLiveReport. As far as the LiveReport itself goes if you have LiveReportExtensions installed (ships free with WR) you could try the turning off"Use Cursor Fetch mode". Please have a handle on how many results youexpect back before doing this - it sounds like you do if you've beeninvestigating with Query Analyzer.On a general note the performance you get from a WebReport is determinedby what it is doing. If you strip out all the HTML and logic andreplace with simple CSV a WebReport will run faster than the equivalentLiveReport. If you make the HTML and logic more complex than theLiveReport there is typically a cost. The key is usually to refine therow section as much as possible because this is repeated for each row ofdata returned. Things are much less sensitive in the header and footer.Let me know how you get on.Thanks,Lee.www.resonatekt.com __________ Information from ESET NOD32 Antivirus, version of virussignature database 3281 (20080718) __________The message was checked by ESET NOD32 Antivirus.http://www.eset.com

Kristy_Shen

Hi Lee:Thanks for your suggestion.I tried putting in the code you suggested. However it still took 2 mins to get the report results. I even commented out that entire line as well as any lines other than the tags to display the data. This didn't help either. We have the Beta version of the LiveReport extensions module. However when I turn off ""Use Cursor Fetch mode", I get this error message: "Livelink Error: WebReport [Error executing an Sql statement.]"As for stripping out all the html in the webreport and replacing it with a simple csv webreport, this is not an option because our users are expecting to see nicely formated results with links that they can click on.

Lee_Butler

Message from Lee Butler via eLinkSorry for the confusion Kristy - I wasn't at all suggesting that youreplace your report with a CSV - I was using it as an example to try toexplain what effect logic and formatting have on response times.I'm not sure why you're running a beta version of LiveReport Ext - doyou have a customization?I think the best thing to do is open a ticket with support (feel free tocopy me so that we can start looking at the issue right away). We needthe WebReports version and patch level, LiveReport Extensions version,Livelink version and patch level, database type, details of anycustomizations in the WebReports/LiveReports area.If you have WebReports 4.0 or later you can get this data by issuing thefollowing commend: ?func=webreports.sysreportThanks,Lee.-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Monday, July 21, 2008 4:55 PMTo: eLink RecipientSubject: RE-simplify the row sectionRE-simplify the row sectionPosted by Shen, Kristy on 07/21/2008 10:52 AMHi Lee:Thanks for your suggestion.I tried putting in the code you suggested. However it still took 2 minsto get the report results. I even commented out that entire line as well as any lines other thanthe tags to display the data. This didn't help either. We have the Beta version of the LiveReport extensions module. Howeverwhen I turn off ""Use Cursor Fetch mode", I get this error message:"Livelink Error: WebReport [Error executing an Sql statement.]"As for stripping out all the html in the webreport and replacing it witha simple csv webreport, this is not an option because our users areexpecting to see nicely formated results with links that they can clickon.[To reply to this thread, use your normal E-mail reply function.]============================================================Topic: Live reports and web reports add too much overhead to myexecution timehttps://knowledge.opentext.com/knowledge/llisapi.dll/open/14787818Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/llisapi.dll/open/Livelink_LiveReports_DiscussionLivelink Server:https://knowledge.opentext.com/knowledge/llisapi.dllTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.livereportsdiscussion@elinkkc.opentext.com. __________ Information from ESET NOD32 Antivirus, version of virussignature database 3284 (20080721) __________The message was checked by ESET NOD32 Antivirus.http://www.eset.com

Kristy_Shen

Hi Lee:Thanks so much for you helpful suggestions. I have spoken to my collegue and I have found that we are using the beta version because our current version of livelink is not compatible with the latest version of extensions. She had come across this issue before as well, and Open Text has said that it is because every line of sql is being wrapped in a transaction. With the latest version of livereport extensions, you can disable this option. (Beta doesn't have this working). We will be migrating to the 971 version of Livelink soon and it will have the latest version of the extensions module, so we should be ok after that.Thanks again!Kristy

Steven_Osborn_(sosborn@opentext.com_(Delete)_43869

Message from Steven Osborn via eLinkHi Kristy,What version of Livelink are you on now? LiveReport Extensions 3.0.1 (themost recent version) will support LL 9.5SP1 through to LL 9.7.1.Cheers,Steve.-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: 22 July 2008 19:12To: eLink RecipientSubject: RE-simplify the row sectionRE-simplify the row sectionPosted by Shen, Kristy on 07/22/2008 02:07 PMHi Lee:Thanks so much for you helpful suggestions. I have spoken to my collegue andI have found that we are using the beta version because our current versionof livelink is not compatible with the latest version of extensions. She had come across this issue before as well, and Open Text has said thatit is because every line of sql is being wrapped in a transaction. With thelatest version of livereport extensions, you can disable this option. (Betadoesn't have this working). We will be migrating to the 971 version of Livelink soon and it will havethe latest version of the extensions module, so we should be ok after that.Thanks again!Kristy[To reply to this thread, use your normal E-mail reply function.]============================================================Topic: Live reports and web reports add too much overhead to myexecution timehttps://knowledge.opentext.com/knowledge/llisapi.dll/open/14787818Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/llisapi.dll/open/Livelink_LiveReports_DiscussionLivelink Server:https://knowledge.opentext.com/knowledge/llisapi.dllTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Kristy_Shen

Hi Steven:Currently we are using 9.5SP1. The extensions version we have installed is: Livelink LiveReport Extensions BETA 2.0.1b. I'm not sure why we didn't install the latest version of the extentsions module since you're saying that it is supported by our current version of Livelink. I will ask my collegue and let you know.Thanks, Kristy

Kristy_Shen

Hi Steven:I have found that the reason we are using the beta version is not because the newest version is not compatible with our current Livelink version but because it was already installed in our system. The decision to update to the latest version has been postphone because of customized modules that we have.Since we are migrating to 971 anyway, we will wait for that and then get the latest version. Thanks for your help, Kristy