Re: Unable to retrieve data from a form
I took the db and API log for the search action on the form. I opened the form in the search mode, put in the request id =001 to search for. In the logs, It starts with ARGetEntry and fetches this record but the last SQL that runs is bit intruding...It selects all request_ID and assigned to SELECT T102.C1,C4 FROM T102 ORDER BY 1 ASC Now this query took 30 mins. there are just 69000 records in this form. Why is this SQL running? or is this normal ? Attached is the excerpt from the log. This Darn issue has extended my stay in Sweden...its funny sometimes when the travel plan gets affected with some technical issues Regards Ankur From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: 18 June 2008 10:59 PM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form ** From the User tool turn on logging (at least API and Database) and see what SQL statement is the one taking so long From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ankur Gulati Sent: Wednesday, June 18, 2008 12:06 PM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form Thank you for the script Fred, All the concerned B Table indexes are coming up as Valid. The selection query for the table does use indexed fields too. Any other things I can check up _ Ankur From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ankur Gulati Sent: 18 June 2008 08:08 PM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form Fred, There are three tables on the form(Email handler Form) First table is attachment table, this brings the attachment to this form from another form. Second table is the attachment pool. Third table fetches the data from AR System Email Attachment Join. I have also turned down the table refreshes on display. No Improvement. I'd be glad if you can fwd me the script to check indexes on tables. Regards Ankur From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: 18 June 2008 07:31 PM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form What table do you have on the form? It still sounds like an index problem (Either you are missing an index on the B table or your selection query for your table on the form is not using an index). Since you are on Oracle I have a script you can use to verify if all tables have their primary (Remedy defined), and User defined indexes. Fred From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ankur Gulati Sent: Wednesday, June 18, 2008 5:15 AM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form Hi Guys, Another thing which I forgot to mention, the status bar at the bottom does show that it found the record and it gets stuck saying Refreshing table up to Administrator`s limit Ankur From: Ankur Gulati Sent: 18 June 2008 03:30 PM To: 'arslist@ARSLIST.ORG' Subject: Unable to retrieve data from a form Hi Listers, We have a form which has 5 BLOB fields. There are around 67000 records and it takes around 3-4 hrs to search for a single record. I search on the request_id. We do have couple of other
Re: Unable to retrieve data from a form
Ankur, Actually the API call structure in the log shows that the select that you are speaking of is from a GLEWF ARGetListEntryWithFields API call. That most likely means that a table field is being refreshed and the qualification on the table field is unqualified. I would look for any Table field with no qualification, or any table field that is totally dependent on one or more EXTERNAL() operators that may be referencing a field that is still NULL at the time the table field is being refreshed. HTH -- Carey Matthew Black Remedy Skilled Professional (RSP) ARS = Action Request System(Remedy) Love, then teach Solution = People + Process + Tools Fast, Accurate, Cheap Pick two. On Thu, Jun 19, 2008 at 6:44 AM, Ankur Gulati [EMAIL PROTECTED] wrote: ** I took the db and API log for the search action on the form. I opened the form in the search mode, put in the request id =001 to search for. In the logs, It starts with ARGetEntry and fetches this record but the last SQL that runs is bit intruding...It selects all request_ID and assigned to SELECT T102.C1,C4 FROM T102 ORDER BY 1 ASC Now this query took 30 mins. there are just 69000 records in this form. Why is this SQL running? or is this normal ? Attached is the excerpt from the log. This Darn issue has extended my stay in Sweden...its funny sometimes when the travel plan gets affected with some technical issues Regards Ankur From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: 18 June 2008 10:59 PM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form ** From the User tool turn on logging (at least API and Database) and see what SQL statement is the one taking so long From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ankur Gulati Sent: Wednesday, June 18, 2008 5:15 AM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form Hi Guys, Another thing which I forgot to mention, the status bar at the bottom does show that it found the record and it gets stuck saying Refreshing table up to Administrator`s limit Ankur From: Ankur Gulati Sent: 18 June 2008 03:30 PM To: 'arslist@ARSLIST.ORG' Subject: Unable to retrieve data from a form Hi Listers, We have a form which has 5 BLOB fields. There are around 67000 records and it takes around 3-4 hrs to search for a single record. I search on the request_id. We do have couple of other indexes in place apart on Request ID field. The actual workflow is that an AL does a push field and creates a new record in a form and then second Set field is fired in same AL which opens this new record in modify window. So this is where it fails to open this new record and even if I open the form and search for any record it takes forever to show up and usually it freezes. I have rebuild the indexes but no improvement. On another form without no BLOB data and 100+ fields, it takes less than a sec to search a record. Logs doesn't show anything. AL, SQL and filter log just shows the perfect creation of new record and then on Display it does show the opening of the form in SET Operation. What all should I check? ARS. 7.0 Patch 5 DatabaseOracle 9i Regards Ankur ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Unable to retrieve data from a form
Hi Guys, Another thing which I forgot to mention, the status bar at the bottom does show that it found the record and it gets stuck saying Refreshing table up to Administrator`s limit Ankur From: Ankur Gulati Sent: 18 June 2008 03:30 PM To: 'arslist@ARSLIST.ORG' Subject: Unable to retrieve data from a form Hi Listers, We have a form which has 5 BLOB fields. There are around 67000 records and it takes around 3-4 hrs to search for a single record. I search on the request_id. We do have couple of other indexes in place apart on Request ID field. The actual workflow is that an AL does a push field and creates a new record in a form and then second Set field is fired in same AL which opens this new record in modify window. So this is where it fails to open this new record and even if I open the form and search for any record it takes forever to show up and usually it freezes. I have rebuild the indexes but no improvement. On another form without no BLOB data and 100+ fields, it takes less than a sec to search a record. Logs doesn't show anything. AL, SQL and filter log just shows the perfect creation of new record and then on Display it does show the opening of the form in SET Operation. What all should I check? ARS. 7.0 Patch 5 DatabaseOracle 9i Regards Ankur ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Unable to retrieve data from a form
Ankur, Try turning off the table refreshes on open and see if it still takes a long time. If it's the tables, then you need to look at your table qualifications and try to slim the results down or chunk them. And what I would do if it is the blobs that's causing your problem is move them off to another table with an ID field to reference them. But, I'm relatively new to this game, so I'm sure there are other, better ways. Brian From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ankur Gulati Sent: Wednesday, June 18, 2008 3:15 AM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form ** Hi Guys, Another thing which I forgot to mention, the status bar at the bottom does show that it found the record and it gets stuck saying Refreshing table up to Administrator`s limit Ankur _ From: Ankur Gulati Sent: 18 June 2008 03:30 PM To: 'arslist@ARSLIST.ORG' Subject: Unable to retrieve data from a form Hi Listers, We have a form which has 5 BLOB fields. There are around 67000 records and it takes around 3-4 hrs to search for a single record. I search on the request_id. We do have couple of other indexes in place apart on Request ID field. The actual workflow is that an AL does a push field and creates a new record in a form and then second Set field is fired in same AL which opens this new record in modify window. So this is where it fails to open this new record and even if I open the form and search for any record it takes forever to show up and usually it freezes. I have rebuild the indexes but no improvement. On another form without no BLOB data and 100+ fields, it takes less than a sec to search a record. Logs doesn't show anything. AL, SQL and filter log just shows the perfect creation of new record and then on Display it does show the opening of the form in SET Operation. What all should I check? ARS. 7.0 Patch 5 DatabaseOracle 9i Regards Ankur __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Unable to retrieve data from a form
You need to look at what the table field(s) are doing on that form. My guess is that they are pulling every record from the underlying table which is why it takes so long and often hangs. On Wed, Jun 18, 2008 at 9:09 AM, Brian Gillock [EMAIL PROTECTED] wrote: ** Ankur, Try turning off the table refreshes on open and see if it still takes a long time. If it's the tables, then you need to look at your table qualifications and try to slim the results down or chunk them. And what I would do if it is the blobs that's causing your problem is move them off to another table with an ID field to reference them. But, I'm relatively new to this game, so I'm sure there are other, better ways. Brian *From:* Action Request System discussion list(ARSList) [mailto: [EMAIL PROTECTED] *On Behalf Of *Ankur Gulati *Sent:* Wednesday, June 18, 2008 3:15 AM *To:* arslist@ARSLIST.ORG *Subject:* Re: Unable to retrieve data from a form ** Hi Guys, Another thing which I forgot to mention, the status bar at the bottom does show that it found the record and it gets stuck saying *Refreshing table up to Administrator`s limit* Ankur -- *From:* Ankur Gulati *Sent:* 18 June 2008 03:30 PM *To:* 'arslist@ARSLIST.ORG' *Subject:* Unable to retrieve data from a form Hi Listers, We have a form which has 5 BLOB fields. There are around 67000 records and it takes around 3-4 hrs to search for a single record. I search on the request_id. We do have couple of other indexes in place apart on Request ID field. The actual workflow is that an AL does a push field and creates a new record in a form and then second Set field is fired in same AL which opens this new record in modify window. So this is where it fails to open this new record and even if I open the form and search for any record it takes forever to show up and usually it freezes. I have rebuild the indexes but no improvement. On another form without no BLOB data and 100+ fields, it takes less than a sec to search a record. Logs doesn't show anything. AL, SQL and filter log just shows the perfect creation of new record and then on Display it does show the opening of the form in SET Operation. What all should I check? ARS. 7.0 Patch 5 DatabaseOracle 9i Regards Ankur __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Unable to retrieve data from a form
What table do you have on the form? It still sounds like an index problem (Either you are missing an index on the B table or your selection query for your table on the form is not using an index). Since you are on Oracle I have a script you can use to verify if all tables have their primary (Remedy defined), and User defined indexes. Fred From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ankur Gulati Sent: Wednesday, June 18, 2008 5:15 AM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form Hi Guys, Another thing which I forgot to mention, the status bar at the bottom does show that it found the record and it gets stuck saying Refreshing table up to Administrator`s limit Ankur From: Ankur Gulati Sent: 18 June 2008 03:30 PM To: 'arslist@ARSLIST.ORG' Subject: Unable to retrieve data from a form Hi Listers, We have a form which has 5 BLOB fields. There are around 67000 records and it takes around 3-4 hrs to search for a single record. I search on the request_id. We do have couple of other indexes in place apart on Request ID field. The actual workflow is that an AL does a push field and creates a new record in a form and then second Set field is fired in same AL which opens this new record in modify window. So this is where it fails to open this new record and even if I open the form and search for any record it takes forever to show up and usually it freezes. I have rebuild the indexes but no improvement. On another form without no BLOB data and 100+ fields, it takes less than a sec to search a record. Logs doesn't show anything. AL, SQL and filter log just shows the perfect creation of new record and then on Display it does show the opening of the form in SET Operation. What all should I check? ARS. 7.0 Patch 5 DatabaseOracle 9i Regards Ankur ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Unable to retrieve data from a form
Fred, There are three tables on the form(Email handler Form) First table is attachment table, this brings the attachment to this form from another form. Second table is the attachment pool. Third table fetches the data from AR System Email Attachment Join. I have also turned down the table refreshes on display. No Improvement. I'd be glad if you can fwd me the script to check indexes on tables. Regards Ankur From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: 18 June 2008 07:31 PM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form ** What table do you have on the form? It still sounds like an index problem (Either you are missing an index on the B table or your selection query for your table on the form is not using an index). Since you are on Oracle I have a script you can use to verify if all tables have their primary (Remedy defined), and User defined indexes. Fred From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ankur Gulati Sent: Wednesday, June 18, 2008 5:15 AM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form Hi Guys, Another thing which I forgot to mention, the status bar at the bottom does show that it found the record and it gets stuck saying Refreshing table up to Administrator`s limit Ankur From: Ankur Gulati Sent: 18 June 2008 03:30 PM To: 'arslist@ARSLIST.ORG' Subject: Unable to retrieve data from a form Hi Listers, We have a form which has 5 BLOB fields. There are around 67000 records and it takes around 3-4 hrs to search for a single record. I search on the request_id. We do have couple of other indexes in place apart on Request ID field. The actual workflow is that an AL does a push field and creates a new record in a form and then second Set field is fired in same AL which opens this new record in modify window. So this is where it fails to open this new record and even if I open the form and search for any record it takes forever to show up and usually it freezes. I have rebuild the indexes but no improvement. On another form without no BLOB data and 100+ fields, it takes less than a sec to search a record. Logs doesn't show anything. AL, SQL and filter log just shows the perfect creation of new record and then on Display it does show the opening of the form in SET Operation. What all should I check? ARS. 7.0 Patch 5 DatabaseOracle 9i Regards Ankur __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Unable to retrieve data from a form
Thank you for the script Fred, All the concerned B Table indexes are coming up as Valid. The selection query for the table does use indexed fields too. Any other things I can check up _ Ankur From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ankur Gulati Sent: 18 June 2008 08:08 PM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form ** Fred, There are three tables on the form(Email handler Form) First table is attachment table, this brings the attachment to this form from another form. Second table is the attachment pool. Third table fetches the data from AR System Email Attachment Join. I have also turned down the table refreshes on display. No Improvement. I'd be glad if you can fwd me the script to check indexes on tables. Regards Ankur From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: 18 June 2008 07:31 PM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form ** What table do you have on the form? It still sounds like an index problem (Either you are missing an index on the B table or your selection query for your table on the form is not using an index). Since you are on Oracle I have a script you can use to verify if all tables have their primary (Remedy defined), and User defined indexes. Fred From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ankur Gulati Sent: Wednesday, June 18, 2008 5:15 AM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form Hi Guys, Another thing which I forgot to mention, the status bar at the bottom does show that it found the record and it gets stuck saying Refreshing table up to Administrator`s limit Ankur From: Ankur Gulati Sent: 18 June 2008 03:30 PM To: 'arslist@ARSLIST.ORG' Subject: Unable to retrieve data from a form Hi Listers, We have a form which has 5 BLOB fields. There are around 67000 records and it takes around 3-4 hrs to search for a single record. I search on the request_id. We do have couple of other indexes in place apart on Request ID field. The actual workflow is that an AL does a push field and creates a new record in a form and then second Set field is fired in same AL which opens this new record in modify window. So this is where it fails to open this new record and even if I open the form and search for any record it takes forever to show up and usually it freezes. I have rebuild the indexes but no improvement. On another form without no BLOB data and 100+ fields, it takes less than a sec to search a record. Logs doesn't show anything. AL, SQL and filter log just shows the perfect creation of new record and then on Display it does show the opening of the form in SET Operation. What all should I check? ARS. 7.0 Patch 5 DatabaseOracle 9i Regards Ankur __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Unable to retrieve data from a form
From the User tool turn on logging (at least API and Database) and see what SQL statement is the one taking so long From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ankur Gulati Sent: Wednesday, June 18, 2008 12:06 PM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form Thank you for the script Fred, All the concerned B Table indexes are coming up as Valid. The selection query for the table does use indexed fields too. Any other things I can check up _ Ankur From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ankur Gulati Sent: 18 June 2008 08:08 PM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form Fred, There are three tables on the form(Email handler Form) First table is attachment table, this brings the attachment to this form from another form. Second table is the attachment pool. Third table fetches the data from AR System Email Attachment Join. I have also turned down the table refreshes on display. No Improvement. I'd be glad if you can fwd me the script to check indexes on tables. Regards Ankur From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: 18 June 2008 07:31 PM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form What table do you have on the form? It still sounds like an index problem (Either you are missing an index on the B table or your selection query for your table on the form is not using an index). Since you are on Oracle I have a script you can use to verify if all tables have their primary (Remedy defined), and User defined indexes. Fred From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ankur Gulati Sent: Wednesday, June 18, 2008 5:15 AM To: arslist@ARSLIST.ORG Subject: Re: Unable to retrieve data from a form Hi Guys, Another thing which I forgot to mention, the status bar at the bottom does show that it found the record and it gets stuck saying Refreshing table up to Administrator`s limit Ankur From: Ankur Gulati Sent: 18 June 2008 03:30 PM To: 'arslist@ARSLIST.ORG' Subject: Unable to retrieve data from a form Hi Listers, We have a form which has 5 BLOB fields. There are around 67000 records and it takes around 3-4 hrs to search for a single record. I search on the request_id. We do have couple of other indexes in place apart on Request ID field. The actual workflow is that an AL does a push field and creates a new record in a form and then second Set field is fired in same AL which opens this new record in modify window. So this is where it fails to open this new record and even if I open the form and search for any record it takes forever to show up and usually it freezes. I have rebuild the indexes but no improvement. On another form without no BLOB data and 100+ fields, it takes less than a sec to search a record. Logs doesn't show anything. AL, SQL and filter log just shows the perfect creation of new record and then on Display it does show the opening of the form in SET Operation. What all should I check? ARS. 7.0 Patch 5 DatabaseOracle 9i Regards Ankur ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Unable to retrieve data from a form
Turn on the client side sql logging to see where it stops, then go from there. Axton Grams On Wed, Jun 18, 2008 at 6:14 AM, Ankur Gulati [EMAIL PROTECTED] wrote: ** Hi Guys, Another thing which I forgot to mention, the status bar at the bottom does show that it found the record and it gets stuck saying Refreshing table up to Administrator`s limit Ankur From: Ankur Gulati Sent: 18 June 2008 03:30 PM To: 'arslist@ARSLIST.ORG' Subject: Unable to retrieve data from a form Hi Listers, We have a form which has 5 BLOB fields. There are around 67000 records and it takes around 3-4 hrs to search for a single record. I search on the request_id. We do have couple of other indexes in place apart on Request ID field. The actual workflow is that an AL does a push field and creates a new record in a form and then second Set field is fired in same AL which opens this new record in modify window. So this is where it fails to open this new record and even if I open the form and search for any record it takes forever to show up and usually it freezes. I have rebuild the indexes but no improvement. On another form without no BLOB data and 100+ fields, it takes less than a sec to search a record. Logs doesn't show anything. AL, SQL and filter log just shows the perfect creation of new record and then on Display it does show the opening of the form in SET Operation. What all should I check? ARS. 7.0 Patch 5 DatabaseOracle 9i Regards Ankur __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
