Optimiser hints in static SQL?

[David H.]

Are there any plans to support optimiser hints in static SQL? I know they are supported ok in Dynamic 4gl, but we still have a lot of static SQL and often prefer it for readability over the small performance gains.

I assume currently the compiler strips the comment.  If this cannot be changed can we have an alternative style of comment for hints that can be retained?

On a separate note, I would also like to have a mechanism to exclude specific SQL statements from the FGLSQLDEBUG trace. We have some low level SQL statements that we have no need to see in a debug trace so it would be nice to have a way to exclude them. Alternatively could we perhaps have a trace on/off function that we can embed in the code?

[Sebastien F.]

Hi David,

You mean certainly dynamic SQL not Dynamic 4gl ;-)

Googling around it appears that there is no real standard for SQL optimizer hints.

Informix has:
{+ ... }
/*+ ... */
--+ ...

Oracle supports:
/*+ ... */
--+ ...

MySQL includes the hints in the syntax (not as special comments):

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;

So if you write such SQL, it will not be portable, and if you want to achieve SQL portability, you will have to use dynamic SQL anyway...

We could support /*+ ... */ style comments, but this is not as easy as it looks like because we have to change several syntax parsers to support this properly. Filed as enhancement #24367... low priority.

Regarding a new built-in function to enabled/disable SQL debug output, this is easy to do, filed as enhancement #19214, certainly without risk, will see if it can be done for 2.50.

Cheers

[Sebastien F.]

Regarding SQL debug activation with a program function, what's that triggered this need?
Is it the size of the FGLSQLDEBUG output? I doubt...
Do you have problems to find the statements in the output?
Do you know that we display the source module line number?

SQL: DELETE FROM dbit1
| 4gl source      : sqldebug.4gl line=4                   <----------- here
| sqlcode         : 0
| curr driver     : ident='dbmdefault'
| curr connection : ident='_1' (dbspec=[test1])
| Execution time  :   0 00:00:00.00764

Even if it's easy to do, I want to be sure that there is a good reason...

[David H.]

Hi Seb.

Thanks for you replies. Why am I not surprised there is no standard for SQL hints! I guess I was hoping that database specific variances could be handled via the ODI layer...

RE: the trace, its various reasons. The trace files can grow quite large for big processes causing hassles if its on a client site and it can also be difficult to find a specific piece of SQL. Yes I am aware of the module/line numbers, but for a say a syntax error in function taking a dynamic SQL string as a parameter this does not help much!

Our low level library functions are fully tested and don't change much so it would be nice to be able to exclude them from a trace. We would probably use an environment variable to decide at runtime whether they should be included in the trace or not. Sometimes a programmer knows where an error is but its hard to find out why from just looking at the code. In this case just the function concerned could be traced so only a small trace file with the problem SQL would be generated.

Cheers,

David