sperry4
asked on
Joins? with Linked Server Tables
I am moving transactional data from our ERP (SAP) database to SQL Server (2K)for reporting purposes. The idea is to keep the reporting load off the transactional system. I am tring to keep the master data in SAP so I don't have to replicate all tables, just the big ones). I have set up a Linked server to the Oracel data.
I have a SQL Server (2K) table "custhist" with columns "custno" and "totalsales".
I have a linked server (Oracle/SAP) on the same server with table "custmaster" with columns "custno" and "custname" index on custno.
What is the best performing syntax to return all records from custhist for custno = "123" containing columns custno, custname and totalsales ?
I have a SQL Server (2K) table "custhist" with columns "custno" and "totalsales".
I have a linked server (Oracle/SAP) on the same server with table "custmaster" with columns "custno" and "custname" index on custno.
What is the best performing syntax to return all records from custhist for custno = "123" containing columns custno, custname and totalsales ?
ASKER
looking for the exact syntax of the select statement that would join the SQL server table data to the linked server table data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry if I'm turning this into a new question but I believe the example query given will run slow (with out using index?).
I believe this to be true as when I run Query 1 below, it takes minutes versus the seconds Query 2 takes. There is an ORACLE index on MANDT+VRGAR+RBLEN.
Is there a way to use OPENQUERY() in the example query given by arbert on 4/1 ?
Query 1
Select * from SAPLINKED..SAPR3.CE1MEDA AS X where X.MANDT='100' and X.VRGAR='F' and X.RBELN = '1014580858'
Query 2
Select * From OPENQUERY(SAPLINKED, "SELECT * from SAPR3.CE1MEDA where SAPR3.CE1MEDA.MANDT='100' and SAPR3.CE1MEDA.VRGAR='F' and SAPR3.CE1MEDA.RBELN = '1014580858'")
I believe this to be true as when I run Query 1 below, it takes minutes versus the seconds Query 2 takes. There is an ORACLE index on MANDT+VRGAR+RBLEN.
Is there a way to use OPENQUERY() in the example query given by arbert on 4/1 ?
Query 1
Select * from SAPLINKED..SAPR3.CE1MEDA AS X where X.MANDT='100' and X.VRGAR='F' and X.RBELN = '1014580858'
Query 2
Select * From OPENQUERY(SAPLINKED, "SELECT * from SAPR3.CE1MEDA where SAPR3.CE1MEDA.MANDT='100' and SAPR3.CE1MEDA.VRGAR='F' and SAPR3.CE1MEDA.RBELN = '1014580858'")
Query 2 is run at server side, but there are some changes in SP3.
You could do a pass-through query as:
SELECT * FROM OpenQuery(LinkedOracleServ
Or, you could query it on SQL Server:
SELECT * FROM OracleServer.Database.Tabl
The syntax might have to be messed with.
You could also look into Microsoft Analysis Services if you did want to move some of the data over in the future for data mining. It takes a bit of time to capture, massage and save the data, but the response time for browsing cubes and the like is almost instantaneous in some situations.