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 evolving....for 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 summing....took a fairly powerful server some 5 min's to get me the sum....whereas 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 thing....the table itself isn't set to chunk....the 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 does....it simply causes the > server to increase the chunk size by that amount until it's dealing with > the > entire table....and it does this automatically when you ask for a > colsum....it 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 necessary....thus 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 column....so 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" _______________________________________________________________________________ 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"
