go to post Vitaliy Serdtsev · Sep 17, 2018 Still can be so: select relation_name tablename, %Library.SQLCatalog_SQLClassname(relation_name) classname from %Library.SQLCatalog_SQLTables() -- where relation_name [ 'aaa'
go to post Vitaliy Serdtsev · Sep 14, 2018 I have a few comments.: your code finds by the class name, not the table name, which is confusing For example, for the next class, the method finds nothing: Class dc.test Extends %Persistent [ SqlTableName = aaa ] { ... } USER>d ##class(objectscript.findTable).test("aaa") ? according to the documentation, your code uses the deprecated API: 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. in fact, the search is done elementary: select ClassName from %Dictionary.ClassDefinition_ClassIndex() where ClassName [ 'test' or select ClassName from %Dictionary.ClassDefinition_ClassIndex() where nvl(SqlTableName,ClassName) [ 'aaa'
go to post Vitaliy Serdtsev · Sep 14, 2018 Task Schedule (see Delete) %SYS.Task.History:PurgeHistory()
go to post Vitaliy Serdtsev · Sep 14, 2018 See %Document.Object; %SQL.Statement; %SQL.Util.Procedures (SQLProc = %SQL_Util.CSV / %SQL_Util.CSV_TO_CLASS).
go to post Vitaliy Serdtsev · Jul 26, 2018 Declaring Stream Properties You can store in the database not the files themselves, but only links to them. Thus, the size of your database will be small. Managing Caché You can create a custom task on a schedule in which you need programmatic to clean up old files and then compress cache.dat (compact/truncate).
go to post Vitaliy Serdtsev · Jul 26, 2018 $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")
go to post Vitaliy Serdtsev · Jul 26, 2018 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
go to post Vitaliy Serdtsev · Jul 26, 2018 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)
go to post Vitaliy Serdtsev · Jul 26, 2018 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)"
go to post Vitaliy Serdtsev · Jul 25, 2018 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?
go to post Vitaliy Serdtsev · Jul 20, 2018 There are one error in the Test5 method: t4case* -> t5case*
go to post Vitaliy Serdtsev · Jul 20, 2018 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" qBut the quickest option is if/elseif/else, since here is used inline-call, and not external-call.
go to post Vitaliy Serdtsev · Jul 19, 2018 So, in order. There is no limit to perfection. Better then d:(v=2)!(v=8)!(v=9) t3case289 q There are one error in the Test2 method: s:'$d(addr) addr(1)=1, (addr(2),addr(8),addr(9))=289, add(3)=3 There are error in the Test3 method: d:v=1 t3case1 Q ;will work in any way d:v=2 t3case289 Q d:v=8 t3case289 Q d:v=9 t3case289 Q d:v=3 t3case3 Q d t3case Q Speed will depend on many factors: RAM size, number of conditions (the more of them, the slower the lasts of them will be executed and more RAM is required).
go to post Vitaliy Serdtsev · Jul 19, 2018 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.
go to post Vitaliy Serdtsev · Jul 18, 2018 See: %ScrollableResultSet Is there any way of paginate SQL Query in Caché? comment Efficient pagination of a table with 100M records PS: need to replace the heading on "Speedup SQL pagination"
go to post Vitaliy Serdtsev · Jul 17, 2018 And what you are not satisfied with the native classes %Stream.FileBinaryGzip, %Stream.FileCharacterGzip?
