Slowly building a QVD to avoid SQL server performance degredation
My application uses QVD's to store an audit trail.
The issue is that on the first load, the SQL query will often cause the SQL server to grind to a halt in production. In the dev environment with less records, this isn't an issue.
Once the QVD is built, then the subsequent loads will only add to the QVD what has changed, which is all good.
So one option is to use the SELECT TOP X records to load only the first say 10,000 records at a time. These records are then written to the QVD and the record counter is updated, so on the next reload, the next X records are loaded.
This works OK, but I would need to ask my user to keep hitting the reload command.
(the second option is to rewrite the SQL query and use temporary tables etc.)
Is there a way to do this in a For Loop?
What I would need is to have is a variable which will tell me how many records have been returned by the query.
I have tried the
load field, count(field) as NumberOfRecordsLoaded
Group by field;
but that doesn't seem to work, as the NumberOfRecordsLoaded doesn't seem to get a value.