Materialize view.

#1

Hi , I am new to MYSQL so here is a trivial question for you advanced users:

I need to materialize a view each time the main table is filled with records
wich happens once a day. I know that MYSQL is not able to materialize views.

So, my idea is to:
- Set up something like a trigger after the event of the main table (the one
that de view depends on), is truncated and then refilled with new records.

-This trigger should:
- delete old materialized view, (delete a table)
- and then create a new table (materialized view) with the view
structure filling it up with records.

I'd appreciate any help.

Thanks in advance.

#2

On 11/6/2011 7:05 AM, Centrino wrote:

Quote:

Hi , I am new to MYSQL so here is a trivial question for you advanced users:

I need to materialize a view each time the main table is filled with records
wich happens once a day. I know that MYSQL is not able to materialize views.

So, my idea is to:
- Set up something like a trigger after the event of the main table (the one
that de view depends on), is truncated and then refilled with new records.

-This trigger should:
- delete old materialized view, (delete a table)
- and then create a new table (materialized view) with the view
structure filling it up with records.

I'd appreciate any help.

Thanks in advance.

First question - Why do you think you need to materialize the view?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#3

Because the view is an extremely complex query that takes about one hour to
be solved.

Several processes and users use that view, and so, each time the view is
queryed in order to obtain data in different formats one hour is lost. And
so, materializing the view in one only query per day should speed up the
total computing time since the main table is only updated once per day.

"Jerry Stuckle" <jstucklex (AT) attglobal (DOT) net> escribió en el mensaje
news:j95uoe$a4v$1 (AT) dont-email (DOT) me...

Quote:

On 11/6/2011 7:05 AM, Centrino wrote:
Hi , I am new to MYSQL so here is a trivial question for you advanced
users:

I need to materialize a view each time the main table is filled with
records
wich happens once a day. I know that MYSQL is not able to materialize
views.

So, my idea is to:
- Set up something like a trigger after the event of the main table (the
one
that de view depends on), is truncated and then refilled with new
records.

-This trigger should:
- delete old materialized view, (delete a table)
- and then create a new table (materialized view) with the view
structure filling it up with records.

I'd appreciate any help.

Thanks in advance.

First question - Why do you think you need to materialize the view?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#4

On 11/6/2011 12:24 PM, Centrino wrote:

Quote:

"Jerry Stuckle"<jstucklex (AT) attglobal (DOT) net> escribió en el mensaje
news:j95uoe$a4v$1 (AT) dont-email (DOT) me...
On 11/6/2011 7:05 AM, Centrino wrote:
Hi , I am new to MYSQL so here is a trivial question for you advanced
users:

I need to materialize a view each time the main table is filled with
records
wich happens once a day. I know that MYSQL is not able to materialize
views.

So, my idea is to:
- Set up something like a trigger after the event of the main table (the
one
that de view depends on), is truncated and then refilled with new
records.

-This trigger should:
- delete old materialized view, (delete a table)
- and then create a new table (materialized view) with the view
structure filling it up with records.

I'd appreciate any help.

Thanks in advance.

First question - Why do you think you need to materialize the view?

Because the view is an extremely complex query that takes about one hour to
be solved.

Several processes and users use that view, and so, each time the view is
queryed in order to obtain data in different formats one hour is lost. And
so, materializing the view in one only query per day should speed up the
total computing time since the main table is only updated once per day.

top posting fixed

Second question - why does it take so long? Even with millions of rows
across multiple tables I've never had a query take that long. I think
you would be better off optimizing things.

However, since you are hell bent on such a stupid procedure, just create
the table(s) as part of your update job. A trigger is not the right
tool for this.

P.S. Pleade don't top post.g

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#5

The view query, runs perfectly well, as spected in design, No performance
problems nor indexes problems. Key efficiency is 100%. The unswer is simple:
complexity by design.

"Jerry Stuckle" <jstucklex (AT) attglobal (DOT) net> escribió en el mensaje
news:j96hfk$3rd$1 (AT) dont-email (DOT) me...

Quote:

On 11/6/2011 12:24 PM, Centrino wrote:
"Jerry Stuckle"<jstucklex (AT) attglobal (DOT) net> escribió en el mensaje
news:j95uoe$a4v$1 (AT) dont-email (DOT) me...
On 11/6/2011 7:05 AM, Centrino wrote:
Hi , I am new to MYSQL so here is a trivial question for you advanced
users:

I need to materialize a view each time the main table is filled with
records
wich happens once a day. I know that MYSQL is not able to materialize
views.

So, my idea is to:
- Set up something like a trigger after the event of the main table
(the
one
that de view depends on), is truncated and then refilled with new
records.

-This trigger should:
- delete old materialized view, (delete a table)
- and then create a new table (materialized view) with the
view
structure filling it up with records.

I'd appreciate any help.

Thanks in advance.

First question - Why do you think you need to materialize the view?

Because the view is an extremely complex query that takes about one hour
to
be solved.

Several processes and users use that view, and so, each time the view is
queryed in order to obtain data in different formats one hour is lost.
And
so, materializing the view in one only query per day should speed up the
total computing time since the main table is only updated once per day.

top posting fixed

Second question - why does it take so long? Even with millions of rows
across multiple tables I've never had a query take that long. I think you
would be better off optimizing things.

However, since you are hell bent on such a stupid procedure, just create
the table(s) as part of your update job. A trigger is not the right tool
for this.

P.S. Pleade don't top post.g

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#6

On 11/6/2011 12:57 PM, Centrino wrote:

Quote:

"Jerry Stuckle"<jstucklex (AT) attglobal (DOT) net> escribió en el mensaje
news:j96hfk$3rd$1 (AT) dont-email (DOT) me...
On 11/6/2011 12:24 PM, Centrino wrote:
"Jerry Stuckle"<jstucklex (AT) attglobal (DOT) net> escribió en el mensaje
news:j95uoe$a4v$1 (AT) dont-email (DOT) me...
On 11/6/2011 7:05 AM, Centrino wrote:
Hi , I am new to MYSQL so here is a trivial question for you advanced
users:

I need to materialize a view each time the main table is filled with
records
wich happens once a day. I know that MYSQL is not able to materialize
views.

So, my idea is to:
- Set up something like a trigger after the event of the main table
(the
one
that de view depends on), is truncated and then refilled with new
records.

-This trigger should:
- delete old materialized view, (delete a table)
- and then create a new table (materialized view) with the
view
structure filling it up with records.

I'd appreciate any help.

Thanks in advance.

First question - Why do you think you need to materialize the view?

Because the view is an extremely complex query that takes about one hour
to
be solved.

Several processes and users use that view, and so, each time the view is
queryed in order to obtain data in different formats one hour is lost.
And
so, materializing the view in one only query per day should speed up the
total computing time since the main table is only updated once per day.

top posting fixed

Second question - why does it take so long? Even with millions of rows
across multiple tables I've never had a query take that long. I think you
would be better off optimizing things.

However, since you are hell bent on such a stupid procedure, just create
the table(s) as part of your update job. A trigger is not the right tool
for this.

P.S. Pleade don't top post.g

The view query, runs perfectly well, as spected in design, No
performance problems nor indexes problems. Key efficiency is 100%.
The unswer is simple: complexity by design.

<top posting fixed again>

No, the answer is even simpler. You're simply unable to fix your
performance problems. You read somewhere about materialized views and
figure that's a way around your problem (never mind the other problems
it will create). After all, it's much simpler than finding the real
cause of the problem and solving it.

But I would expect nothing more from anyone who insists on top posting
despite a polite request to follow netiquette.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#7

But who the hell are you to figure out what are our team programing skills
or efficency !! ??

We are new to Mysql that's all. And not preciselly new to Db2 programming.

Remember that if IBM, the inventor of Structured Query Language;, yes, SQL
and relational algebrae, puts in the hands of programmers the materialized
views, investing time and money developing them like Oracle did too, it is
becouse they are very useul in real life.

So please, save your insults to someone asking for ideas.

"Jerry Stuckle" <jstucklex (AT) attglobal (DOT) net> escribió en el mensaje
news:j96omq$nci$1 (AT) dont-email (DOT) me...

Quote:

On 11/6/2011 12:57 PM, Centrino wrote:
"Jerry Stuckle"<jstucklex (AT) attglobal (DOT) net> escribió en el mensaje
news:j96hfk$3rd$1 (AT) dont-email (DOT) me...
On 11/6/2011 12:24 PM, Centrino wrote:
"Jerry Stuckle"<jstucklex (AT) attglobal (DOT) net> escribió en el mensaje
news:j95uoe$a4v$1 (AT) dont-email (DOT) me...
On 11/6/2011 7:05 AM, Centrino wrote:
Hi , I am new to MYSQL so here is a trivial question for you advanced
users:

I need to materialize a view each time the main table is filled with
records
wich happens once a day. I know that MYSQL is not able to materialize
views.

So, my idea is to:
- Set up something like a trigger after the event of the main table
(the
one
that de view depends on), is truncated and then refilled with new
records.

-This trigger should:
- delete old materialized view, (delete a table)
- and then create a new table (materialized view) with the
view
structure filling it up with records.

I'd appreciate any help.

Thanks in advance.

First question - Why do you think you need to materialize the view?

Because the view is an extremely complex query that takes about one
hour
to
be solved.

Several processes and users use that view, and so, each time the view
is
queryed in order to obtain data in different formats one hour is lost.
And
so, materializing the view in one only query per day should speed up
the
total computing time since the main table is only updated once per day.

top posting fixed

Second question - why does it take so long? Even with millions of rows
across multiple tables I've never had a query take that long. I think
you
would be better off optimizing things.

However, since you are hell bent on such a stupid procedure, just create
the table(s) as part of your update job. A trigger is not the right
tool
for this.

P.S. Pleade don't top post.g

The view query, runs perfectly well, as spected in design, No
performance problems nor indexes problems. Key efficiency is 100%.
The unswer is simple: complexity by design.

top posting fixed again

No, the answer is even simpler. You're simply unable to fix your
performance problems. You read somewhere about materialized views and
figure that's a way around your problem (never mind the other problems it
will create). After all, it's much simpler than finding the real cause of
the problem and solving it.

But I would expect nothing more from anyone who insists on top posting
despite a polite request to follow netiquette.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#8

On 06-11-2011 21:48, Centrino wrote:

Quote:

So please, save your insults to someone asking for ideas.

some idea (at least 1) was given,

But you are ignoring is

--
Luuk

#9

On 11/6/2011 3:48 PM, Centrino wrote:

Quote:

But who the hell are you to figure out what are our team programing skills
or efficency !! ??

We are new to Mysql that's all. And not preciselly new to Db2 programming.

Remember that if IBM, the inventor of Structured Query Language;, yes, SQL
and relational algebrae, puts in the hands of programmers the materialized
views, investing time and money developing them like Oracle did too, it is
becouse they are very useul in real life.

So please, save your insults to someone asking for ideas.

Over 40 years of programming experience and I can spot someone like you
a mile away. And BTW - I've been working with RDBMSs since DB2 was
released internally in IBM (where I worked at the time).

The repeated top posting is just further confirmation of the fact.

As I said - you can fix your problem, or you can try to bypass it and
cause more problems. You chose to do the latter.

And whether you're new to MySQL or not is immaterial. You can be stupid
with any database.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#10

Centrino:

Quote:

I need to materialize a view each time the main table is filled with
records wich happens once a day. I know that MYSQL is not able to
materialize views.

How did you set up this daily update? Is this a cron job, directly
calling the mysql client? Or do you have some type of application
script running once a day?

Quote:

So, my idea is to:
- Set up something like a trigger after the event of the main table
(the one that de view depends on), is truncated and then refilled
with new records.

-This trigger should:
- delete old materialized view, (delete a table)
- and then create a new table (materialized view) with the view
structure filling it up with records.

If your update job consists of a large number of insert statements, I
would probably not try and use triggers. You would rather first confirm
that the update job was completed without failure, and then start a new
step in the update job to create the view table.

In that case, you might have an issue, however, if you want to make
sure that nobody commits any updates between completion of the first
step and completion of the second step.

--
Erick