SQL performance vs. Internal pgogram database access

Tags:
AS/400
SQL
Is SQL access to a database faster than using the file internally within a program? Assuming that the following: The program was coded to use the keyed index. The SQL statement is coded to use the index that exists on the logical in the most efficient manor.
ASKED: August 29, 2005  10:43 AM
UPDATED: October 31, 2010  12:37 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

SQL is hardly the most efficient way to access a database file. SQL’s strength relies on being a structed language that can be applied to several databases without change not on its speed.
Internal program database access will perform better in most cases, especially if the program is coded in a language optimized to access external data, like RPG.
Of course, the comparison must be made between correct ways to do the database access either in SQL or from within a program.
If there is keyed index in the system, it also exists, as far as AS/400 or iSeries is concerned, or UDB2 for that matter, a keyed access path. And you would use that access path as a logical file from within a program.
SQL performace improves when the number of records handled increase; for large amounts of data SQL maybe the correct choice. Reading one record at a time, however, is much faster when made by a file declared in a program.

========================================================

<i>Reading one record at a time, however, is much faster when made by a file declared in a program.</i>

If reading is random one-record-at-a-time, that’s probably true. SQL is not intended for random retrieval. But if it is sequential, then appropriate SQL will most likely outperform program declared files and I/O. It’s been a few releases since SQL couldn’t outperform native when sets of records are involved — records that can can be defined as a set in an SQL SELECT statement.

Tom

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TheQuigs
    I think the real answer is, "That depends." You cannot really control the index SQL will use. That can be an advantage or a disadvantage. If you know for certain the most efficient index to use in every case, you can code the program with internal file access and probably be faster. However, IBM spends a lot of effort developing the query optimizer which will review all the indexes it finds "of interest" to the query being processed. For more information on query optimization you can take a look at the following article (1st in a series) from the eServer iSeries Extra magazine: http://www.eservercomputing.com/ME2/Audiences/dirmod.asp?sid=BCF4DE820EA64A858FB46EECB7C00BB4&nm=&type=Publishing&mod=Publications%3A%3AArticle&mid=8F3A7027421841978F18BE895F87F791&AudID=1E8FEE745A284521B6CFB3FD70B49099&tier=4&id=FF99621CEC0743D0963727A7EB546E60
    0 pointsBadges:
    report
  • TomLiotta
    Maybe one will be faster; maybe the other. Without any clue about what the program is doing, there's no way to know. If it's doing work that's suited for SQL, then the SQL will probably be faster and more efficient. If it's interactive random updates, native is probably faster. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following