DB Performance

  • Thread starter Thread starter krishjaya
  • Start date Start date
K

krishjaya

I have to use large volume SQL DB.

My Table name is 'Customer' and has 52 fields with heavy data.

Which SQL command is more fast? & why?

1. SELECT COUNT (*) FROM Customer WHERE CustId =
'1212322321313';
2. SELECT COUNT ( CustId ) FROM Customer WHERE CustId =
'1212322321313';

Thanx
 
Number 2 is faster. For number 1, the database needs to query for the
columns. For number 2, the columns are already specified.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Chicken Salad Alchemist

Sequence, Selection, Iteration.
 
If CustID is not nullable, then you'll get the same results.

However, I think you'll get different behavior if you have a nullable
column.. and you specify it.

Test it to make sure.

declare @t table (EmpID int not null , DOB datetime null )
set nocount on
insert into @t (EmpID , DOB) values ( 123 , GETDATE() )
insert into @t (EmpID) values (456 )
set nocount off


SELECT COUNT ( *) FROM @t
select count(dob) from @t


Just throwing it out there ....... as a small gotcha.
 
Hello krishjaya,

You can use SQL Clients such as Management Studio, EMS or smth else to see
Exec plan of your query.
A lot of SQL clients can analyzy sql query and show you the execution plan
of your queries and time on each state of execution.

k> I have to use large volume SQL DB.
k>
k> My Table name is 'Customer' and has 52 fields with heavy data.
k>
k> Which SQL command is more fast? & why?
k>
k> 1. SELECT COUNT (*) FROM Customer WHERE CustId =
k> '1212322321313';
k> 2. SELECT COUNT ( CustId ) FROM Customer WHERE CustId =
k> '1212322321313';
k> Thanx
k>
---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche
 
Ok Thanx for comments

Then can u explain me what is deference between

SELECT COUNT ( * ) FROM Customer
SELECT COUNT ( 0 ) FROM Customer

Thnx

KJ
 
Back
Top