next »

[David H.]

We used the use these in F4GL and were disappointed to lose them when we first made the move to Genero. I now see they are available again from v2.40 onwards. Before diving back in and using them, can we be assured they'll continue to be supported in future versions?

[Sebastien F.]

Hello David,

SQL Blocks have been re-introduced for backward compatibility with I4GL, to easy migration of I4GL application to Genero.

I would not recommend to use SQL Blocks:
The SQL text in these blocks is not checked by the compiler.
Always use static SQL as long as the syntax is allowed.
If you have specific SQL to execute, build it dynamically and run it with PREPARE/EXECUTE.

Specific SQL should be seldom, what is the % of specific SQL that you have in your application?

BTW:
Once we had the idea to support a kind of mix of static and dynamic SQL, especially for QBE/CONSTRUCTs:

    DEFINE where_part STRING
    ...
    SELECT cust_num, cust_name .......
      FROM customer
       WHERE  %where_part%

Seb

[David H.]

Hi Seb.

When SQL Blocks did not make it into Genero I ended up writing a DSQL extension to our precompiler to make it easier for our developers to generate their Dynamic SQL without introducing runtime errors (missing spaces,quotes etc). I did a find across one part of our sourcecode and found this has been used around 1500 times to date. From the looks of it the majority of uses are to make the Dynamic SQL easier to read/maintain, i.e.

Code

{$DSQL query_txt}
        SELECT note_unique_key,
               user_key,
               date_updated
          FROM note
         WHERE note_unique_key BETWEEN 0 AND &MAX_INTEGER&
           AND parent_type = {@'p_type'}
           AND parent_ukey = {@p_key}
        ORDER BY note_unique_key
{$DSQL}
 

Produces at compile time:-

Code

        LET query_txt = ""
         ," SELECT note_unique_key,"
                ," user_key,"
                ," date_updated"
           ," FROM note"
          ," WHERE note_unique_key BETWEEN 0 AND 2147483647"
            ," AND parent_type = " , ASCII 39,p_type CLIPPED, ASCII 39
            ," AND parent_ukey = " ,p_key CLIPPED
         ," ORDER BY note_unique_key"
 

Adding SFMT() to the language has improved things somewhat but using placeholders makes the code harder to read especially when you have several arguments. If dynamic SQL could somehow be written more naturally like static SQL then I think we would use a lot more of it.

[Sebastien F.]

Hi David,
I guess your example is missing some SQL part that is produced at runtime... no?
There is no need to use this pre-processing plus dynamic SQL, for an SQL statement using a standard syntax, and when all parts are known at compile time.
Seb

[David H.]

Hi Seb,

Lots do yes. We also have ones (like that example) that don't when we need to produce a string (to pass as a parameter to another function perhaps) or for other reasons, like performance (prepare once, execute multiple) etc.

[Reuben B.]

Lots do yes. We also have ones (like that example) that don't when we need to produce a string (to pass as a parameter to another function perhaps) or for other reasons, like performance (prepare once, execute multiple) etc.

Always use static SQL as long as the syntax is allowed.
If you have specific SQL to execute, build it dynamically and run it with PREPARE/EXECUTE.

I had a discussion sometime last year with Seb where I was advocating the opposite.  When I was a mere junior programmer in the mid 90's, one thing that was reinforced was never to use static SQL inside a loop.  The reason being that every-time the static SQL statement was being executed, the query optimizer was figuring out what to do.  You would and did get much better run-time performance PREPARE/DECLARING a cursor as the query optimizer would only run once.

I suspect overtime the query optimizers have improved so that  they are clever enough to say, hey I just worked this query plan 1 millisecond ago, I'll reuse it, but it is still my habit to PREPARE cursors and use dynamic SQL statements that I believe will be executed more than once in a short time frame.

One of these days I should crunch some numbers and see if this is still the case.

Reuben

[David H.]

One of these days I should crunch some numbers and see if this is still the case.

Hi Reuben,


Interestingly enough we have an old program which someone wrote here which basically inserts a number of rows using different mechanisms. I just recompiled it with v2.4 (Windows) and ran it for 10,000 rows. The timings were:-

Static: 9.000 secs
Prepared: 2.719 secs
Insert cursor : 0.125 secs.

Regards,

David