Solved

How does sybase proxy table work?

Posted on 2006-07-19
5
9,244 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
  • 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
In this step by step tutorial with screenshots, we will show you HOW TO: Enable SSH Remote Access on a VMware vSphere Hypervisor 6.5 (ESXi 6.5). This is important if you need to enable SSH remote access for additional troubleshooting of the ESXi hos…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now