检查除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.

我想检查表中是否有任何记录用于某个条目。我使用COUNT(*)来检查记录的数量并使其工作。但是,当条目的记录数非常高时,我的页面加载速度很慢。

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.

我猜COUNT(*)导致问题,但如何在不使用它的情况下检查记录是否存在?我只想检查条目是否存在任何记录,然后执行一些代码。请帮我找到替代解决方案。

Thanks for any help.

谢谢你的帮助。

6 个解决方案

#1

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

有几种方法可行。您可以使用exists,它允许数据库优化方法以获得答案:

if exists(select * from ...)

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

您可以使用top 1,以便在找到第一个匹配项后数据库可以停止:

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

#2

use select top 1 and check is there is an row

使用select top 1并检查是否有一行

#3

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.

其他可行解决方案你怎么用伯爵?计数(*)?如果是,则尝试使用COUNT(id)。我记得这应该更快。

#4

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

我建议测试以查看表中是否存在满足您标准的至少1条记录,然后相应地继续。例如:

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

#5

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

我在codeproject上发现了这个。这很方便。

-- 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

#6

you should use

你应该使用

select count(1) from

从中选择count(1)

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

如果你说(*)它会扩展所有列,然后计数

#1