Complex view to table

General
Enthusiast

Complex view to table

Hi,

Performance of the insert query increased by creating a table instead of using a complex view.

ex:

insert into tableA

from viewB

join tableC

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?

3 REPLIES
Junior Contributor

Re: Complex view to table

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?

Dieter

Enthusiast

Re: Complex view to table

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.

Thanks,

Karthik. N

Junior Contributor

Re: Complex view to table

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?

Dieter