• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • SQL Performance: IN vs. EXISTS

    June 16, 2010 Hey, Ted

    Concerning your article Update One File Based on Another File, I would agree that the IN is more intuitive than the EXISTS. When looking at the volume of data, possible number of rows to update, and the rows returned for the IN, is there a preferred method if considering performance? Does the IN or EXISTS result in better performance under certain conditions?

    –Sarah

    The prevailing wisdom is that EXISTS currently tends to outperform IN. Here are comments from two readers that support that position.

    A nice consequence of going the IN route (as opposed to using EXISTS) is that, at least on V5R3, Visual Explain gives consistently better execution times.

    –Luis

    I don’t know if they’ve fixed it recently, but I’ve steered away from the IN predicate in SQL, if implemented with a SELECT, because performance was poor compared to an EXISTS. I found the IN to be okay for small lists, but if it contained a large result set, my query or update slowed to a crawl.

    –Darren

    Now please permit me to make a few comments. First, performance is not a static science. The database team at IBM is constantly working to improve the query engine. Whereas technique A performs better than technique B today, the reverse may be true next week. Hence the words “currently” and “tends” in my first sentence.

    Second, since EXISTS has exhibited superior performance, it may pay to learn how to use it.

    Third, it is not necessary to optimize every query. Concentrate on the dogs.

    Fourth, it may be a good idea to sign up for the IBM DB2 for i5/OS SQL Performance Monitoring and Tuning Workshop.

    Other readers wrote in with words of praise for row-value expressions. The following response is typical.

    I’ve been looking for this capability in System i SQL for years. I had no idea it had been released in V5R4. Thanks for bringing this to light.

    –Curt

    Thanks to all who took time to write. I very much appreciate it.

    –Ted

    RELATED STORY

    Update One File Based on Another File



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    OCEAN User Group

    OCEAN TechCon25 Online

    It’s an Exciting Time for IBM i !

    July 16 & 17, 2025 – ONLINE

    Two virtual days of learning, presented by an outstanding group of IBM’ers and IBM Champions, featuring leading-edge topics.

    FREE for OCEAN members!

    Register NOW!

    Annual (12-month) Individual OCEAN Memberships are $80 and a Corporate Membership is $250. A Corporate Membership would allow your entire company to have full access to the OCEAN website & video library and to attend OCEAN events at member rates. Act now because rates are increasing on August 1, 2025.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    looksoftware:  Recreate your IBM i applications with re:new! Free Webinar!
    Shield Advanced Solutions:  Receiver Apply Program ~ affordable availability for the IBM i
    COMMON:  Join us at the Fall 2010 Conference & Expo, Oct. 4 - 6, in San Antonio, Texas

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    Shield Unveils New DR Solution for i/OS The AS/400 at 22: Yesterday and Forever

    Leave a Reply Cancel reply

Volume 10, Number 19 -- June 16, 2010
THIS ISSUE SPONSORED BY:

ProData Computer Services
System i Developer
Twin Data Corporation

Table of Contents

  • Client/Server Performance, Part 1: Blocking
  • SQL Performance: IN vs. EXISTS
  • How Do I Tell These Partitions Apart?

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle