Home » RDBMS Server » Performance Tuning » Access Remote Data Slow
icon4.gif  Access Remote Data Slow [message #624927] Sun, 28 September 2014 21:15 Go to next message
oasisin2014
Messages: 8
Registered: September 2014
Junior Member
I have a query running slow, and the query and the analyzing are as the following.

The most important part is from the remote view. The remote view slows down the speed. This remote view consist of 300 tables which uses 300 "union all" from different users.

The operation analyzing shows that the view domains the loading time(from step 20 ).

It gets 2921 rows and takes Elapsed time of more than 10 minutes.

What could do to speed up the speed of the query? Thanks for all possible help!


SELECT
  2  fView.usN AS usN,
  3  fView.idS as idS,
  4  fView.idL as idL,
  5  fView.usT as usT,
  6  fView.psDT as psDT,
  7  fView.exDT as exDT,
     fView.exFR as exFR,
     fView.exRB as exRB,
  8  fView.exRE as exRE,
 11  fView.cbDT as cbDT,
 12  fView.ceDT as ceDT,
 13  fView.cs as cs,
 14  fView.dbdt as dbdt,
 15  fView.dedt AS dedt,
 16  fView.ds AS ds,
 17  LIB.numLIB AS numLIB,
 18  LIB.numLIBST as numLIBRT

FROM
    (select 
 20    HS.usN as usN,
 22    hs.idS as idS,
 23    HSS.idL AS idL,
 24    HSS.HS_DOH_IND as usT,
 25    d.date_dt as psDT,  
 26    ps.exDT as exDT,
 27    fml1.file_id  as exFR,
 28    ps.exRB as exRB,
 29    ps.exRE as exRE,
 30    HR.BEG_DT_TM as cbDT,
 31    HR.END_DT_TM as ceDT,
 32    HR.STATUS_FLG  as cs,  
 33    HR2.END_DT_TM as dedt,
 35    HR2.STATUS_FLG as ds
 36  from hsysR hs
 37  cross join hd d 
 38  left join hr HR on HS.usN = HR.usN 
 39  LEFT JOIN hr HR2 ON HR.HF_RUN_GROUP_ID = HR2.HF_RUN_GROUP_ID  
 40  INNER JOIN hss2 HSS ON HS.idS = HSS.idS  
 41  left join synDASH ps on ps.idL = hss.idL  
 42  left join fLo fl1 on hss.idL = fl1.idL 
 43  left join cnfVal cv1 on hss.idL = cv1.idL  
 45  where 
 46  HS.ACTIVE_IND = 1 
 49  GROUP BY HS.usN, HS.idS, HSS.idL) fView 

LEFT JOIN

 52  (SELECT
 53  hs2.idS as idS,
 54  D2.DATE_DT AS psDT,
 55  FL2.FILE_ID AS numLIB,
 56  FML2.FILE_ID  as numLIBST
 57  FROM hsysR hs2
 58  CROSS JOIN hd D2
 60  INNER JOIN hss2 HSS2 ON HS2.idS = HSS2.idS  
 61  left join fLo fl2 on hss2.idL = fl2.idL and 
 62  left join cnfVal CV2 on HSS2.idL = CV2.idL  
 64  where 
 65  HS2.ACTIVE_IND = 1  
 68  GROUP BY HS2.idS, D2.DATE_DT) LIB 
 
ON 

 70  fView.idS = LIB.idS;



The analyzing is:
2921 rows selected.

Elapsed: 00:11:09.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3972478313

--------------------------------------------------------------------------------
------------------------------------------------------------------

| Id  | Operation				  | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time	 | Inst   |IN-OUT|

--------------------------------------------------------------------------------
------------------------------------------------------------------

|   0 | SELECT STATEMENT			  |  | 39160 |  7571K|	 | 11778   (3)| 00:04:24 |	  |	 |
|*  1 |  HASH JOIN RIGHT OUTER			  |  | 39160 |  7571K|	 | 11778   (3)| 00:04:24 |	  |	 |
|   2 |   VIEW					  |    |  2268 |   106K|	 |  2123   (2)| 00:00:48 |	  |	 |
|   3 |    SORT GROUP BY			  | |  2268 |   294K|	 |  2123   (2)| 00:00:48 |	  |	 |
|*  4 |     HASH JOIN RIGHT OUTER		  |  |  2268 |   294K|	 |  2122   (2)| 00:00:48 |	  |	 |
|*  5 |      TABLE ACCESS BY INDEX ROWID	  | cnfVal|     3 |   150 |	 |     1   (0)| 00:00:01 |	  |	 |
|*  6 |       INDEX RANGE SCAN			  | Ind_wocv |     3 |	 |	 |     1   (0)| 00:00:01 |	  |	 |
|   7 |      VIEW				  | |  2268 |   183K|	 |  2120   (2)| 00:00:48 |	  |	 |
|   8 |       NESTED LOOPS OUTER		  | |  2268 |   205K|	 |  2120   (2)| 00:00:48 |	  |	 |
|*  9 |        HASH JOIN OUTER			  | |   500 | 41500 |	 |  1970   (2)| 00:00:45 |	  |	 |
|  10 | 	VIEW				  | |   500 | 30500 |	 |     5  (20)| 00:00:01 |	  |	 |
|* 11 | 	 FILTER 			  | |	 |	 |	 |	      | 	 |	  |	 |
|* 12 | 	  HASH JOIN			  | |   500 | 19000 |	 |     5  (20)| 00:00:01 |	  |	 |
|* 13 | 	   INDEX RANGE SCAN		  | Ind_hsysR|    42 |   798 |	 |     1   (0)| 00:00:01 |	  |	 |
|  14 | 	   MERGE JOIN CARTESIAN 	  ||  1002 | 19038 |	 |     3   (0)| 00:00:01 |	  |	 |
|* 15 | 	    INDEX RANGE SCAN		  | Ind_hd|    12 |    96 |	 |     1   (0)| 00:00:01 |	  |	 |
|  16 | 	    BUFFER SORT 		  ||    84 |   924 |	 |     2   (0)| 00:00:01 |	  |	 |
|* 17 | 	     INDEX FAST FULL SCAN	  | Ind_wohsr|    84 |   924 |	 |     0   (0)| 00:00:01 |	  |	 |
|* 18 | 	TABLE ACCESS FULL		  | fLo| 10470 |   224K|	 |  1965   (2)| 00:00:44 |	  |	 |
|* 19 |        INDEX RANGE SCAN 		  | Ind_wflog|     5 |    50 |	 |     1   (0)| 00:00:01 |	  |	 |
|  20 |   VIEW					  || 39160 |  5736K|	 |  9655   (3)| 00:03:36 |	  |	 |
|  21 |    HASH GROUP BY			  || 39160 |  4665K|  6152K|  9655   (3)| 00:03:36 |	  |	 |
|  22 |     VIEW				  | VW_DAG_0| 39160 |  4665K|	 |  8762   (3)| 00:03:16 |	  |	 |
|  23 |      HASH GROUP BY			  || 39160 |  6577K|  7128K|  8762   (3)| 00:03:16 |	  |	 |
|* 24 |       HASH JOIN RIGHT OUTER		  || 39160 |  6577K|	 |  7534   (4)| 00:02:49 |	  |	 |
|* 25 |        TABLE ACCESS BY INDEX ROWID	  | cnfVal|     3 |   150 |	 |     1   (0)| 00:00:01 |	  |	 |
|* 26 | 	INDEX RANGE SCAN		  | Ind_wocv|     3 |	 |	 |     1   (0)| 00:00:01 |	  |	 |
|  27 |        VIEW				  || 39160 |  4665K|	 |  7532   (4)| 00:02:49 |	  |	 |
|* 28 | 	HASH JOIN OUTER 		  || 39160 |  8642K|	 |  7532   (4)| 00:02:49 |	  |	 |
|* 29 | 	 HASH JOIN OUTER		  ||  8634 |  1821K|	 |  5920   (4)| 00:02:13 |	  |	 |
|  30 | 	  VIEW				  ||  8331 |  1578K|	 |  3967   (5)| 00:01:29 |	  |	 |
|* 31 | 	   HASH JOIN OUTER		  ||  8331 |  1700K|	 |  3967   (5)| 00:01:29 |	  |	 |
|  32 | 	    VIEW			  ||   328 | 52480 |	 |   307   (1)| 00:00:07 |	  |	 |
|  33 | 	     NESTED LOOPS OUTER 	  ||   328 | 48544 |	 |   307   (1)| 00:00:07 |	  |	 |
|  34 | 	      NESTED LOOPS OUTER	  ||   247 | 30628 |	 |   159   (1)| 00:00:04 |	  |	 |
|* 35 | 	       HASH JOIN		  ||   247 | 22724 |	 |    11  (10)| 00:00:01 |	  |	 |
|  36 | 		JOIN FILTER CREATE	  | :BF0000|    84 |  1848 |	 |     3   (0)| 00:00:01 |	  |	 |
|* 37 | 		 TABLE ACCESS FULL	  | hss2|    84 |  1848 |	 |     3   (0)| 00:00:01 |	  |	 |
|  38 | 		VIEW			  ||  1002 | 70140 |	 |     7   (0)| 00:00:01 |	  |	 |
|* 39 | 		 FILTER 		  ||	 |	 |	 |	      | 	 |	  |	 |
|  40 | 		  JOIN FILTER USE	  | :BF0000|  1002 | 27054 |	 |     7   (0)| 00:00:01 |	  |	 |
|  41 | 		   MERGE JOIN CARTESIAN   ||  1002 | 27054 |	 |     7   (0)| 00:00:01 |	  |	 |
|* 42 | 		    INDEX RANGE SCAN	  | Ind_hd|    12 |    96 |	 |     1   (0)| 00:00:01 |	  |	 |
|  43 | 		    BUFFER SORT 	  ||    84 |  1596 |	 |     6   (0)| 00:00:01 |	  |	 |
|* 44 | 		     TABLE ACCESS FULL	  | hsysR|    84 |  1596 |	 |     1   (0)| 00:00:01 |	  |	 |
|* 45 | 	       TABLE ACCESS BY INDEX ROWID| hr|     1 |    32 |	 |     1   (0)| 00:00:01 |	  |	 |
|* 46 | 		INDEX RANGE SCAN	  | Ind_run|     1 |	 |	 |     1   (0)| 00:00:01 |	  |	 |
|* 47 | 	      TABLE ACCESS BY INDEX ROWID | hr|     1 |    24 |	 |     1   (0)| 00:00:01 |	  |	 |
|* 48 | 	       INDEX RANGE SCAN 	  | Ind_run|     2 |	 |	 |     1   (0)| 00:00:01 |	  |	 |
|  49 | 	    REMOTE			  | proVIEW|    12M|   561M|	 |  3610   (4)| 00:01:21 |     
|* 50 | 	  TABLE ACCESS FULL		  | fLo|   107K|  2313K|	 |  1952   (1)| 00:00:44 |	  |	 |
 
--------------------------------------------------------------------------------
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("fView"."psDT"="LIB"."psDT"(+) AND "fView"."idS"="LIB"."idS"(+))

Remote SQL Information (identified by operation id):
----------------------------------------------------

  49 - SELECT "idL","exDT","exRB","exRE","pDT" FROM  "proVIEW"



synDASH is a synonym

CREATE OR REPLACE SYNONYM "synDASH" FOR "proVIEW";


proVIEW is view for the same table bTab on 300 schema
 CREATE OR REPLACE FORCE VIEW "proVIEW" ("idL", "exDT", "exRB", "exRE", "pDT") AS 
    select distinct idL, exDT, exRB, exRE, pDT from schema1.bTab
  union all 
    select distinct idL, exDT, exRB, exRE, pDT from schema2.bTab
  ...........
  300 union all 
Re: Access Remote Data Slow [message #624928 is a reply to message #624927] Sun, 28 September 2014 21:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
says me: Doctor, it hurts when I poke myself in the eye. How can I stop the pain?
says the Doctor: Stop poking yourself in the eye.

>This remote view consist of 300 tables which uses 300 "union all" from different users.
Yes, this will take a long time.
You got the performance you designed into this query.
You can make it faster by not using a remote view of 300 tables doing 300 UNION ALL.

As Pogo once said, "We have met the enemy & they is us!"

You remind me of a guy who has tied his right wrist to his left ankle & then ask what can be done to improve the speed at which he runs the 100m sprint.
Re: Access Remote Data Slow [message #624930 is a reply to message #624928] Sun, 28 September 2014 22:13 Go to previous messageGo to next message
oasisin2014
Messages: 8
Registered: September 2014
Junior Member
The reason to build the remote view is that all the rest tables in the query are in one schema, and the view has to be in another schema.

Is there a way to re-optimize the union all in the view, then speed up? Thanks.
Re: Access Remote Data Slow [message #624931 is a reply to message #624930] Sun, 28 September 2014 22:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>It gets 2921 rows

as constructed, MANY, Many many rows must be passed across the network only to discard the VAST MAJORITY of the rows.
I don't think you could construct SQL to return the same desired rows to take longer than it does now.
Re: Access Remote Data Slow [message #624943 is a reply to message #624930] Mon, 29 September 2014 03:13 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
oasisin2014 wrote on Mon, 29 September 2014 04:13
The reason to build the remote view is that all the rest tables in the query are in one schema, and the view has to be in another schema.

Is there a way to re-optimize the union all in the view, then speed up? Thanks.


Union all is pretty un-optimizable - it's highly efficient so there's nothing you can do to make it better, apart from possibly using parallel processing. The fundamental problem is that it's searching through 300 tables. The only way to significantly speed it up is to get it to not search through 300 tables.
The best bet from a performance point of view (and I realize this will probably require a lot of code changes) is to combine the 300 tables into 1 table.
Re: Access Remote Data Slow [message #624944 is a reply to message #624943] Mon, 29 September 2014 03:17 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
cookiemonster wrote on Mon, 29 September 2014 09:13
The best bet from a performance point of view (and I realize this will probably require a lot of code changes) is to combine the 300 tables into 1 table.



Depending on call frequency, might be worth making an MV with the table outputs and using a synonym or similar for the thing jumping across the link.

It really depends, but that at least would mitigate some of the dev time.
Re: Access Remote Data Slow [message #624956 is a reply to message #624944] Mon, 29 September 2014 07:45 Go to previous messageGo to next message
oasisin2014
Messages: 8
Registered: September 2014
Junior Member
Thanks for all the suggestions.

Those 300 tables are for 300 users from 300 schema. All the 300 tables have the same table definition and have different data depending on the users. The purpose is to gather the data from those 300 users,with different time range.

Could try to make an MV, but no experience on it. But the analyzing showed that the optimizer would not pick up any query index for this view(even indexes are used for the base table). If we create indexes for the MV, maybe that would speed up it?
Re: Access Remote Data Slow [message #624960 is a reply to message #624956] Mon, 29 September 2014 08:04 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Maybe. It's hard to tell since I can't actually work out what the view is in the original query, proview doesn't appear to be mentioned.
Re: Access Remote Data Slow [message #624963 is a reply to message #624960] Mon, 29 September 2014 08:24 Go to previous messageGo to next message
oasisin2014
Messages: 8
Registered: September 2014
Junior Member
cookiemonster wrote on Mon, 29 September 2014 08:04
Maybe. It's hard to tell since I can't actually work out what the view is in the original query, proview doesn't appear to be mentioned.



In the original query, synDASH is used as a synonym for the view proview. And this view proview is built with 300 identical base table 'bTab' from 300 users. For the convenience, the code for this view is listed below again:


CREATE OR REPLACE FORCE VIEW "proVIEW" ("idL", "exDT", "exRB", "exRE", "pDT") AS 
    select distinct idL, exDT, exRB, exRE, pDT from schema1.bTab
  union all 
    select distinct idL, exDT, exRB, exRE, pDT from schema2.bTab
  ...........
  300 union all 
Re: Access Remote Data Slow [message #624965 is a reply to message #624963] Mon, 29 September 2014 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The data in the VIEW is not normalized.
No application should ever have more than 1 table with the exact same columns.
Application data, like owner name ever be part of object name.

What results if SQL is modified to run from "other" system where the 300 base tables reside?
Re: Access Remote Data Slow [message #624966 is a reply to message #624956] Mon, 29 September 2014 08:49 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
oasisin2014 wrote on Mon, 29 September 2014 13:45
But the analyzing showed that the optimizer would not pick up any query index for this view(even indexes are used for the base table). If we create indexes for the MV, maybe that would speed up it?


What analysis are we talking about here? There is after all a big difference between querying a view that unions 300 tables and querying a single table (and an mv is a table for this purpose).

Try it with an MV, then if you still have an issue try adding an index on idL.
Re: Access Remote Data Slow [message #624967 is a reply to message #624966] Mon, 29 September 2014 09:30 Go to previous messageGo to next message
oasisinsky
Messages: 3
Registered: September 2014
Junior Member
cookiemonster wrote on Mon, 29 September 2014 08:49
oasisin2014 wrote on Mon, 29 September 2014 13:45
But the analyzing showed that the optimizer would not pick up any query index for this view(even indexes are used for the base table). If we create indexes for the MV, maybe that would speed up it?


What analysis are we talking about here? There is after all a big difference between querying a view that unions 300 tables and querying a single table (and an mv is a table for this purpose).

Try it with an MV, then if you still have an issue try adding an index on idL.


The analyzing is :
 20 |   VIEW					  || 39160 |  5736K|	 |  9655   (3)| 00:03:36 |	  |	 |
|  21 |    HASH GROUP BY			  || 39160 |  4665K|  6152K|  9655   (3)| 00:03:36 |	  |	 |
|  22 |     VIEW				  | VW_DAG_0| 39160 |  4665K|	 |  8762   (3)| 00:03:16 |	  |	 |
|  23 |      HASH GROUP BY			  || 39160 |  6577K|  7128K|  8762   (3)| 00:03:16 |	  |	 |
|* 24 |       HASH JOIN RIGHT OUTER		  || 39160 |  6577K|	 |  7534   (4)| 00:02:49 |	  |	 |
|* 25 |        TABLE ACCESS BY INDEX ROWID	  | cnfVal|     3 |   150 |	 |     1   (0)| 00:00:01 |	  |	 |
|* 26 | 	INDEX RANGE SCAN		  | Ind_wocv|     3 |	 |	 |     1   (0)| 00:00:01 |	  |	 |
|  27 |        VIEW				  || 39160 |  4665K|	 |  7532   (4)| 00:02:49 |	  |	 |


I am thinking if it pick up indexes, it would be different.
Re: Access Remote Data Slow [message #624968 is a reply to message #624967] Mon, 29 September 2014 09:51 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So the analysis is the explain plan against the existing query?
That's pretty much irrelevant at this point. Plus the bit you've highligted has nothing to do with the remote and view and it shows index usage, so I'm really not sure what you think it proves.

Try the MV, see what happens
Re: Access Remote Data Slow [message #624969 is a reply to message #624968] Mon, 29 September 2014 10:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I am thinking if it pick up indexes, it would be different

Please explain how or why you think INDEX will improve VIEW performance when no WHERE clause exists & all rows are returned?
Re: Access Remote Data Slow [message #624971 is a reply to message #624969] Mon, 29 September 2014 10:33 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Blackswan - view is being joined to in a larger query, so there is a where clause and not all rows are returned.
Re: Access Remote Data Slow [message #624977 is a reply to message #624971] Mon, 29 September 2014 20:36 Go to previous messageGo to next message
oasisinsky
Messages: 3
Registered: September 2014
Junior Member
Thanks, cookiemonster. It turned out that the MV with indexes of the base table speed up the loading by about 4 times faster. I am still thinking what else could be done to make the loading faster if more rows are returned.

One more interesting thing is that the operation analyzing showed that the indexed of the base table in the MV was not used at all. But with it, the speed did speed up.
Re: Access Remote Data Slow [message #625003 is a reply to message #624977] Tue, 30 September 2014 03:39 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you mean the indexes on the tables the MW query is based on aren't used?
They couldn't be. The whole point of an MV is that it creates a seperate table with the results of the view query.
So any indexes on the base tables are irrelevant when querying it.
You can put indexes on the MV itself.
Re: Access Remote Data Slow [message #625005 is a reply to message #624927] Tue, 30 September 2014 04:03 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your query includes many outer joins. These cripple the optimizer, because they force the join order. The query has aggregations. This also cripples the optimizer, because they force Oracle to stop what is doing and materialize the in-line view.
Often programmers throw in outer joins when they are unnecessary (why? Perhaps because they are afraid of losing rows) and aggregations when they are unnecessary (why? Perhaps because they are afraid of getting duplicates.) It is less common that I see BOTH these structures used.
Why do you have those constructs in the query? Are they really needed? If you have proper constraints, they possible aren't. Don't just say that you need those joins and aggregations. Do some work on them. You need to understand your data before you query it.
I wouldn't worry about the efficiency of UNION ALL on many tables, that is (in effect) no different from a full scan of a partitioned table.

[Updated on: Tue, 30 September 2014 04:03]

Report message to a moderator

Re: Access Remote Data Slow [message #625008 is a reply to message #625005] Tue, 30 September 2014 04:38 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
John Watson wrote on Tue, 30 September 2014 10:03
This also cripples the optimizer, because they force Oracle to stop what is doing and materialize the in-line view.

Actually looking at the query the group by doesn't marry up to the select list at all, so I can only assume the query is edited.

John Watson wrote on Tue, 30 September 2014 10:03

I wouldn't worry about the efficiency of UNION ALL on many tables, that is (in effect) no different from a full scan of a partitioned table.

I'm not convinced that's true. I used to have 30 tables with identical structures and a union all view over the top, queries against the view were horribly slow. Combining the tables into 1 gave a marked increase in performance even for queries that didn't use indexes on the resulting table. Now admittedly I didn't use partitioning, cause I didn't need it, but I have to assume a partitioned table wouldn't be worse than a non-partitioned one in that regard. I'd test but I don't have an EE DB handy.
Of course non of this proves there's a problem with union-all, there isn't, it's as efficient as it can be, the problem is having related data spread across lots of tables.
Re: Access Remote Data Slow [message #625028 is a reply to message #625003] Tue, 30 September 2014 08:07 Go to previous messageGo to next message
oasisin2014
Messages: 8
Registered: September 2014
Junior Member
cookiemonster wrote on Tue, 30 September 2014 03:39
Do you mean the indexes on the tables the MW query is based on aren't used?
They couldn't be. The whole point of an MV is that it creates a seperate table with the results of the view query.
So any indexes on the base tables are irrelevant when querying it.
You can put indexes on the MV itself.



I put the indexes in the MV. But the plan did not show those indexes at all. The only thing i could think of is that it is a remote MV. Meanwhile, the below information of the plan showed the 3 fields of the view is used:

Remote SQL Information (identified by operation id):
----------------------------------------------------

  47 - SELECT "DT1" "DT2" "Field3_ID"FROM

        "MV" "PS" WHERE "DT1"<=:1 AND "DT2">=:2 AND "Field3_ID"=:3



Maybe here indexes is used in the remote sql informaiton, but it does not display? FIY, I created 2 index for the view: Field3_ID(index 1) and ""DT1" "DT2" "Field3_ID"(index 2 for 3 fields, not sure about this one)
Re: Access Remote Data Slow [message #625030 is a reply to message #625008] Tue, 30 September 2014 08:12 Go to previous messageGo to next message
oasisin2014
Messages: 8
Registered: September 2014
Junior Member

Quote:


I'm not convinced that's true. I used to have 30 tables with identical structures and a union all view over the top, queries against the view were horribly slow. Combining the tables into 1 gave a marked increase in performance even for queries that didn't use indexes on the resulting table. Now admittedly I didn't use partitioning, cause I didn't need it, but I have to assume a partitioned table wouldn't be worse than a non-partitioned one in that regard. I'd test but I don't have an EE DB handy.
Of course non of this proves there's a problem with union-all, there isn't, it's as efficient as it can be, the problem is having related data spread across lots of tables.


+1, all the union all tables are in 1 MV, and it speeds up.

[Updated on: Tue, 30 September 2014 08:13]

Report message to a moderator

Re: Access Remote Data Slow [message #625033 is a reply to message #625030] Tue, 30 September 2014 08:31 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well Field3_ID, dt1 and dt2 aren't mentioned anywhere in the original sql so I have no idea how they're involved. If you want suggestions you'll need to supply the correct query plus the MV structure and also tell us how many rows are in the MV and how many match the where clause of your query.
Re: Access Remote Data Slow [message #625055 is a reply to message #625033] Tue, 30 September 2014 13:56 Go to previous message
oasisinsky
Messages: 3
Registered: September 2014
Junior Member
Sorry about the fields in the query. They are just 3 fields.

Another import thing is that the base table in the MV needs to have the very current data, since new data are imported to the base table very often, maybe every hour.
But now the thing is the MV could not have those updated data after creating. We need to refresh it very very often. But in this way, the loading could get delayed again.
Is there a good/efficient way to refresh? like Oracle scheduler?
Previous Topic: Private message
Next Topic: code taking long amount of time to execute
Goto Forum:
  


Current Time: Fri Mar 29 04:43:15 CDT 2024