Pulling data from Oracle tables to SQL Server
-
Depends how you filter the set. I don't know how Oracle optimization works. You'll have to do your tests yourself to answer that one.
-
One easy way of getting as few rows as possible could be to retrieve just the rows you absolutely need into a temporary table. Thus you know no more work is allocated to Oracle then the SELECT of the rows...
-
Remi, thanks for all your help - I really appreciate the time you've put in.
Hans, are you saying the Select is undertaken by the Oracle server rather that the SQL server which is sending it? I guess this shows how ignorant I am about this......
If I create a select which just takes updated records between dates, it will give me less rows, but which server will do the work?
Paul
-
Can you post the query you are using??? It's gonna clear up a lot of misconceptions about your work
. -
Hi Remi
I've made some changes to protect the innocent, but the flavour remains the same. #STY1 is a temp table which is used on the SQL Server to update a variety of tables. LinkServerName is the Oracle server.
Which server is doing most of the work here?
Thanks
Pau
select p_link.STUDENTID,p_link.ForeName, p_link.Surname, p_link.Title,
p_link.Sex,p_link.Date_Of_Birth as DOB, p_link.Nationality,p_link.Ethnicity,Ex_Mailshot,
r_link.FS_per_STUDENTID,
R_link.Rtable_code,RU_link.reg_Rtable_code,RU_link.FS_PC,
RU_link.FS_unit_instance_code as Course_Code, UIO_link.offering_organisation,
RU_link.UO_Code AS CourseYear, UI_Link.FS_long_description as Course_Title,
RU_link.RUL_Code,RU_link.UPDATED_DATE as WD_Date,
RU_link.FS_PROGRESS_DATE as LA_Date,
RU_link.FS_USER_7 as EE_TutorGroup
into #STY1
from LinkServerName.STUDENT
as P_link
inner join LinkServerName.RtableS as R_link
on p_link.STUDENTID=r_link.FS_per_STUDENTID
inner join LinkServerName.Rtable_UNITS as RU_link
on R_link.Rtable_Code=RU_link.Reg_Rtable_Code
inner join LinkServerName.UIO as UIO_link
on RU_link.UIO_ID=UIO_link.UIO_ID
inner join LinkServerName.UNIT_ISTS as UI_link
on RU_link.FS_unit_instance_code=UI_Link.unit_unit_code
where ((RU_link.UO_CODE=(select currentacademicyear collate Latin1_general_CI_AS from tblacademicyear)
or RU_link.UO_CODE=(select nextacademicyear collate Latin1_general_CI_AS from tblacademicyear)
or RU_link.UO_CODE=(select followingacademicyear collate Latin1_general_CI_AS from tblacademicyear)
or RU_link.UO_CODE=(select previousacademicyear collate Latin1_general_CI_AS from tblacademicyear)))
-
Still can't answer that one. The other option you have is to split the select completely like this :
Create a stored proc on Oracle with the PrevYear, nextyear... parameters. Then execute this storedproc as a remote query. Maybe that'll make the select run as it's optimal speed on oracle... Then you'll be able to do whatever you need with the data on sql server.
-
Thanks again Remi
I think that is a good idea. However, futher support issues preclude the working of SPs on the Oracle server. I am going to have to make it work from SQL Server.
Thanks again for all your help
Paul
-
I guess you should be able to deduce from the query plan where the work is beeing done, if it is on SQL Server of Oracle side. (Look for the number of rows that 'come' from the Icon that indicates RemoteQuery. If it is a high number of rows, the data (probably the entire table) is being transferred to SQL Server over the network before it gets elaborated. If it is a small number of rows, Oracle is not sending the whole table/tables, but only the result of the remote query). I would try to look for that and one thing I would change would be the WHERE clause
where ((RU_link.UO_CODE=(select currentacademicyear collate Latin1_general_CI_AS from tblacademicyear)
or RU_link.UO_CODE=(select nextacademicyear collate Latin1_general_CI_AS from tblacademicyear)
or RU_link.UO_CODE=(select followingacademicyear collate Latin1_general_CI_AS from tblacademicyear)
or RU_link.UO_CODE=(select previousacademicyear collate Latin1_general_CI_AS from tblacademicyear)))
Try to extract the (local) data into variables (and do a = VARIABLE instead of retrieving it from the tables) to see if that might change the query plan for the better... The best for you, I guess, would be to get SQL Server to first elaborate those values and then pass them to Oracle along with the rest of the query to get the biggest benefit of potential Oracle indices...
-
Thanks, Hans
I'll give this a try and get back to you.
Thanks again
Paul
-
Hi Hans
I've just had the opportunity to try this. Unfortunately there was no real improvement.
Thanks for trying.
Paul
-
I almost guessed so... I think at this point it should be up to your third party vendor to show that their WebForm software actually IS slowed down. What ever is done on a server ALWAYS have an impact. The question is, is it big enough? Maybe it is only easier for them to blame someone else instead of dealing with performance issues that might not even be related to your DTS? Maybe you guys could get together to define good ways of measuring performance and then execute them both while your DTS is running and when it is not? Maybe a normal user of their software produces bigger impact on the oracle server... Without concrete ways of identifying bottlenecks they shouldn't be able to say anything...
//Hanslindgren
-
Hi Hans
I couldn't agree more. I just wanted to make sure I had explored every possibility my end before tackling the software vendors. I've also learned quite a lot.
Many thanks for your time and effort.
Paul
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply