How to dynamically change the entry defined in SQL storage map

Question

Question

CM Wang · Jul 18, 2017

I have a class and I would like to dynamically change its selectivity attribute in run-time and then call $system.OBJ.Compile to

recompile the class. I have changed the selectivity to a global, but it seems like it does not work. Any suggestions? Thanks.

<Property name="field1">
<AverageFieldSize>8.91</AverageFieldSize>
<Selectivity>^selectivity</Selectivity>
</Property>

Discussion (3)0

Log in or sign up to continue

Eduard Lebedyuk · Jul 18, 2017

You can use %Dictionary package to do that. Here's a method that sets selectivity of a specified class/property (assuming Default storage) to an arbitrary value:

/// w $system.Status.DisplayError(##class(User.Selectivity).ModifySelectuvity())
ClassMethod ModifySelectuvity(class As %Dictionary.CacheClassname = {$classname()}, property As %String = "field1", selectivity As %Integer(MINVAL=0,MAXVAL=100) = {$random(101)}) As %Status
{
    #dim sc As %Status = $$$OK
    set id = $lts($lb(class, "Default", property), "||")
    set strategy = ##class(%Dictionary.StoragePropertyDefinition).%OpenId(id)
    set strategy.Selectivity = selectivity _ ".0000%"
    set sc = strategy.%Save()
    quit:$$$ISERR(sc) sc
    
    set sc = $system.OBJ.Compile(class)
    quit sc
}

1 0

score 1 · Answer 1 · 2017-07-18T15:05:46-04:00

Eduard Lebedyuk · Jul 18, 2017

Why do you want to change selectivity?

You can run TuneTable to recalculate selectivity.

1 0

score 0 · Answer 2 · 2017-07-18T15:55:52-04:00

I want to evaluate the performances of different query plans.

I dont really need TuneTable since I don't really care the statistics is correct or not.

I just have to evaluate impact of those different parameters feed into optimizer.

Below is my sample test program and it should reveal my intention. It seems like BlockCount is not fetching the newest value from ^Blk when I compile. Any solution for this ?

set Blks = $lb(-100,-200,-300)

for r = 1:1:$ll(Blks){
set ^Blk = $li(Blks,r)

do $system.SQL.Purge()
w !,$system.OBJ.Compile("User.Table")
set query = "select XXXX"
set stat = ##class(%SQL.Statement).%New()
if 'stat.%Prepare(query) w !,"prepare failed "_query
set startT = $zts
set r = stat.%Execute()
set endT = $zts

//record startT and endT

}