slow performance with query that has layers of views
441858Feb 14 2012 — edited Feb 21 2012Oracle 11gR2, RHEL5-64bit (SQL Server 2005 64bit)
Hi All,
Recently I have run into a slow performing query. The query is done from SQL Server and then goes to Oracle (via linked server) and tries to pull data from an Oracle table. Here is how it goes:
I query a view (SQL Server), which queries another view (SQL Server), which queries another view (Oracle) which queries a table (Oracle).
So you see the several layers here. This query (or one version of it) takes about 36 seconds, which is way too long.
I did some performance analysis on the SQL Server side, but it did not show any problems, not even any recommendations for indexes or anything. I think this is because the processing is probably being done in Oracle and not SQL Server. So knowing that you can't create an index on a view (and doesn't serve a purpose because if the base table has an index, the view will usually use it) I decided to create materialized views for the Oracle views in this chain. I did that and also created an index on relevant columns, and re-ran the query, but the performance remained the same.
So now I would like some alternative ideas (performance or otherwise) as to what I can do in this situation.
I would appreciate any and all suggestions.
Thank you all.
JrOraDBA