We have a little bit of a debate going on here. Someone has LOVs that are set to refresh on open (automatic refresh) – and they have PROMPTs in their report conditions.
When they write a full-client query and report – and it’s available for a WebI user to refresh – when do the LOVs refresh?
Do the LOVs get refreshed before the Prompt screen is displayed?
Or does a given LOV only get refreshed if the user clicks the “Values” button?
Anita Craig 
(BOB member since 2002-06-17)
LOVs get refreshed when the Values button is hit. For WebI, if the option to choose from list is chosen, then the LOV get’s refreshed as the prompt comes up.
Steve Krandel (BOB member since 2002-06-25)
I would like to re phrase the question. We are in the process of implementing a new process where we uncheck the “refresh on open” for LOVs and we export the LOV with the universe. We will then refresh the LOV values using a nightly batch job on BCA. When a user clicks on “Values” the user gets a cached version of the LOV from the nightly run (so a the LOV is not running another refresh).
My question is, “Will any of this bring the prompt screen up faster?” My assumption is that this new process will not bring up the prompt screen any faster. I don’t think the prompt screen is cached with the refreshing of the LOV nightly. So, the LOV may come back faster, but the screen for the prompts won’t come back any quicker will it? We sometimes have a delay of almost a minute waiting for the prompt screen to show up. What can we do improve the performance here?
kmahler 
(BOB member since 2002-07-29)
Thanks for the reply. My problem is happening with webi 2.7.3 csp701 not ZABO. But I think I know why it is occurring. The report has 8 dps and so with 8 dps it just takes a long time for the prompt screen to come up. I also read somewhere that upgrading to 2.7.4 might help this problem. I will pass along all of these recommendations.
kmahler (BOB member since 2002-07-29)
Did you take a closer look at this post from Sandy Brotje in the thread?
Andreas 
(BOB member since 2002-06-20)
OOOHHHHHH! You guys are on UNIX, right. I remember this from a long time ago and it makes no sense at all. I need to find the solution for you. I had something to do with some settings in Supervisor that really should not have mattered, but did. I’ll try to find it for you.
Steve Krandel (BOB member since 2002-06-25)
See this topic:
Questions:
Are you connections set to Synchronous and Disconnect after each transaction?
Are your LOVs set to export with the universe?
Steve Krandel (BOB member since 2002-06-25)
We are set to use Synchronous and keep the connection active for one minute. Right now our LOVs are not set to export with universe, but we are planning to change that. The new procedure is to export LOV with universe and uncheck the Automatic Refresh before Use. Currently we are checked with “Automatic Refresh before Use”.
kmahler (BOB member since 2002-07-29)
I believe “they” changed to keep the connection active for one minute based on feedback from the DBAs – something to the effect that they were having trouble with slow logins, so they thought that keeping the connection active for one minute would cause less thrashing, or something to that effect.
Do you think that being on Unix – with complex row-level restrictions set in Supervisor (a huge IF EXISTS clause) – could be causing the prompt screen to be slow?
One of their reports in question has 8 Data Providers – and the 8 queries share 5 or 6 Prompts.
Anita Craig (BOB member since 2002-06-17)
Disconnect after each transaction will help.
Steve Krandel (BOB member since 2002-06-25)
All, there’s another issue related to prompt times that we’ve just tested. WebI 2.7.4 (I think - it was either .3 or .4) has fixed a bug in WebI whereby every single prompt was causing another copy of the universe to be copied/cached. The more complex the universe, the more noticable this is.
We have a huge universe with over 150 tables in it, and on a report with 3 prompts the time to went down drastically upgrading from 2.7.0 to 2.7.4. 150 seconds total to display all three prompts (press the Show Values button for each), down to 46 seconds. Two of the three prompts are particularly large (thousands of rows), but the SQL and resulting HTML for the LOVs appear to be unchanged. So, we think the savings are strictly due to the large universe not having to get copied/cached over and over.
Additionally, on promtps agains smaller universes (10-20 tables), there was only a five second savings.
Hope this helps!
craigpeters (BOB member since 2003-05-09)
Hi kmahler,
If I make the LOVs refresh in the universe using a nightly batch job on BCA , would it refresh the LOVs for each Zabo User A/c?
What I am trying to achieve is
Uncheck the Auto Refresh option but still Zabo users need to be able to see the latest values when values button is clicked to see LOVs.
Thanks
CS
mybob (BOB member since 2004-05-19)
We did this for WebI users. I’m not entirely sure if it works the same for ZABO users. I think it probably does since ZABO goes through the web server. What you have to do is uncheck the auto refresh and also check export with universe in Designer. This caches the LOV and when the user clicks on show values, the cached values are shown. We also have to run a nightly batch job on BCA which refreshes the LOVs. The batch job uses BO-Designer SDK/macro to refresh the values for LOVs. So your answer is probably yes.
kmahler (BOB member since 2002-07-29)
Thanks Cathy for your reply. When user accessies BO reports thru ZABO (BO , not viewing in HTML or PDF) , LOVs get downloaded and stored on his/hers m/c locally. My question is how to replace/delete them ?
mybob
mybob (BOB member since 2004-05-19)