Pulling data from Oracle tables to SQL Server
-
Hi
I am using DTS packages to run every 20mins and pull data from our main Oracle database. The data is used to update tables in SQL Server 2000, which are then independently connected to Access and Web front ends - giving us the additional functionality we need.
I have created a Link Server to the Oracle database from SQL Server which facilitates this data import. SQL Server Agent runs the import as a DTS Job.
All the processing appears to be done on the SQL Server. However, the software house which has developed the main front-end (which runs from the Oracle Server) has now launched a new Web version of some forms, which it feels is being slowed down significantly by the DTS imports. Up until this point everything has been working wonderfully for over 2 years
Is this possible? Could the Oracle server be very significantly affected by DTS imports from SQL Server, or, could it be that there is a problem with their Web forms?
Could somebody who knows please give me a pointer on this.
Many thanks
Paul
-
Unless your select statement is causing locks or running a mammoth sql statement, I don't see why it would slow down a web form.
All you are doing is a select statement just as you would if you had a client linked to the oracle db. Could be that the web forms are running on a box that is too low spec.
You need to get an oracle dba to investigate rather than the sql server equivalent. Watch the connection as it happens and see what oracle is doing. Whilst it is true that sql server is doing a lot of the work, there will be processing on the oracle server for sure.
------------------------------
The Users are always right - when I'm not wrong! -
Or maybe there won't be enough work done on Oracle... I've often seen that sql server will import the whole table(s) and do the work locally without indexes. while if the code was ran entirely on Oracle, it would take only a few ms because the proper plan would be used.
-
Thanks to both of you.
The SQL is selecting data from several tables on the Oracle database, according to date and several other criteria - and updating properly indexed tables on the SQL Server.
The tables are big on the Oracle server, and the DTS job takes about 6 minutes to run. Would you consider this to be a 'Mammoth' amount of work? Also, is this likely (and under what circumstances is this likely) to cause record locks?
If your answer to either of these questions is yes, can you suggest a way to overcome this. I would hate to lose what has been so successful over the last couple of years.
Thanks again
Paul
-
Define big (1k, 1M, 1B rows???)
Is the where condition sarg (look up bol for this one)?
How long does it take to simply select the data from Oracle without redoing any work to it on the sql server side?
-
I was pleasantly surprised when I just tried this. Peeling away everything but the selects from Oracle I got:
The First select statement took 35 seconds and returned 3457 rows.
The Second select statement took 47 seconds and returned 34553 rows.
Thanks
Paul
-
Which doesn't seem extremely slow. It's not fast but it's hard to judge without the rest of query and the table size...
Now can you look up the execution plan with the full query compared with this one?
-
Hi Remi
When I try to run an Execution Plan it returns:
Server: Msg 208, Level 16, State 1, Line 4
Invalid object name '#CMA'
Can't you do this when the SQL produces temporary tables? Also, what are you expecting to find?
Thanks for taking the time and trouble to reply.
Paul
P.S. I may be late in getting back to you - I've just been called out to a problem.
-
Don't run the plan, just tell me what you see. What part of the plan takes the most time (in %) of the query. If you have something like 99% remote query and 300K (mouse over the arrow for the row count) rows instead of 3k, you know that the server is importing too much data, if not (get 3k and 30K), then you know that the query is fast on Oracle and slowing down on Sql server... We'll need to see where we have to work before going forward.
-
One Select shows the Remote Query is 26% and the row count is 10k. The other Select shows Remote Query as 0%.
What do you think? Is 26% excesive? If so, what should I do?
Thanks Remi
Paul
Apologies if I am delayed in getting back to you.
-
I'm gonna be delaying a lot after this one
.Anyways, I think 26% is not a lot. Thge fact is that you still fetch about 7k records too much. I think that if you shorten that out it'll help a lot.
Talk to you on monday.
-
I'll do my best to get this down to around 3k?
Many thanks for your time and trouble with this - it is much appreciated.
Paul
-
Hi again
I've been playing around with the SQL and really just substituted a series of chosen dates for a BETWEEN two dates. It still gives me what I want.
Originally (I have just noticed) the 26% Remote server featured twice in the Estimated Exectution Plan (I can't pretend to understand what the EEP is telling me) both returning 10k rows
I now have the following:
The Estimated Exectution Plan returns 7% twice for the Remote Server but still returns 10k. However the Compute Scalar cost is up to 79% from 3%. Everything else is either negligable or 0. It aso exectues a little more quickly.
Do you think I can now say that I am not poaching too much processing power from the remote server?
Paul
-
Just make sure you get the minimum rowcount from the remote server. This will help a lot because it'll be less work for sql server.
Also test a few versions to see which one is faster overhall. There's no clear cut answer in this situation.
-
Hi Remi
Will do. I'm not so bothered about the SQL Server having to work - I've got lots of spare capacity, its just that I want to ensure that the Oracle server (where it is pulling the data from)is doing as little as possible - and that is the major issue I need to address.
If I get the rowcount down, will this ensure that the Oracle server is relatively untroubled?
Paul
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply