New post
Question
· Dec 15, 2017

ECP: Run code on the database server instead of the application server

#Caché #ECP

Hi all,

I have a database with 140 million records containing financial transactions. When I do a query to calculate the sum of those records spanning a number of months it takes around 4 seconds on my database server which seems reasonable. However if I run the same query on an application server it takes around 40 seconds to do the same query.

In just this instance I would like to run that piece of code on the database server instead of the application server. It's a query that is only run a few times a day so I would prefer to have a fast result and don't mind putting a bit more stress on my database server for that.

Right now I have a SOAP webservice giving me the required result which I just call internally, but I was hoping there is a better solution for this.

Any ideas?

Discussion (4)0
Log in or sign up to continue
Dmitry Maslennikov · Dec 15, 2017

Each Application Server uses own Global Buffer plus Server Buffer, to fetch data, but the speed of the connection between those servers also matter.

If you don't care where to run so rare queries, you can do it on Database Server. ECP connection only gives a way to get access to the data in databases. And does not help to call any code on Database Server directly. And since %Net.RemoteConnection already deprecated, you can use other ways to call the main server or any other, and SOAP can be one of the ways, but I would prefer JSON Rest API, which in result will transfer fewer data.

0 0
Kevin Furze · Dec 18, 2017

Robert, 

can you give a simple example please.

I have an sql that I want to expose to ODBC, but that sql doesn't return sufficient for my MS-EXCEL to interpret,

At the moment, I run a classmethod that calls SQL, and then converts the result (including extra calculations for a further 3-4 columns) into a CSV file. 

I think It would be nicer to just call the "_ClassMethod()" over the odbc connection

kevin

0 0
Robert Cemper · Dec 15, 2017

An alternate approach to SOAP:

a) you run your query over  JDBC on your DataServer
     so you can query what ever you like

b) slightly more sophisticated
    you create a ClassMethod with [SqlProc] parameter and also call it over JDBC
    like this.  SELECT Pkg.Class_ClassMethod()  
    It's totally up to you what the content of your return value is.   

I personally prefer JDBC as it's platform independent

in fact b) is pretty much the same as SOAP just with a different transport that avoids %CSP and its side effects.......

1 0
Robert Cemper · Dec 18, 2017

Kevin,

if you call a ClassMethod() it will return whatever the type of return value is like.
some variants:

- your Classmethod generates the CSV file as you do and the return value is the filename

ClassMethod Kevin1() as %String [SqlProc]
{                                        
....... generate CVS File                
 quit Filename                           
}                                        

 

- instead of a file you generate a 2 dimensional $Piece String (, as field separator and ^ as record separator)

ClassMethod Kevin2() as %String(MAXLEN="") [SqlProc]
{                                                   
....... setup ResultSet                             
    set output=""                                   
    while rs.Next() {                               
        for col=1:1:rcws.GetColumnCount() { set output=output_rs.GetData(col)_"," }
        set output=output_"^"                       
        }                                           
    quit output                                     
}                                                   

 

- a similar thing could be rows as a list of JSON sets

ClassMethod Kevin3() as %String(MAXLEN="") [SqlProc]
{                                                   
    set output=""
    &SQL( SELECT LIST(line) into :output FROM (
             Select top 3 JSON_OBJECT('Name':Name
                                      ,'ZIP':Home_ZIP
                                      ,'State':Home_State) as line
                      from sample.Person where name [ 'A' )
             )
         )                                      
    quit output                                     
} 
///  {"Name":"Xiang,Agnes O.","ZIP":56604,"State":"MT"},{"Name":"Zubik,Bill A.","ZIP":78872,"State":"NV"},"Name":"Schaefer,Alvin X.","ZIP":63607,"State":"ME"}                                                 

in all cases you have to disassemble the result on the EXCEL end.

BUT as this is MS-EXCEL

my personal preference would be anyhow  to connect to Cache over ODBC with a local DSN
and just call your data via SQL and get back a full working XLS table that feeds the rest in your XLS.
This is standard in EXCEL and except for the DSN the same for ACCESS  or real DBs.
(as I don't own and use EXCEL any more  I can't offer an example)

- your initial question didn't mention CSV nor MS-EXCEL
so my Article on Light weight EXCEL Download may apply
https://community.intersystems.com/post/light-weight-excel-download

or  Tips & Tricks - SQL to Excel
https://community.intersystems.com/post/tips-tricks-sql-excel

HTH,
Robert

0 0