SQL Developer Data Modeler

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Data Modeler extreme slowdown loading design

Jeffrey KempOct 4 2016 — edited Oct 5 2016

I just upgraded from 4.1.3 to 4.1.5 and now it takes ages to load my data model design. The progress bar sits at about the 40% mark for about 10 minutes.

(In case it's relevant: I had a problem where my domains did not get migrated, so I had to copy the defaultdomains.xml file across to the new installation manually. Unfortunately when I open the data model, all the domains have been removed from all the columns throughout my data model. It's going to take ages to put them all back on again )

I tried it in 4.1.3 again and that's slow too, so something in the upgrade has done something to the data model files.

Unfortunately this has ruined SQL Developer's data modeler for me, as I've just started build on a new large application and was using data modeler extensively. That'll teach me to take regular backups of everything

Anurag Tibrewal

Hi,

One way

with t1 as
(
	select 	a.acct
		, b.score
		, to_char(a.date_changed,'MM') - to_char(b.dateupload,'MM') priormonth
	from tableA a, tableB b
	where 	a.acct=b.acct
		and (to_char(a.date_changed,'MM') = to_char(b.dateupload,'MM') +1
			or to_char(a.date_changed,'MM') = to_char(b.dateupload,'MM')+2)
)
select 	t1.acct
	, max(decode(priormonth,1, b.score,0)) score1priormonth
	, max(decode(priormonth,2,b.score,0)) score1priormonth
from t1 group by acct

Regards
Anurag

1 person found this helpful

fsitja

SELECT acct, score2monthprior, score1monthprior
  FROM (SELECT a.*,
               b.dateupload,
               b.score,
               lag(b.dateupload) over(PARTITION BY a.acct, a.datechanged ORDER BY b.dateupload) dt_prv_upload,
               lag(b.score) over(PARTITION BY a.acct, a.datechanged ORDER BY b.dateupload) score1monthprior,
               lag(b.score, 2) over(PARTITION BY a.acct, a.datechanged ORDER BY b.dateupload) score2monthprior
          FROM table_a a
          JOIN table_b b
            ON a.acct = b.acct
         ORDER BY b.dateupload)
 WHERE datechanged BETWEEN dt_prv_upload AND dateupload

Can you have more than one record on table_b for each month? If so it won't work as this. I'm simply looking up the record from the previous month here.

1 person found this helpful

Solomon Yakobson

If you understood your reqs correctly, it should be:

with a as (
           select 123 acct,to_date('1/12/2008','mm/dd/yyyy') date_changed from dual union all
           select 456,to_date('8/25/2008','mm/dd/yyyy') from dual
          ),
     b as (
           select 123 acct,to_date('11/30/2007','mm/dd/yyyy') date_upload,620 score from dual union all
           select 123,to_date('12/31/2007','mm/dd/yyyy'),650 from dual union all
           select 123,to_date('01/31/2008','mm/dd/yyyy'),712 from dual union all
           select 123,to_date('02/29/2008','mm/dd/yyyy'),800 from dual union all
           select 456,to_date('05/31/2008','mm/dd/yyyy'),520 from dual union all
           select 456,to_date('06/30/2008','mm/dd/yyyy'),630 from dual union all
           select 456,to_date('07/31/2008','mm/dd/yyyy'),680 from dual union all
           select 456,to_date('08/31/2008','mm/dd/yyyy'),710 from dual union all
           select 456,to_date('09/30/2008','mm/dd/yyyy'),780 from dual union all
           select 456,to_date('10/31/2008','mm/dd/yyyy'),786 from dual union all
           select 456,to_date('11/30/2008','mm/dd/yyyy'),823 from dual
          ),
      c as (
            select  acct,
                    date_upload,
                    score,
                    lead(date_upload,1,sysdate) over(partition by acct order by date_upload) next_date_upload
              from  b
           )
select  acct,
        (select score from c where a.acct = c.acct and add_months(date_changed,-2) >= date_upload and add_months(date_changed,-2) < next_date_upload) Score2MonthPrior,
        (select score from c where a.acct = c.acct and add_months(date_changed,-1) >= date_upload and add_months(date_changed,-1) < next_date_upload) Score1MonthPrior
  from  a
/

      ACCT SCORE2MONTHPRIOR SCORE1MONTHPRIOR
---------- ---------------- ----------------
       123                               620
       456              520              630

SQL>

Two months prior 1/12/2008 is 11/12/2007 which falls before the earliest record for acct 123. One month prior 1/12/2008 is 12/12/2007 which falls between 11/30/2007 and 12/31/2007 and therefore score should be 620, not 650.

SY.

Frank Kulash

Hi,

Anurag Tibrewal wrote:
Hi,

One way

with t1 as
(
	select 	a.acct
		, b.score
		, to_char(a.date_changed,'MM') - to_char(b.dateupload,'MM') priormonth
	from tableA a, tableB b
	where 	a.acct=b.acct
		and (to_char(a.date_changed,'MM') = to_char(b.dateupload,'MM') +1
			or to_char(a.date_changed,'MM') = to_char(b.dateupload,'MM')+2)
)
select 	t1.acct
	, max(decode(priormonth,1, b.score,0)) score1priormonth
	, max(decode(priormonth,2,b.score,0)) score1priormonth
from t1 group by acct

Regards
Anurag

Right idea. I'm sure if OP had posted some sample data, then you would have tested it and found the mistakes.

We don't want to use TO_CHAR here. If this is run in month X, then we usually want months X-2 and X-1, but not early in the year. Right now, the month i s '01', but the months wanted would be '11' and '12', ,not '-1' and '00'.

Also, table alias b has no meaning in the main query.

I think you meant something like:

WITH	got_priormonth		AS
(
	SELECT	a.acct
	,	b.score
	,	MONTHS_BETWEEN ( TRUNC (b.date_upload,  'MONTH')
			       , TRUNC (a.date_changed, 'MONTH')
			       )	AS priormonth	-- Alias added
	FROM	tableA  a
	JOIN	tableB	b	ON	a.acct	= b.acct
	WHERE	a.date_changed	>= ADD_MONTHS  ( TRUNC (b.date_upload, 'MONTH')
				  	       , -2
					       )
	AND	a.date_changed  <                TRUNC (b.date_upload, 'MONTH')
)
SELECT	  acct
,	  MAX (CASE WHEN priormonth = 2 THEN score ELSE 0 END)	AS score2monthprior
,	  MAX (CASE WHEN priormonth = 1 THEN score ELSE 0 END)	AS score1monthprior
FROM	  got_priormonth
GROUP BY  acct
;

Edited by: Frank Kulash on Jan 22, 2010 3:00 PM
Changed SYSDATE to b.date_upload

Edited by: Frank Kulash on Jan 22, 2010 3:57 PM
Fixed several typos

Anurag Tibrewal

Agreed Frank,
You are really a guru.

Regards
Anurag

684479

I got a ORA-00904 error indicating b.score invalid.

I also changed the second score1priormonth to score2priormonth, don't we need to look at year as well? Thanks,

684479

No, one rcord for each month but sometimes the month end dates got screwed up (not month and year they are fine).

684479

Hi Frank, I keep getting a missing right parenthesis message but I think all of them are accounted for. We need to name month_between calculation as priormonth correct?

Frank Kulash

Answer

Hi,

Whenever you have a question, post a little sample data in a form people can actually use. CREATE TABLE and INSERT statements are best. A WITH clause, like Solomon posted, is better than nothing. Without that, people will help you as much as they can, but don't expect them to test anything.

Using Solomon's data, I was able to test (and correct) my query.

WITH	got_priormonth		AS
(
	SELECT	a.acct
	,	b.score
	,	MONTHS_BETWEEN ( TRUNC (b.date_upload,  'MONTH')
			       , TRUNC (a.date_changed, 'MONTH')
			       )	AS priormonth	-- Alias added
	FROM	tableA  a
	JOIN	tableB	b	ON	a.acct	= b.acct
	WHERE	a.date_changed	>= ADD_MONTHS  ( TRUNC (b.date_upload, 'MONTH')
				  	       , -2
					       )
	AND	a.date_changed  <                TRUNC (b.date_upload, 'MONTH')
)
SELECT	  acct
,	  MAX (CASE WHEN priormonth = 2 THEN score ELSE 0 END)	AS score2monthprior
,	  MAX (CASE WHEN priormonth = 1 THEN score ELSE 0 END)	AS score1monthprior
FROM	  got_priormonth
GROUP BY  acct
;

ORA-00907 really means "absolutely clue-free regarding what you meant".
Perhaps a missing right parenthesis causes the error as much as anything, but when you get that error, look for any kind of syntax error at (or slightly before) the point indicated.

You're right, I did forget the column alias. Sorry; if I could have tested it before posting, I would have.

Marked as Answer by 684479 · Sep 27 2020

684479

I moved the join condition inside of the where clause and it worked (not sure why "on" would not work and give that Alicia Silverstone Clueless error).

Few issues though:

I get scores from months after change date not prior, two months prior is two months after score and one month prior is one month after. I looked at the syntax can't see why.

I missed some records in table A, is there anyway all the records from base table are returned?

Thanks,

Frank Kulash

Hi,

Sorry, I had the dates backwards.
Swap all references to a.date_changed and b.date_upload in sub-query got_priormonth:

WITH	got_priormonth		AS
(
	SELECT	a.acct
	,	b.score
	,	MONTHS_BETWEEN ( TRUNC (a.date_changed,  'MONTH')
			       , TRUNC (b.date_upload,   'MONTH')
			       )	AS priormonth
	FROM	tableA  a
	JOIN	tableB	b	ON	a.acct	= b.acct
	WHERE	b.date_upload	>= ADD_MONTHS  ( TRUNC (a.date_changed, 'MONTH')
				  	       , -2
					       )
	AND	b.date_upload   <                TRUNC (a.date_changed, 'MONTH')
)
SELECT	  acct
,	  MAX (CASE WHEN priormonth = 2 THEN score ELSE 0 END)	AS score2monthprior
,	  MAX (CASE WHEN priormonth = 1 THEN score ELSE 0 END)	AS score1monthprior
FROM	  got_priormonth
GROUP BY  acct
;

Output, using Solomon's data:

.     ACCT SCORE2MONTHPRIOR SCORE1MONTHPRIOR
---------- ---------------- ----------------
       123              620              650
       456              630              680

684479

Perfect!

Thanks, Frank.

Aketi Jyuuzou

Hehe I like Pivot B-)
But recently,I think that group by and max and decode is sometimes readable than Pivot.
Because Pivot do implicit group by and we must image it implicit group by

with a as (
select 123 acct,to_date('1/12/2008','mm/dd/yyyy') date_changed from dual union all
select 456,to_date('8/25/2008','mm/dd/yyyy') from dual),
b as (
select 123 acct,to_date('11/30/2007','mm/dd/yyyy') date_upload,620 score from dual union all
select 123,to_date('12/31/2007','mm/dd/yyyy'),650 from dual union all
select 123,to_date('01/31/2008','mm/dd/yyyy'),712 from dual union all
select 123,to_date('02/29/2008','mm/dd/yyyy'),800 from dual union all
select 456,to_date('05/31/2008','mm/dd/yyyy'),520 from dual union all
select 456,to_date('06/30/2008','mm/dd/yyyy'),630 from dual union all
select 456,to_date('07/31/2008','mm/dd/yyyy'),680 from dual union all
select 456,to_date('08/31/2008','mm/dd/yyyy'),710 from dual union all
select 456,to_date('09/30/2008','mm/dd/yyyy'),780 from dual union all
select 456,to_date('10/31/2008','mm/dd/yyyy'),786 from dual union all
select 456,to_date('11/30/2008','mm/dd/yyyy'),823 from dual)
select Acct,Score2MonthPrior,Score1MonthPrior
from (select a.acct,b.score,
       extract( year from a.date_changed)*12
      +extract(month from a.date_changed)
      -extract( year from b.date_upload)*12
      -extract(month from b.date_upload) as diff
      from a join b
        on a.acct = b.acct)
Pivot (max(score) for diff in(2 as Score2MonthPrior,
                              1 as Score1MonthPrior));

ACCT  Score2MonthPrior  Score1MonthPrior
----  ----------------  ----------------
 123               620               650
 456               630               680

Aketi Jyuuzou

I like this query more than my previous :8}

select acct,
max(decode(diff,2,score)) as Score2MonthPrior
max(decode(diff,1,score)) as Score1MonthPrior,
from (select a.acct,b.score,
      months_between(trunc(a.date_changed,'mm'),
                     trunc(b.date_upload,'mm')) as diff
        from a join b
          on a.acct = b.acct)
 where diff in(1,2)
group by acct;

acct  Score2MonthPrior  Score1MonthPrior
----  ----------------  ----------------
 123               620               650
 456               630               680

1 person found this helpful

1 - 14

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 2 2016

Added on Oct 4 2016

#performance

4 comments

716 views

SQL Developer Data Modeler

Data Modeler extreme slowdown loading design

Comments

Post Details