LS

I have a table with 100.000+ records. Each record is a departure date of a
boat (so the combination boatID and departdate are unique) In a listbox I
want to show the possible departure months (by year)
This is what I have (departdate is indexed):

SELECT DISTINCT EXTRACT (YEAR FROM departdate) AS departyear,
EXTRACT (MONTH FROM departdate) AS departmonth FROM vh_
WHERE id_seasontype = :st
AND byInternet = TRUE
ORDER BY departyear,  departmonth

It is taking some time (requests are by browser to a intrawebserver with
kbmmw middleware)? Is there a way to speed up this query?

Regards, Harry
dbIsam 3.3, kbmMW 2.5+, IW7.18

Harry,

<< It is taking some time (requests are by browser to a intrawebserver with
kbmmw middleware)? Is there a way to speed up this query? >>

Unfortunately 3.x was a bit slow with DISTINCT or GROUP BY queries due to
how they were processed.  How many records are being returned in the query
result set ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

8 (1 year 8 months) records are in the resultset.

Regards Harry

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:89418710-0977-46A4-8897-FD5FE8FD9EDF@news.elevatesoft.com...
> Harry,
>
> << It is taking some time (requests are by browser to a intrawebserver
with
> kbmmw middleware)? Is there a way to speed up this query? >>
>
> Unfortunately 3.x was a bit slow with DISTINCT or GROUP BY queries due to
> how they were processed.  How many records are being returned in the query
> result set ?
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Harry,

<< 8 (1 year 8 months) records are in the resultset. >>

I'm sorry, I asked the wrong question.  What I meant to ask was how many
records are in the result set without the DISTINCT included ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Depending on the values for the fields in the where clause. Could be the
whole table, so 100.000 + if the conditions are true. In the case where I
tried it: 78.000 records.

Regards, Harry


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:5F174937-289C-4D3F-A325-5B2A151B75B0@news.elevatesoft.com...
> Harry,
>
> << 8 (1 year 8 months) records are in the resultset. >>
>
> I'm sorry, I asked the wrong question.  What I meant to ask was how many
> records are in the result set without the DISTINCT included ?
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Harry,

<< Depending on the values for the fields in the where clause. Could be the
whole table, so 100.000 + if the conditions are true. In the case where I
tried it: 78.000 records. >>

That's the reason - 3.x will dump out 78,000 records and then remove the
dups via a sort.  4.x doesn't do this, and is much faster.

--
Tim Young
Elevate Software
www.elevatesoft.com