Re: driver.exe, row counts...
I am TARDY for the PARTY but Yeah Danny you are correct: select * from some_table where rownum = (some number you specify goes here) Thanks For Letting Me Speak, Mike Date: Fri, 31 Aug 2012 18:29:17 +0200 From: danny.kell...@strategicworkflow.com Subject: Re: driver.exe, row counts... To: arslist@ARSLIST.ORG 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 lj.longwing@mda.mil wrote: 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 itaccording 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 WAYYY T 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
Re: driver.exe, row counts...
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 lj.longwing@mda.mil wrote: 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 itaccording 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 WAYYY T 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
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 itaccording 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 WAYYY T 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. 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
Re: driver.exe, row counts...
Hi, A workaround for your problems would be to make the child records sum themselves up whenever they are changed in a specific record in a special summary form. When you then need the enormous COLSUM(), you just read the value from the single record in the summary form. So it all depends on how you design things, right ;-) If you keep the Remedy architecture and possibilities in mind when designing things, you can usually make good use of the built in functionality. Best Regards - Misi, RRR AB, http://rrr.se 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 itaccording 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 WAYYY T 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. 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 ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where
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 itaccording 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 WAYYY T 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
Re: driver.exe, row counts...
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 itaccording 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 WAYYY T 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
Re: driver.exe, row counts...
The code (arserverd) know the database it's on and generates the appropriate SQL. Oracle has an equivalent so it's not much code to handle it the same way - though indeed it could be handled in an entirely different way. Developers choice as usual. That being said, I saw an interesting bug in Oracle on Linux (7.6.04) which caused a corrupted SQL statement to be generated and a huge whack of memory to be lost. The same (identical) bug would probably not be triggered (code paths ..) on an SQL Server. Ben www.softwaretoolhouse.com -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Longwing, LJ CTR MDA/IC Sent: August-30-12 09:04 To: arslist@ARSLIST.ORG Subject: Re: driver.exe, row counts... 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 itaccording 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 WAYYY T 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
AD: driver.exe, row counts...
Meta-Query, bundled with Meta-Update, allows you to run any SQL query without
any SQL clients. In addition, one of the script library samples, AR Tables
Report
http://www.softwaretoolhouse.com/product/SthMupd/scriptlib/scr_Rpt-arschema.html
, generates a CSV of pertinent fields from arschema and adds a record count
for some table types.
The distribution can be unzipped on any client machine. Deleting the files is
the uninstall procedure.
Trials are free and include all bundled utilities: Meta-Update, Query, Schema,
Delete.
Cheers
Ben Chernys
Senior Software Architect
Description: logoSthInc-sm
Canada / Deutschland
Mobile: +49 171 380 2329GMT + 1 + [ DST ]
Email:mailto:ben.cher...@softwaretoolhouse.com
Ben.Chernys_AT_softwaretoolhouse.com
Web: http://www.softwaretoolhouse.com/ www.softwaretoolhouse.com
Check out Software Tool House's free Diary Editor and out Freebies
Section for an ITSM 7.6.04 Forms and Fields spreadsheet.
Meta-Update, our premium ARS Data tool, lets you automate
your imports, migrations, in no time at all, without programming,
without staging forms, without merge workflow.
http://www.softwaretoolhouse.com/ http://www.softwaretoolhouse.com/
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky
Sent: August-28-12 23:03
To: arslist@ARSLIST.ORG
Subject: Re: driver.exe, row counts...
Hi,
The driver-program should work fine with the ARGetListEntry()-api-call.
The important thing here is to minimize the impact on the target if you have
millions of records. That is why I specify one Query List Field where I only
return one byte.
Secondly I specify 1 as max entries returned.
You can still see the total count in Number of matches: 174
If your server does not allow unqualified searches, you might have to add
(1=1), or ('1' 0) or something like that.
Command: gle
GETLIST ENTRY
Schema: testform
Qualifier Struct:
None, And, Or, Not, Relop or From Field (0, 1, 2, 3, 4, 5) (0):
Number of query list fields (0): 1
Entry list field entry
Field id (0): 1
Column width (20): 1
Separator ( ):
Number of sort fields (0):
First Retrieve (0):
Maximum number of entries to retrieve (500): 1 Get number of matches? (F): T
Use Locale for search? (F):
ARGetListEntry results
ReturnCode: WARNING
Entry List List : 1 items
Entry List Struct:
Entry Id : F1
Short Description : F
Number of matches: 174
Status List : 1 items
Status Struct :
Message type : WARNING
Message number : 66
Message: The query matched more than the maximum number of entries
specified for retrieval
Command:
Best Regards - Misi, RRR AB, http://www.rrr.se 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
http://rrr.se.
You should be able to use the Misc List SQL and put in SELECT COUNT(*)
From T1920
That will return 1 integer value
Fred
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Rick
Sent: Tuesday, August 28, 2012 5:53 PM
To: arslist@ARSLIST.ORG
Subject: Re: driver.exe, row counts...
Thanks for the quick response.
I don't have access to the db, and can't introduce new code without
going through the change process. I was using driver.exe on another
task, and tried select count (*) from 1920, but that didn't work.
Using t1920, t1920;, 1920, didn't work either, so I thought I'd ask.
I just need a fairly quick way to keep tabs on row counts.
rp
-Original Message-
On 8/28/2012 3:00 PM, Pat Zandi wrote:
QQ: do you need this data outside of remedy cause you can just run a
SQL line count and put in a file on server? Vice calling from outside
for the data? Just wondering
Sent from my iPhone
-Original Message-
On Aug 28, 2012, at 17:53, Rick wrote:
Hi, everyone,
Is it possible to use driver.exe to determine row counts for a form?
I'm on 7.6.04 sp3, windows, ms sql.
Thanks,
Rick
__
_ UNSUBSCRIBE or access ARSlist Archives at
http://www.arslist.org www.arslist.org
attend wwrug12 http://www.wwrug12.com www.wwrug12.com ARSList: Where the
Answers Are
___
UNSUBSCRIBE or access ARSlist Archives at http://www.arslist.org
www.arslist.org attend wwrug12 http://www.wwrug12.com www.wwrug12.com
ARSList: Where the Answers
Re: driver.exe, row counts...
I misunderstood your question last night Rick. I did the following today: glsql select count(*) from HPD_Help_Desk [Enter] for the remaining questions If all you need are counts it is must faster to do direct SQL calls than GetListEntry, I would think. Use the names in the view instead of the T names. Dale Hurtt ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: driver.exe, row counts...
thanks to everyone who replied! r On 8/29/2012 8:31 AM, Dale Hurtt wrote: I misunderstood your question last night Rick. I did the following today: glsql select count(*) from HPD_Help_Desk [Enter] for the remaining questions If all you need are counts it is must faster to do direct SQL calls than GetListEntry, I would think. Use the names in the view instead of the T names. 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
Re: driver.exe, row counts...
Hi, It might be faster, but it is direct SQL and may not work on any database... Best Regards - Misi, RRR AB, http://rrr.se I misunderstood your question last night Rick. I did the following today: glsql select count(*) from HPD_Help_Desk [Enter] for the remaining questions If all you need are counts it is must faster to do direct SQL calls than GetListEntry, I would think. Use the names in the view instead of the T names. 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
Re: driver.exe, row counts...
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
Re: driver.exe, row counts...
No database that I know of :) The flat file database was dropped some time ago. I expect it would not have worked on it. View Names can be different in different databases and are given by the ViewName column of arschema (since 7.1). For an algorithm to get the view names prior to 7.1 for all databases, look at the sample script ArSchema-Report http://www.softwaretoolhouse.com/product/SthMupd/scriptlib/scr_Rpt-arschema.html (also available in the Script Library). Cheers Ben -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Dale Hurtt Sent: August-29-12 15:57 To: arslist@ARSLIST.ORG Subject: Re: driver.exe, row counts... 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 http://www.arslist.org www.arslist.org attend wwrug12 http://www.wwrug12.com 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 smime.p7s Description: S/MIME cryptographic signature
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. 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
driver.exe, row counts...
Hi, everyone, Is it possible to use driver.exe to determine row counts for a form? I'm on 7.6.04 sp3, windows, ms sql. Thanks, Rick ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: driver.exe, row counts...
QQ: do you need this data outside of remedy cause you can just run a SQL line count and put in a file on server? Vice calling from outside for the data? Just wondering Sent from my iPhone On Aug 28, 2012, at 17:53, Rick r...@netfirst.com wrote: Hi, everyone, Is it possible to use driver.exe to determine row counts for a form? I'm on 7.6.04 sp3, windows, ms sql. Thanks, Rick ___ 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
Re: driver.exe, row counts...
Thanks for the quick response. I don't have access to the db, and can't introduce new code without going through the change process. I was using driver.exe on another task, and tried select count (*) from 1920, but that didn't work. Using t1920, t1920;, 1920, didn't work either, so I thought I'd ask. I just need a fairly quick way to keep tabs on row counts. rp On 8/28/2012 3:00 PM, Pat Zandi wrote: QQ: do you need this data outside of remedy cause you can just run a SQL line count and put in a file on server? Vice calling from outside for the data? Just wondering Sent from my iPhone On Aug 28, 2012, at 17:53, Rick r...@netfirst.com wrote: Hi, everyone, Is it possible to use driver.exe to determine row counts for a form? I'm on 7.6.04 sp3, windows, ms sql. Thanks, Rick ___ 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
Re: driver.exe, row counts...
You should be able to use the Misc List SQL and put in SELECT COUNT(*) From T1920 That will return 1 integer value Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Rick Sent: Tuesday, August 28, 2012 5:53 PM To: arslist@ARSLIST.ORG Subject: Re: driver.exe, row counts... Thanks for the quick response. I don't have access to the db, and can't introduce new code without going through the change process. I was using driver.exe on another task, and tried select count (*) from 1920, but that didn't work. Using t1920, t1920;, 1920, didn't work either, so I thought I'd ask. I just need a fairly quick way to keep tabs on row counts. rp -Original Message- On 8/28/2012 3:00 PM, Pat Zandi wrote: QQ: do you need this data outside of remedy cause you can just run a SQL line count and put in a file on server? Vice calling from outside for the data? Just wondering Sent from my iPhone -Original Message- On Aug 28, 2012, at 17:53, Rick wrote: Hi, everyone, Is it possible to use driver.exe to determine row counts for a form? I'm on 7.6.04 sp3, windows, ms sql. Thanks, Rick ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: driver.exe, row counts...
Hi,
The driver-program should work fine with the ARGetListEntry()-api-call.
The important thing here is to minimize the impact on the target if you
have millions of records. That is why I specify one Query List Field where
I only return one byte.
Secondly I specify 1 as max entries returned.
You can still see the total count in Number of matches: 174
If your server does not allow unqualified searches, you might have to add
(1=1), or ('1' 0) or something like that.
Command: gle
GETLIST ENTRY
Schema: testform
Qualifier Struct:
None, And, Or, Not, Relop or From Field (0, 1, 2, 3, 4, 5) (0):
Number of query list fields (0): 1
Entry list field entry
Field id (0): 1
Column width (20): 1
Separator ( ):
Number of sort fields (0):
First Retrieve (0):
Maximum number of entries to retrieve (500): 1
Get number of matches? (F): T
Use Locale for search? (F):
ARGetListEntry results
ReturnCode: WARNING
Entry List List : 1 items
Entry List Struct:
Entry Id : F1
Short Description : F
Number of matches: 174
Status List : 1 items
Status Struct :
Message type : WARNING
Message number : 66
Message: The query matched more than the maximum number of entries
specified for retrieval
Command:
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.
You should be able to use the Misc List SQL and put in SELECT COUNT(*)
From T1920
That will return 1 integer value
Fred
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Rick
Sent: Tuesday, August 28, 2012 5:53 PM
To: arslist@ARSLIST.ORG
Subject: Re: driver.exe, row counts...
Thanks for the quick response.
I don't have access to the db, and can't introduce new code without
going through the change process. I was using driver.exe on another
task, and tried select count (*) from 1920, but that didn't work. Using
t1920, t1920;, 1920, didn't work either, so I thought I'd ask. I just
need a fairly quick way to keep tabs on row counts.
rp
-Original Message-
On 8/28/2012 3:00 PM, Pat Zandi wrote:
QQ: do you need this data outside of remedy cause you can just run a SQL
line count and put in a file on server? Vice calling from outside for
the data? Just wondering
Sent from my iPhone
-Original Message-
On Aug 28, 2012, at 17:53, Rick wrote:
Hi, everyone,
Is it possible to use driver.exe to determine row counts for a form?
I'm on 7.6.04 sp3, windows, ms sql.
Thanks,
Rick
___
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
