|
The logical is faster, if once built you leave it there. And the logical, I assume, is keyed on Customer and not selecting JUST the customer you are interested in. Also, the logical is assumed to be *IMMED maintenance.
The SQL, when run for the first time in the job, will spend some time building its own view of the data. However, if you run it again it will be faster because the SQL view is now built and useable.
Query, which you did not mention, is very slow compared to the other methods. I mention it here because many people create queries, and many wonder if there is a faster way of getting the data without having to create logicals -- embedded SQL is that way, for situations where a permanent query would traditionally be used.
The speed differences are not worth bothering about for such a simple case; but when you look at real-life factors you will find that the decisions you make are based on the following factors:
1. Do we really want a logical file keyed on these fields? Can we use it elsewhere or is it only for this seldom-used program?
If you can't use it elsewhere, the overhead of keeping the access path may be too expensive -- use SQL.
2. Are there complex and changing conditions for reading this data?
If it's just a simple key, use a logical. If the program must read conditionally, and those conditions can change, embedded SQL may be better.
3. Are our programmers capable of learning and maintaining embedded SQL?
If not, either train them or don't use it. Better to train them, since it's very useful!
We have some user-requested reports here where the user sees a screen on which he can enter various selection values, ranges and so on. The screen is customized by a configuration record that is keyed on the report name, and which determines what fields appear, what validation is performed, whether a download file or printout is being created, and which program is going to do the job.
When the user submits the report, the program specified reads the configuration record with the user's selections in it and executes its particular embedded SQL. This SQL looks like this:
C/EXEC SQL DECLARE C1 CURSOR FOR
C+ SELECT *
C+ FROM VDNDDOC
C+ WHERE NDLINE IN (:DWLN01, :DWLN02, :DWLN03, :DWLN04,
C+ :DWLN05, :DWLN06, :DWLN07, :DWLN08,
C+ :DWLN09, :DWLN10) AND
C+ NDPOL IN (:DWPL01, :DWPL02, :DWPL03, :DWPL04,
C+ :DWPL05, :DWPL06, :DWPL07, :DWPL08,
C+ :DWPL09, :DWPL10) AND
C+ NDSTPRCC >= :DWSTPF AND
C+ NDSTPRCC <= :DWSTPT AND
C+ NDBP = :DWP101
C+ ORDER BY NDPOL, NDLINE, NDSTPRCC
C/END-EXEC
C/EXEC SQL
C+ OPEN C1
C/END-EXEC
C/EXEC SQL WHENEVER NOT FOUND GO TO FINISHED
C/END-EXEC
C DoU SQLCOD = 100
C/EXEC SQL
C+ FETCH C1 INTO
C+ :FORMATND
C/END-EXEC
FORMATND is a record format data structure based on the file being processed (VDNDDOC). The NDxxxx fields are from that file.
The DWxxxx fields are from the configuration record. The *INZSR fills in default values like current date/time and ensures that un-entered values are set correctly for the SQL to pick up the right data.
If we had to do this using logicals, the program would become unbearably complex and we would have to create too many logicals that would be little use elsewhere.
Embedded SQL is certainly about 10 to 20 times faster than Query in an application like this, so I would definitely recommend its use when a logical file is impractical. ==================================
MORE INFORMATION ON THIS TOPIC
==================================
The Best Web Links: tips, tutorials and more.
Visit the ITKnowledge Exchange and get answers to your developing questions fast.
Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.
|
