This particular report has "work info" records included, so there is a subreport, and therefor a join. No "memo" or blob in report or subreport.
However, what is happening is that I see a sql query to get all incidents. That happens very much in the beginning. Then it issues one sql query at a time to get work info record for that incident. Together with that, it sends queries to field_dispprop table, to get display properties of each field on the worklog form. There are 95 fields on worklog form (never mind that most of them are display only), so 75 separate sql to get display properties of 75 fields. Then when its getting worklog entries for the next Incident, it wants to do it all over again. See the sequence in api+sql log pasted at the end of this post. Looks like either the join is not being handled properly, or the ODBC driver is drunk. I think. <API > < 12:53:58.4762 */+GLEWF ARGetListEntryWithFields -- schema HPD:WorkLog from ARODBC (protocol 13) at IP address 10.10.1.12 <SQL > < 12:53:58.4776 */SELECT T949.C1,C1000000000,C1000000161,C3,C1000000159,C1000000157 FROM T949 WHERE (T949.C1000000161 = 'NAINC0011373214') ORDER BY 6 DESC, 1 ASC <SQL > < 12:53:58.4798 */OK <API > < 12:53:58.4815 */-GLEWF OK <API > < 12:53:58.4988 */+GLG ARGetListGroup -- user * from ARODBC (protocol 13) at IP address 10.10.1.12 <API > < 12:53:58.5014 */-GLG OK <API > < 12:53:58.6320 */+GLS ARGetListSchema -- changed since Wed Dec 31 19:00:00 1969 from ARODBC (protocol 13) at IP address 10.10.1.12 <API > < 12:53:58.9444 */-GLS OK <API > < 12:53:58.9755 */+GS ARGetSchema -- schema HPD:WorkLog from ARODBC (protocol 13) at IP address 10.10.1.12 <API > < 12:53:58.9764 */-GS OK <API > < 12:53:58.9929 */+GSF ARGetField -- schema HPD:WorkLog fieldId 7 from ARODBC (protocol 13) at IP address 10.10.1.12 <API > < 12:53:58.9941 */-GSF OK <API > < 12:53:59.0282 */+GLSF ARGetListField -- schema HPD:WorkLog changed since Wed Dec 31 19:00:00 1969 from ARODBC (protocol 13) at IP address 10.10.1.12 <API > < 12:53:59.0295 */-GLSF OK <API > < 12:53:59.0465 */+GMSF ARGetMultipleFields -- schema HPD:WorkLog # of fields 95 from ARODBC (protocol 13) at IP address 10.10.1.12 <SQL > < 12:53:59.0481 */SELECT fieldId,vuiId,propShort,propLong FROM field_dispprop WHERE (schemaId=949) AND (fieldId=1) ORDER BY 1 ASC,2 ASC <SQL > < 12:53:59.0501 */OK <SQL > < 12:53:59.0900 */SELECT fieldId,vuiId,propShort,propLong FROM field_dispprop WHERE (schemaId=949) AND (fieldId=2) ORDER BY 1 ASC,2 ASC <SQL > < 12:53:59.0915 */OK <SQL > < 12:53:59.0928 */SELECT fieldId,vuiId,propShort,propLong FROM field_dispprop WHERE (schemaId=949) AND (fieldId=3) ORDER BY 1 ASC,2 ASC <SQL > < 12:53:59.0943 */OK <SQL > < 12:53:59.0956 */SELECT fieldId,vuiId,propShort,propLong FROM field_dispprop WHERE (schemaId=949) AND (fieldId=4) ORDER BY 1 ASC,2 ASC --- On Tue, 2/10/09, Icarus4 <[email protected]> wrote: > From: Icarus4 <[email protected]> > Subject: Re: Somebody please run this ITSM 7 report for me > To: [email protected] > Date: Tuesday, February 10, 2009, 5:55 AM > Hello Rabi, > > I don't see your report file, however, do you have any > diary fields in it? > (worklog, Audit trail, etc) > > We have noticed that these type of fields (CLOB) are > killing the > performances when it comes to reporting. > > Try to remove them and run your report again just to see. > > On our side we do not publish reports containing those > fields anymore. We > only distribute them on request directly to the user and > with a notice about > performance. > > > > Rabi Tripathi wrote: > > > > Hi all, > > I have complaints of slow or never-ending reporting in > ITSM 7. After a lot > > of looking around, I am wondering if the inherent > design of the reports is > > a big factor. Such as a Join done in the report (with > a subreport), ODBC > > overhead etc. > > > > Can somebody run this report for me and tell me how > long it takes. If you > > can use Remedy User instead of a browser and do this > on a powerful box > > (may I suggest production?) that would be nice-- > > > > Incident Management Console->Reports > > > > On "ReportSelection" popup choose this > "Report Name": > > Incident->Incident Information->All > Incidents->Incicent Details By Date > > Range > > > > For "Start Date" and "End Date" > select dates that would give you at least > > 500 Incidents in your environment. Closer to 1000 > would be nice. > > > > Choose "Destination" = "Screen" > > > > Click "Run Report" and start your "Stop > Watch" > > > > On the prompt asking for titles, just click > "Ok" > > > > Let me know: > > (1) how long until the report completes...meaning > ReportViewer pop-up has > > finished loading all data (hint where it shows page > info like this "1/6+", > > presence of "+" means its still loading > more. > > > > (2) how many Incidents did you catch > > > > (3) does your report have "work info" > included for incidents? > > > > (4) Quick detail of your environment. The network path > between your PC and > > the server, how "strong" is the AR > Server/Database > > > > Ok, it's quite some work...but may I plead that I > would do this for you? > > :) > > > > If you want to be really nice to me, if you could do > the same report such > > that your date criteria catches no Incidents at all, > how much time till > > the Report windows shows up and completes loading with > no info on the > > report? > > > > Thank you in advance. > > > > > > > > > > > _______________________________________________________________________________ > > UNSUBSCRIBE or access ARSlist Archives at > www.arslist.org > > Platinum Sponsor: RMI Solutions ARSlist: "Where > the Answers Are" > > > > > > -- > View this message in context: > http://www.nabble.com/Somebody-please-run-this-ITSM-7-report-for-me-tp21919656p21931329.html > Sent from the ARS (Action Request System) mailing list > archive at Nabble.com. > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor: RMI Solutions ARSlist: "Where the > Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are"
