CODEWITHSUNDEEP
sqlsql-servert-sql
ajithmanmu
ajithmanmu

Reputation: 1169

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

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.

Upvotes: 0

Views: 1491

Answers (6)

Niketya
Niketya

Reputation: 171

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

Upvotes: 0

John Sansom
John Sansom

Reputation: 41819

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

Upvotes: 0

sameer karjatkar
sameer karjatkar

Reputation: 2057

you should use

select count(1) from

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

Upvotes: -1

Guffa
Guffa

Reputation: 700212

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

Upvotes: 6

czuk
czuk

Reputation: 6408

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.

Upvotes: 3

Arsen Mkrtchyan
Arsen Mkrtchyan

Reputation: 50712

use select top 1 and check is there is an row

Upvotes: 3

Related Questions