If you do universal LPAD, it is best $$lpad^%qarfunc, because for certain arguments may produce an incorrect result, for example:
#define lpad(%s,%len,%pad) $tr($j(%s,%len)," ",%pad) s s="a b",len=8,pad="0" w $$$lpad(s,len,pad),! ,$$lpad^%qarfunc(s,len,pad),! ,$$$lpad(s,len,s),! ,$$lpad^%qarfunc(s,len,s),!
Result:
USER>d ^test 00000a0b 00000a b aaaaaaab a ba a b
The functions from %qarfunc are actively used in system classes, so I don't think ^%qarfunc will be removed in the near future, otherwise everything will stop working.
USER>w $$lpad^%qarfunc("1",4,"0")
0001Try
ClientMethod changeParams() [ Language = javascript ]
{
var query = zen('categorieBeneficiaire');
var param1 = zen('catBParam1');
var param2 = zen('catBParam2');
param1.value='OS';
param2.value='NSAL';
query.refreshContents();
}
/// This client event, if present, is fired when the page is loaded.
ClientMethod onloadHandler() [ Language = javascript ]
{
zenPage.changeParams();
}or
w $zcvt(x,"I","HTML")
You don't need anything else to work with globals directly: Using the Globals API.
See the code examples in <install-dir>\dev\dotnet\samples\globals
Another variants:
USER>k s args=3,args(1)="-h",args(2)=21,args(3)="community.intersystems.com" do $system.OBJ.DisplayError(##class(%Net.Remote.Utility).RunCommandViaCPIPE("tracert",,.output,,900,.args)) w output
or
USER>k s args=3,args(1)="-h",args(2)=21,args(3)="community.intersystems.com" do $system.OBJ.DisplayError(##class(%Net.Remote.Utility).RunCommandViaZF("tracert",,.output,,900,,.args)) w outputSee Handling of static dataEN or Обработка статикиRU.
In your case, is enough will be the standard class %CSP.StreamServer (see streamserve.csp from CSP Samples). And you can use it to download external files, for example: %25CSP.StreamServer.cls?FILE=blablabla
PS: by the way, it is not safe to transmit OID in open form, so pay attention to the method %CSP.Page:Encrypt(oid).
Changed a bit and added new code:
/// d ##class(Scratch.test).ClassVsInst()
ClassMethod ClassVsInst(N = {1e6}) [ ProcedureBlock = 0, PublicList = st ]
{
n p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, xClass, xInst, st
s p1="пропоывшыщзшвыщшв"
,p2="гшщыгвыовлдыовдьыовдлоыдлв"
,p3="widuiowudoiwudoiwudoiwud"
,p4="прпроыпворыпворыпворыпв"
,p5="uywyiusywisywzxbabzjhagjЭ"
,p6="пропоывшыщзшвыщшв"
,p7="гшщыгвыовлдыовдьыовдлоыдлв"
,p8="widuiowudoiwudoiwudoiwud"
,p9="прпроыпворыпворыпворыпв"
,p10="uywyiusywisywzxbabzjhagjЭ"
,xClass="(args...) f i=1:1:N s sc=##class(Scratch.test).%1(args...)"
,xInst="(args...) f i=1:1:N s sc=st.%1(args...)"
s st=##class(Scratch.test).%New()
w $p($zv,"(Build"),!!
d runClassmethod("dummyClass10", p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)
,runMethod("dummyClass10", p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)
,runMethod("dummyInst10", p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)
,runClassmethod("dummyClass5", p1, p2, p3, p4, p5)
,runMethod("dummyClass5", p1, p2, p3, p4, p5)
,runMethod("dummyInst5", p1, p2, p3, p4, p5)
,runClassmethod("dummyClassNull")
,runMethod("dummyClassNull")
,runMethod("dummyInstNull")
,runX("dummyClass10",xClass, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)
,runX("dummyInst10",xInst, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)
,runX("dummyClass5",xClass, p1, p2, p3, p4, p5)
,runX("dummyInst5",xInst, p1, p2, p3, p4, p5)
,runX("dummyClassNull",xClass)
,runX("dummyInstNull",xInst)
runMethod(methodname,args...)
n t,i,sc
s t=$zh f i=1:1:N s sc=$method(st, methodname, args...)
s t=$zh-t w methodname,?16,"total time = "_t,?38,"avg time = "_(t/N),!
q
runClassmethod(methodname,args...)
n t,i,sc
s t=$zh f i=1:1:N s sc=$classmethod("Scratch.test", methodname, args...)
s t=$zh-t w methodname_"*",?16,"total time = "_t,?38,"avg time = "_(t/N),!
q
runX(methodname,x,args...)
n t,i,sc
s t=$zh
x ($$$FormatText(x,methodname), args...)
s t=$zh-t w "X"_methodname,?16,"total time = "_t,?38,"avg time = "_(t/N),!
q
}Result: USER>d ##class(Scratch.test).ClassVsInst() Cache for Windows (x86-64) 2018.1 dummyClass10* total time = .328227 avg time = .000000328227 dummyClass10 total time = .27655 avg time = .00000027655 dummyInst10 total time = .259913 avg time = .000000259913 dummyClass5* total time = .286983 avg time = .000000286983 dummyClass5 total time = .25666 avg time = .00000025666 dummyInst5 total time = .240312 avg time = .000000240312 dummyClassNull* total time = .274406 avg time = .000000274406 dummyClassNull total time = .250926 avg time = .000000250926 dummyInstNull total time = .234486 avg time = .000000234486 XdummyClass10 total time = .312917 avg time = .000000312917 XdummyInst10 total time = .264871 avg time = .000000264871 XdummyClass5 total time = .286985 avg time = .000000286985 XdummyInst5 total time = .238557 avg time = .000000238557 XdummyClassNull total time = .278684 avg time = .000000278684 XdummyInstNull total time = .236815 avg time = .000000236815
Still can be so:
select relation_name tablename, %Library.SQLCatalog_SQLClassname(relation_name) classname from %Library.SQLCatalog_SQLTables() -- where relation_name [ 'aaa'
I have a few comments.:
For example, for the next class, the method finds nothing:
Class dc.test Extends %Persistent [ SqlTableName = aaa ]
{
...
}
USER>d ##class(objectscript.findTable).test("aaa")
?proof
The Caché Library set of class definitions classes has been superseded by the %Dictionary package. The %Library classes described here are maintained for compatibility with existing applications.New code should make use of the classes within the %Dictionary package.
select ClassName from %Dictionary.ClassDefinition_ClassIndex() where ClassName [ 'test'
or
select ClassName from %Dictionary.ClassDefinition_ClassIndex() where nvl(SqlTableName,ClassName) [ 'aaa'
$system.OBJ.Export()
E.g.
s all("blabla.MAC")=""
s all("blabla.INT")=""
s all("blabla.INC")=""
s all("blabla.OBJ")=""
s all("blabla.CLS")=""
; ...
d $system.OBJ.Export(.all,"C:\temp\all.xml")SELECT %ID,prop3 FROM mp.test WHERE %ID IN ( SELECT * FROM ( SELECT TOP ALL %ID FROM mp.test WHERE prop1='name' AND prop3>='1' AND prop3<='30' AND prop2='prop2' ORDER BY prop3 DESC ) WHERE %VID BETWEEN 1 AND 5 ) -- ORDER BY prop3 DESC
Working with Streams
E.g.
;s stream=##class(%GlobalBinaryStream).%New()
;d stream.Write("--------")
s file=##class(%Stream.FileBinary).%New()
s file.Filename="C:\temp\test.txt"
d file.CopyFromAndSave(stream)And if so?
s query = "select %ID,prop3 from mp.test where %ID in (SELECT * FROM (SELECT %ID FROM mp.test WHERE prop1='name' AND prop3>='1' AND prop3<='30' AND prop2='prop2') WHERE %VID BETWEEN 1 AND 5)"
Could you provide a complete example similar to mine (code + data + queries with sorting and filtering) that you have a issue with?
How much all rows in the table? How much RAM? Which version $zv?
There are one error in the Test5 method: t4case* -> t5case*
See Using SSL to Connect for %Net.HttpRequest.
Can also speed up, abandoning the indirect:
s:'$d(addr3) addr3(1)=1, (addr3(2),addr3(8),addr3(9))=289, addr3(3)=3 d $case(addr3(value),1:t3case1,289:t3case289,3:t3case3,:t3case) ##; w !,r q t3case1 s r="Case 1" q t3case289 s r="Case 2,8-9" q t3case3 s r="Case 3" q t3case s r= "Case default" q
But the quickest option is if/elseif/else, since here is used inline-call, and not external-call.
So, in order.
There is no limit to perfection.
Better then d:(v=2)!(v=8)!(v=9) t3case289 qs:'$d(addr) addr(1)=1, (addr(2),addr(8),addr(9))=289, add(3)=3
d:v=1 t3case1 Q ;will work in any wayd:v=2 t3case289 Q d:v=8 t3case289 Q d:v=9 t3case289 Q d:v=3 t3case3 Q d t3case Q
So, in order.
- The third article mentions a few options of which some are not available in Cache, such as LIMIT and OFFSET.
Caché has analogues - TOP N and %vid, which with more than replace the LIMIT/OFFSET. In the second link this is discussed in detail.
- And the 4th talks about using LIMIT and OFFSET which are, again, not available in Cache.
The essence of the article is to replace query
SELECT user_id, external_id, name, metadata, date_created FROM users ORDER BY user_id ASC LIMIT 50 000 000, 10 000; --- 5 000th page * 10 000 page size 10 000 rows in set (40.81 sec)
to
SELECT user_id, external_id, name, metadata, date_created FROM users WHERE user_id > 51 234 123 --- value of user_id for 50 000 000th record ORDER BY user_id ASC LIMIT 10 000 10 000 rows in set (0.03 sec)
Eventhough I am only asking for the first 100, there is still a major performance hit when executing the first rset.%Next() due to, what I assume is, the code trying to find the 100 records I am requesting out of the 1 mil records.
In this case, the following query is sufficient:
SELECT TOP 100 prop FROM table WHERE prop=?
-The second suggestion is what I am doing above. Using %VID with a subquery is just too slow when dealing with large datasets.
Is there an index for "prop"?
You tuned the table?
I have all works quickly:
Class dc.test Extends %Persistent
{
Index iprop On prop;
Property prop As %String;
ClassMethod Fill(
Nrow = {1e6},
Npage = {1e3})
{
d DISABLE^%NOJRN
,..%KillExtent()
s time=$zh
,^dc.testD=Nrow
f id=1:1:Nrow {
s p=id-1\Npage+1
,v=id-1#Npage+1
,val=p_":"_v
,^dc.testD(id)=$lb("",val)
,^dc.testI("iprop",$$$SQLUPPER(val),id)=""
}
w "(Fill) time = ",$zh-time," s.",!!
zw:Nrow<=50 ^dc.testD,^dc.testI
d ENABLE^%NOJRN
,$system.SQL.TuneTable($classname(),$$$YES)
,$system.OBJ.Compile($classname(),"cu-d")
}
ClassMethod Query(
q As %TinyInt = 1,
prop As %String,
rownum1 As %Integer,
rownum2 As %Integer)
{
s sql(1)="select *,%vid from (select %ID,prop from dc.test where prop %startswith ?) where %vid between ? and ?"
,sql(2)="select *,%vid from (select %ID,prop from %ignoreindex iprop dc.test where prop like ?) where %vid between ? and ?"
,sql(3)="select *,%vid from (select top ? %ID,prop from %ignoreindex iprop dc.test where prop like ? order by %ID desc) order by %vid desc"
,time=$zh
,rs=$s(q=3:##class(%SQL.Statement).%ExecDirect(,sql(q),rownum1,prop),
1:##class(%SQL.Statement).%ExecDirect(,sql(q),prop,rownum1,rownum2))
i 'rs.%SQLCODE {
while rs.%Next() {
/*
s id=rs.%Get("ID")
,prop=rs.%Get("prop")
w id," ",prop,!
*/
d rs.%Print()
}
}
w "(",$lts($lb(q,prop,rownum1,rownum2)),") time = ",$zh-time," s.",!!
}
/// d ##class(dc.test).Test()
ClassMethod Test()
{
d ..Fill()
,..Query(1,"1000:",111,111+16)
,..Query(2,"%12%",111,111+16)
,..Query(1,"1",111984,111984+16)
,..Query(2,"%12%",39584,39584+16) ;# slow (last 17)
,..Query(3,"%12%",17,"") ;# fast (last 17)
}
}Result:
USER>d ##class(dc.test).Test() (Fill) time = 1.277645 s. 999111 1000:111 111 999112 1000:112 112 999113 1000:113 113 999114 1000:114 114 999115 1000:115 115 999116 1000:116 116 999117 1000:117 117 999118 1000:118 118 999119 1000:119 119 999120 1000:120 120 999121 1000:121 121 999122 1000:122 122 999123 1000:123 123 999124 1000:124 124 999125 1000:125 125 999126 1000:126 126 999127 1000:127 127 (1,1000:,111,127) time = .084489 s. 5128 6:128 111 5129 6:129 112 5212 6:212 113 5312 6:312 114 5412 6:412 115 5512 6:512 116 5612 6:612 117 5712 6:712 118 5812 6:812 119 5912 6:912 120 6012 7:12 121 6112 7:112 122 6120 7:120 123 6121 7:121 124 6122 7:122 125 6123 7:123 126 6124 7:124 127 (2,%12%,111,127) time = .091251 s. 999984 1000:984 111984 999985 1000:985 111985 999986 1000:986 111986 999987 1000:987 111987 999988 1000:988 111988 999989 1000:989 111989 999990 1000:990 111990 999991 1000:991 111991 999992 1000:992 111992 999993 1000:993 111993 999994 1000:994 111994 999995 1000:995 111995 999996 1000:996 111996 999997 1000:997 111997 999998 1000:998 111998 999999 1000:999 111999 1000000 1000:1000 112000 (1,1,111984,112000) time = .66504 s. 999121 1000:121 39584 999122 1000:122 39585 999123 1000:123 39586 999124 1000:124 39587 999125 1000:125 39588 999126 1000:126 39589 999127 1000:127 39590 999128 1000:128 39591 999129 1000:129 39592 999212 1000:212 39593 999312 1000:312 39594 999412 1000:412 39595 999512 1000:512 39596 999612 1000:612 39597 999712 1000:712 39598 999812 1000:812 39599 999912 1000:912 39600 (2,%12%,39584,39600) time = 1.946264 s. 999121 1000:121 17 999122 1000:122 16 999123 1000:123 15 999124 1000:124 14 999125 1000:125 13 999126 1000:126 12 999127 1000:127 11 999128 1000:128 10 999129 1000:129 9 999212 1000:212 8 999312 1000:312 7 999412 1000:412 6 999512 1000:512 5 999612 1000:612 4 999712 1000:712 3 999812 1000:812 2 999912 1000:912 1 (3,%12%,17,) time = .089032 s.
Then try methods $system.Util.Compress/Decompress
See:
PS: need to replace the heading on "Speedup SQL pagination"And what you are not satisfied with the native classes %Stream.FileBinaryGzip, %Stream.FileCharacterGzip?