Different flavors of relational database

Discussion created by ca.portal.admin on May 2, 2006
We did a comparison retrieval between IDMS/SQL (on the mainframe) and
SQL Server (on a Windows server). A database was defined the same way
on both, and loaded with the same data. Queries that used keys (index
or calc) had about the same response time on both. But queries that
generated an area sweep ran noticeably faster on SQL Server, for example
1 second vs. 15 seconds. I am trying to figure out why such a big

I did not consider the response time for area sweep unreasonable for the
size of the database. But the programmer insisted that it was, and
proved his point with the SQL Server demo. I considered these queries
to be large (for online access), but the SQL Server DBA said that they
are considered small by SQL Server standards. They reported that DB2
(on AIX) could also do similarly large queries with subsecond response

I do realize that there are many differences between the environments,
i.e. hardware, software, operating system, DASD, number of users, etc.
But is there something inherently different about IDMS/SQL that would
cause large queries (area sweeps) to run so much slower than the other
two relational databases?

If anyone else has done similar comparison testing, would you be willing
to share your results?

Kay Rozeboom
State of Iowa
Information Technology Enterprise
Department of Administrative Services
Telephone: 515.281.6139 Fax: 515.281.6137
Email: Kay.Rozeboom@Iowa.Gov

IDMS Public Discussion Forum


Re: Different flavors of relational database
"First observation is that relational databases do full tablespace scans,
not area sweeps; it may sound like a fine point but it may contain the
key to explaining the differences in performance.

On an area sweep, IDMS is going to read every page in the area (or
subrange that a particular record is restricted to). The time it takes
will be dependent on the number of physical pages/I/Os it needs to do.
IDMS has no way of knowing which pages contain the record type of
interest, so it must read them all.

In a ""true"" (please, don't start on me about that term, that's why I put
it on quotes) relational dbms, the way tablespace scans are done MAY be
controlled by structures (e.g. bitmaps) that indicate on which physical
blocks records reside, or there could be other things in play which make
it ""smarter"" in finding blocks of interest. (I don't know for sure, just
postulating.) In your particular test, are the two databases of
comparable physical size/block counts?

In any event, if you want an IDMS area sweep to run as fast as possible
design the database so the page sizes are large, and the database is
really really full.