[Theforum] [DesDev] RE: [Content] Article cleanup issue

Matt Liotta mliotta at r337.com
Wed Jul 24 09:43:00 CDT 2002


Perfect, this is just what we needed. As you can see, the sub-query is
the major problem and the joins don't help. The next question that comes
to mind is what happens if we break the query up. For example, instead
of joining three tables in one query, what happens if we use three
queries? Additionally, it would be worth trying to move the sub-query
into its own separate query for each article on the front page.

Of course if using separate queries helps, then you may well run into an
issue with CF's query caching. You will want to check the CF
Administrator to see how many cached queries can be stored in memory at
any given time.

If you can get the information about the suggested changes from above; I
think we should almost be ready to try a solution. I'm still thinking
that a materialized view is going to be that solution, but I would
rather it be done on the CF side of things since everyone here seems to
be more experienced with CF than Oracle and it allows you to change the
RDBMS to another DB without fear as Oracle is the only RDBMS that has a
feature like materialized views. SQL Server comes close, but doesn't
support aggregate functions.

Matt Liotta
President & CEO
Montara Software, Inc.
http://www.montarasoftware.com/
V: 415-577-8070
F: 415-341-8906
P: 4155778070 at messaging.sprintpcs.com

> -----Original Message-----
> From: .jeff [mailto:jeff at members.evolt.org]
> Sent: Wednesday, July 24, 2002 12:31 AM
> To: Matt Liotta
> Subject: RE: [Theforum] [DesDev] RE: [Content] Article cleanup issue
>
> matt,
>
> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> > From: Matt Liotta [mailto:mliotta at r337.com]
> >
> > As stated in another email, we need to determine what
> > part of the query is slowing the whole thing down. I'm
> > guess it is either the sub-query or one of the two
> > joins.
> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
>
> if it would help at all i can give you the schema for the 3 tables
> involved.
>
> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> > Could you please try different permutations of the query
> > with those different parts removed? Once we know what
> > the culprit is we can fix it.
> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
>
> http://members.evolt.org/jeff/weo_query_research.cfm
>
> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> > And yes, I do have extensive experience scaling
> > ColdFusion with a variety of databases including Oracle.
> ><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
>
> i thought so.
>
> thanks for whatever help you can give with this.  it appears as though
we
> may be moving away from oracle in the near future due to not being
able to
> prove we're compliant with licenses.  but, not a big deal.  i'd prefer
to
> be on a database server that more people are likely to be able to work
> with.
>
> .jeff
>
> http://evolt.org/
> jeff at members.evolt.org
> http://members.evolt.org/jeff/




More information about the theforum mailing list