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 - 1749 days ago
Last viewed on: 11 Dec 2018 (3167 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