Finding the right pattern for creating indexes,redesign query structure

Question

I'm planning to redesign the structure of a query which uses over 20 full/left joins for fetching different data from the tables.

Because the logic of the initial query was to bring data based on some conditions, not all the joins were needed if a certain condition is satisfied.

For example : The query has 5 different conditions, each condition satisfies a certain scenario where only some table joins are required.

INITIAL QUERY EXAMPLE :

Select
  -- fields
From table1 t1
Join table 2 t2 ON t1.id = t2.id
... 
Join table n tn+1 ON .....
Where  
(t1.field1 = 1 and t1.field2 = 0) AND (
(t2.type = 1 and somefield = value and somefield2 = value ... somefield n+1 = value )
OR (t2.type = 2 and (somefield3 = value or somefield4=value or .... somefield n+1 = value) )
.......
OR (t2.type = n and (somefield n...n+1 = value)))

Note: The fields on which the conditions are written are never changed, the conditions are exactly as those written in the example above.

My thoughts are to redesign the structure of the query by splitting it into multiple queries joined together with UNION ALL. By doing this, I would get rid of the entire amount of joins for each condition.

I'm not sure if this could bring any real benefits mentioning the fact that in some cases when a certain condition is being satisfied , some of the table joins are on null values because the type of the conditions makes the query to bring a certain data which doesn't have the data the join needs.

The real questions would be :

1. Splitting the query into UNIONS would be better/faster design ?
2. Which is the pattern in the query for making an INDEX? If so, how should I create the index, which fields from the given example?

Answer 1

1. Without knowing anything about the data, if we assume your JOIN's are on related data, a UNION isn't what you want. The related data will be presented disjointed on separate result rows.

2. There are no hard-and-fast rules on index design; the right design is the one that increases performance on your queries without consuming excessive resources. As a rule of thumb, however, you want an index that contains your JOIN, WHERE, and ORDER BY columns. INCLUDE the columns you are SELECTing. But before you create the index, be sure that another index can't do the trick with an added column or two.

Start there, and see how performant the query is. Use SET STATISTICS TIME ON and SET STATISTICS IO ON and run with "Include Actual Execution Plan" enabled while you tinker. I actually like to do this before I create any indexes so I have a baseline to compare my "improved" query with.

Also, be sure that 'optional' tables are LEFT JOIN.