Re: slow query execution

[Trigve Siver <trigves(at)yahoo(dot)com>]

Thanks for reply,

This solution looks promising. I'll look at it and test it and let you know.

Thanks once more

Trigve

----- Original Message ----
From: Richard Huxton <dev(at)archonet(dot)com>
To: Trigve Siver <trigves(at)yahoo(dot)com>
Sent: Thursday, May 31, 2007 10:33:40 AM
Subject: Re: [SQL] slow query execution

Trigve Siver wrote:
> Hi, thanks for reply
> 
> No, I'm working with c++ and libpqxx (pgsql c++ binding). I'm using
> Win32 Listview control with LS_OWNERDATA style. I can use std::map to
> map row_number to ID field but then I must fetch all records from
> that table. This could be ineffective when table has about 10.000+
> records and user want to view/search only first 100 records.

So - you want something like:

The user runs a query ("all blue things") and that gives a list of 
results. They can then filter those results further ("shape=round") and 
you want to highlight those elements that match.

You either can't or don't want to filter in the application, rather you 
would like to run this as two queries but need to match up results from 
the second query with the first query (your list).

Suggestion:

For the first query, make sure you have the relevant primary key columns 
in your query and do:
   CREATE TEMPORARY TABLE my_results AS SELECT ...
Then, you can join against that table in the second query. The temporary 
table will exist until you disconnect - see CREATE TABLE for details.

-- 
   Richard Huxton
   Archonet Ltd





       
____________________________________________________________________________________Ready for the edge of your seat? 
Check out tonight's top picks on Yahoo! TV. 
http://tv.yahoo.com/