Performance for big query in SQL Server view

Question

I have a big query for a view that takes a couple of hours to run and I feel like it may be possible to work on its performance "a bit"..

The problem is that I am not sure of what I should do. The query SELECT 39 values, LEFT OUTER JOIN 25 tables and each table could have up to a couple of million rows.

Any tip is good. Is there any good way to attack this problem? I tried to look at the actual execution plan on a test with less data (took about 10 min to run) but it's crazy big. Is there any general things I could do to make this faster? Do I have to tackle one small part at the time..?

Maybe there is just one join that slows down everything? How do I detect it? So what I mean for short, how do I work on a query like this?

As a said, all feedback is good. Is there some more information I need to show, tell me!

The query looks something like this:

SELECT DISTINCT 
    A.something,
    A.somethingElse,
    B.something,
    C.somethingElse,
    ISNULL(C.somethingElseElse, '')
    C.somethingElseElseElse,
    CASE *** THEN D.something ELSE 0,
    E.something,
    ...
    U.something
FROM 
    TableA A 
JOIN 
    TableB B on ...
JOIN 
    TableC C on ...
JOIN 
    TableD D on ...
JOIN 
    TableE E on ...
JOIN 
    TableF F on ...
JOIN 
    TableG G on ...
...
JOIN 
    Table U on ...

Answer 1

Break your problem into manageable pieces. If the execution plan is too large for you to analyze, start with a smaller part of the query, check its execution plan and optimize it.

There is no general answer on how to optimize a query, since there is a whole bunch of possible reasons why a query can be slow. You have to check the execution plan.

Generally the most promising ways to improve performance are:

Indexing:

When you see a a Clustered Index Scan or - even worse (because then you don't have a clustered index) - a Table Scan in your query plan for a table that you join, you need an index for your JOIN predicate. This is especially true if you have tables with millions of entries, and you select only a small subset of those entries. Check also the index suggestions in the execution plan.

You see that the index works when your Clustered Index Scan turns into an Index Seek.

Index includes:

You probably are displaying columns from your joined tables that are different from the fields you use to join (otherwise, why would you need to join then?). SQL Server needs to get the fields that you need from the table, which you see in the execution plan as Key Lookup.

Since you are taking 39 values from 25 tables, there will be very few fields per table that you will need to get (mostly one or two). SQL Server needs to load entire pages of the respecitive table and get the values from them.

In this case, you should INCLUDE the column(s) you want to display in your index to avoid the key lookups. This comes at an increased index size, but considering you only include a few columns, that cost should be neglectable compared to the size of your tables.

Checking views that you join:

When you join VIEWs you should be aware that it basically means an extension to your query (which means also of the execution plan). Do the same performance optimizations for the view as you do for your main query. Also, check if you join tables in the view that you already join in the main query. These joins might be unnecessary.

Indexed views (maybe):

In general, you can add indexes to views you are joining to your query or create one or more indexed views for parts of your query. There are some caveats though:

1. Indexed views take storage space in your DB, because you store parts of the data multiple times.
2. There are a lot of restrictions to indexed views, most notably in your case that OUTER JOINs are forbidden. If you can transform at least some of your OUTER JOINs to INNER JOINs this might be an option.

When you join indexed views, don't forget to use WITH(NOEXPAND) in your join, otherwise they might be ignored.

Partitioned tables (maybe):

If you are running on the Enterprise Edition of SQL Server, you can partition your tables. That can be useful if the rows you join are always selected from a small subset of the available rows. You can make a partition for this subset and increase performance.

Summary:

Divide and conquer. Analyze your query bit by bit to optimize it. The most promising options are indexes and index includes. If you still have trouble, go from there.