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
 

 

Count Sql performance

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

New User


Joined: 20 Jul 2007
Posts: 27
Location: bangalore

PostPosted: Tue Aug 25, 2009 2:56 am    Post subject: Count Sql performance
Reply with quote

I have a SQl to get the Count in the Program

Select count(*) from xxx
where xxx= :xxxxx

Is there any other method of SQL to get the count without using Count(*) in the SQl

Some people says using count(*) with degrade the performace.

thanks
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: Tue Aug 25, 2009 3:15 am    Post subject:
Reply with quote

Hello,

Quote:
Is there any other method of SQL to get the count without using Count(*) in the SQl
Sure, but they all take more resources. . .

Quote:
Some people says using count(*) with degrade the performace.
Counting rows does not cause degradation. Reading rows does. If the predicate in the example is not an index, the entire table must be read. If this is an index, only those rows needed will be read.
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Tue Aug 25, 2009 10:44 am    Post subject:
Reply with quote

Count * does not degrade the performance, even I was also think the same but later found out that sometime count * is been used for the existence check of some of the data in the table or not at the moment we should not use the count * but if the requirement want to have the number of rows present then by using count * will not degrade the performance,
However you can use the SQLERRD or get diagnostic for the same feature, check if these functionality does fit in your requirement.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Tue Aug 25, 2009 7:33 pm    Post subject:
Reply with quote

You can use
SELECT COUNT(YOUR_TABLE_PRIMARY_KEY).

This can ensure that you're using index, which is more efficient.
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: Tue Aug 25, 2009 8:47 pm    Post subject:
Reply with quote

Hello,

Quote:
where xxx= :xxxxx

How does that accomplish the WHERE. . . icon_confused.gif

Care must be taken to post replies that answer the posted question. . .
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 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


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