Re: suggestion to improve a query

From: Keith Boulton <boulke_at_globalnet.co.uk>
Date: 2000/03/26
Message-ID: <38dde2e7.3110052@read.news.globalnet.co.uk>#1/1

On Thu, 23 Mar 2000 13:49:19 +0100, "Tisan Gabriel" <de_at_dorner.at> wrote:

>Your idea :
>
>SQL> select * from kundeWdatum a
> 2 where ( a.kunde_nr, a.kunde_datum ) in
> 3 ( select b.kunde_nr, min(b.kunde_datum)
> 4 from kundeWdatum b
> 5 where b.kunde_datum > '15-Aug-1999'
> 6 group by b.kunde_nr
> 7 );
>
>200000 Zeilen ausgewõhlt.
>

I believe the fastest way to do this (i.e. get the first row for each customer following a given date) is to use a stored procedure to populate a temporary table. The pseudocode is something like

create table temp with structure required.

for each row in ( select * from table order by customer, date where date > <input date>)

	on change of customer
 		insert into temp values ....

end loop

Then select from the temporary table. This process requires only a single scan of the input table, followed by a scan of the temp table. In oracle 8 you can use pl/sql bulk processing to insert a block of records at a time into the temp table to improve performance.

If I have time, I will look at implementing this.

Also, your time to fetch the data from the server to vb is way too long.   Received on Sun Mar 26 2000 - 00:00:00 CST