GAMS 48 tests

Some minor quibbles. 

gdx2sqlite

The latest version of GAMS contains a replacement of gdx2sqlite. This dumps a GDX file into a SQLite database. It is a tool I use a lot. Here is a comparison using the indus89 model in the GAMS model library:

Command	Time
gdx2sqlite -i indus89.gdx -o indus89.sqlite	1.766
gdx2sqlite -i indus89.gdx -o indus89.sqlite -fast -small	0.141
executeTool 'sqlitewrite gdxIn=indus89.gdx o=indus89.sqlite'	3.453

Notes:

• For this case: the new sqlitewrite is a factor of 25 slower. This number is a little bit bigger than I expected. The old gdx2sqlite was written with speed in mind, and that shows.
• I tend to use gdx2sqlite with -fast -small options for obvious reasons: it is 13 times as fast and produces smaller database files due to normalization of the database. I believe this speedup is not an exception: I have seen that on many datasets.
• The sqlitewrite documentation says:
     The -small and -fast options have been removed due to minimal performance impact.
  This is not completely true. In my opinion: 13 times as fast ≠ "minimal performance impact".

The test script looks like:

option profile=1;

$if not exist indus89.gms $call gamslib indus89

$call gams indus89 gdx=indus89

$call rm -f indus89*.sqlite

execute 'gdx2sqlite -i indus89.gdx -o indus89-1.sqlite';

execute 'gdx2sqlite -i indus89.gdx -o indus89-2.sqlite -fast -small';

executeTool 'sqlitewrite gdxIn=indus89.gdx o=indus89-3.sqlite';

It shows:

----      8 Execute                  1.766     1.766 SECS      3 MB 

----      9 Execute                  0.141     1.907 SECS      3 MB 

----     10 Execute                  3.453     5.360 SECS      3 MB 

Just in case, the second column has cumulative time. 

Conclusion: on larger, more complex data sets the old gdx2sqlite tool can be much faster than the newer sqlitewrite approach.

GAMS/CONNECT

We can simulate the above using GAMS/CONNECT:

option profile=1;

$set name indus89

$if not exist %name%.gms $call gamslib %name%

$if not exist %name%.gdx $call gams %name% gdx=%name%

$call rm -f %name%.sqlite

$call gdxdump %name%.gdx output=%name%.inc nodata

$include %name%.inc

embeddedCode Connect:

- GAMSReader:

symbols: all

- SQLWriter:

connection: {'database': '%name%.sqlite'}

symbols: all

endEmbeddedCode

This fails with an incorrectly formulated SQL DROP TABLE statement:

Exception from Connect: Cannot drop table >is<

Table names need to be protected if they clash with keywords. Valid SQL can look like:  DROP TABLE "is" or DROP TABLE [is].

Conclusion: here table and column names are not properly protected by "." or [.]. The behavior of sqlitewrite and SQLwriter is different in this respect. 

invert 

A minor bug in invert. If the output parameter has already data, the results are not always correct. When inverting an identity matrix we can see:

----     13 PARAMETER invA  inverse of identity matrix

             i1          i2          i3          i4          i5          i6          i7          i8          i9         i10

i1        1.000       0.843       0.550       0.301       0.292       0.224       0.350       0.856       0.067       0.500

i2        0.998       1.000       0.991       0.762       0.131       0.640       0.160       0.250       0.669       0.435

i3        0.360       0.351       1.000       0.150       0.589       0.831       0.231       0.666       0.776       0.304

i4        0.110       0.502       0.160       1.000       0.265       0.286       0.594       0.723       0.628       0.464

i5        0.413       0.118       0.314       0.047       1.000       0.182       0.646       0.561       0.770       0.298

i6        0.661       0.756       0.627       0.284       0.086       1.000       0.641       0.545       0.032       0.792

i7        0.073       0.176       0.526       0.750       0.178       0.034       1.000       0.621       0.389       0.359

i8        0.243       0.246       0.131       0.933       0.380       0.783       0.300       1.000       0.749       0.069

i9        0.202       0.005       0.270       0.500       0.151       0.174       0.331       0.317       1.000       0.964

i10       0.994       0.370       0.373       0.772       0.397       0.913       0.120       0.735       0.055       1.000

This is obviously not correct. The reason is that only the nonzero values are shipped back. In this case that is the diagonal.  The off-diagonal part contains old values that were not reset to zero.

Here is a test script. It also shows a simple workaround. I needed that for [1].

set i /i1*i10/;

alias (i,j);

parameters

A(i,j) 'identity matrix'

invA(i,j) 'inverse of identity matrix'

;

A(i,i) = 1;

* assume invA contains already some data

invA(i,j) = uniform(0,1);

executeTool.checkErrorLevel 'linalg.invert i A invA';

display invA;

* simple workaround: always clear before calling invert

invA(i,j) = 0;

executeTool.checkErrorLevel 'linalg.invert i A invA';

display invA;

Conclusion: if the result parameter contains already data, the invert tool gives wrong results.

 

References

1. Revised Simplex LP Solver written in GAMS, https://yetanothermathprogrammingconsultant.blogspot.com/2024/08/revised-simplex-lp-solver-written-in.html