Looping using READ vs Embedded SQL

20 pts.
Tags:
AS 400
SQL
Which approach is better? What all the parameters, we need to consider to come to a proper conclusion? Which approach is better in which scenario? Can anyone explain in detail?


Software/Hardware used:
as400
1

Answer Wiki

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

They both have pros and cons. It depends on the project as to which will perform better. Things like if you already have logical files created versus creating views in SQL. What you are doing with the data after a read is another thing to consider. Returning a single record or a table of data will affect your choice.. Details on the project are need to point you in a direction that may be better suited

Discuss This Question: 3  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.
  • xselvam
    It's not related to any project. I was just wondering which approach is better.

    Let's say, I have a file with 10000 records. I have to perform 5 validation checks on all the 10000 records.Only If all the 10000 records satisfy  those 5 checks, I'll go to next step. 

    In the next step, I'll update 2 fields in the file for all 10000 records and generate one report.

    Now for the above scenario, which approach would be better ?
    1) Two file read loops ( one while validating 5 checks & another for updating )
    2) One file read loop and one normal loop ( during file read loop , ill move all the 10000 records into an array. During normal loop, I'll look up the array to update )
    3) Embedded SQL
    20 pointsBadges:
    report
  • ToddN2000
    In your example I would go with the embedded SQL to perform the test. I believe you would see better performance. If you want to test it, just create a temp file for holding a timestamp value and log a start and stop time before each type of read. The SQL version and the start and end of the loop.
    134,930 pointsBadges:
    report
  • TheRealRaven
    It seems plausible that neither would be "best". Best could be to create a VIEW that does all necessary validation and a SQL statement that does the UPDATE. Without knowing what the "validation" is nor the "update", it's hard to be sure.

    Next best is almost certainly a SQL stored proc, followed by embedded SQL, and finally various combinations of READ loops.

    It's hard to come up with actual scenarios that fit the question and that favor READ loops. Also, it's possible that some interpretations of "better" could change recommendations.
    36,115 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.

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

Following

Share this item with your network: