Performance Question

[bharder@nlrha.ab.ca]

My understanding is that these would always result in the same query plan. The reason is that the statements are simply alternate ways of achieving the same thing, and the alternate ways are simple syntactical differences. You can go further too. The iSeries has a unified query engine, so you get the same results from multiple different interfaces (Query/400, iSQL, embedded SQL, etc.). Even knowing that there are actually 2 query engines (CQE and SQE), the actual process of deciding which query engine will execute the query plan is itself unified. The only ways you get different performance out of a pair of statements like these are: 1). You execute against different tables; 2). You execute at different times and the table data has significantly changed between those times; 3). You execute sequentially and gain a performance boost due to I/O caching, query plan caching, etc. 4). Other workloads on the system vary and impact one query more than the other.

[Guest.Visitor]

Performance Question

Put the job in debug (specify *NONE for program) and then run those 2 statements. The SQL query engine will put lots of useful information in your job log and will tell you what access paths were used. Then you can determine if the statements are systematically equivalent. Chris

[buck.calabro@commsoft.net]

Performance Question

> Put the job in debug (specify *NONE for program) > and then run those 2 statements. In addition to Chris' excellent advice, be aware that the two statements *may* be the same for one dataset, but different for others. You'd need to model it in a database having millions of records, with keys spaced tightly and loosely to be certain. Performance questions often boil down to 'it depends on your situation.' Especially with SQL, since you tell it what you want, not how to do it. --buck

[Guest.Visitor]

Performance Question

Buck Calabro wrote: > In addition to Chris' excellent advice, be aware that the two > statements *may* be the same for one dataset, but different for > others. You'd need to model it in a database having millions of > records, with keys spaced tightly and loosely to be certain. > Performance questions often boil down to 'it depends on your > situation.' Especially with SQL, since you tell it what you want, not > how to do it. I would also add that even though these two statments may come out the same, I'd imagine you will find they are different if you add selection criteria since record selection and joining actions have important timing considerations (you want to select records before joining tables). Bill

[Guest.Visitor]

Performance Question

I'm curious. The following 2 select statements are equal in the data returned. sample 1) Select * from FILE1 join FILE2 on FILE1FLD1 = FILE2FLD1 sample 2) Select * from FILE1, FILE2 where FILE1FLD1 = FILE2FLD1 However, are they identical as far as SQL mechanics is concerned? Does one perform better than the other? If so, which one performs better and why does it perform better? Thanks

[solpssi@bellsouth.net]

Performance Question

I have done some testing and execution of 2 SQL statements is usually faster than use of a Single SQL Join statement. (small result set, 0-1000 rows) However, does the Join return a lot of rows for processing like printing a report or Batch posting. Join is probably the better choice. ( 500? or more rows) Use of a single select, fetch n Rows into MODS and loop (SQL, SETLL or other RPG OPCODES) to validate. Use of UDF's to take values from primary table and evaluate against secondary table, still 2 SQL statements but highly reusable and the programmer has more control in specifying SQL statement. Also SQL UDF's may be executed in parrallel to primary SQL statement. SQL is also simpler and easier to code and modify.