Remote query - database from another server

My objective is to verify that the following setup is robust and reliable: I have 2 unix servers, both running IDS 7.31. Both servers are configured to "trust" each other. From server A, I have a code that queries a database-table from server B using an index key, in the form of a prepared statement: select column from database$serverB:table where column=? 1) Is this the best possible setup to get information from server B, other than outside the database, ie, to unload data and ftp into server A. 2) What tuning can be done so that the query is fast? Thx, MC

The query will be as fast as it would be connected directly to the remote server. If you are fetching large quantities of data from a remote server it is sometimes faster to maintain a separate connection to the two servers and switch back and forth between them (using the WITH CONCURRENT TRANSACTIONS on both connections so transactions are not rolled back when you switch connections) or using separate threads for each connection. This is because you eliminate the overhead of speaking to the remote server through the local server so you cut out one level of communications overhead. But for small queries returning few rows it probably doesn't matter. Your syntax is a bit off: select column from database@serverB:table where column=? No special tuning neccessary. IFF you have to join between local and remote tables in the same query, then you need to be careful that the local optimizer doesn't decide to ask the remote for lots of rows and filter locally. Art S. Kagel ----- Original Message ----- From: Margarita Cruz <ids@iiug.org> To: ids@iiug.org At: 10/10 17:17:03 My objective is to verify that the following setup is robust and reliable: I have 2 unix servers, both running IDS 7.31. Both servers are configured to "trust" each other. From server A, I have a code that queries a database-table from server B using an index key, in the form of a prepared statement: select column from database$serverB:table where column=? 1) Is this the best possible setup to get information from server B, other than outside the database, ie, to unload data and ftp into server A. 2) What tuning can be done so that the query is fast? Thx, MC ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

That should work fine. You might consider setting up a synonym to the remote table, just so that your code doesn't have to change downstream. As to tuning... select as little data as possible. The query is shipped to the remote server to execute and the results are piped back to be used in your local query, so the less data you can ship back, the faster it will run. If you find that performance is an issue and changes to the remote table are low, you might consider creating a local copy of he table and using triggers on the remote table to ship inserts/updates and deletes over. For that matter you could use ER to perform the same task. But if your query is 'light' then your original approach should be fine. j. >From: MARGARITA CRUZ <margarita.cruz@dhl.com> >Date: 2007/10/10 Wed PM 04:16:30 CDT >To: ids@iiug.org >Subject: Remote query - database from another server [10105] >My objective is to verify that the following setup is robust and reliable: > >I have 2 unix servers, both running IDS 7.31. Both servers are configured to >"trust" each other. From server A, I have a code that queries a database-table >from server B using an index key, in the form of a prepared statement: > >select column from database$serverB:table where column=?> >1) Is this the best possible setup to get information from server B, other >than outside the database, ie, to unload data and ftp into server A. >2) What tuning can be done so that the query is fast? > >Thx, >MC > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.

The speed of the code with the remote query for a defined data set is 9 minutes, compared to a local query on the same data set which took 1 minute. The code used a prepared statement for the remote query and the query searched by an index key. Any suggestions to speed this up?

Start with calculating the theoretical throughput of the remote request. How much data was returned and how fast was the connection? That will tell you how fast you COULD make the remote query in an ideal world. Only then can you know if your efforts to improve it will result in any actual improvement. Also, post versions and platform information on both the server and the client(s). Are we talking about local versus remote against the same server or an 'identical' server with the same data? Post the query. Run remote and local using SET EXPLAIN ON and compare the query plans. Are you using the same front-end tool for the local and remote executions or different? Art S. Kagel ----- Original Message ----- From: Margarita Cruz <ids@iiug.org> To: ids@iiug.org At: 10/12 11:41:55 The speed of the code with the remote query for a defined data set is 9 minutes, compared to a local query on the same data set which took 1 minute. The code used a prepared statement for the remote query and the query searched by an index key. Any suggestions to speed this up? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.