Is there a way to make the Load Statement dynamic so that the dataset pulled for the QVW is only the records the user wants?
We have two normalized tables that, once denormalized, grows to a set of over 6 million rows. The user wants to report on these data; but wants to choose which records based on criteria in the original tables.
Please help me to understand how loading all 6mil records is faster than only loading say 500,000? I have to think that the less data pulled at run-time, the faster the report loads. Our users interact with reports that are hosted on our server but they do not change them as a developer might, re-load, file, etc are all locked out and they are only end users in the strictest sense.
Would this keep the initial data set from loading until a variable is filled out? Or would you put in a default value that returns a null until a var is put in? Our users can't reload at will; is there a statement that can be run as a pre-load?
It's faster in the sense that you don't have to reload each time you need a different set of records. You load all 6 million once. Let's say that takes 15 minutes. It's batch, it runs overnight and no user is waiting.
Now each user uses the same 6mil row dashboard. User A wants products A,B,C. Click. In a few seconds they have a completed report. User B wants products X,Y,Z. Click. In a few seconds they have a report.
If I understand your original question, you are asking about rerunning the script each time the user wants a different set of data. That woud take minutes and a user would be waiting for the result.
That's what I meant by faster. Am I understanding your requirement?
"Users cannot reload by will" - are you saying that the application is on QV server? In this case Rob is 100% correct. Reloading application with 6 mln records once is faster than reloading many applications with 500K records, which can be done using Publisher. And, one application accessed by many users takes fewer resourses than multiple applications with one user each.