Unnecessary additional query in Filter...
I found this filter which appears like it may be a part of a filter guide (I haven’t investigated).. If it turns out to be an unused filter, ignore this.. If not I think there is one additional query which is rather unnecessary in this. The Filter Name is INT:AASFND:DAT:Site_Count_Q There are two direct SQL’s in two set fields, where the result of the first is used in the second.. First Set Fields query: SELECT schemaId FROM arschema WHERE name = 'AAS:Activity' “T” + $1$ is set to $z1D_schemaID$ Second set fields query: SELECT COUNT(C1) FROM $z1D_schemaID$ WHERE C26001 = '$Site$' COUNT(C1) is then set to $z1D_RecordCount01$ If a direct SQL had to be used, couldn’t these two have been combined into a single SQL against the view of AAS:Activity instead? SELECT COUNT(REQUEST_ID) FROM AAS_ACTIVITY WHERE SITE = $SITE$ would work just as well with just one set field to $z1D_RecordCount01$ I’m guessing this must be running on a mass data load, so possibly resulting in 2 SQLs per record... Again I have not found out where this filter is used yet, so I may be wrong about it being fired for every record imported.. But the point is even if it is running on a single record, it could have been better written against the view. Joe ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: Unnecessary additional query in Filter...
They may be trying to work around inconsistent naming for the database views. Sometimes there are issues with the views being created (ever seen an invalid view in your remedy database?). By using the table (instead of the view) they avoid these issues. The arschema table now has a column that stores the view name. This did not always exist. I do the same kind of thing in my workflow at times, just because I have learned over the years not to trust the views. They could have done a set fields from the form (qual of 1=1), use the first matching request, then use the $LASTID$ keyword as well, thus avoiding the direct sql altogether, but I don't know what they were thinking at the time nor do I have any context on this workflow that would warrant the use of direct sql. Axton Grams On Wed, Apr 18, 2012 at 1:55 PM, Joe Martin D'Souza jdso...@shyle.netwrote: ** I found this filter which appears like it may be a part of a filter guide (I haven’t investigated).. If it turns out to be an unused filter, ignore this.. If not I think there is one additional query which is rather unnecessary in this. The Filter Name is INT:AASFND:DAT:Site_Count_Q There are two direct SQL’s in two set fields, where the result of the first is used in the second.. First Set Fields query: SELECT schemaId FROM arschema WHERE name = 'AAS:Activity' “T” + $1$ is set to $z1D_schemaID$ Second set fields query: SELECT COUNT(C1) FROM $z1D_schemaID$ WHERE C26001 = '$Site$' COUNT(C1) is then set to $z1D_RecordCount01$ If a direct SQL had to be used, couldn’t these two have been combined into a single SQL against the view of AAS:Activity instead? SELECT COUNT(REQUEST_ID) FROM AAS_ACTIVITY WHERE SITE = $SITE$ would work just as well with just one set field to $z1D_RecordCount01$ I’m guessing this must be running on a mass data load, so possibly resulting in 2 SQLs per record... Again I have not found out where this filter is used yet, so I may be wrong about it being fired for every record imported.. But the point is even if it is running on a single record, it could have been better written against the view. Joe _attend WWRUG12 www.wwrug.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: Unnecessary additional query in Filter...
That seems to be a fair explanation, but I would still go with the SQLs against the views concept until there is a problem. In 99.99% cases it works fine with a few exceptions that can cause it to not work.. I just thought it was not the best piece of workflow, that’s all.. Joe From: Axton Sent: Wednesday, April 18, 2012 3:24 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Unnecessary additional query in Filter... ** They may be trying to work around inconsistent naming for the database views. Sometimes there are issues with the views being created (ever seen an invalid view in your remedy database?). By using the table (instead of the view) they avoid these issues. The arschema table now has a column that stores the view name. This did not always exist. I do the same kind of thing in my workflow at times, just because I have learned over the years not to trust the views. They could have done a set fields from the form (qual of 1=1), use the first matching request, then use the $LASTID$ keyword as well, thus avoiding the direct sql altogether, but I don't know what they were thinking at the time nor do I have any context on this workflow that would warrant the use of direct sql. Axton Grams On Wed, Apr 18, 2012 at 1:55 PM, Joe Martin D'Souza jdso...@shyle.net wrote: ** I found this filter which appears like it may be a part of a filter guide (I haven’t investigated).. If it turns out to be an unused filter, ignore this.. If not I think there is one additional query which is rather unnecessary in this. The Filter Name is INT:AASFND:DAT:Site_Count_Q There are two direct SQL’s in two set fields, where the result of the first is used in the second.. First Set Fields query: SELECT schemaId FROM arschema WHERE name = 'AAS:Activity' “T” + $1$ is set to $z1D_schemaID$ Second set fields query: SELECT COUNT(C1) FROM $z1D_schemaID$ WHERE C26001 = '$Site$' COUNT(C1) is then set to $z1D_RecordCount01$ If a direct SQL had to be used, couldn’t these two have been combined into a single SQL against the view of AAS:Activity instead? SELECT COUNT(REQUEST_ID) FROM AAS_ACTIVITY WHERE SITE = $SITE$ would work just as well with just one set field to $z1D_RecordCount01$ I’m guessing this must be running on a mass data load, so possibly resulting in 2 SQLs per record... Again I have not found out where this filter is used yet, so I may be wrong about it being fired for every record imported.. But the point is even if it is running on a single record, it could have been better written against the view. Joe ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
