CODEWITHSUNDEEP
sqlsql-server
user2621858
user2621858

Reputation: 121

Calculating Total count from different SELECTs

I have following code in my Stored Procedure written in SQL Server 2008:

    select sum(Sum1)
    from
    (
        select count(1) as Sum1 from Table1
        UNION ALL 
        select count(1) as Sum1 from Table2
        UNION ALL 
        select count(1) as Sum1 from Table3
        UNION ALL 
        select count(1) as Sum1 from Table4

    ) as SumCount

There are more SELECT statements joined by UNION ALL. How I can rewrite the query to optimize performance? Does any other operator instead of UNION ALL will help to improve performance?

Upvotes: 1

Views: 114

Answers (2)

Bohemian
Bohemian

Reputation: 424983

Your query is how I would do it, because it allows the optimizer to use data in the catalog to produce the answer, rather than table/index I/O.

There is another way to do this that I have never tried, but it might be worth a shot:

select count(*) from
(
    select 1 a from Table1
    UNION ALL 
    select 1 from Table2
    UNION ALL 
    select 1 from Table3
    UNION ALL 
    select 1 from Table4
) as SumCount

Let me know how it compares with your query.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

This is about as fast as it gets. You want to count all rows from all tables and this is what you are doing. (To use COUNT(*) would be more clear, though, as you want to count rows. Using COUNT(1) means count all rows for which a generated 1 is not null, but of course the dbms optimizes this to COUNT(*) internally.)

You can also try:

select
  (select count(*) from Table1) +
  (select count(*) from Table2) +
  (select count(*) from Table3) +
  (select count(*) from Table4);

which is even more readable I think. I suppose that the dbms will use the same execution plan though. Well, you can still try.

Upvotes: 1

Related Questions