October 1, 2009 at 9:11 am
Hi all
I'm have nightmare quering oracle tables from sql server using linkserver. Here is what my query looks like:
SELECT RowRef, A.UniqueRef, A.ClientRef FROM #Logs vl
CROSS APPLY dbo.FN00PropertyTableRowTMP(vl.RowRef, vl.DataTableRef) A
Basically #Logs contains about 11000 rows. It contains RowRef and DataTableRef columns. Within FN00PropertyTableRowTMP TVF I have written select queries for each DataTableRef scenario, to get UniqueRef and ClientRef from Oracle tables.
Here is how I write query in this function
IF @DataTableRef = XXX
SELECT UniqueRef, ClientRef FROM SERVER..DB.ORATABLE1 WHERE RowRef = @RowRef
ELSE IF @DataTableRef = YYY
SELECT UniqueRef, ClientRef FROM SERVER..DB.ORATABLE2 WHERE RowRef = @RowRef
.
.
.
However, performance wise my select query mentioned above is just aweful. It takes 5mins to query 11000 rows from oracle.
Please can someone advice me how I can improve performance of this query?
Bhavesh
.NET and SQL Server Blog
October 2, 2009 at 6:47 am
1- Please check cardinality on affected Oracle tables.
2- Please describe available indexes on affected Oracle tables.
3- Check execution plan on Oracle side.
4- Ask Oracle DBA to do a '10046 trace name context forever, level 12' trace for you and come back with Wait Events.
5- Post results.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply