10 Replies Latest reply: Apr 20, 2010 6:26 PM by John Martin RSS

    Speed of loading a QVD file

    rhadl1

      Hi All. I've got a qvw which I load by loading the existing data from a qvd file, loading new data, then saving the qvd. The idea of this is that the data load should be much quicker, and only pull new data from the server which should be slower, but will involve much less data.

      I've been having time out problems when it is scheduled, so reloaded it in qv and monitored the progress. As expected the loading of the records from the qvd file was fast (35 million rows, about 70 columns wide), but it then sat there using 25% of the CPU (4 dual core processors) for 50 minutes before moving to the next step which was to check for the most recent data in the data loaded.

      Any ideas why it is taking so long after the data is loaded (does it need to be ordered when saved or something like that), and is there any reason that the CPU is capped at 25%, querying the qvw via internet explorer uses the full 100%.

       

      Thanks,

      Richard

        • Speed of loading a QVD file

          Hi Richard, when you load the QVD file, the message is (qvd Optimized ) 30,000,000 loaded ?May be in the script you have script like If, or group by, in my case work's on 300,000,000 the time on load is 1 hour the CPU Xeon 3Ghz.

          Regards.

            • Speed of loading a QVD file
              rhadl1

              Thanks for the reply. Normally it is loaded optimised, i.e Load * from C:\File.qvd; If I add a where clause to this load, the spead of the reading of the qvd file slows (takes about 5 or 6 times longer), but the CPU time after is still the same.

            • Speed of loading a QVD file

              Hi Richard,

              I had a similar problem where the load from the QVD included several manipulations of the incoming data. The following snippet illustrates my solution, loading to a temporary table and then reloading with my data manipulation - in your case you'd get the incremental data in the second load:

               


              TmpIncidents:
              // Load base incident data
              LOAD
              *
              FROM
              Incidents.qvd (qvd);

              Incidents:
              // Reload incident data for manipulation
              LOAD
              IncidentID,
              If(IsNull(CausedByID), -99, If(CausedByID = 0, -99, CausedByID)) AS CausedByID,
              %ClipTypeID,
              IncidentLogged,
              IncidentClosed,
              If(IsNull(POSuiteHierarchyCatID), -99, If(POSuiteHierarchyCatID = 0, -99, If(Trim(POSuiteHierarchyCatID) = '', -99, POSuiteHierarchyCatID))) AS POSuiteHierarchyCatID,
              If(IsNull(POSuitesOtherCatID), -99, If(POSuitesOtherCatID = 0, -99, If(Trim(POSuitesOtherCatID) = '', -99, POSuitesOtherCatID))) AS POSuitesOtherCatID,
              IncidentImpact,
              IncidentStart,
              IncidentEnd,
              IncidentType,
              ...
              Trim(LoggedByFirstname & ' ' & LoggedByLastName) AS LoggedByName,
              ...
              RESIDENT TmpIncidents
              WHERE
              NOT IsNull(IncidentLogged);

              DROP TABLE TmpIncidents;


              • Speed of loading a QVD file
                John Martin

                I just wondered if you ever found a solution to this problem?? I have the exact same thing happening - version 7.51 on a 32-bit server. Straight load of a qvd - LOAD * FROM xxx.qvd - no WHERE clause or data manipulation. It gets to record 1,173,648 int the qvd within a couple of seconds, then stops. It is not hung - every 5 to 10 minutes it will progress another few thousand records. It's using 25% of the CPU, as you said.

                Even odder- if I specify the fields to load and exclude 5 specific fields (out of 19 fields), the data loads in seconds. All 5 fields are fully populated with data - basic Text information.

                • Speed of loading a QVD file
                  Peter Rieper

                  When there is no calculation of data or linking with previously loaded data, can only guess that it is related to available memory. CPU-load is not the key in this case. Have you checked this?

                  Peter