| Author |
Topic |
|
mitchiboy
Starting Member
7 Posts |
 Posted - 2009-04-06 : 09:32:47
|
| Hi,We're maintaining an application sadly having to access frequently an Oracle database residing on another server. Of course, performance is horrible when having to join two tables of different providers on different machines. When possible, we fetch the minimum Oracle data necessary into temporary or variable tables. Still, we're looking for ways of improving performance (migrating all data to only one provider is not an option at the moment).The remote Oracle server is accessed through a linked server.Except syntactic differences, is there a better option between Exec At and Open Query from a performance point of view? Does anyone has references on the subject?Thank you! |
|
|
theboyholty
Posting Yak Master
226 Posts |
Posted - 2009-04-06 : 09:39:13
|
| We have a similar situation at our place and our solution is to do daily updates to a SQL version of the Oracle database. Each evening, we automatically run a series of imports which populate SQL tables. The main drawback of this system is that the imported data is not live but is in fact a snapshot of the data at close of play yesterday. This is something we have learnt to live with.I don't know too much detail about the imports but they're basically a load of SPs that fire at a specific time out of office hours, the DBA looks after the import routines and I make it my business to be completely oblivious as to the ins and out of the process.Might be worth looking at. |
 |
|
|
mitchiboy
Starting Member
7 Posts |
Posted - 2009-04-06 : 10:07:22
|
| Thank you theboyholty, we are already trying to convince the customer to accept to work with disconnected (cached) data, I'm glad to hear of somebody that it can work fine.However, some of our data have no choice but to be accessed realtime. That's why I'm still looking for ways to improve the performance of our remote accesses. |
|
|
|
mitchiboy
Starting Member
7 Posts |
Posted - 2009-04-07 : 11:55:42
|
| I know it is a very precise question, but I can't seem to find any information on the subject... Anybody knows something about this? |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-04-07 : 12:14:40
|
| Have you made sure the indexes are covered on both sides when you are doing a join? that the datatype on the join fields are the same etc. haev you looked at the Execution Plan. Before you do anything major make sure your code/design etc is not the bottle neck.THere are have been occasions I haev been bent out of shape with customers setup only to find out part of the problem was my own code and the other part was customers data design etc.<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
mitchiboy
Starting Member
7 Posts |
Posted - 2009-04-07 : 13:57:27
|
| Thanks yosiasz, but my question is really the following : Except syntactic differences, is there a better option between Exec At and Open Query from a performance point of view? Does anyone has references on the subject? |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2009-04-07 : 14:00:15
|
I don't know the answer to that, but why not just run statistics on both, and see?
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
mitchiboy
Starting Member
7 Posts |
Posted - 2009-04-07 : 14:07:20
|
| I may not understand your answer, but SQL Server tools are not able to analyze or propose an execution plan when dynamic SQL or a remote (and different provider) database is used... |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-04-07 : 14:07:46
|
| I do not know answer to that question either. mitchiboy, I agree with Vinnie881. Create some test scripts and see what happens, I would say also.<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
mitchiboy
Starting Member
7 Posts |
Posted - 2009-04-07 : 14:45:59
|
| I actually tried to do some test scripts a few weeks ago and ran them 10 times each, three times during the week. The problem is that... Depending on the request, the best situation differs, hence the need to know the difference between the two remote data access type. |
|
|
|
mitchiboy
Starting Member
7 Posts |
Posted - 2009-04-08 : 13:28:02
|
| Anyone got a hint about this? |
|
|
|
|
Open Query VS Exec at