CODEWITHSUNDEEP
sqlperformancesql-server-2008
Bonzay
Bonzay

Reputation: 750

Improve query performance in 300 million rows table

I need to improve the performance of a query which takes way too long. The query is used in an SSIS data flow in the source task. It is part of a longer query but this is what causes the problem so I narrowed it down as follows:

select xa.*, s.idA 
from tableA s 
inner join tableB xa on s.idA = xa.idB
where xa.type = 'type_A'

TableA has a non clustered index on idA and TableB a non clustered index on idB. The execution plan uses the index of TableA and performs a full table scan on TableB which takes 98% of the effort. I have tried to create a non clustered index on TableB, on column idB including column Type. I had to kill the create query after an hour because it was suspended with a CXPACKET wait type. Probably it was waiting to read all the data but my SSIS flows can't afford to waste resources for so much time. I then tried to create a clustered index on tableB column type, which I thought would take less time. The plan was to modify the query using a cte like this:

;with tmp as 
(
     select * 
     from tableB 
     where type = 'Type_A'
)
select xa.*, s.idA 
from tableA s 
inner join tmp xa on s.idA = xa.idB

Using this query i will have only 200k rows in the tmp table and i will avoid full table scan to filter tableB utilizing all the available indexes so i think it will reasonably improve the speed

However, i had to kill the create query as it was taking more than 1 hour (suspended again, CXPACKET again). So my question is: Is it possible to speed up the index creation process? Is it possible to estimate the time needed for the index creation?

Upvotes: 0

Views: 1129

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Moving criteria to a subquery (CTE) is not the solution. You are supposed to use SQL to tell the DBMS what to get, not how to get it. It's the DBMS's task to find the optimal plan. So write the query as readable as possible and help the DBMS by providing appropriate indexes.

You should have the following indexes for the query:

create index idx1 on tableB ( type, idB ); -- to find B quickly and have the ID ready for the join
create index idx2 on tableA ( idA );

(It looks strange that you join on idA = idB, but I guess that's just in the example, right?)

Upvotes: 2

Related Questions