Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ramfrom84

New User


Joined: 23 Aug 2006
Posts: 93
Location: chennai

PostPosted: Thu Jun 28, 2007 7:05 pm    Post subject: Which is better performance.....Between or Equal
Reply with quote

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....
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 28, 2007 7:54 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 149
Location: Ottawa Canada

PostPosted: Thu Jun 28, 2007 11:50 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Jun 29, 2007 12:57 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm
No new posts LIKE vs EQUAL Nileshkul DB2 7 Sat Jul 16, 2016 7:50 am
No new posts DISP=(SHR,PASS) performance opinion steve-myers JCL & VSAM 1 Wed Dec 02, 2015 11:53 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us