Subscribe for automatic updates: RSS icon RSS

Login icon Sign in for full access | Help icon Help
Advanced search

Pages: [1]
  Reply  |  Print  
Author Topic: SqlHanded method and performance ?  (Read 8359 times)
Benjamin G.
Posts: 98


« on: October 21, 2020, 02:58:39 pm »

Hi,

Only a suggestion, i've tested SqlHandle and it will be nice to have a method "getResultValueByName" so if we change the column order of a SQL we don't need to change the "getResultValue" by position.

Another question, do you know if there is a difference in performance between "classic way" and "sqlHandle way" ?

Regards
Sebastien F.
Four Js
Posts: 509


« Reply #1 on: October 21, 2020, 03:48:59 pm »

Hello,

Please check:

https://4js.com/support/issue/?id=FGL-04063#startissue

There are 2 main reasons why we won't support *ByName() methods with base.SqlHandle:

1) The column names of a SELECT (or any SQL that returns a result set) are provided by the database client API, not by Genero BDL / ODI drivers.
Sometimes it's not fully supported / not reliable.
Try expressions and column aliases with different DB engines supported by Genero and see what you get in getResultName()

2) The purpose of base.SqlHandle is to write generic code. Why would you want to use column names?!

Seb
Sebastien F.
Four Js
Posts: 509


« Reply #2 on: October 21, 2020, 03:55:25 pm »

Regarding performances:

There is not much differences with static SQL or dynamic SQL statements and base.SqlHandle.

However, DO NOT replace all you existing SQL statements with base.SqlHandle!

Keep code readability with static SQL and use dynamic SQL only when needed.

Seb
Benjamin G.
Posts: 98


« Reply #3 on: October 21, 2020, 04:14:38 pm »

Hi,

You are absolutely right about the differences in the presentation of a "result set" according to the DB engines. I was only saying that it would be a "nice to have feature" if the "select" is correctly written with the use of "columns alias".

Using the "byname" makes writing the code easier because you don't have to count the positions ...

We always "prepare" SQL statements. The use of SqlHandle would allow declaring "cursors" in a global way. In addition it makes the writing more "structured" and no need to use the "declare cursor".

regards

Reuben B.
Four Js
Posts: 1047


« Reply #4 on: October 22, 2020, 12:26:23 am »

The equivalent of getResultValueByName() is very easy to implement using tools available to you.

Create a dictionary of integer

Code
  1. DEFINE d DICTIONARY OF INTEGER

Populate it using base.Sqlhandle.getResultName()

Code
  1.    FOR i = 1 TO h.getResultCount()
  2.        LET d[h.getResultName(i)] = i
  3.    END FOR

And then you can code

Code
  1. DISPLAY h.getResultValue(d["column-name"])
  2.  
as an equivalent to the

Code
  1. DISPLAY h.getResultValueByName("column-name")
  2.  
you seek.

As previously noted, you can explore what base.SqlHandle.getResultName returns for expressions, aliases etc, and make appropriate allowances in the above suggestion.

Performance wise, you can always measure performance using the profiler http://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_profiler_001.html and come to your own conclusions.  They key thing with base.Sqlhandle is developer productivity.  If you code every SQL in your system, using base.SqlHandle, your developers will be slower, but if you can code a generic function using base.SqlHandle and reuse that generic function over and over then your developers will be faster.










Product Consultant (Asia Pacific)
Developer Relations Manager (Worldwide)
Author of https://4js.com/ask-reuben
Contributor to https://github.com/FourjsGenero
Pages: [1]
  Reply  |  Print  
 
Jump to:  

Powered by SMF 1.1.21 | SMF © 2015, Simple Machines