Home » RDBMS Server » Performance Tuning » bad view - slow reports
| bad view - slow reports [message #157859] |
Mon, 06 February 2006 14:28  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I have an old bad setup of a bunch of reports running off a regular views. With the table growth, the reports are running slower and slower. The question is that I cannot change the reports - they'll run the way they are now for at least for 2-3 months till the development team puts the new one on production. In the same time, may be there's something I can do. I suggested replacing the views with Materialized views as in the past that have saved us a lot of time. But the management did not agree.
What else I can do the improve the views. Here is one of them:
CREATE VIEW DATA1 AS
select '1' as ord, s.test_id, s. ent1 as ent_id, 'Name' as data_type,
n.last_name || ', ' || n.first_name || ' ' || n.mid_name || ' ' || n.name_gen as data_value
from name n, s_con s
where s.conf_ent1 = n.ent_id
union
select '2' as ord, s.s_test_id, s.conf_ent1 as ent_id, 'Address' as data_type,
a.addr1 || ' ' || addr2 || ' ' || addr3 || ' ' || city || ' ' || state_code || ' ' || country_code as data_value
from s_con s, address a
where s.conf_ent1 = a.ent_id
union
select '3' as ord, s.s_test_id, s.conf_ent1 as ent_id,
(select t.num_desc from num_type t where n.num_type_id = t.num_type_id) as data_type, n.num_value as data_value
from s_con s, nums n
where s.conf_ent1 = n.ent_id
union
select '5' as ord, s.s_test_id, s.conf_ent1 as ent_id,
'Email' as data_type,e.email as data_value
from s_con s, emaile
where s.conf_ent1 = e.ent_id;
It uses the indexes on the name, addresss, e-mail tables but always does a FTS on s_con as it selects almost all of the tables.
Thanks a lot for the help, MJ
|
|
|
|
| Re: bad view - slow reports [message #157871 is a reply to message #157859] |
Mon, 06 February 2006 16:39  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Ugh, can I just say that this data model reeks to high heaven.
The only real improvement I could suggest (other than designing the data model from scratch) would be to use UNION ALL instead of UNION.CREATE OR REPLACE VIEW data1 (
ord
, test_id
, ent_id
, data_type
, data_value
)
AS
SELECT x.ord
, s.test_id
, DECODE(x.ord
, '1', s.ent1
, x.ent_id) ent_id
, x.data_type
, x.data_value
FROM (SELECT '1' ord
, n.ent_id ent_id
, 'Name' data_type
, n.last_name
|| ', '
|| n.first_name
|| DECODE(n.mid_name
, NULL, NULL
, ' ' || n.mid_name)
|| DECODE(n.name_gen
, NULL, NULL
, ' ' || n.name_gen) data_value
FROM name n
UNION ALL
SELECT '2' ord
, a.ent_id ent_id
, 'Address' data_type
, a.addr1
|| DECODE(a.addr2
, NULL, NULL
, a.addr2 || ' ')
|| DECODE(a.addr3
, NULL, NULL
, a.addr3 || ' ')
|| a.city
|| ' '
|| a.state_code
|| DECODE(a.country_code
, NULL, NULL
, ' ' || a.country_code) data_value
FROM address a
UNION ALL
SELECT '3' ord
, nu.ent_id ent_id
, t.num_desc data_type
, nu.num_value data_value
FROM nums nu
, num_type t
WHERE nu.num_type_id = t.num_type_id
UNION ALL
SELECT '5' ord
, e.ent_id ent_id
, 'Email' data_type
, e.email data_value
FROM email e) x
, s_con s
WHERE s.conf_ent1 = x.ent_id |
|
|
|
| Re: bad view - slow reports [message #157952 is a reply to message #157859] |
Tue, 07 February 2006 08:11 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Just gave this a quick scan, but couldn't this be rewritten as a "single" query with all tables in the from clause and outer joins if needed because some s_con doesn't have a matching name, email, or address? Don't know if it would be any better or not, but might be another option to try if the rewrite is doable in your situation.
And is this view trying to take a perfectly nice normalized set of table and turn them into one of those entity attribute value sorts of non- data model types of data models? Ugh is right.
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 14:31:46 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
