I don't know if this is what you were looking for, but here it is:
The thing is, I know there is plenty I can do to make these queries run
faster - it is just a general question: why does CF just hang for all the
other users when this query is executed from a CF page, and not when
executed from the db itself?
thanks, by the way, for your suggestions so far. You sound as though you
have experience of CF with Postgres on a Lunux box?
SR
EXPLAIN ANALYZE
SELECT count(*) as count,
ROUND(sum(order_value(h.orderid))/100.0,2) as value
FROM statusstats3 h
INNER JOIN orders o ON (o.orderid = h.orderid)
WHERE h.time::date BETWEEN '2002-10-01' AND '2002-10-31'
AND h.tostatus = 'sales_approved'
AND o.status != 'order_cancelled'
AND o.existing = false
NOTICE: QUERY PLAN:
Aggregate (cost=5459.05..5459.05 rows=1 width=8) (actual
time=52296.17..52296.17 rows=1 loops=1)
-> Nested Loop (cost=5453.01..5459.04 rows=1 width=8) (actual
time=39563.71..39704.95 rows=571 loops=1)
-> Subquery Scan h (cost=5453.01..5453.02 rows=1 width=36) (actual
time=39563.47..39574.80 rows=742 loops=1)
-> Unique (cost=5453.01..5453.02 rows=1 width=36) (actual
time=39563.46..39570.71 rows=742 loops=1)
-> Sort (cost=5453.01..5453.01 rows=1 width=36)
(actual
time=39563.46..39565.25 rows=849 loops=1)
-> Seq Scan on orderhistory (cost=0.00..5453.00
rows=1 width=36) (actual time=9998.10..39559.34 rows=849 loops=1)
-> Index Scan using orders_pkey on orders o (cost=0.00..6.01
rows=1
width=4) (actual time=0.15..0.16 rows=1 loops=742)
Total runtime: 52296.58 msec
messina=# select count(*) from orders;
count
-------
4914
(1 row)
messina=# select count(*) from orderatoms;
count
-------
9222
(1 row)
messina=# select count(*) from statusstats3;
count
-------
21452
(1 row)
Statusstats3 is a view of the orderhistory table, hence the reference to
orderhistory in the explain above.
messina=#
messina=# \d statusstats3
View "statusstats3"
Column | Type | Modifiers
----------+-----------------------------+-----------
orderid | integer |
tostatus | character varying |
time | timestamp without time zone |
View definition: SELECT DISTINCT orderhistory.orderid,
get_status((orderhistory.changes)::"varchar") AS tostatus,
min_status_time(orderhistory.orderid,
get_status((orderhistory.changes)::"varchar"))
AS "time" FROM orderhistory WHERE ((orderhistory.tablename =
'orders'::"varchar") AND (orderhistory.changes ~~ '%ST:%,%'::text)) ORDER BY
orderhistory.orderid, get_status((orderhistory.changes)::"varchar"),
min_status_time(orderhistory.orderid,
get_status((orderhistory.changes)::"varchar"));
messina=# \d orderhistory
Table "orderhistory"
Column | Type | Modifiers
-----------+-----------------------------+----------------------------------
-
tablename | character varying(32) | not null
orderid | integer | not null
time | timestamp without time zone | not null default now()
who | character varying(32) | not null default "current_user"()
action | character(1) | not null
changes | text |
Indexes: orderhistory_key
Check constraints: "orderhistory_action" ((("action" = 'I'::bpchar) OR
("action" = 'U'::bpchar)) OR ("action" = 'D'::bpchar))
-----------------------------
Stephen Richards
020 7903 3226
------------------------------
----- Original Message -----
From: "Jochem van Dieten" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, December 05, 2002 4:50 PM
Subject: Re: Fw: Is Coldfusion REALLY multi threaded?
> Stephen Richards wrote:
> > ----- Original Message -----
> > From: "Jochem van Dieten" <[EMAIL PROTECTED]>
> > To: "CF-Talk" <[EMAIL PROTECTED]>
> > Sent: Thursday, December 05, 2002 4:14 PM
> > Subject: Re: Is Coldfusion REALLY multi threaded?
> >
> >>And how many of them are idle?
> >
> > how do I tell which ones are active?
>
> top
>
>
> > Yep, I agree with you about the query, and we're taking action on that
>
> Send me the EXPLAIN ANALYZE output together with table metrics and I
> will have a look.
>
> Jochem
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
