2 Replies Latest reply: Jul 18, 2012 6:04 PM by agswin_05 RSS

    Update statistics on a volatile table

      In order to improve performance, I am trying to update statistics on a volatile table that I created and inserted data into.  This is the statement:



      update statistics for table VT_HIER_WITH_REVENUE_FLASH_BUDGET on every column, (FISC_YR_QTR_DSPLY_CD, PRFT_CTR_GRP_DN, PRFT_CTR_LVL_0_DN, PRFT_CTR_LVL_1_DN, PRFT_CTR_LVL_2_DN) sample;



      Without the SQL, the command fails with syntax error, so I am using this to indicate this is a SQL command.  With the SQL in there, the script run stops and fails.  Has anyone successfully updated stats on a volatile table in Qlikview?

        • Re: Update statistics on a volatile table
          Mayil Vahanan Ramasamy



             Try like this:


          Its in macro:


          sub Update

          set vDisplayCd = ActiveDocument.Variables("vDisplayCd")

          NewDisplayCd = vDisplayCd.GetContent.String 

          Set Result= ActiveDocument.DynamicUpdateCommand("UPDATE * SET FISC_YR_QTR_DSPLY_CD="&NewDisplayCd&" WHERE FieldName='Condition'")


          if Result = false then

          msgbox Result.ErrorMessage

          end if


          End sub


          But why you used update in qlikview? you can use in database, then you will use that table here?? That gives more performance rather than this, i think so...


          For more details:




          Hope it helps;

            • Re: Update statistics on a volatile table

              I am trying to update the stats on the table to improve query performance.  I am not doing this on the database as this is a volatile table that is created in the Qlikview script itself.


              I am not sure what your macro is achieving, but it looks like it is would update fields within the table.  I want to ensure my queries compile with up-to-date statistics on the volatile table.