2 Replies Latest reply: Oct 10, 2012 5:37 AM by John-Paul Della Putta RSS

    Slowly building a QVD to avoid SQL server performance degredation

    John-Paul Della Putta

      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

      Resident Table

      Group by field;

       

      but that doesn't seem to work, as the NumberOfRecordsLoaded doesn't seem to get a value.

       

      Anyone done something similar to this?