Query performance help

Transact-SQL
1

Hi,
I need help in reducing the execution time of the query.

First query - it join 6 tables and there select of few specified columns, when executed separately it is taking 10 mins - return 118K records

second query - join of three tables and return 5 columns, when executed separately it is taking 19 secs - 7568 records

but when left outer join first and second query it is taking one hour 15 mins. there also select column and not select *. There is group by on final dataset for sum(Qty) column

I checked the execution plan, it shows 49% in second query table for clustered index and 26% in first query in one of the join. For that I have added filter and it reduced to 5 % in first query.

Apart from that all steps are showing 0% or < 3%. Any suggestion will be great.

2

seems you don't have the correct indexes, but hard to say something without knowing the data structure or query syntax

1 Like
3

hi

just to give you an idea

do it in small steps at a time

put into temp tables
create indexes on the temp tables

select * into #temp1 from table1 join table2
select * into #temp2 from #temp1 join table3 join table4

4

What column(s) are being joined on?

What type of join is SQL doing? If it's doing a loop join, you may be better of forcing a hash join.

But without more details, I/we can't say for sure.

5

thanks all for your time, I went with @harishgg1 suggestion of #temp tables and waiting for user feedback.