SQL performance vs. Internal pgogram database access

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.

Answer Wiki

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.

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

Processing your response...

Discuss This Question: 2 &nbspReplies

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

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

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

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

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

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

Share this item with your network:

To follow this tag...

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