Tuning Relativity

If I am not mistaken, OpenQuery is a mechanism for doing a pass-through query.  That is probably your best starting spot.

You have to make sure that the top level engine is not somehow reordering your well-crafted query.  If your query is doing a join, then make sure the join is coded such that the table on the right (of a left outer join) has the leftmost characters of (one of) the key(s) fully described.  Think in terms of a COBOL program doing a START keyname NOT LESS THAN and then sequential reads using the data provided by the table/file on the left of the join.  And in an equijoin, one of the two tables has to have its underlying file's key fully described.  

Relativity's optimizer can help out on less than optimum queries, but for queries that demand high performance (such as you describe) it is always better to take the time to get the join condition coded well.  I normally recommend to any COBOL programmer that is using Relativity that one should consider, "Can I write a high performance COBOL program to do the same I/O as this query?"  Considering the details of how one would write such a COBOL program often helps clarify how to construct the join.

By the way, how did you measure, "access via connection string is very good?"  Relativity Server starts returning results to the client as soon as it can, so the entire query may not have completed by the time first results are returned.

Tom,

Sorry I missed your reply here. Thanks for your suggestiosn. When I said "connection string" above that is just using the DSN and not the SQL linked server. The linked server seems to really slow things down, but for this particular application I am just trying to do a data dump from a Linux server to sql tables.

Can anyone share are info on tuning on the server side? In particular I am looking for logging options and other settings in ..../etc/odbc.ini file

Probably the most interesting setting would be to turn on OdbcTrace in the [Simba Trace] section of odbc.ini.  It has been a while since I used this setting, but I think it will give you some insight about the actual ODBC statements that are being sent from the client.  You can turn on any of the trace settings you want, but sometimes the actual trace output may be a bit cryptic.  (And don't forget to turn them back off.after your experiment.)  You might also need to restart the server to pick up new odbc,ini settings.  Like I said above, it has been a while since I tinkered in this area...