ca.portal.admin

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
difference.

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
time.

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? =20

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
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: Different flavors of relational database
"Onni, what do you do when the 'explain' shows a path that's not
optimal? Rework the SQL statement? Does IDMS/SQL have 'hints' like
Oracle?
Besides, the file structures are different on midranges and pc's then
mainframes, and so is the underlying software technology. I doubt that
any mainframe database can retrieve data as rapidly as a google search
of anything.

Lutz Petzold



-----------------------------------------
This e-mail may contain confidential or privileged information. If
you think you have received this
e-mail in error, please advise the sender by reply
e-mail and then delete this e-mail immediately. Thank you. Aetna


"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: Different flavors of relational database
"Lutz,

This is a good question. I do not know about hints w SQL Option but all
optimizers can be ""fooled"" provided that you know the logic used to
select an access path. In DB2 world DBAs used to tweak statistics to
change filter factors used by its optimizer. I recall that SQL Option
Optimizer is very sensitive to ORDER BY sub-statement.

The reason for the difference can be very simple: e.g. the
compared-to-SQL holds database in a buffer while SQL Option reads it
from a disk device.

Onni Kukkonen
Vegasoft

Outcomes