Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    12

    Unanswered: Efficient way to do this...

    We use selects like this in embedded our programs:

    select * from table where (field = :field or :field = ' ')

    :field depends on a filter condition and we don't know if it's going to have a value in it or not. If :field has a value in it, we want to only show the rows with field = :field. If :field is blanks (' ') we want to show all rows.

    It seems that to put several conditions like this in a query results in bad peformance. Is there a better, performance-friendly way to write this?

    We're on DB2 for iSeries

    Thanks in advance
    Last edited by sordax; 03-15-06 at 13:31.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would try this:

    set fieldb to blanks

    select col1, col2 from table where field in (:fielda, :fieldb)

    I hope that you don't use "select *" in embeded SQL and that was only an example.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    such a construct will most likely lead to a tablespace-scan.
    Using the BIND parameter REOPT(VARS) might increase performance

    consider using dynamic sql for that issue.

  4. #4
    Join Date
    Feb 2005
    Posts
    12
    Quote Originally Posted by Marcus_A
    I would try this:

    set fieldb to blanks

    select col1, col2 from table where field in (:fielda, :fieldb)

    I hope that you don't use "select *" in embeded SQL and that was only an example.
    I think this won't give the desired result as it won't select all rows when host variable :fielda = ' ', it will only select rows having field = ' ' ! By the other hand I don't think field in (:fielda, :fieldb) is better than field = :fielda or field = fieldb

    Don't worry we do not use "select *", it was only an example.

  5. #5
    Join Date
    Oct 2005
    Posts
    12
    You'd better run EXPLAIN with the different options to have all the info. Then choose the best one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •