| View previous topic :: View next topic |
| Author |
Message |
satyenderd
Beginner
Joined: 26 Aug 2005
Posts: 144
Topics: 73
|
 Posted: Thu Sep 03, 2009 4:34 pm Post subject: Why SELECT * is slower than SELECT COUNT(*) in DB2? |
 |
|
Hi all,
I have a small question.
I would like to know, Why SELECT * is slower than SELECT COUNT(*) in DB2?
I have gone thru few forums, but i didnt found the correct one.
Could anyone please throw some light on this question?
Thanks in advance to all.
_________________
Satya |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Thu Sep 03, 2009 5:31 pm Post subject: |
|
|
satyenderd,
I don't think they are even comparable in the first place. Select * retrieves ALL the columns of the table where as select count(*) retrieves just 1 row 1 column. DB2 is sensitive to the number of columns selected. Remember the number of columns DB2 needs to adjust the work area buffers.
Kolusu |
|
| Back to top |
|
|
satyenderd
Beginner
Joined: 26 Aug 2005
Posts: 144
Topics: 73
|
| Posted: Thu Sep 03, 2009 5:55 pm Post subject: |
|
|
Thank you very much Kolusu.
_________________
Satya |
|
| Back to top |
|
|
Anuj Dhawan
Intermediate
Joined: 19 Jul 2007
Posts: 298
Topics: 7
Location: Mumbai,India
|
| Posted: Thu Sep 17, 2009 4:26 am Post subject: |
|
|
Why do you want to compare Oranges with Apples? One is merely the count of rows while other is entire data. 
_________________
Regards,
Anuj |
|
| Back to top |
|
|
psmadhusudhan
Beginner
Joined: 28 Nov 2006
Posts: 143
Topics: 48
|
| Posted: Thu Sep 17, 2009 5:44 am Post subject: |
|
|
I have small query. Suppose I have table TBL1 has comprised of 95 columns and has 3000 million rows. what would I get if I fire query Select count(7) from ADM.TBL1.
_________________
Thanks
Madhu Sudhan |
|
| Back to top |
|
|
Anuj Dhawan
Intermediate
Joined: 19 Jul 2007
Posts: 298
Topics: 7
Location: Mumbai,India
|
| Posted: Thu Sep 17, 2009 5:51 am Post subject: |
|
|
1. Why do you append your query to a totally un-related thread?
2. What happend when you try it at your end?
_________________
Regards,
Anuj |
|
| Back to top |
|
|
psmadhusudhan
Beginner
Joined: 28 Nov 2006
Posts: 143
Topics: 48
|
| Posted: Thu Sep 17, 2009 6:36 am Post subject: |
|
|
Dear Anuj,
1. The thread is abt SELECT COUNT(*) and I have asked abt SELECT COUNT(7).
2. I dont have access to DB2 currently. So I am unable to test it otherwise I would have done myself rather posting it here.
Please reply if you know the solution.
_________________
Thanks
Madhu Sudhan |
|
| Back to top |
|
|
Anuj Dhawan
Intermediate
Joined: 19 Jul 2007
Posts: 298
Topics: 7
Location: Mumbai,India
|
| Posted: Thu Sep 17, 2009 11:25 am Post subject: |
|
|
| Quote: | | Please reply if you know the solution. |
I'll try...I'll try...
The COUNT function returns the number of rows in a query. The syntax for the COUNT function is: | Code: | SELECT COUNT(expression)
FROM tables
WHERE predicates;
|
Note: The COUNT function will only count those records in which the field in the brackets is NOT NULL.
For example, if you have the following table called suppliers: | Code: | Supplier_ID| Supplier_Name |State
1 | Anuj | CA
2 | Madhu |
3 | Sudhan |
|
So the result for the below query will return 3. | Code: | | Select COUNT(Supplier_ID) from suppliers; |
where suppliers is table name.
While the result for the next query will only return 1, since there is only one row in the suppliers table where the State field is NOT NULL. | Code: | | Select COUNT(State) from suppliers; |
Having said that, since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) or any number as you choose 7, to get better performance as the database engine will not have to fetch back the data fields.
For example, the following syntax would result in better performance: | Code: | SELECT department, COUNT(1) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;
|
instead of | Code: | SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department; |
Now, in former query, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.
_________________
Regards,
Anuj |
|
| Back to top |
|
|
psmadhusudhan
Beginner
Joined: 28 Nov 2006
Posts: 143
Topics: 48
|
| Posted: Thu Sep 17, 2009 11:50 pm Post subject: |
|
|
Thanks Anuj for your detailed reply  . My doubt is cleared now.
_________________
Thanks
Madhu Sudhan |
|
| Back to top |
|
|
Anuj Dhawan
Intermediate
Joined: 19 Jul 2007
Posts: 298
Topics: 7
Location: Mumbai,India
|
| Posted: Thu Sep 17, 2009 11:58 pm Post subject: |
|
|
Glad to hear. Hopefully I'd been helpful...
_________________
Regards,
Anuj |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
