Does it use rownum? I think that's how you do it via SQL if I remember correctly
On 30 Aug 2012, at 17:04, "Longwing, LJ CTR MDA/IC" <[email protected]> wrote: > Yes...SQL...I'm curious how it handles the same situation in Oracle... > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:[email protected]] On Behalf Of Grooms, Frederick W > Sent: Thursday, August 30, 2012 8:26 AM > To: [email protected] > Subject: Re: driver.exe, row counts... > > One thing to remember ... Oracle does not support TOP, so I didn't think > Remedy would be using it. (Is your example from the SQL log on the server?) > > Fred > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:[email protected]] On Behalf Of Longwing, LJ CTR MDA/IC > Sent: Thursday, August 30, 2012 7:50 AM > To: [email protected] > Subject: Re: driver.exe, row counts... > > Misi, > I tend to agree with you, but in the last several years I found MANY things > that the Remedy server does poorly...I'll give you an example. > > You need to get the sum of a particular table column via filter with the > default server side table chunk of 1000, and you have 200,000 records in the > table. > > Issuing a SELECT SUM(COLUMN) FROM TABLE WHERE QUALIFICATION returns the > result in sub second. > > If you however do a set-field COLSUM(COLUMN), it's interesting to watch the > Remedy server do its thing and how it does it....according to the docs, the > server side table chunk size is used to manage memory, but in this situation > it actually causes NO memory savings, and causes the entire process to take > WAYYYYYYY TOOOO long...basically what it does is > > SELECT TOP 1001 C1, C2, C3, etc FROM TABLE WHERE QUALIFICATION > > Where C1-x are the columns in the table. Then it determines that there are > more records than that, so it adds 1000 to the select > > SELECT TOP 2001 C1, C2, C3, etc FROM TABLE WHERE QUALIFICATION > > Again, determining that it needs more records > > Etc > > It continues issuing queries until the 'top' is high enough to select all of > the records, each subsequent select taking more time than the last. I have > seen this process take over 10 minutes with a single transaction in a custom > system I was working on at the time. Converting COLSUM set-field calls to > SELECT SUM calls greatly enhanced the performance and scalability of the > process. > > I agree with you that things should be done 'inside' the Remedy framework > where that doesn't significantly impact performance of the process, but I > have come across countless situations in the last several years that pushed > Remedy beyond its boundaries and needed 'help' from other methods of doing > things to get them done properly. In fact I'm presenting one of those @ RUG > this year :) > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:[email protected]] On Behalf Of Misi Mladoniczky > Sent: Wednesday, August 29, 2012 10:14 PM > To: [email protected] > Subject: Re: driver.exe, row counts... > > Hi, > > Well, this particular code might very well work across databases. > > The other main concern with Direct SQL is that it is untraceable by the AR > System. The AR System does not understand what you are doing, and you can not > use AR tools to trace and analyze it in the same way as other workflow. > > Then we have the issue of going straight at the T-tables, and other things > related to datatype-conversion for timestamps, enum-values > > You will also be bypassing permissions. > > I am convinced that using the ARAPI and the provided AR-workflow-actions is > worth some effort in lieu of direct SQL. Even if it would mean some small > performance hit. > > In this specific case, it much depends on the nomber of records in the table. > > Best Regards - Misi, RRR AB, http://www.rrr.se (ARSList MVP 2011) > > Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10/11): > * 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. > > -----Original Message----- >>> It might be faster, but it is direct SQL and may not work on any >>> database... >> >>>> select count(*) from HPD_Help_Desk >> >> Does Remedy support a database where the above ANSI SQL won't work? >> >> I suppose if ARS implements view names for their tables differently in >> another database, it would not be "HPD_Help_Desk", but does anyone >> know of which database that would be? Just curious because the above >> works on Oracle and SQL Server. >> >> Dale Hurtt > > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 > www.wwrug12.com ARSList: "Where the Answers Are" > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
