topic Re: Update statistics on a volatile table in QlikView

Update statistics on a volatile table

Tue, 17 Jul 2012 21:11:09 GMT

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 — Tue, 17 Jul 2012 21:26:07 GMT

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

Wed, 18 Jul 2012 22:04:17 GMT

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.