Sql-server – Finding the right pattern for creating indexes,redesign query structure

optimizationperformancequery-performancesql server

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 :

Splitting the query into UNIONS would be better/faster design ?
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?

Best Answer

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.
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.

Related Solutions

Sql-server – Logical reads amount difference with almost identical indexes

It all depends on the definitions and the key (and non-key) columns defined in the nonclustered index. The clustered index is the actual table data. Therefore it contains all of the data in the data pages, whereas the nonclustered index is only containing columns' data as defined in the index creation DDL.

Let's set up a test scenario:

use testdb;
go

if exists (select 1 from sys.tables where name = 'TestTable')
begin
    drop table TestTable;
end
create table dbo.TestTable
(
    id int identity(1, 1) not null
        constraint PK_TestTable_Id primary key clustered,
    some_int int not null,
    some_string char(128) not null,
    some_bigint bigint not null
);
go

create unique index IX_TestTable_SomeInt
on dbo.TestTable(some_int);
go

declare @i int;
set @i = 0;

while @i < 1000
begin
    insert into dbo.TestTable(some_int, some_string, some_bigint)
    values(@i, 'hello', @i * 1000);

    set @i = @i + 1;
end

So we've got a table loaded with 1000 rows, and a clustered index (PK_TestTable_Id) and a nonclustered index (IX_TestTable_SomeInt). As you've seen in your testing, but just for thoroughness:

set statistics io on;
set statistics time on;

select some_int
from dbo.TestTable -- with(index(PK_TestTable_Id));

set statistics io off;
set statistics time off;
-- nonclustered index scan (IX_TestTable_SomeInt)
-- logical reads: 4

Here we have a nonclustered index scan on the IX_TestTable_SomeInt index. We have 4 logical reads for this operation. Now let's force the clustered index to be used.

set statistics io on;
set statistics time on;

select some_int
from dbo.TestTable with(index(PK_TestTable_Id));

set statistics io off;
set statistics time off;
-- clustered index scan (PK_TestTable_Id)
-- logical reads: 22

Here with the clustered index scan we have 22 logical reads. Why? Here's why. It all matters on how many pages that SQL Server has to read in order to grab the entire result set. Get the average row count per page:

select
    object_name(i.object_id) as object_name,
    i.name as index_name,
    i.type_desc,
    ips.page_count,
    ips.record_count,
    ips.record_count / ips.page_count as avg_rows_per_page
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TestTable'), null, null, 'detailed') ips
inner join sys.indexes i
on ips.object_id = i.object_id
and ips.index_id = i.index_id
where ips.index_level = 0;

Take a look at my result set of the above query:

enter image description here

As we can see here, there are an average of 50 rows per page on the leaf pages for the clustered index, and an average of 500 rows per page on the leaf pages for the nonclustered index. Therefore, in order to satisfy the query more pages need to be read from the clustered index.

Mysql – Querying MySQL, duration increases 10x fold after adding one more Join

You have forgotten the join criteria and ist.id = i_team_2.status_id.

If you had used the modern JOIN syntax this would have stood out like a sore thumb.

Best Answer

Related Solutions

Sql-server – Logical reads amount difference with almost identical indexes

Mysql – Querying MySQL, duration increases 10x fold after adding one more Join

Related Question