robert fuschetto Posted November 10, 2021 Share Posted November 10, 2021 I am looking for some sort of technical description of what happens when an IBI fex is run. Right Click Run on the fex name. What we experience: Right Click Run We wait, anywhere from a minute to 2 minutes for parameters to appear. We select values. We press the run button. Data is returned in anywhere from 15s to 1minwhich we find acceptable. *If the user picks a specific DEPT, data is returned much quicker than if they had chosen an Country. We are good with this part. The fex hit a table with 11M rows. It prompts for 5 items. Selection is applied and data extracted into a HOLD file. Some Calculations are done off the hold file etcand a report pops up. Again form the time the user enters parameter values and hits run to when the report pops up varies but the return time is acceptable. THE QUESTION iswhat is webfocus doing during the time I right click the fex name, click runand wait up 2 minutes for the parameters to appear Link to comment Share on other sites More sharing options...
Martin Yergeau Posted November 10, 2021 Share Posted November 10, 2021 It extrating data to create the filters choices. So, depending on how you have built your parameters filters and from which source of data, it may have to query your table with 11M rows. And since its a parameters list of choices, need to display only distinct values. At the end, if you are missing indexes on the 11M rows table, it may takes a while to have the data returned. A lot of things may occurs and differents reasons may explain that, but its the main one that I can see. Link to comment Share on other sites More sharing options...
Evan Brown 2 Posted November 10, 2021 Share Posted November 10, 2021 I got this in an IBI presentation once: What Happens When You Run A Report Locate The File Description(s) And Data Files Determine Access Method (SUFFIX=) Select Records Based On Database Values Determine Values Of Defined Fields Select Records Based On Defined Fields Sort The Data Prepare Individual And/ Or Aggregated Values Resolve Computed Fields Select Records Based On Total Tests Process BY TOTAL Format The Report Link to comment Share on other sites More sharing options...
robert fuschetto Posted November 10, 2021 Author Share Posted November 10, 2021 MartinY, that is what I feared. On the HTML page I can redirect parameters to Dictionarybut not in the fex. To be honest, on the HTML page, either way I must wait for the reports to load it would seem, the only value I have found in redirecting parameters on the HTML page to dict table is that chaining is faster. I guess I should see if the table is Indexed. Link to comment Share on other sites More sharing options...
Martin Yergeau Posted November 10, 2021 Share Posted November 10, 2021 robertf: I guess I should see if the table is Indexed. The above is one option and one tthing to check. Another is to create specific tables for parameters list only from which youll go against. E.g. with an overnight process (using Report Caster as an example) you can read your 11M rows table and extract the values against which you will create filters/parameters and hold that. Then use the hold table to feed your list box/parameters. Assume that in my below sample GGSALES change everyday. Let say that i would like to create a list of product per city. It may take a while to extract the list directly from GGSALES meaning that each time that I need that list of city/product I need to go against the 4317 rows of the table (actual since). But if I, over night, I extract that list and hold it I then have a table of only 117 rows. Using that hold table to create and feed my parameters list will be much more faster than reading each time GGSALES. And I can even create an index on that hold file. TABLE FILE GGSALES BY CITY BY PRODUCT ON TABLE HOLD AS MySavedPath/MyParamList FORMAT XFOCUS INDEX CITY PRODUCT END I normally dont use the source table as the source for the parameters list, when possible, I hold the parameters values in a seperate table. Especially for large table as yours. Link to comment Share on other sites More sharing options...
robert fuschetto Posted November 10, 2021 Author Share Posted November 10, 2021 MartinY, What you describe I already have in place and use with the HTML page. Say I have 5 parameters on the HTML page all pointing to the 11M row table. Chaining gets very sloooooow. So I redirect the parameters to, wellthe exact type of table you describe. Now chaining is fast! Are you saying in the report fex, NOT HTML PAGE, I can somehow redirect the parameters away from the 11M rows of data instead to this small table How so I dont follow this at all: ON TABLE HOLD AS MySavedPath/MyParamList FORMAT XFOCUS INDEX CITY PRODUCT So lets say we hit our 11M row table TABLE FILE BIGTABLE SUM Amt1, Amt2 Amt3 By Country, State, Provence, Country WHERE Country MultiSelct any country value in BIGTABLE State MultiSelct any State values in BIGTABLE etc ect. When I right Click run, it goes through the 11M rows to populate the dropdowns for the user. I wait a minute or two then run the report. So, what would I do in my fex to make those drop downs get populated by my smaller table that I already have containing just the 1000 or so rows of Country,State,Province etc values you have me excited here! Link to comment Share on other sites More sharing options...
David Beagan Posted November 10, 2021 Share Posted November 10, 2021 Just like you redirected the HTML pages parameters to new tables, you can do so with your report.fex. I assume you have created the report in InfoAssist which doesnt seem to offer you the option to redirect to new tables. But you can go into the code editor and find where it has, for example, WHRE STATE EQ &STATE.(FIND STATE IN BIGTABLE). and change it to WHERE STATE EQ &STATE.(FIND STATE IN STATETABLE). Link to comment Share on other sites More sharing options...
robert fuschetto Posted November 11, 2021 Author Share Posted November 11, 2021 If I can get this to work it would be amazingnever knew of this. Is the syntax that simple Further, if I do this will I lose the GUI interface. We are working in App Studioinfoassist for charts. So when I start the report in the GUI I reference BIGTABLE. Must i make some sort of reference to, say SMALLTABLE before I adjust the WHERE Link to comment Share on other sites More sharing options...
Martin Yergeau Posted November 11, 2021 Share Posted November 11, 2021 Robert, your best guest is : try it. Make a copy of your fex then from the copy try the new process Link to comment Share on other sites More sharing options...
robert fuschetto Posted November 11, 2021 Author Share Posted November 11, 2021 IT WORKED! Question, since I can now NO LONGER get into the GUI (unless I first go in to text mode and comment out the WHERE conditions)is there a way around thisnot complaining mind you! Link to comment Share on other sites More sharing options...
Martin Yergeau Posted November 11, 2021 Share Posted November 11, 2021 I dont think so. In a sense, GUI is very limited and not that flexible compared to what we can do when coding. This is the greatest reason why a hate GUIs : lack of flexibilities When using GUI you normaly end up with a robust result (because you have not been able to add something that does not respect the rules) but often loosing performance and flexibilities as you encounter. As you realized, the problem do not rely on the report itself that goes against the 11M rows table, but the parameters prompting that is not efficient reading 11M rows table. Link to comment Share on other sites More sharing options...
Walter Brengel Posted November 11, 2021 Share Posted November 11, 2021 Hi Evan, What you shared is the behind the scenes of what happens from the Server point of view after all the client processing has happened. Walter Link to comment Share on other sites More sharing options...
Debra Waybright Posted November 11, 2021 Share Posted November 11, 2021 @RobertF, did you try putting your WHERE clause in an Expression in the GUI (Choose new expression in the filter window) This should let you type in the WHERE clause you need without breaking your ability to edit the report in InfoAssist. Link to comment Share on other sites More sharing options...
john cullen Posted November 11, 2021 Share Posted November 11, 2021 Hello Martin, I think I have a way you can get this to work in the GUI. Normally, this is used for Modify, but I think you can leverage it for TABLE. In the describing data manual, there is an attribute called ACCEPT, go find that, research it. If you put the accept attribute in your master file, then InfoAssist / autoprompt should use the file in the accept declaration, to do the lookup. so, build your crossreference files that you use for chaining, and make them the accept attribute for that field in your main master file InfoAssist / Autoprompt will then use the crossreference file instead of a table scan. My confidence in this is not 100%, but it is very high that this will work. I have not used it recently, but I used it in the past. It is subtle, and not often thought about, because ACCEPT is usually used for modify, but it is can be used (at least I think it can be used) this way for autoprompt and InfoAssist WHERE statements. Link to comment Share on other sites More sharing options...
robert fuschetto Posted November 15, 2021 Author Share Posted November 15, 2021 I repointed the WHERE conditions in the fex to another table as was suggested. Open right up now. THANKS!!! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now