Thanks for the tips Jochem. But as I say, I know we can tidy the queries up and make them more efficient, but what I don;t know how to do is make CF not wait for one query to finish before starting to execute another. No matter what I do to the queries, unless that is fixed we're storing up trouble for the future as usage increases.
One suggestion here was that it was the Unix Postgres ODBC driver that was single threaded, and therefore the bottleneck. Do you have any views on that? Can anyone say for sure that this problem would be solvd if we bought the Enterprise edition which has, I beleive, a native postgres driver? ----------------------------- Stephen Richards 020 7903 3226 ------------------------------ ----- Original Message ----- From: "Jochem van Dieten" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, December 05, 2002 10:34 PM Subject: Re: Fw: Is Coldfusion REALLY multi threaded? > Jochem van Dieten wrote: > > > > I am betting that it is the LIKE that is killing you here, it is pretty > > much impossible to index one using a normal index. Which is why you need > > a partial index on the predefined search pattern: > > CREATE INDEX test_idx ON orderhistory USING HASH (changes) > > WHERE (changes ~~ '%ST:%,%'::text); > > Whether it works depends on the cardinality of the table and the > > expected resultset. Just remember that you need to create partial > > indexes for each query pattern. > > Correction: > CREATE INDEX t1_idx ON orderhistory (get_status(orderhistory.changes)) > WHERE (changes ~~ '%ST:%,%'::text); > > Some other thing: > - remove the order by from the view, ordering is no longer guaranteed > after the next join anyway > - use count(1) instead of count(*) so toast tables do not have to be fetched > > 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 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
