Relativity performance

New to using Relativity and being told that there is no way to do a TOP command. How do the rest of you handle situations like this and also deal with temp files that grow to more than 4 times the size of the actual Visual Cobol data file? Any other helpful guidelines welcomed as we are trying to stop using a different ODBC application and stick with MicroFocus.


  • The fact that you are seeing temp files means that you are doing an operation that requires some type of sorting.  This implies that your indexed file keys are (1) not being used properly or, more likely, (2) the keys that are available are not helpful to the particular task.

    Some requests:

    Can you post the query, please?  In doing so, please indicate which fields involved in selection or join are keys, or leading fields (leftmost characters) in keys.  (Or if you don't mind, simply post the relavent portions of the COBOL record descriptions.

    Are you using Relativity DataServer, or Relativity DataManager?

    Do you have signed numeric in your keys?

    Have you defined nullable columns in your keys?

    Almost all performance problems revolve around getting the table definitions and key definitions right.  There are several sections in the relativity help (Designer, I think) that deal with such issues.

    My rule of thumb is to ask myself, "Can a COBOL program do this query efficiently?"  If the answer is yes, then almost certainly Relativity can also do the query efficiently - but only if you give the Relativity engine as much information as you 'know' in the COBOL program.

    Tom Morrison

    Hill Country Software

  • By the way, in general TOP can only be applied after the work is done, so it merely truncates the result set.

  • Hi Tom,

    I don't have the exact query right now, but for discussion sake say I have an inventory file that has a field storing year to date sales qty and I only want to see the 10 most active items and this field is not in a key.

    The query would look like select top 10 * from inventory order by inv_sales

    I appreciate your "rule of thumb" in regard to having the query only try to accomplish what a Cobol program could - so are you saying in this case I should create a temp file that is sorted by inv_sales? But I would still get all the records and not just the top 10.

    Your comment about temp files has me questioning something else we notice .. one of my co-workers is convinced that when the temp file goes over 2GB then Relativity will error out. Have you seen this or know how to get around it?

    Appreciate you comments.

  • Understood - but how do I share just this truncated information with an application outside of Cobol?

  • Cliff, without seeing the query, it is hard to get very specific.  However, if the inv_sales is not in a key, and you are doing an ORDER BY on that field, then you are conjuring up a full table scan and a sort.   Not much of a way around that.  In a COBOL program, you will still be doing a full table scan (that is, reading all of the records), and to do what you are asking (SELECT *) the COBOL program would use SORT most likely with an INPUT PROCEDURE and an OUTPUT PROCEDURE.  The OUTPUT PROCEDURE could stop after RETURNing 10 records, but the sort would still be done.  Without the inv_sales value being available in a key (in a format that allows the resulting SQL index to be ordered), I can think of no way to avoid a full table scan and a sort.  And that is the crux of your performance issue. 

    You say that you "get all the records" but if the consuming ODBC application only fetches the first ten rows of the result set, and then drops the handle for the result set, the rest of the result set goes away.  In some cases, the rest of the result set has not yet been instantiated (because the data that will make up the rows of the result set remains in a sort temp file).  The details of this might differ between Relativity DataServer and Relativity DataManager.

    What is the ODBC application that is consuming the data?

    Since I don't have any insight into the Micro Focus development priorities, I have no idea if TOP is even on the development radar.  So the best that can be done is work with what's there.

    Finally, Relativity uses the underlying COBOL file system.  For Visual COBOL that would be the same file system as your Visual COBOL programs use.  If it can handle files larger than 2 GB then so can Relativity.  I do know that the RM/COBOL version of Relativity can handle files larger than 2GB, but it requires some configuration. 

    [Apologies for not generating a response sooner.  I only have the daily digest for the Visual COBOL forum, and failed to enable notification on responses to this thread.  Fixed that...]

    Tom Morrison
    Hill Country Software

  • << Your comment about temp files has me questioning something else we notice .. one of my co-workers is convinced that when the temp file goes over 2GB then Relativity will error out. Have you seen this or know how to get around it? >>

    The temp files are managed by the SQL query engine component of Relativity, and, they are not COBOL data files.  Therefore, a temporary sort file can grow as large as the operating system will allow.

    So, if you are encountering an "Out of disk space..." error, then, that indicates that you either have run out of space in the partition where the sort file is being created, or, there is a ulimit in place for the Relativity Data Server user account that limits file size.

    As a note, you can configure the temporary sort file location using the Relativity Server Admin Control Panel utility.

  • Tom,

    Thanks for your comments. I am working with Micro Focus to have more sql like adaptation to Relativity so it can properly replace a competing product from Transoft.

  • Transoft ... Now there's a blast from the past.  Oh, but I almost forgot, this is Legacy Land.  Long Live Legacy.