Optimizing unions

Question

HI, im having trouble trying to optimize the following query for sql server 2005. Does anyone know how could i improve it. Each one of the tables used there have about 40 million rows each. I've tried my best trying to optimize it but i manage to do the exact opposite.

Thanks

SELECT
        cos
      , SIN
    FROM
        ConSisHis2005
    union all
    SELECT
        cos
      , SIN
    FROM
        ConSisHis2006
    union all
    SELECT
        cos
      , SIN
    FROM
        ConSisHis2007
    UNION ALL
    SELECT
        cos
      , SIN
    FROM
        ConSisHis2008

Maybe i should have said something else about the schema, all the tables used here are historical tables, they are not referenced to any other table. And theres already an index for cos and SIN. I was just wondering if there was any other way to optimize the query... as you can imagine 160millon records are hard to get :s

Answer 1

Put a composite index on cos and sin on each of the tables. That's as good as you're going to get without restructuring the table design (in this example, it looks like you should have just 1 table to begin with)

Answer 2

It seems that the query is just combining the separated history tables into a single result set containing all the data. In that case the query is already optimal.

Answer 3

Since there is no WHERE clause, I don't believe there's anything you can do to improve the performance from this PoV.

You've correctly used UNION ALL so there's no help there.

The only other thing I can think of is whether there are more columns on the tables? If so, you might be fetching more data from disk than you need, thus slowing the query down.

Answer 4

Another approach would be to tackle the problem of why do you need to have all the 160 million rows? If you are doing some kind of reporting can you create separate reporting tables that already have some of the data aggregated. Or do you actually need a data warehouse to support your reporting needs.

Answer 5

It might be worth experimenting with indexed views. You could put the above statement into a view with the indexes Dave suggested. This would take a little time to build initially but would return your results a little quicker (this is on the assumption that the data set does not change much and therefore you can live with the extra transactional overhead).

Answer 6

do you have/need duplicate entry?

maybe you could filter that to get less rows?

Answer 7

You might consider using a single partitioned table with a year indicator.

I'm still curious - is this code in a view or SP which operates on 160m rows or is it actually going to return 160m rows down the wire. If so, that's an awful lot of data to return that's effectively an extract and it's going to take a while just to come down the wire.

Answer 8

There's no optimization to be done. Since you're selecting all the records from all the tables, by definition you get all the records from all the tables in one result set.

What's the reason for doing this?