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
 

 

SQL question - Count or not to Count - Efficiency

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

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Thu Nov 06, 2008 11:09 pm    Post subject: SQL question - Count or not to Count - Efficiency
Reply with quote

In some programs here I read that to test to see if a records already exists they code
Code:
select count(*)
into host-variable-counter
from table
where key = ...


Then they check host-variable-counter being > 0

My question is ~ Is it more efficient to code ?

Code:
select key-column
into host-variable-counter
from table
where key  = ...



Then check for SQLCODE = 0
Back to top
View user's profile Send private message

Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Thu Nov 06, 2008 11:30 pm    Post subject:
Reply with quote

Hi

The First one is better since we are using Key column in the where clause.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2180
Location: @my desk

PostPosted: Fri Nov 07, 2008 12:46 am    Post subject:
Reply with quote

Suresh,

How does the WHERE clauses differ in this context? Both the SQLs use the key column in the where clause.
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Fri Nov 07, 2008 12:57 am    Post subject:
Reply with quote

True.. Both the queries are using the same WHERE Clause. So WHERE Clause doesn't matter here.

Thanks for correcting me.
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: Fri Nov 07, 2008 2:47 am    Post subject:
Reply with quote

Hello,

I'd suggest using the first format with count. . .

If there is more than one row that satisfies the where, a non-zero (which does not indicate "row not found") will be returned (-811 iirc) if a column is named rather than count.

The check for sqlcode = zero would not work correctly.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2180
Location: @my desk

PostPosted: Fri Nov 07, 2008 6:52 am    Post subject:
Reply with quote

Hi Dick,

If he meant the "key column" to be having only unique rows, I guess it'll not make any difference... icon_smile.gif
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: Fri Nov 07, 2008 7:32 am    Post subject:
Reply with quote

Hi Arun,

Yup, if the key-column represents a single-column, unique key icon_smile.gif

The "count" format should work for all cases and would not potentially cause additonal work or problems later if the key were expanded to include a second column because the first column could no longer guarantee uniqueness (say some major business change or merger).
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2180
Location: @my desk

PostPosted: Fri Nov 07, 2008 8:21 am    Post subject:
Reply with quote

Hi Dick,

In similar situations we have used something like this.
Code:
select DISTINCT 1
into :host-variable
from table
where key  = ...


and then check for SQLCODE = 0
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: Fri Nov 07, 2008 8:32 am    Post subject: Reply to: SQL question - Count or not to Count - Efficiency
Reply with quote

Hi Arun,

That would surely work as well icon_smile.gif

d
Back to top
View user's profile Send private message
srj1957

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Mon Nov 10, 2008 7:21 pm    Post subject: Reply to: SQL question - Count or not to Count - Efficiency
Reply with quote

There was no didfference in the where clause

So the better of the two is ..

Code:
Select count(*)
into ws-counter
from table
where key-cloumn = host-var


then check the counter > 0


Thanx for your help...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Nov 10, 2008 7:52 pm    Post subject:
Reply with quote

IF you are using DB2 Vsn7 or above:

you only care if the count is 0 or >0

then select literal or select keyfield
with a
FETCH FIRST ROW ONLY
clause,
which will stop the search.
A count will continue to the end of the table ,
or index if your WHERE clause makes it an index scan instead of a table scan.

if you only want 0, 1 or >1
select a field or literal and let the -811 stop the search when it finds the second.

If you need the COUNT, then do count,
Back to top
View user's profile Send private message
srj1957

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Thu Nov 13, 2008 7:07 pm    Post subject:
Reply with quote

Thanks for all the suggestions
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 abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm
No new posts Fault Analyzer - listings question. egrove IBM Tools 4 Thu Aug 11, 2016 5:31 pm
No new posts dataset copy question - REPRO or some... atulxp TSO/ISPF 2 Wed Aug 03, 2016 10:56 pm
No new posts Update the Sortout file with record c... karthik_sripal SYNCSORT 8 Tue May 17, 2016 8:52 pm


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