Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    44

    Unanswered: SQL Performance advice please....

    DB2 v7.2 on z/OS (embedded SQL in Cobol)

    I have a table with 8 date columns and user can base their search criteria on any of the date columns, So now I have 3 ways of doing it:

    1) Write 9 different static sqls and run the appropriate one
    Select c1, c2 from t1 where date1 = :ws-date1
    Select c1, c2 from t1 where date2 = :ws-date2 and so on......

    2) write one big static SQL and populate the required host variables while populating the rest of the variables with low and high values (in the sense all '0' and all '9's)
    select c1, c2 from t1 where
    date1 between :ws-date1L and :ws-date1H and
    date2 between :ws-date2L and :ws-date2H.......
    if the user specifies date ranges for date2, I populate ws-date2L and ws-date2H and move zeroes to ws-date1L and all 9's to ws-date1H (performance drag I guess!)

    3) Dynamic SQL

    The table is brand new and has only a few rows, so EXPLAIN/PLAN_TABLE/ DSN_STATEMENT table suggestions may not be good. I would appreciate if anyone could throw some light on which one would be the most efficient (based on query return time and optimal machine performance) way to do it.
    Thanks in advance

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It depends on a few things:

    1. Are there any other predicates in the where clause besides the date(s)?

    2. Are the data columns indexed (each with its own index)?

    3. What is the normal or average date range in each search expressed at a percentage of the data in the table?

    Option 1 is definitely more efficient, but depending on the answers to these questions, it may, or may not, be significantly more efficient.
    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
    Sep 2003
    Posts
    44
    1. Are there any other predicates in the where clause besides the date(s)? No

    2. Are the data columns indexed (each with its own index)? No

    3. What is the normal or average date range in each search expressed at a percentage of the data in the table? Not sure about it, Business analyst may know more....

    I am weighing between 1 and 3 except for the fact Dynamic SQL does too much stuff at runtime, but with option 1, I got 9 separate cursors to open, fetch and close!!! and it doesn't look cool :-(

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If there are no indexes on the dates, then you will be doing a tablespace scan (reading all the rows to determine which ones met the search criteria). In that situation, option 2 will not be much slower than option 1, but it will be slower.

    But maybe you should consider the indexes if the table is large enough?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Sep 2003
    Posts
    44
    tablespace scan seems to be a given in this case. with option 2, DB2 will have to take every 8 columns and compare it against 16 host variables for every row (even though only 2 of those host variables will valid values at anytime), so I believe it will be a lot slower

    This is kind of a "test the waters" project and at the most I expect a few thousand rows in the first year - so I was thinking about worrying about indexes when I get a feel of the data distribution/patterns.

    it's been a couple of years since I did Dynamic SQL and I am itching for it too :-) even though this query is more boring than challenging!

  6. #6
    Join Date
    Jan 2005
    Posts
    191
    Sort of option 2 with a couple of extra things
    1) use
    (datex = :hvdate or :hv-indx = 1)
    and
    (datey = :hvdate or :hv-indy = 1)
    and ...
    Set all the :hv-inds to 1, except the one for the date column you are searching against. Make that one zero - or anything but 1.
    2) put an index onto each date column. If most of the date values will be in the past, consider making the sequence DESC.
    3) bind with REOPT(VARS)


    James Campbell

  7. #7
    Join Date
    Sep 2003
    Posts
    44
    Quote Originally Posted by jacampbell
    Sort of option 2 with a couple of extra things
    1) use
    (datex = :hvdate or :hv-indx = 1)
    and
    (datey = :hvdate or :hv-indy = 1)
    and ...
    Set all the :hv-inds to 1, except the one for the date column you are searching against. Make that one zero - or anything but 1.
    2) put an index onto each date column. If most of the date values will be in the past, consider making the sequence DESC.
    3) bind with REOPT(VARS)


    James Campbell
    Thank you James & Marcus, I will try that

Posting Permissions

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