Skip to Content
0
Former Member
Jun 19, 2014 at 06:51 PM

Question about Proxy table

46 Views

1. Want to know if there is any different with following syntax to create proxy table for ase 12.5:

CREATE TABLE dbo.my_proxy_table

(

id int not null,

....

)

LOCK DATAPAGES

external table at 'loopback.mydb..myproc'

go

create existing table my_proxy_table (

id int not null,

....

) on 'default'

external procedure

at 'loopback.mydb..myproc'

go

2. How to change segment or data cache for a proxy table, or no segment or data cache for proxy table?

3. I have performance problem with a query to use this proxy table like

SELECT * FROM tab1, tab2, my_proxy_table

WHERE tab1.id = tab2.id and tab2.id2 *= my_proxy_table.id2

....

but If I run myproc, there is no performance problem. check with sp_lock, find out there is remote I/O. but this remote is actually local server(identify with loopback). Not sure how to identify the problem

If I want to use the result set from a sp like myproc as a table, except using proxy table(view is not available for this case, as there are more than one sql statement in sp), any other solution for this case?