计数(*)需要长时间执行 [英] Count(*) taking long time execution
问题描述
我有一个sql server 2012.我有一个表员工包含2千万条记录的记录。我有一个查询,
I have a sql server 2012. I have table employee contains records of 2 crore records. I have a query as,
SELECT empname,place,Count(*)
SELECT empname,place,Count(*)
FROM dbo。员工
FROM dbo. employee
其中Isactive = 1
Where Isactive=1
按empname分组,放置。
Group by empname,place.
如果我注释掉了该行of codecount(*),然后它将在几分之一秒内执行得非常快。但是如果我保持COUNT(*),则需要2分钟才能完成执行。
If i commented out the line of codecount(*) , then it will execute very fast with in fraction of seconds.But if i am keeping COUNT(*), then it will take 2 minutes to complete for execution .
在查询庞大的表记录时COUNT(*)的替代方法是什么?
What is the alternative for COUNT(*) in query of a huge table records?
谢谢&问候,
Dileep
推荐答案
好吧,你可以尝试:
Well, you can try:
;使用cte as(选择EmpName,Place,row_number()结束(按EmpName分区,按EmpId排序)作为Rn
;with cte as (select EmpName, Place, row_number() over (partition by EmpName, Place order by EmpId) as Rn
来自dbo.Employee,其中IsActive = 1 )
from dbo.Employee where IsActive = 1)
选择EmpName,Place,max(Rn)作为来自cte group by EmpName的EmployeesCount,Place
select EmpName, Place, max(Rn) as EmployeesCount from cte group by EmpName, Place
--------- ------
---------------
但我怀疑表现会一样。
这篇关于计数(*)需要长时间执行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!