SqlHanded method and performance ?

Started by Benjamin G., October 21, 2020, 02:58:39 PM

Previous topic - Next topic

Benjamin G.

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.

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.

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.

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.

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

Create a dictionary of integer

Code (genero) Select
DEFINE d DICTIONARY OF INTEGER

Populate it using base.Sqlhandle.getResultName()

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


And then you can code

Code (genero) Select
DISPLAY h.getResultValue(d["column-name"])

as an equivalent to the

Code (genero) Select
DISPLAY h.getResultValueByName("column-name")

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