Re: Search for full Case ID takes longer than just the number
I think you are falling into a QBE match issue. When you use just the number the system pre-pends the prefix onto the number and does an equal search. When you put in the entire number I bet it takes the default QBE for the field. You can verify this by looking at the SQL logs. Wes -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Cecil, Ken Sent: Thursday, June 18, 2009 9:03 AM To: arslist@ARSLIST.ORG Subject: Search for full Case ID takes longer than just the number ** Anybody have an idea on why it is taking 10 times longer to search for a record using the fully qualified ID than it does when searching using just the number? This is only when searching using query by example (entering the id on the form). Any ideas on where to look/troubleshoot? Search examples: Query By Example using the Case ID field (field 1 on this form) 135303 ...takes about 9 seconds. HD0135303 ...takes about 90 seconds to return?? Advanced search using criteria instead... 'Case ID+' LIKE %135303 ...Takes 95 seconds. 'Case ID+' = HD0135303 ...Takes 10 seconds. It is like it is having to do a full table scan even though this field is obviously indexed. Also, from what I can tell it is just on the Help Desk form; other forms perform well either way. For what it is worth, I am working with ARS 6.0 and Help Desk ITSM 5.6 Thanks, Ken. *** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept for the presence of computer viruses. www.Hubbell.com - Hubbell Incorporated** _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Search for full Case ID takes longer than just the number
I captured a database log of searching using both ways. The where clause on each is identical and has the full case id in it. All of the SQL queries in the log are identical. The total time for all the DB activity is pretty close too. It appears that the delay is occurring after all the DB activity; happening when the client is preparing to display the result. Any other ideas on logs I should compare or why displaying the result from the full id search is taking a lot longer? Thanks, Ken. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Nichols, Wesley D CTR USAF AFMC 72 CS/SCBAF Sent: Thursday, June 18, 2009 10:26 AM To: arslist@ARSLIST.ORG Subject: Re: Search for full Case ID takes longer than just the number I think you are falling into a QBE match issue. When you use just the number the system pre-pends the prefix onto the number and does an equal search. When you put in the entire number I bet it takes the default QBE for the field. You can verify this by looking at the SQL logs. Wes -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Cecil, Ken Sent: Thursday, June 18, 2009 9:03 AM To: arslist@ARSLIST.ORG Subject: Search for full Case ID takes longer than just the number ** Anybody have an idea on why it is taking 10 times longer to search for a record using the fully qualified ID than it does when searching using just the number? This is only when searching using query by example (entering the id on the form). Any ideas on where to look/troubleshoot? Search examples: Query By Example using the Case ID field (field 1 on this form) 135303 ...takes about 9 seconds. HD0135303 ...takes about 90 seconds to return?? Advanced search using criteria instead... 'Case ID+' LIKE %135303 ...Takes 95 seconds. 'Case ID+' = HD0135303 ...Takes 10 seconds. It is like it is having to do a full table scan even though this field is obviously indexed. Also, from what I can tell it is just on the Help Desk form; other forms perform well either way. For what it is worth, I am working with ARS 6.0 and Help Desk ITSM 5.6 Thanks, Ken. *** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept for the presence of computer viruses. www.Hubbell.com - Hubbell Incorporated** _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are *** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept for the presence of computer viruses. www.Hubbell.com - Hubbell Incorporated** ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Search for full Case ID takes longer than just the number
Ken, You need to look at all of these things: Server side logs: API, SQL, FILTER Client side logs: Active Links, Macros ( You know.. the whole ball of wax.) The flow basically (roughly off the top of my head) goes like this: Starting with the window already open in search mode with all the values filled in. Client Starts to do the query: Active links on Query (and maybe other execute on conditions) Client sends API call to the AR server ARServer sends SQL to DB.(Start API log times) Start DB log times DB does it works... and returns results to AR Server. End DB log times ARServer gets data ARServer may do Filters on (Get Entry) ARServer returns data to the client. (end API logs time) Client may do more Active Links (window Open(?),After Query,Loaded,...) Client may refresh table fields (depending on field definition and/or Client configurations) Which could trigger more active links on the client. User sees that the Client has completed the query. I am sure that my estimate above missed a few things. I may have even got some of them in the wrong order. (The docs kind of cover the order of these processes, but not very clearly in my opinion.) However, the point is that lots of things are going to happen even when the input search conditions are very simple. Look at all of the details and you can find the cause. You may also need to look at things outside of ARS to. Things like: DB performance, ARSystem Server(host) performance, Network traffic load, Load balancer (if they are involved), Web servers (if your using the Mid-Tier and not the User Tool), and maybe even local client CPU/Memory loads too. Performance tuning/trouble shooting can be a big task. Good luck. FWIW: To avoid a bit of confusion about what query is actually being done. I suggest that you issue all of your search with the Advanced Search Bar (ASB) for testing. AR System does not muck with them as much as if you use the Search by Example (SBE) features. Obviously that is not what your users are going to be doing, but I would still start there and then work your way out to using SBE and ASB. And if needed only using SBE conditions last. HTH. -- Carey Matthew Black BMC Remedy AR System Skilled Professional (RSP) ARS = Action Request System(Remedy) Love, then teach Solution = People + Process + Tools Fast, Accurate, Cheap Pick two. On Thu, Jun 18, 2009 at 11:29 AM, Cecil, Kenkce...@hubbell.com wrote: I captured a database log of searching using both ways. The where clause on each is identical and has the full case id in it. All of the SQL queries in the log are identical. The total time for all the DB activity is pretty close too. It appears that the delay is occurring after all the DB activity; happening when the client is preparing to display the result. Any other ideas on logs I should compare or why displaying the result from the full id search is taking a lot longer? Thanks, Ken. snip -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Cecil, Ken Sent: Thursday, June 18, 2009 9:03 AM To: arslist@ARSLIST.ORG Subject: Search for full Case ID takes longer than just the number ** Anybody have an idea on why it is taking 10 times longer to search for a record using the fully qualified ID than it does when searching using just the number? This is only when searching using query by example (entering the id on the form). Any ideas on where to look/troubleshoot? Search examples: Query By Example using the Case ID field (field 1 on this form) 135303 ...takes about 9 seconds. HD0135303 ...takes about 90 seconds to return?? Advanced search using criteria instead... 'Case ID+' LIKE %135303 ...Takes 95 seconds. 'Case ID+' = HD0135303 ...Takes 10 seconds. It is like it is having to do a full table scan even though this field is obviously indexed. Also, from what I can tell it is just on the Help Desk form; other forms perform well either way. For what it is worth, I am working with ARS 6.0 and Help Desk ITSM 5.6 Thanks, Ken. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
