Re: Search Results Returned Limit
Is there a way to limit the query results server-wide i.e. not controlled by the client - may be a parameter in ar.cfg file? v7.1 Thanks Atul Vohra -Original Message- From: Mueller, Doug [doug_muel...@bmc.com] Date: 04/22/2010 03:56 PM To: arslist@ARSLIST.ORG Subject: Re: Search Results Returned Limit ** Kali, How things work depends on the version of the AR System server you are using. Older servers (pre about 2005) The DB query searches and builds its internal list, our code then gets the next item from the list in a loop adding them to a memory structure for return to the client. When we hit the limit, we stop retrieving from the DB and return the list. HOWEVER, the entire query at the DB level was run. But, you never got more than the limit loaded into the server and never more than the limit across the wire. Newer servers -- and which version depends on which database (and I don't have a list) The DBs have added extra syntax to allow you to control the query run. Top and MaxRows are the types of clauses you can add depending on the DB. The AR System server uses these clauses as appropriate when a limit (either a server limit or client limit) has been specified. This tells the DB to stop searching once the limit has been reached and you save the DB continuing to gather items into its queue. There is no change at the AR System level about retrieval, it gets up to the limit and stops. There is no change on the wire, still only the limit requested goes. The change is that the DB stops the search faster and so saves time at the DB level. NOTE: If you specify sorting and there is no index on the sort column the DB will likely still get the entire list because it needs to do that to do the sort to then select the top items. So, generally and for most queries, the limit will help at the DB level but sometimes it will not. If you want to know if your AR System version is late enough to take advantage of the extra clause Set a limit Turn on logging Issue a query that finds more than the limit Turn off logging Look at the log to see if the extra clause is present I know, pretty sophisticated isn't it! Doug Mueller From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Kali Obsum Sent: Thursday, April 22, 2010 3:17 AM To: arslist@ARSLIST.ORG Subject: Search Results Returned Limit ** In the Remedy User Tool, there is an option to limit the number of search results to be returned. How does this work in the DB level? Does it mean that if the query performed produces 10,000 results, the DB will still search for those 10,000 but only a limited number will get displayed in the User Tool? Or will the limit already be incorporated in the query such that only the top N results will be queried? Regards, Kali NOTICE The information contained in this email is confidential. If you are not the intended recipient, you must not disclose or use the information in this email in any way. If you received it in error, please tell us immediately by return email and delete the document. We do not guarantee the integrity of any e-mails or attached files and are not responsible for any changes made to them by any other person. _attend WWRUG10 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG10 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: Where the Answers Are
Re: Search Results Returned Limit
Yes, under Server Information - Configuration - Max entries returned by get list Wes -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Atul Vohra Sent: Friday, April 23, 2010 9:48 AM To: arslist@ARSLIST.ORG Subject: Re: Search Results Returned Limit Is there a way to limit the query results server-wide i.e. not controlled by the client - may be a parameter in ar.cfg file? v7.1 Thanks Atul Vohra -Original Message- From: Mueller, Doug [doug_muel...@bmc.com] Date: 04/22/2010 03:56 PM To: arslist@ARSLIST.ORG Subject: Re: Search Results Returned Limit ** Kali, How things work depends on the version of the AR System server you are using. Older servers (pre about 2005) The DB query searches and builds its internal list, our code then gets the next item from the list in a loop adding them to a memory structure for return to the client. When we hit the limit, we stop retrieving from the DB and return the list. HOWEVER, the entire query at the DB level was run. But, you never got more than the limit loaded into the server and never more than the limit across the wire. Newer servers -- and which version depends on which database (and I don't have a list) The DBs have added extra syntax to allow you to control the query run. Top and MaxRows are the types of clauses you can add depending on the DB. The AR System server uses these clauses as appropriate when a limit (either a server limit or client limit) has been specified. This tells the DB to stop searching once the limit has been reached and you save the DB continuing to gather items into its queue. There is no change at the AR System level about retrieval, it gets up to the limit and stops. There is no change on the wire, still only the limit requested goes. The change is that the DB stops the search faster and so saves time at the DB level. NOTE: If you specify sorting and there is no index on the sort column the DB will likely still get the entire list because it needs to do that to do the sort to then select the top items. So, generally and for most queries, the limit will help at the DB level but sometimes it will not. If you want to know if your AR System version is late enough to take advantage of the extra clause Set a limit Turn on logging Issue a query that finds more than the limit Turn off logging Look at the log to see if the extra clause is present I know, pretty sophisticated isn't it! Doug Mueller From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Kali Obsum Sent: Thursday, April 22, 2010 3:17 AM To: arslist@ARSLIST.ORG Subject: Search Results Returned Limit ** In the Remedy User Tool, there is an option to limit the number of search results to be returned. How does this work in the DB level? Does it mean that if the query performed produces 10,000 results, the DB will still search for those 10,000 but only a limited number will get displayed in the User Tool? Or will the limit already be incorporated in the query such that only the top N results will be queried? Regards, Kali NOTICE The information contained in this email is confidential. If you are not the intended recipient, you must not disclose or use the information in this email in any way. If you received it in error, please tell us immediately by return email and delete the document. We do not guarantee the integrity of any e-mails or attached files and are not responsible for any changes made to them by any other person. _attend WWRUG10 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG10 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: Where the Answers Are
Re: Search Results Returned Limit
Kali, I haven't double checked with recent versions, but in the past it only limited what was displayed and not what was actually queried. I really don't expect that to ever change since the database has to query the whole set if there is a sort. The SQL log will show the query as it is sent to the database if you want to confirm that. I am not sure what the AR server does with it. I would suspect (hope) that it retrieves the entire result set and then sends the client only the top . Juan Ingles On Thu, Apr 22, 2010 at 3:16 AM, Kali Obsum kali.ob...@macquarie.com wrote: ** In the Remedy User Tool, there is an option to limit the number of search results to be returned. How does this work in the DB level? Does it mean that if the query performed produces 10,000 results, the DB will still search for those 10,000 but only a limited number will get displayed in the User Tool? Or will the limit already be incorporated in the query such that only the top N results will be queried? Regards, Kali NOTICE The information contained in this email is confidential. If you are not the intended recipient, you must not disclose or use the information in this email in any way. If you received it in error, please tell us immediately by return email and delete the document. We do not guarantee the integrity of any e-mails or attached files and are not responsible for any changes made to them by any other person. _attend WWRUG10 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: Where the Answers Are
Re: Search Results Returned Limit
My understanding is that in ARS 7.5, an extra top xxx is added to the SQL statement in sql server databases and a extra AND ROWNUM is added to Oracle ones. I can't remember what is the syntax for DB2 So for instance in Oracle SELECT C1.T500, C2.T500 FROM T500 where C2 = 'Demo' would become SELECT C1.T500, C2.T500 FROM T500 where C2 = 'Demo' AND ROWNUM 1 Guillaume From: Action Request System discussion list(ARSList) [arsl...@arslist.org] on behalf of Kali Obsum [kali.ob...@macquarie.com] Sent: Thursday, April 22, 2010 6:16 AM To: arslist@ARSLIST.ORG Subject: Search Results Returned Limit ** In the Remedy User Tool, there is an option to limit the number of search results to be returned. How does this work in the DB level? Does it mean that if the query performed produces 10,000 results, the DB will still search for those 10,000 but only a limited number will get displayed in the User Tool? Or will the limit already be incorporated in the query such that only the top N results will be queried? Regards, Kali NOTICE The information contained in this email is confidential. If you are not the intended recipient, you must not disclose or use the information in this email in any way. If you received it in error, please tell us immediately by return email and delete the document. We do not guarantee the integrity of any e-mails or attached files and are not responsible for any changes made to them by any other person. _attend WWRUG10 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: Where the Answers Are
Re: Search Results Returned Limit
Kali, How things work depends on the version of the AR System server you are using. Older servers (pre about 2005) The DB query searches and builds its internal list, our code then gets the next item from the list in a loop adding them to a memory structure for return to the client. When we hit the limit, we stop retrieving from the DB and return the list. HOWEVER, the entire query at the DB level was run. But, you never got more than the limit loaded into the server and never more than the limit across the wire. Newer servers -- and which version depends on which database (and I don't have a list) The DBs have added extra syntax to allow you to control the query run. Top and MaxRows are the types of clauses you can add depending on the DB. The AR System server uses these clauses as appropriate when a limit (either a server limit or client limit) has been specified. This tells the DB to stop searching once the limit has been reached and you save the DB continuing to gather items into its queue. There is no change at the AR System level about retrieval, it gets up to the limit and stops. There is no change on the wire, still only the limit requested goes. The change is that the DB stops the search faster and so saves time at the DB level. NOTE: If you specify sorting and there is no index on the sort column the DB will likely still get the entire list because it needs to do that to do the sort to then select the top items. So, generally and for most queries, the limit will help at the DB level but sometimes it will not. If you want to know if your AR System version is late enough to take advantage of the extra clause Set a limit Turn on logging Issue a query that finds more than the limit Turn off logging Look at the log to see if the extra clause is present I know, pretty sophisticated isn't it! Doug Mueller From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Kali Obsum Sent: Thursday, April 22, 2010 3:17 AM To: arslist@ARSLIST.ORG Subject: Search Results Returned Limit ** In the Remedy User Tool, there is an option to limit the number of search results to be returned. How does this work in the DB level? Does it mean that if the query performed produces 10,000 results, the DB will still search for those 10,000 but only a limited number will get displayed in the User Tool? Or will the limit already be incorporated in the query such that only the top N results will be queried? Regards, Kali NOTICE The information contained in this email is confidential. If you are not the intended recipient, you must not disclose or use the information in this email in any way. If you received it in error, please tell us immediately by return email and delete the document. We do not guarantee the integrity of any e-mails or attached files and are not responsible for any changes made to them by any other person. _attend WWRUG10 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: Where the Answers Are
