Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Optimizing an embedded SQL statement
has no ratings.
Published: 27 Feb 2014
Revised: 27 Feb 2014 - 2058 days ago
Last viewed on: 17 Oct 2019 (3465 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Optimizing an embedded SQL statement Published by: Chris Proctor on 27 Feb 2014 view comments(6)

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Optimizing an embedded SQL statement
Posted: 5 years 7 months 20 days 13 hours 24 minutes ago

WHERE EXISTS

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Optimizing an embedded SQL statement
Posted: 5 years 7 months 20 days 13 hours 10 minutes ago

Hi Bob. Duh!! That would be better. I also noticed that there is an ALTSEQ keyword that I can use in the file that somehow ignores the case. Are you familiar with this? I was thinking that perhaps he could just create a logical by the fields he's trying to compare then just to a SETLL to see if the rcord exists. Not sure if this would work or if it'd be faster or slower than a where exists.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Optimizing an embedded SQL statement
Posted: 5 years 7 months 20 days 12 hours 26 minutes ago
Edited: Thu, 27 Feb, 2014 at 11:13:34 (2058 days ago)

Pretty sure SQL ignores ALTSEQ. ALTSEQ applies to the keys of a PF or LF, not do the data itself. TRNTBL might come into play if you name an LF in a SQL statement, but that would be dynamic, so no faster than SQL UPPER().

I think Bob has it with the WHERE EXISTS. Unless you need the COUNT for some other reason, EXISTS will stop as soon as the count reaches 1 and return TRUE.

Here's a question, though: Do you have an index or indexes on nciadr by any or all of the fields in your WHERE tests? That could also speed things up greatly. Without supporting indexes, you may be doing table scans.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Optimizing an embedded SQL statement
Posted: 5 years 7 months 20 days 11 hours 58 minutes ago

Hi Dale. No, I don't believe there's an index by the where clause fields. That would definitely help. I'm not sure how the WHERE EXIST would look being as I'm only dealing with one file. Can you give me an example? Thanks! BTW, I wasn't thinking of using the ALTSEQ LF with an SQL statement, I was thinking about using a SETLL instead.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Optimizing an embedded SQL statement
Posted: 5 years 7 months 20 days 11 hours 35 minutes ago

ALTSEQ means (typically) that the key fields are converted to uppercase, but only in the key/index, not in the data portion of the file. 

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Optimizing an embedded SQL statement
Posted: 5 years 7 months 20 days 10 hours 13 minutes ago
Edited: Thu, 27 Feb, 2014 at 13:34:41 (2058 days ago)

Right. I dont' think ALTSEQ would help.

SQL example something like:

exec sql SELECT CASE
                  WHEN EXISTS (SELECT * FROM nciadr

                                 WHERE admrrn=:store
                                   AND upper(ademl1)=upper(:email)
                                   AND upper(adstfn)=upper(:fname)
                                   AND upper(adstln)=upper(:lname)
                              ) THEN '1'
                  ELSE               '0'
                END INTO :MyInd
           FROM SYSIBM.SYSDUMMY1;

SELECT needs a from clause, and the dummy table always provides exactly one row. The dummy table is a real table, and it has a single column, but you never need to refer to that column. Basically, this table lets you calculate any arbitrary SQL expression. Couldn't find a concrete definition of it, but it's all over the SQL reference in the examples of the built-in functions. Other SQLs have a similar table; I think in Oracle it's called DUAL.

If you put the SELECT in interactive SQL, and remove the INTO :MyInd, and supply a value where :store is, you would get a result table with one row, and one column, with either a '1' or a '0', which is the value that comes out of the CASE expression.