Performance of the insert query increased by creating a table instead of using a complex view.
insert into tableA
left join TableD
when we created a table TableB with the content in ViewB and executed the sql , time taken to insert reduced to 1/4 of the previous.
Is it because the data is stored in the amp when we created the table and using spool when we used the complex view?
Do you mean 1/4 of the runtime for the whole insert/select or 1/4 of the time for the actual merge step? TableB might be a SET table (thus already without duplicate rows) and when using the view the file system might do duplicate row checks during the insert.
Did you compare QueryLog data?
Yes Dieter, you are correct. TableB is a SET table.
My previous insert query with view took 2 hours, now the insert query is executed in 30 min.
Applied collect stats also on TableB.
view do not contain any duplicates. It has 8,914,400 records which are unique.
You probably don't need stats for tableB.
- how long does it take to create tableB?
- is tableA empty?
- Did you check QueryLog? How long does the insert step take, is it actually different between both versions?