Yes...SQL...I'm curious how it handles the same situation in Oracle... -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Thursday, August 30, 2012 8:26 AM To: arslist@ARSLIST.ORG 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:arslist@ARSLIST.ORG] On Behalf Of Longwing, LJ CTR MDA/IC Sent: Thursday, August 30, 2012 7:50 AM To: arslist@ARSLIST.ORG 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:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: Wednesday, August 29, 2012 10:14 PM To: arslist@ARSLIST.ORG 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"
