Re: COLSUM
That's the thingthe table itself isn't set to chunkthe chunking is happening at the server and affects ALL tables the server needs to read. The table in question has 4 columns, all that need to at some point have a colsum done on them. Based on my reading of the server side table chunking, it's designed to keep the memory usage down by handling x records at a time...but in this case that's not what it doesit simply causes the server to increase the chunk size by that amount until it's dealing with the entire tableand it does this automatically when you ask for a colsumit knows that all you want is a sum of the values, yet it does massively more work than is necessary to get the information and ends up taking massively more time than necessarythus I was looking for Doug's view on the subject as he seems to always have an eloquent response to such design questions. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Misi Mladoniczky Sent: Monday, April 19, 2010 11:11 PM To: arslist@ARSLIST.ORG Subject: Re: COLSUM Hi, I seems to be doing what you asked it to do. The way both client and FLTR tables work, is by filling the table with data (in memory). If you tell it to chunk, it apparently reads it that way. Create a new table-field without chinking, with a single column, and do the COLSUM there instead. Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia: * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. Doug, Since you have been replying to list posts more frequently, I figured I would ask you a question real quick. We have several pieces of workflow that use filter side tables and COLSUM. In situations where there are more records in the table than the 'Server Table Field Chunk Size', I have noticed that it does a 'select first x' on the table columns, and it does it again till it finds a number higher than the number of records returned. In our situation we had the setting at 1000 and had over 50K records in the table...so the SQL log shows select first 1000, then 2000, 3000, 4000, etc till it hits a value above the count in the table. This of course kills our performance when we are just doing a sum on a columnso I converted them to direct SQL doing SUM() and aren't having problems anymore. Why was this done as a method to perform COLSUM?it doesn't seem efficient to me at all. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: Where the Answers Are -- This message was scanned by ESVA and is believed to be clean. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org 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: COLSUM
Hi, I now understand what you are saying. I guess that when it reads for example 3000 records, it just drops record 1-2000 and uses the trailing 1000 records. So for you, this results in a massive number of SQL-searches instead of reading through the table once. In this case, I guess a direct SQL call would be only workable solution... I think that the best way for BMC to handle/implement this would be to use the chunk-size you specify for the table-field, with a possible maximum-limit equal to the system wide max-entries-returned-by-get-list... I must confess that I typically work with fewer records in filter-tables. Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia: * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. That's the thingthe table itself isn't set to chunkthe chunking is happening at the server and affects ALL tables the server needs to read. The table in question has 4 columns, all that need to at some point have a colsum done on them. Based on my reading of the server side table chunking, it's designed to keep the memory usage down by handling x records at a time...but in this case that's not what it doesit simply causes the server to increase the chunk size by that amount until it's dealing with the entire tableand it does this automatically when you ask for a colsumit knows that all you want is a sum of the values, yet it does massively more work than is necessary to get the information and ends up taking massively more time than necessarythus I was looking for Doug's view on the subject as he seems to always have an eloquent response to such design questions. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Misi Mladoniczky Sent: Monday, April 19, 2010 11:11 PM To: arslist@ARSLIST.ORG Subject: Re: COLSUM Hi, I seems to be doing what you asked it to do. The way both client and FLTR tables work, is by filling the table with data (in memory). If you tell it to chunk, it apparently reads it that way. Create a new table-field without chinking, with a single column, and do the COLSUM there instead. Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia: * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. Doug, Since you have been replying to list posts more frequently, I figured I would ask you a question real quick. We have several pieces of workflow that use filter side tables and COLSUM. In situations where there are more records in the table than the 'Server Table Field Chunk Size', I have noticed that it does a 'select first x' on the table columns, and it does it again till it finds a number higher than the number of records returned. In our situation we had the setting at 1000 and had over 50K records in the table...so the SQL log shows select first 1000, then 2000, 3000, 4000, etc till it hits a value above the count in the table. This of course kills our performance when we are just doing a sum on a columnso I converted them to direct SQL doing SUM() and aren't having problems anymore. Why was this done as a method to perform COLSUM?it doesn't seem efficient to me at all. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: Where the Answers Are -- This message was scanned by ESVA and is believed to be clean. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org 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 -- This message was scanned by ESVA and is believed to be clean. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: Where the Answers Are
Re: COLSUM
I had one example I was working with that had over a million records that it was summingtook a fairly powerful server some 5 min's to get me the sumwhereas a simple select sum(field) took microseconds.interested in the design considerations I'm not aware of...:) -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Misi Mladoniczky Sent: Tuesday, April 20, 2010 9:10 AM To: arslist@ARSLIST.ORG Subject: Re: COLSUM Hi, I now understand what you are saying. I guess that when it reads for example 3000 records, it just drops record 1-2000 and uses the trailing 1000 records. So for you, this results in a massive number of SQL-searches instead of reading through the table once. In this case, I guess a direct SQL call would be only workable solution... I think that the best way for BMC to handle/implement this would be to use the chunk-size you specify for the table-field, with a possible maximum-limit equal to the system wide max-entries-returned-by-get-list... I must confess that I typically work with fewer records in filter-tables. Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia: * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. That's the thingthe table itself isn't set to chunkthe chunking is happening at the server and affects ALL tables the server needs to read. The table in question has 4 columns, all that need to at some point have a colsum done on them. Based on my reading of the server side table chunking, it's designed to keep the memory usage down by handling x records at a time...but in this case that's not what it doesit simply causes the server to increase the chunk size by that amount until it's dealing with the entire tableand it does this automatically when you ask for a colsumit knows that all you want is a sum of the values, yet it does massively more work than is necessary to get the information and ends up taking massively more time than necessarythus I was looking for Doug's view on the subject as he seems to always have an eloquent response to such design questions. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Misi Mladoniczky Sent: Monday, April 19, 2010 11:11 PM To: arslist@ARSLIST.ORG Subject: Re: COLSUM Hi, I seems to be doing what you asked it to do. The way both client and FLTR tables work, is by filling the table with data (in memory). If you tell it to chunk, it apparently reads it that way. Create a new table-field without chinking, with a single column, and do the COLSUM there instead. Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia: * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. Doug, Since you have been replying to list posts more frequently, I figured I would ask you a question real quick. We have several pieces of workflow that use filter side tables and COLSUM. In situations where there are more records in the table than the 'Server Table Field Chunk Size', I have noticed that it does a 'select first x' on the table columns, and it does it again till it finds a number higher than the number of records returned. In our situation we had the setting at 1000 and had over 50K records in the table...so the SQL log shows select first 1000, then 2000, 3000, 4000, etc till it hits a value above the count in the table. This of course kills our performance when we are just doing a sum on a columnso I converted them to direct SQL doing SUM() and aren't having problems anymore. Why was this done as a method to perform COLSUM?it doesn't seem efficient to me at all. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: Where the Answers Are -- This message was scanned by ESVA and is believed to be clean. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org 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 -- This message was scanned by ESVA and is believed to be clean. ___ UNSUBSCRIBE or access ARSlist
Re: COLSUM
LJ, This is definitely a very good example of why sometimes you have to bypass the ARS layer and go straight to the database, and take advantage of the broader functionality available at the database layer; SQL functions is a perfect example. I don't think ARS will ever provide all the functionality that you can have at the database layer, since anyway database functionality keeps evolving as ARS is evolvingfor instance, in Oracle 10g and 11g, you can use regular expressions in SQL statements...very powerful. Will ARS provide regular expressions in SQL statements? probably not, not until DB2 and SQL server can do that; and that's OK, 'cause you can execute direct SQL from ARS. Guillaume From: Action Request System discussion list(ARSList) [arsl...@arslist.org] on behalf of LJ LongWing [lj.longw...@gmail.com] Sent: Tuesday, April 20, 2010 11:53 AM To: arslist@ARSLIST.ORG Subject: Re: COLSUM I had one example I was working with that had over a million records that it was summingtook a fairly powerful server some 5 min's to get me the sumwhereas a simple select sum(field) took microseconds.interested in the design considerations I'm not aware of...:) -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Misi Mladoniczky Sent: Tuesday, April 20, 2010 9:10 AM To: arslist@ARSLIST.ORG Subject: Re: COLSUM Hi, I now understand what you are saying. I guess that when it reads for example 3000 records, it just drops record 1-2000 and uses the trailing 1000 records. So for you, this results in a massive number of SQL-searches instead of reading through the table once. In this case, I guess a direct SQL call would be only workable solution... I think that the best way for BMC to handle/implement this would be to use the chunk-size you specify for the table-field, with a possible maximum-limit equal to the system wide max-entries-returned-by-get-list... I must confess that I typically work with fewer records in filter-tables. Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia: * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. That's the thingthe table itself isn't set to chunkthe chunking is happening at the server and affects ALL tables the server needs to read. The table in question has 4 columns, all that need to at some point have a colsum done on them. Based on my reading of the server side table chunking, it's designed to keep the memory usage down by handling x records at a time...but in this case that's not what it doesit simply causes the server to increase the chunk size by that amount until it's dealing with the entire tableand it does this automatically when you ask for a colsumit knows that all you want is a sum of the values, yet it does massively more work than is necessary to get the information and ends up taking massively more time than necessarythus I was looking for Doug's view on the subject as he seems to always have an eloquent response to such design questions. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Misi Mladoniczky Sent: Monday, April 19, 2010 11:11 PM To: arslist@ARSLIST.ORG Subject: Re: COLSUM Hi, I seems to be doing what you asked it to do. The way both client and FLTR tables work, is by filling the table with data (in memory). If you tell it to chunk, it apparently reads it that way. Create a new table-field without chinking, with a single column, and do the COLSUM there instead. Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia: * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. Doug, Since you have been replying to list posts more frequently, I figured I would ask you a question real quick. We have several pieces of workflow that use filter side tables and COLSUM. In situations where there are more records in the table than the 'Server Table Field Chunk Size', I have noticed that it does a 'select first x' on the table columns, and it does it again till it finds a number higher than the number of records returned. In our situation we had the setting at 1000 and had over 50K records in the table...so the SQL log shows select first 1000, then 2000, 3000, 4000, etc till it hits a value above the count in the table. This of course kills our performance when we are just doing a sum on a columnso I converted them to direct SQL doing SUM() and aren't having problems anymore
COLSUM
Doug, Since you have been replying to list posts more frequently, I figured I would ask you a question real quick. We have several pieces of workflow that use filter side tables and COLSUM. In situations where there are more records in the table than the 'Server Table Field Chunk Size', I have noticed that it does a 'select first x' on the table columns, and it does it again till it finds a number higher than the number of records returned. In our situation we had the setting at 1000 and had over 50K records in the table...so the SQL log shows select first 1000, then 2000, 3000, 4000, etc till it hits a value above the count in the table. This of course kills our performance when we are just doing a sum on a columnso I converted them to direct SQL doing SUM() and aren't having problems anymore. Why was this done as a method to perform COLSUM?it doesn't seem efficient to me at all. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: Where the Answers Are
Re: COLSUM
Hi, I seems to be doing what you asked it to do. The way both client and FLTR tables work, is by filling the table with data (in memory). If you tell it to chunk, it apparently reads it that way. Create a new table-field without chinking, with a single column, and do the COLSUM there instead. Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia: * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. Doug, Since you have been replying to list posts more frequently, I figured I would ask you a question real quick. We have several pieces of workflow that use filter side tables and COLSUM. In situations where there are more records in the table than the 'Server Table Field Chunk Size', I have noticed that it does a 'select first x' on the table columns, and it does it again till it finds a number higher than the number of records returned. In our situation we had the setting at 1000 and had over 50K records in the table...so the SQL log shows select first 1000, then 2000, 3000, 4000, etc till it hits a value above the count in the table. This of course kills our performance when we are just doing a sum on a columnso I converted them to direct SQL doing SUM() and aren't having problems anymore. Why was this done as a method to perform COLSUM?it doesn't seem efficient to me at all. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: Where the Answers Are -- This message was scanned by ESVA and is believed to be clean. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: Where the Answers Are
Re: COLSUM - How do I make a field show the sum of all tickets with a certain category or distict attribute ?
ALL, I am sure this is an easy one. I have a table that list all the tickets that meet a certain distinct attribute in this case a field called 'ServiceYesNo' Values In Service or Out of Service. The table displays fine but I don't want a list, all I want is a count. I am pretty sure I should use the COLSUM Function. I created an active link with a set fields, choosing the column and choosing COLSUM as the value. Oh great remedy gurus help this mere mortal that I may thwart the request for constant reporting data from my highers by giving them real time data..(Today IS Friday) THANKS !! John Mosco W01/GS-11 IT Coordinator Customer Support Manager PNG JFHQ J6 Com: 717-861-8143 DSN: 312-491-8143 Fax: 717-861-8414 CALL, FAX, or EMAIL the Pennsylvania National Guard Operations Desk and start a REMEDY Ticket for all RCAS issues or requests. Operations Desk Commercial: 717.861.2899 Operations Desk DSN: 312.491.2899 Operations Desk FAX: 717.861.8414 [EMAIL PROTECTED] Do not withhold good from those who deserve it, when it is in your power to act. Proverbs 3:27 __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: COLSUM - How do I make a field show the sum of all tickets with a certain category or distict attribute ?
Look up LASTCOUNT, I think that might be the easy solution to what you're looking for. Ben Cantatore Remedy Administrator Avon (914) 935-2946 Mosco, John D. [EMAIL PROTECTED] Sent by: Action Request System discussion list(ARSList) arslist@ARSLIST.ORG 05/25/2007 08:48 AM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Re: COLSUM - How do I make a field show the sum of all tickets with a certain category or distict attribute ? ** ALL, I am sure this is an easy one. I have a table that list all the tickets that meet a certain distinct attribute in this case a field called ?ServiceYesNo? Values ?In Service? or ?Out of Service?. The table displays fine but I don?t want a list, all I want is a count. I am pretty sure I should use the COLSUM Function. I created an active link with a set fields, choosing the column and choosing ?COLSUM? as the value. Oh great remedy gurus help this mere mortal that I may thwart the request for constant reporting data from my highers by giving them real time data??(Today IS Friday) THANKS !! John Mosco W01/GS-11 IT Coordinator Customer Support Manager PNG JFHQ J6 Com: 717-861-8143 DSN: 312-491-8143 Fax: 717-861-8414 CALL, FAX, or EMAIL the Pennsylvania National Guard Operations Desk and start a REMEDY Ticket for all RCAS issues or requests. Operations Desk Commercial: 717.861.2899 Operations Desk DSN: 312.491.2899 Operations Desk FAX: 717.861.8414 [EMAIL PROTECTED] Do not withhold good from those who deserve it, when it is in your power to act. Proverbs 3:27 __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: COLSUM - How do I make a field show the sum of all tickets with a certain category or distict attribute ?
You are likely looking for COLCOUNT instead of sum...if your just looking for a count of the tickets _ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Mosco, John D. Sent: Friday, May 25, 2007 6:48 AM To: arslist@ARSLIST.ORG Subject: Re: COLSUM - How do I make a field show the sum of all tickets with a certain category or distict attribute ? ** ALL, I am sure this is an easy one. I have a table that list all the tickets that meet a certain distinct attribute in this case a field called 'ServiceYesNo' Values In Service or Out of Service. The table displays fine but I don't want a list, all I want is a count. I am pretty sure I should use the COLSUM Function. I created an active link with a set fields, choosing the column and choosing COLSUM as the value. Oh great remedy gurus help this mere mortal that I may thwart the request for constant reporting data from my highers by giving them real time data..(Today IS Friday) THANKS !! John Mosco W01/GS-11 IT Coordinator Customer Support Manager PNG JFHQ J6 Com: 717-861-8143 DSN: 312-491-8143 Fax: 717-861-8414 CALL, FAX, or EMAIL the Pennsylvania National Guard Operations Desk and start a REMEDY Ticket for all RCAS issues or requests. Operations Desk Commercial: 717.861.2899 Operations Desk DSN: 312.491.2899 Operations Desk FAX: 717.861.8414 [EMAIL PROTECTED] Do not withhold good from those who deserve it, when it is in your power to act. Proverbs 3:27 __20060125___This posting was submitted with HTML in it___ __20060125___This posting was submitted with HTML in it___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: COLSUM Results???
Quoting from the Admin tool Help: Help(menu) -- Contents and Index (first item on the menu) Index (tab) Search for COLSUM For a given column on a table or results list field, returns the sum of all values as a real type. Following are exceptions: For char columns, converts the column values to numeric and sums up the numeric values. If the column type is currency, the sum of the currency value is calculated, and the currency type is ignored. If the column type is enum (radio button or selection), the sum of the enum value is calculated. COLSUM ignores date/time, date, and time columns; and returns NULL. Are you seeing a different behaviour? ARS101 -- 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 10/10/06, Christopher Pruitt [EMAIL PROTECTED] wrote: Quick question for the list. When using the COLSUM we are getting some strange results. My question is how does COLSUM handle NULL values? Does it ignore them or will the NULL value cause the COLSUM to fail? Christopher Pruitt Consultant Specialist EDS - Bank of America I3-Inventory IW Infrastructure Team Phone: +1-972-605-7702 (8-835) mailto:[EMAIL PROTECTED] ___ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
