× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



From: CRPence

> And yet, with a simple ISAM access, we can generate the required
> report with the existing database. It seems to me that it is NOT
> the database design that is at fault, but simply the wrong choice
> of tool.

Of course the historical representation requires two transaction
times. When stored as separate rows that is fine with ISAM, but stored
in one row is better for SQL. Yes it is true that SQL is often the
wrong tool for the job if the data is not stored in a relational
database table in a form already conducive to the SQL. In the same
manner we could argue against SQL on flat file and stream data.

You are suggesting storing the same information in two records. Your end
time of one event is the same as the start time of the next event. That's
by definition not normalized data, and thus non-relational in nature. In
order to make SQL work, you have resorted to what we lovingly call
"strategically denormalized" data. It's the same as storing total
information in a header, or an overall status flag. In short, it's
cheating. But hey, that's okay.


There was no claim that "changing the database just isn't an option."

No, nor did I say your approach was "wrong". I was just noting that
changing the DDS just to make SQL work might indicate that SQL was the wrong
tool for the job.


So if SQL is the wrong tool for the given files, then either the
files should change, or the move to the SQL maybe should be
discouraged.? That is, why should the OP be offered up some possibly
complex, ugly, or even poor performing SQL as the tool to solve the
problem, when SQL is considered the wrong tool and approach for the
given database design? Note that question is not an attempt to
disparage the SQL offered, as the SQL I would have offered was the same.

Yes, this is the important point. Moving to SQL is sometimes a bad choice.


I just moved the work from the inquiry to the write; no more writes,
just the additional field+storage,

You do have extra writes. You write the owner record once with a NULL
"until" value, and then again with the actual "until" value.


and the same subquery logic required,
but once per write instead of many per lookup. Since the majority of
resources are typically consumed in retrieval and reporting, a change to
the database which involves more resources when storing the data while
reducing the cost during inquiry, is often a good trade-off.

Yes, but look at the bigger issue: the whole thing can be done fine without
SQL with the existing database. So what we're seeing is that, in order to
shoehorn this into SQL, we're adding storage, extra writes and a database
change.

It's just hard to justify. Especially the denormalized data.


Besides,
replacing some DDS keyed logical files used for ISAM, with appropriate
SQL INDEX objects to support query activity instead, could reduce the
overall storage requirement for the database.

You could do this with or without changing the underlying records in the
database. You can take advantage of DDL in both RPG and SQL. This is not
an advantage for SQL vs. ISAM.


And if fewer access paths, then less maintenance on writes as well.

I didn't see any fewer access paths. An INDEX is still an access path that
needs to be maintained.


Sorry to keep arguing, Chuck, but I just want to be clear, since this is
such a great example. Using ISAM, you can write data that is more concise
(and more normalized) and requires less work on both the write and the
subsequent read. This is a situation where clearly SQL is not the best
choice for the job.


Joe



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.