Clever Geek Handbook

Optimizing alliances

I am having trouble optimizing the following query for SQL Server 2005. Does anyone know how I can improve it. Each of the tables used there has about 40 million rows. I tried my best trying to optimize it, but I managed to do exactly the opposite.

thank

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

Perhaps I should have said something else about the schema, all tables used here are historical tables, which they do not reference any other table. And theres already an index for cos and SIN. I'm just wondering if there is any other way to optimize the query ... as you can imagine 160 million records are hard to get: s

+1


source to share


7 replies


It seems that the query is simply concatenating the split history tables into a single result set containing all the data. In this case, the query is already optimal.



+2


source


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



+2


source


Place a composite index on cos and sin on each of the tables. This is as good as you are going to get without restructuring the table design (in this example, it looks like you only need one table to start with)

+1


source


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

You used UNION ALL correctly so that there is no help there.

The only thing I can think of is are there more columns in the tables? If so, you may be getting more data from disk than you need, thus slowing down the request.

+1


source


It might be worth experimenting with indexed views. You could bring the above statement into perspective with the metrics Dave suggested. It will take a little time to build initially, but will return your results a little faster (this is done assuming the dataset doesn't change much and therefore you can live with the additional transaction overhead).

+1


source


You can use one partitioned table with a year indicator.

I'm still wondering is this code in a view or SP that runs on 160m lines or is actually going to return 160m lines over the wire. If so, then there is a lot of data to return this extract efficiently, and it will take a while to just go down the wires.

0


source


No optimization. Since you are fetching all records from all tables, by definition, you get all records from all tables in one result set.

What is the reason for this?

0


source






More articles:


All Articles