Check number of records in a database table other than count(*)

Question

Hi all,

I want to check if there are any records in a table for a certain entry. I used COUNT(*) to check the number of records and got it to work. However, when the number of records for an entry is very high, my page loads slowly.

I guess COUNT(*) is causing the problem, but how do I check if the records exist without using it? I only want to check whether any records exist for the entry and then execute some code. Please help me find an alternative solution for this.

Thanks for any help.

Answer 1

use select top 1 and check is there is an row

Answer 2

You can try selecting the first entry for given condition.

SELECT id FROM table WHERE <condition> LIMIT 1

I'm not sure if this will be quicker but you can try.

Other possible solution. How do you use count? COUNT(*)? If yes, then try using COUNT(id). As I remember this should be faster.

Answer 3

There are several ways that may work. You can use exists, which lets the database optimise the method to get the answer:

if exists(select * from ...)

You can use top 1 so that the database can stop after finding the first match:

if (select count(*) from (select top 1 * from ...)) > 0

Answer 4

you should use

select count(1) from

If you are saying (*) it will expand all the column's and then count

Answer 5

I would recommend testing to see if at least 1 record exists in the table, that meets your criteria then continue accordingly. For example:

IF EXISTS
(
    SELECT TOP 1 Table_Name --Or Your ColumnName
    FROM INFORMATION_SCHEMA.Tables -- Or your TableName
)
BEGIN
    PRINT 'At least one record exists in table'
END

Answer 6

I found this on codeproject. It's quite handy.

-- Author,,Md. Marufuzzaman

SELECT SYS_OBJ.NAME AS "TABLE NAME"
     , SYS_INDX.ROWCNT AS "ROW COUNT"
FROM SYSOBJECTS SYS_OBJ, SYSINDEXES SYS_INDX

WHERE SYS_INDX.ID = SYS_OBJ.ID
  AND INDID IN(0,1) --This specifies 'user' databases only
  AND XTYPE = 'U' --This omits the diagrams table of the database
--You may find other system tables will need to be ommitted,
 AND SYS_OBJ.NAME <> 'SYSDIAGRAMS'

ORDER BY SYS_INDX.rowcnt DESC --I found it more useful to display 
--The following line adds up all the rowcount results and places
--the final result into a separate column [below the first resulting table]
COMPUTE SUM(SYS_INDX.ROWCNT) 

GO