Skip to Content
0
Former Member
Apr 25, 2017 at 06:54 AM

Calculation view performance

304 Views Last edit Apr 25, 2017 at 06:54 AM 2 rev

Hello,

I'm trying to compare performance of calculation view to regular SQL:

SELECTtop 200 lpad("STA_ID",30,'0') ,lpad(to_nvarchar("INSERT_TIME",'YYYYMMDDHH24MISS'),15,'0')

FROM"SSIS"."OFFK470"

ORDERBY"STA_ID"ASC

successfully executed in 761 ms 977 µs(server processing time: 720 ms 483 µs)

Fetched 200 row(s) in 35 ms 614 µs (server processing time: 0 ms 289 µs)

To:

SELECT top 200"INSRT_TIME_CALC", "STA_ID_CALC_copy_1"

FROM"_SYS_BIC"."TEST/COPY_2_OF_STA_OMRI_O"

ORDERBY"STA_ID_CALC_copy_1"ASC

successfully executed in 15.943 seconds(server processing time: 15.910 seconds)

Fetched 200 row(s) in 41 ms 583 µs (server processing time: 0 ms 370 µs)

The CV "TEST/COPY_2_OF_STA_OMRI_O" contains 2 calculated columns with exactly the same logic as in the SQL.

When comparing the visio plan I clearly see that it materialize result

Can someone suggest how to improve the CV performance?

Thanks,

Amir

Attachments

lbgq8.png (96.9 kB)
xomax.png (121.2 kB)