× 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: DeLong, Eric
> 
> The HLL advantage comes from being able to rapidly get access to a record
> in
> a table.  The real issue here is "what is a transaction".  If the
> transaction involves a great deal of file IO, then SQL will probably
> outperform native access.

This is only true if the I/O can be encapsulated into sets of like actions.
The problem is that this is rarely the case.  The example that's often used
is "adding 5% to list prices".  Well, how often do you actually add 5% to
all the prices in a catalog?  The answer is "never".

Now if you have a small file of item number and price increments, it will
almost certainly be faster to do an UPDATE statement in SQL that applies
that increment to all the items.  However even this is not a perfect
example, you have to take into account the time it took to write the records
to that little file in the first place.

Generally speaking, it's hard to find cases in OLTP where the number of
records in the set justifies using SQL.  Typically the only place in an
interactive environment where SQL makes sense is in queries.

In the end, though, it's all in the application design.  I have long had a
private belief that a properly designed database might make an SQL MRP
generation fly; not only that, it would be able to take care of DRP
requirements at the same time.  But I've yet to design such a database nor
seen anyone else do so.  Until such time as we manage this sort of
application design, SQL is still best used for queries.


> There's no doubt that SQL can outperform native in many circumstances, but
> there's no guarantee that this is always the case.  The DB2 developers
> have
> done a great job of optimizing SQL over the last few releases, and it's
> harder to make sweeping generalizations about native IO being faster than
> SQL...

No, it's still pretty easy.  Single record chains and updates are faster in
native I/O than SQL.  Period.  And the break even point for sets seems to be
about ten records for a read and 100 records for update, although those
numbers are definitely subject to the ongoing optimizations (they used to be
much higher).

Joe



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
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.