We are connecting to Hive through InfoAssist+ and we’ve found that the order of our WHERE clause is the number one key to efficiency.

The WHERE clause will filter these tables down by the order they appear in the WHERE clause generated by InfoAssist. For example, we have our data in HIVE grouped by Month, so If we first filter on month then we are looking at just one folder. However, if we filter by another value and then by Month, it will go through all of the folders looking for that first filter and then filter down by month.

Select * from table where Month = ‘201807’ and thing = ‘searchForValue’
That works great!

Select * from table where thing = ‘searchForValue’ and Month = ‘201807’
That will go through everything in order to limit the table by ‘searchForValue’ and then it will go through those records limiting things by ‘201807’

Is there any way to ensure that the Month criteria is always evaluated first?

We’ve noticed that InfoAssist will build the SQL of the WHERE clause using user created filters and then the mandatory filters that had been added to the reporting object or the synonym. The next order of the filters in the SQL then looks at the first table and moves on with the second and third table and then it will generate in reverse order. So putting in a filter for ‘searchForValue’ then adding the filter for ‘201807’ will generate WHERE clause in with ‘201807’ then ‘searchForValue’ followed by any mandatory filter (or WHERE Clause from a Reporting Object).

We are looking for a way to always push the Month to the top. We’d like to do this as a mandatory filter because we don’t want a user to be able to generate anything without specifying the month.

Thanks for looking at this!

This message has been edited. Last edited by: FP Mod Chuck,

Hi Screen Handle

Welcome to Focal Point! It seems pretty clear that you have done a thorough analysis of this problem. I think your best bet is to open a case with techsupport and see if they have any suggestions on this as well.

There may be someone in FP land that can provide a technique to help as well.

Please edit your profile and provide us information on the release of WF you are using.

Thank you for the quick reply, Chuck.

I updated my profile some.

Edition:    WebFOCUS Enterprise Edition
Product Release:    8.2
Service Pack:    0.2
Package Name:    wf020118a
Release ID:    8202m
Build/GEN Number:    14
Build/GEN Date:    February 1, 2018 3:00:08 PM CST
Application Server:    Apache Tomcat/8.5.20

Thanks!
Screen Handle