-1

In my program I have to run thousands of COUNT operations each with different conditions.

E.g. 

SELECT COUNT(*) FROM Contacts WHERE X='value1' AND Y='value2';
SELECT COUNT(*) FROM Contacts WHERE Z='value3' AND S='value4' AND H='value5';

The number of these operations equals the number of records in my Contacts table. So as the number of contacts increase I am going to have to run more COUNTs.

As one might expect, the queries are going to take a long time to complete. So I am looking for a way to make them run faster. I read the answer to this question in which the user recommends running 

SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st

Instead of normal COUNT. I am wondering if this is really a time-saver?

The problem I am having with comparing the two operations is that for a specific COUNT, the execution time becomes less with second and third runs. Apparently SQL server caches some query results. So I am unable to see which one is truly faster. I am putting my queries between 

set statistics time on
--query
set statistics time off

to see the exact run time. Is there a way to compare the two or is there a way in general to make the COUNTs faster?

Community
  • 1
  • 1
disasterkid
  • 6,948
  • 25
  • 94
  • 179
  • How many conditions do you use? How often data in Contacts changes? – Viktor Bardakov Aug 18 '15 at 10:06
  • 1
    How much data is there? Can your queries make use of indexes? If not, there is a better solution that only scans the data once. – usr Aug 18 '15 at 10:08
  • To compare two sql performance, you should enable "Include Actual Execution Plan" in Management Studio, the place 2 sqls in the editer and run. You can compare them by the percentage. Although it does not reflect to the speed directly, smaller percentage usually can think as "faster"... – daniel Aug 18 '15 at 10:56
  • 1
    Use DBCC FREEPROCCACHE to clear the cache for performance checks. – Scoregraphic Aug 18 '15 at 11:00
  • Why are you comparing those two? The second does not have a where and you cannot add a where WHERE X='value1'. What are these counts that you are adding? – paparazzo Aug 18 '15 at 13:27

1 Answers1

-1

You can just select from the table once, but for each individual condition perform a conditional count as the COUNT function will ignore a null.

SELECT CountAll = COUNT(*) 
, CountCond1 = Count(case when x = 'value1' and y='value2' then 1 else null end)
, CountCond2 = Count(case when z = 'value3' and S='value4'  and H='value5' then 1 else null end)
FROM Contacts ;
Jeremy
  • 4,808
  • 2
  • 21
  • 24
  • I guess you can remove the where clause. – daniel Aug 18 '15 at 11:00
  • @Jeremy This looks very promising. Let me implement it on my program and come back to you. And btw, this `SELECT` statement is supposed to take much shorter time to execute, right? – disasterkid Aug 18 '15 at 11:19
  • it should be quicker to perform one select from the table, rather than multiple selects as you are hitting the table only once. You'll need to test this out to make sure it will work for you. – Jeremy Aug 18 '15 at 11:43
  • Did you test this? This is likely not faster. If those columns columns are indexed this will most likely not use those indexes. – paparazzo Aug 18 '15 at 13:18
  • how could I possibly test when I know nothing of the OPs infrastructure/table structure? It is merely a suggestion, and I told the OP to test it out. In my past experience, though, touching a table IO-wise once is generally better than hitting it over and over again, because IO can be very expensive. – Jeremy Aug 18 '15 at 13:25
  • You could test it out with any counts. I did and putting more than one count in the select caused it to not use the index. In a simple test of 3 indexed columns 3 count(*) was twenty times faster. – paparazzo Aug 18 '15 at 13:45