adabas vs sql server performance

, , ,
1

My company uses ADABAS as our production database system. We have a backup on SQL Server. I am currently writing an application in which I have to perform a partial string search on one field in a table with approximately 1.5-2 million records. In ADABAS, this field is a descriptor field. The SQL Server is indexed.
The queries are as follows:
ADABAS:
READ table BY id-field
EXAMINE id-field FOR #search-string GIVING #num
IF #num > 0
DISPLAY id-field
END-IF
END-READ

SQL Server:
SELECT * FROM table WHERE id-field LIKE ‘%search-string%’;

The ADABAS query takes nearly an hour to complete. The SQL Server query takes about 1 second. The disparity in these values seem ridiculous, given all I’ve heard about the speed of ADABAS. I’m not the administrator of the ADABAS database, so I don’t know what we’ve enacted as far as indexing or configuration of the database or tables.

Any suggestions as to why these query times are so different and what might be done about it?

Thanks in advance.

2

If your id-field is a surname, you may be processing values like SMITH many times. Unless the file has been loaded in id-field sequence, your READ LOGICAL will cause Adabas blocks to be re-read. In either case, a HISTOGRAM may be better than a READ LOGICAL. You should try a READ PHYSICAL, too, to see if that improves things.

Use MULTI-FETCH!

On Mainframes:

HISTOGRAM MULTI-FETCH 10 table BY id-field

On Open Systems:

HISTOGRAM MULTI-FETCH ON table BY id-field

When searching for a string, EXAMINE seems to take the same amount of time whether the string is found in the first position of the source field or at the end. Try replacing the EXAMINE/IF combination with

IF  id-field = SCAN 'string'
3

Good idea! A Histogram only reads in the descriptor (=index). So it should be much more faster than a read - in most cases.

If you want to display more data (not only id-field), the code is as follows:

histogram view1 for id-field  /* maybe with multi-fetch
  if id-field = scan 'string'
    find view2 with view2.id-field = view1.id-field
      display view2
    end-find
  end-if
end-histogram