1

We have a database A on OraOLEDB. Oracle server, and database B on SQL Server. Our department uses SQL Server to manipulate both A and B, so we created some views for database A in SQL Server so that we can read and write query for those data. But when we have a query for A, it took a lot of time on executing, even if it is a simple query. And the execution plan shows the majority time is on remote query. So I would like to know what is the best way to deal with this issue? Thank you!

Here is the example query:

select p.polkey
      ,m.state
  from dbo.policy as p
  inner join dbo.marina as m
  on m.polkey = p.polkey
 where p.state = 'FL'
   and cast(p.issue_date as date) between '20150101' and '20150228'
Ning
  • 21
  • 4
  • Congrats on even getting that working! Can you explain what a "lot of time" is. Are we talking about something that would normally take 1 second now taking 2 seconds or something that is 1 second taking 30 seconds? How much data is getting returned? The complexity of the query is not usually the issue for linked servers, it is the amount of data. Are you then joining it to SQL Server data, or just returning it RAW from Oracle? Any information you can provide, increases the chances of getting an answer. – Steve Jan 21 '16 at 21:37
  • Are you getting any errors in the SQL server error log? – Taraz Jan 21 '16 at 21:39
  • Also, if you only need a subset of the data (vs. all of the data), have you thought about using a user-defined function to return the filtered subset of the data - that might speed things up if less data needs to be returned from the remote queries. – Taraz Jan 21 '16 at 21:47
  • Hey Steve, thank you for your reply! Here is an example, I have a query which takes about 4 min to finish executing and returned 4405 rows. I just use two tables from A to do the INNER JOIN and filter data, and the total amount of data for those two tables are 5 millions and 1 million respectively. – Ning Jan 22 '16 at 14:48
  • Hi Taraz, thank you for your reply! I didn't get any errors in the SQL server error log, it just took so long to finish executing. I don't think I've ever used user-defined function before, could you please give me more information on how should I do that? Thank you! – Ning Jan 22 '16 at 14:54

0 Answers0