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:

       

      SQL

      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
          mayilvahanan ramasamy

          Hi,

           

             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:

          http://community.qlik.com/message/1537#1537

           

           

          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.