Which is better performance.....Between or Equal

ramfrom84 · New User Joined: 23 Aug 2006 Posts: 93 Location: chennai

Hi All,

I have Db2 related job to be run for daily and also Monthly, i m using Cursor with date as condition, Which is Better Between ort Equal

Consider the performance of 1 & 2 ,

1.If i use same cursor for both Daily and Monthly with date field as Condition as below ..

Date between Start_date and End_date.

for Daily, i m moving same date to Start and End Date ,
For Month i moved 01 to start_date and Job run at last day of the month so i moved date to End Date.
EX:
Daily
Date between '2007-06-28' and '2007-06-28'
Monthly
Date between '2007-06-01' and '2007-06-30'

2 . Alternate option :
Two Cursor one for daily and other one for Monthly job as mention below

Daily Cursor should have

Date = WS_date

Monthly Curosr as

Date between Start_date and End_date.

Daily :
Date = '2007-06-28'

Monthly
Date between '2007-06-01' and '2007-06-30'

bye....

dick scherrer · Posted: Thu Jun 28, 2007 7:54 pm

Hello,

Long ago the equals would (always?) perform better than the between.

As the query optomizer is improved, it may recognize the values named in the between as being a single value, and process just as efficiently as the equals.

You could talk with your dbas to set up a performance test to see how this compares on your system.

TG Murphy · Posted: Thu Jun 28, 2007 11:50 pm

Setup yourself up with 2 SQLs. One uses BETWEEN. The other uses =.

1. Feed both SQLs into the EXPLAIN utility and observe the cost estimate from EXPLAIN.

2. Run both SQLs and ask the DBA to do a DB2 trace. The trace will tell you how much CPU the statements chew up.

3. Create your own benchmark program that executes both SQLs. Execute both SQLs many times. For example:

PERFORM SQL1 1000 TIMES
PERFORM SQL2 1000 TIMES
PERFORM SQL1 1000 TIMES
PERFORM SQL2 1000 TIMES

Within each section use SET CURRENT TIMESTAMP to measure and display the elapsed time. Compare.

Craq Giegerich · Posted: Fri Jun 29, 2007 12:57 am

You allso have to consider maintenance -- with one option you have 1 cursor and 1 fetch in the program with the other you have 2 cursors and 2 fetches and the logic to decide which one get used when.