Forum Post: RE: joining 2 virtual tables runs forever or at least a very long time

[Brian K. Maher]

Before adding the index try inserting the {NOREORDER} phrase From: scottemick [mailto:bounce-scottemick@community.progress.com] Sent: Thursday, July 24, 2014 1:31 PM To: TU.OE.Development@community.progress.com Subject: RE: [Technical Users - OE Development] joining 2 virtual tables runs forever or at least a very long time RE: joining 2 virtual tables runs forever or at least a very long time Reply by scottemick I used select "_Pnumber", substring("_Description", 1, 80) from pub."_Sql_Qplan"; It shows me a plan. 12 SELECT COMMAND. 12 PROJECT [44] ( 12 | PROJECT [43] distinct ( 12 | | JOIN [38][NESTED_LOOP-JOIN]( 12 | | | PROJECT [28] [ INTO TMPTBL00000006 ] ERR-ON-MULTI ( 12 | | | | PROJECT [27] ( 12 | | | | | RESTRICT [13] ( 12 | | | | | | PROJECT [25] ( 12 | | | | | | | PUB._Sql_Qplan. [9]( 12 | | | | | | | | TABLE SCAN 12 | | | | | | | ) 12 | | | | | | , PUB._Sql_Qplan._Pnumber 12 | | | | | | , PUB._Sql_Qplan._Ptype 12 | | | | | | ) 12 | | | | | 12 | | | | | | (PEXPR2) (0) 12 | | | | | ) 12 | | | | , max (PEXPR1) 12 | | | | ) 12 | | | , PEXPR1 12 | | | ) 12 | | , 12 | | | no join operator exists 12 | | , 12 | | | (PEXPR1) = (PEXPR2) 12 | | , 12 | | | PROJECT [36] ( 12 | | | | PUB._Sql_Qplan. [5]( 12 | | | | | TABLE SCAN 12 | | | | ) 12 | | | , PUB._Sql_Qplan._Pnumber 12 | | | , PUB._Sql_Qplan._Description 12 | | | , PUB._Sql_Qplan.rowid 12 | | | ) 12 | | ) 12 | , PEXPR2 12 | , PEXPR3 12 | , PEXPR4 12 | ) 12 , PEXPR1 12 , substring (PEXPR2,1,80) 12 ) If I run one of the two subqueries for the virtual tables I get this: 16 SELECT COMMAND. 16 PROJECT [62] ( 16 | JOIN [71][AUG_NESTED_LOOP-JOIN] 16 | | [RHS-SORTED(-ASC-DUPS) ]( 16 | | JOIN [70][AUG_NESTED_LOOP-JOIN] 16 | | | [RHS-SORTED(-ASC-DUPS) ]( 16 | | | RESTRICT [30] ( 16 | | | | PROJECT [56] ( 16 | | | | | PUB.T. [10]( 16 | | | | | | TABLE SCAN 16 | | | | | ) 16 | | | | , PUB.T.NetSales 16 | | | | , PUB.T.dt_StartBusDate 16 | | | | , PUB.T.EntityID 16 | | | | , PUB.T.rowid 16 | | | | ) 16 | | | 16 | | | | (PEXPR2) between (07-20-14,07-26-14) 16 | | | | Evaluation callback list( 16 | | | | | col id# 18 16 | | | | ) 16 | | | ) 16 | | , 16 | | | (PEXPR3) = (PEXPR5) 16 | | | -- above defines ANL left side keys relop right side keys. 16 | | , 16 | | | PROJECT [51] ( 16 | | | | PUB.E. [9]( 16 | | | | | INDEX SCAN OF ( 16 | | | | | | i-EntityID, 16 | | | | | | | (PUB.E.EntityID) = (null)) 16 | | | | ) 16 | | | , PUB.E.EntityID 16 | | | , PUB.E.Name 16 | | | , PUB.E.RegionID 16 | | | , PUB.E.rowid 16 | | | ) 16 | | ) 16 | , 16 | | (PEXPR7) = (PEXPR9) 16 | | -- above defines ANL left side keys relop right side keys. 16 | , 16 | | PROJECT [60] ( 16 | | | PUB.R. [16]( 16 | | | | INDEX SCAN OF ( 16 | | | | | RegionID, 16 | | | | | | (PUB.R.RegionID) = (null)) 16 | | | ) 16 | | , PUB.R.RegionID 16 | | , PUB.R.Name 16 | | , PUB.R.rowid 16 | | ) 16 | ) 16 , PEXPR9 16 , PEXPR10 16 , PEXPR5 16 , PEXPR6 16 , PEXPR1 16 , year (PEXPR2) 16 , dayofweek (PEXPR2) 16 ) For the subquery it appears that the where cause with the date range is causing a table scan, but the EntityId and RegionId are using index scans. I could try to put an index on the date, maybe I'll do that. But that still doesn't tell me why two subqieries that only take a few seconds two return 1,000 rows each can't be downloaded to temp tables and joined on integer keys in a small amount of time. I let the query run for giggles for 4 hours, it never completed. I have the NOLOCK statement so the whole thing shouldn't be locking....It is just strange to me. Stop receiving emails on this subject. Flag this post as spam/abuse.

Continue reading...