How do I limit the sql record using Count(*) in asp.net c#?

  • Thread starter Thread starter Nigil LaVey
  • Start date Start date
N

Nigil LaVey

hi folks..

I have a question on Count(*) Property.. Is there any equivalent property
to specify or limit the number of records when using Count(*)?
cos I realized that when using Count(*) on a very large database.. it is
rather slow. The Count(*) property is actually looping through all records.

what I need is that when count > 2 then stop the looping.
I know by using the following code.. I can achieved that.. just that I do
believe that there must be a better way...

sqlCmd.CommandText="Select * From MyTable where MyID=5";
sqlReader=sql_Cmd.ExecuteReader();

if(sql_Reader.Read())
break;



all thoughts welcome,
Nigil Chua
 
Select * From is very inefficient because you are retrieving the
values from every column in the table. Count(*) doesn't have to
retrieve column values. If you just want to see if the count of
records in a given table is >2 then use the TOP syntax: Select top 2
<keycol> from <tablename>. Where clause and Order by optional
depending on the results you are looking for.

Mary
 
Hi

If you are using SQL Server, 2 ways to limit your records
SET ROWCOUNT
Select * From MyTable where MyID=
Or
Select Top 2 * From MyTable where MyID=
Hope this helps

Bin Song, MC
 
greetings folk..

thanks for all the overwhelming replies..
so which is better? using Set RowCount or Select Top 2 or they are just
about the same performance?

please advise,
Nigil Chua
 
Back
Top