next »

[Ionut F.]

Hi All,

We're not really using Genero, we are using the pre Genero BDL but I figured this might apply here too.
I looked into SQL access plans for queries issued from a 4gl routine and I was surprised to see that SQL Server will very generate innefficient static cursors for even simple SQL Block statements like "select count(*) into $var from myTable".
For explicitly declared cursors is even worse, it will try to generate Dynamic cursors which are really bad.
Is there any setting somewhere that will change this behavior? Things that should take seconds take hours.
Thanks

[Reuben B.]

Hi,

We're not really using Genero, we are using the pre Genero BDL but I figured this might apply here too.

Chances are it will.  However there have been many years of development that have gone into Genero that has not been applied into the pre-Genero BDS, so there may be something in Genero that helps you that is not in BDS.

I looked into SQL access plans for queries issued from a 4gl routine and I was surprised to see that SQL Server will very generate innefficient static cursors for even simple SQL Block statements like "select count(*) into $var from myTable".
For explicitly declared cursors is even worse, it will try to generate Dynamic cursors which are really bad.

Can you please explain these statements more clearly.  Perhaps with reference to the code you have, and what you see in the SQL access plans, and what you think you should see in the SQL access plans instead.

Reuben

[Ionut F.]

Thanks,
I was tracing the execution of a 4gl program and noticed that most statements end up in generating server side cursors, even those SQL statements that only return a scalar - like selecting a count(*) into a variable. Server side cursors often result in horrible access plans and SQL Server always disables parallelism when a server side cursor is requested, sometimes resulting in dramatic drops in performance.
Also, for queries returning multiple rows, I noticed that keyset or dynamic server side cursors are generated, although the code never asks for dynamic cursors (just "declare cursor"). For those cases I would expect a static forward only cursor to be created, although I would prefer a "firehose" result set populating an array of objects, but I guess this is not supported.
I attached the access plans for a "Select count(*) into $SomeVariable from someTable Where SomeCondition" - the one generated by 4gl and the one that I would expect.

Thanks again

[Reuben B.]

Hi,

I'll just start my answer by pointing out that at this time of the year, answers can be slow, as those in the Northern hemisphere take their summer holidays.  So someone might have the answer you want in a few weeks time when they return from holiday

You've raised two interesting issues when using SQL Server

1. Can you choose, and if so how, when to use a Server side cursor versus a Client side cursor? 

2. For queries returning multiple rows, based on what is entered in the 4gl code, what type of cursor is created on the SQL Server database?  We can see that when using SCROLL CURSOR, SQL_ATTR_CURSOR_SCROLLABLE is set to SQL_SCROLLABLE https://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_odiagmsv_046.html.  So the question maybe what is set for non SCROLL cursors? Is that set back to SQL_NON_SCROLLABLE?

However I wouldn't expect anything we are doing, or not doing with the above, to explain why something in your words "should take seconds, takes hours", unless your database is operating at or near capacity

What I would encourage you to do if you have not done already is to ...

use FGLSQLDEBUG environment variable to determine the SQL statement as executed, this output will show the SQL statement from the 4gl code, and the SQL statement that is then passed to the database

make sure you are aware you can pass non-Informix SQL statements to the database. 

You aren't repeatedly creating cursors inside a loop, a common thing I used to see that impacts database performance.  declare the cursor once outside the loop and re-use it

try your code with Genero instead of the old BDS.  I'm not promising any improvement but there are many years of development difference, and there could potentially be an improvement in place already that you are missing out on by sticking with the old BDS.

Reuben

[Ionut F.]

Thanks,

In the 4gl code the cursors are declared as just cursors, no scroll, static or other attributes.
Is there any way to set the ODBC statement or maybe connection wide attributes? I'm pretty sure at some point some of these attributes are changed from their ODBC defaults (like SQL_ATTR_CURSOR_TYPE, SQL_ATTR_ROW_ARRAY_SIZE)?

[Sebastien F.]

Hello Ionut,

There is no general settings to control the SQL Server ODBC cursor options with FOURJS BDS or Genero BDL: These options are defined according to the type of program cursor (regular, SCROLL, FOR UPDATE).

We HAVE TO use server-side cursors with SQL Server, because in 4GL/BDS/Genero programs, several cursors can remain open in parallel.
Maybe we could handle one-shot SQL statements in a different way (not using server-side cursors), but no customer complained yet about SQL Server performance issues related to server-side cursor usage.

There have been several improvements for SQL Server since FOURJS BDS V3.xx. The major improvement is of course the usage of the SQL Native Client ODBC driver, with the "SNC" ODI driver type, introduced in Genero BDL 2.10. For regular SQL statements, we use "Fast forward ODBC cursors" (SQL_SOPT_SS_CURSOR_OPTIONS = SQL_CO_FFO) ...

We will not do any improvements in FOURJS BDS.

As Reuben, I strongly suggest that you make tests with Genero BDL (2.50), and see if you get better results.

Seb