Solved

How does sybase proxy table work?

Posted on 2006-07-19
5
9,679 Views
Last Modified: 2012-06-22
We are building a system which needs the information from two Sybase databases.

The DBA proposed the proxy table solution so that we only need to access one database and the tables from the other database will be treated the same as the local ones.

I have read some documents about Sybase proxy table and understand that only metadata and some statistics of the remote database tables are stored in the local database, not the data.

But I am not clear how the followings are supported through proxy table and what would be the caveats of using the proxy table mechanism:
1. Can we query dthe proxy table with joins between proxy tables and local tables? What would be the performance impacts?
2. Does any data block I/O go to the remote side? Is anything cached locally? Is so, how is local cache synchronized with the remote storage and cache?
3. How does locking work for the proxy tables? Does it require a distributed locking mechanism?

Thanks
0
Comment
Question by:nbkd72e
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 10

Expert Comment

by:bret
ID: 17143933
1)  Yes, you can.  Performance is generally slower than joining two local tables.

2)  When you issue a query involving proxy tables, the local ASE opens a connection to the remote ASE and passes the part of your query involving that table (desired columns, applicable WHERE clauses) to the remote server, where the command is run and the results sent back to the local ASE, which places them in a work table and joins that with any local tables.  Pages belonging to the local work table may enter the local server's data cache.  Any updates to a proxy table - the update command is sent to the remote server and the table updated there.  There is no need to synchronize the caches on the two servers - proxy data is not modified locally.

3) The local ASE's connection to the remote server works much like any other connection to that server.   The remote server handles the locking on it's tables.
0
 

Author Comment

by:nbkd72e
ID: 17146791
Bret:

Thanks a lot for your answers, which make many things clearer.

In 2), you mentioned:
     "Pages belonging to the local work table may enter the local server's data cache"

Does this mean that remote data may be cached locally? Will the cached data be directly used for future proxy table queries? Is so, how do we know that it has not been updated by the remote side?

Thanks


0
 
LVL 19

Accepted Solution

by:
grant300 earned 250 total points
ID: 17157437
No.  There is no sharing of caches between servers at all.  A query is sent to the remote server and the results returned into tempdb.  Think of it as a remote Select Into kind of thing.  Once it is in the worktable in tempdb, it may or may not be in the cache but you would have the same issue if it was a local table.

Proxy tables have a couple of other charactoristics you might find useful.
1)  A proxy table can point to a view on the remote server; it need not come from a single table
2)  A proxy table can point to a stored procedure on the remote server.
That is a very powerful capability because you can do a bunch of processing on the remote server to boil things doesn prior to sending it over the net to the local server.  The documentation shows you how to formulate the query so that elements of the WHERE clause are passed as arguments to the remote stored procedure.  In the extreme case where a particular query is based on lots of data on the remote server and a small amount on the local server but you need to be connected to the local server, you can setup a reverse proxy to make the local server data available on the remote side and access the remote through another proxy.  If you are doing that more than once, you have connected your application to the wrong server but it can get you out of a performance jam when you have one query that goes against the predominant grain.

Finally, I don't know what alternatives you had in mind to the Proxy table solution your DBA proposed but I can tell you that it will be faster, cleaner, and easier to maintain than any application solution that connects to both databases.

Good luck,
Bill
0
 
LVL 10

Expert Comment

by:bret
ID: 17157495
>Does this mean that remote data may be cached locally? Will the cached data be directly used for future proxy table queries? Is so, how do we know that it has not been updated by the remote side?

In a sense, yes, it is.  However, it is only used for the context of that one query, then discarded.  Future queries will again go to the source for their data.

-bret
0
 
LVL 19

Expert Comment

by:grant300
ID: 17158280
Bret,

Sorry.  I thought the author would have split the points.

Bill
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why do some people recommend buying business VoIP from an ISP? What are the benefits to my company? What are the costs?
First of all let me say that the only language that I speak is English, but in answering questions here I often come across people whose English skills are not the best and I’d like to be able to communicate better with them, and the following descr…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question