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 - 1122 days ago
Last viewed on: 24 Mar 2017 (2038 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)

Good morning. A co-worker of mine has a program with the following SQL statement in it. This program has been running for 2 days now and I'm wondering if it's because of this code. What he's doing is converting customer information from one system to another and the names, addresses, etc. can be any combination of upper and lower case, so he's trying to identify if the customer has already been written by converting everything to uppercase. I just wonder if, when processing against a large file, 800K records, if there is a simplier way of determining if a record exists. Here's his statement:

exec sql select count(*) into :count from nciadr where
  admrrn=:store and upper(ademl1)=upper(:email) and   
  upper(adstfn)=upper(:fname) and                     
  upper(adstln)=upper(:lname);                        

Does converting everything to uppercase before getting a count run slow? Any suggestions would be greatly appreciated.

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: 3 years 27 days 6 hours 31 minutes ago

WHERE EXISTS

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Optimizing an embedded SQL statement
Posted: 3 years 27 days 6 hours 17 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: 3 years 27 days 5 hours 33 minutes ago
Edited: Thu, 27 Feb, 2014 at 11:13:34 (1122 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: 3 years 27 days 5 hours 5 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: 3 years 27 days 4 hours 42 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: 3 years 27 days 3 hours 20 minutes ago
Edited: Thu, 27 Feb, 2014 at 13:34:41 (1122 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.