Hi again, Frank!

I don't believe a query on a table with a mere 1000 records to take more
than 20 seconds, but your "table" starts with "V_" so I assume it is a
view rather than a table and that the table(s) the view is based on has
more than 1000 records?

It should be possible to improve marginally on my suggestion, i.e. in
the main select you may benefit (depending on the proportion of records
matching the criteria) from an ascending index on INV_DATE, whereas the
same index will bog down the subselect if an index on id_customer is far
more selective. Hence, to speed up the select, I would add '+0' as follows:

select count(distinct v1.auto_id_customer)
FROM V_TRANSACTIONS v1
WHERE v1.INV_DATE > '01/01/2006'
and (select sum(v2.UNIT_QTY*v2.UNIT_PRICE)
FROM V_TRANSACTIONS v2
where v2.auto_id_customer = v1.auto_id_customer
and v2.INV_DATE+0 > '01/01/2006') < 100000

SUM, COUNT etc. are generally 'slow' operations when they have to sum a
lot of records. Another method for making these kind of requests more
feasible, is to have one summary table and one 'new transactions' table.

Say you had the following sales:

CustID Qty Price Date Summarized
1 3 40 01/15/2006 0
2 4 60 02/27/2006 0
1 1 2000 03/01/2006 0
1 10 10 05/22/2006 0
2 8 50 05/23/2006 0

Then you could at regular intervals (e.g. once a week/month) run
something into the summary table (and update the summarized column):

UPDATE summary s
SET s.Sales2006 = s.Sales2006 +
select sum(v.UNIT_QTY*v.UNIT_PRICE)
FROM V_TRANSACTIONS v
where v.auto_id_customer = s.CustID
and v.INV_DATE between '01/01/2006' and '06/30/2006')
where exists(select *
FROM V_TRANSACTIONS v2
where v2.auto_id_customer = s.CustID
and v2.INV_DATE between '01/01/2006' and '06/30/2006')

That would mean your select would be simplified to look up this table +
add records made to the transactions table since the last time you
updated the summary table.

As for SELECT FROM (SELECT ...), that is a new feature of in Firebird
2.0, but Firebird 2.0 isn't yet ready for production (it is in 'release
candidate' stage) and I don't know if that would be any quicker than the
'slow count' (it may well be, I just don't know).

HTH,
Set

Frank & Set wrote:

>> I think something like
>>
>> select count(distinct v1.auto_id_customer)
>> FROM V_TRANSACTIONS v1
>> WHERE v1.INV_DATE > '01/01/2006'
>> and (select sum(v2.UNIT_QTY*v2.UNIT_PRICE)
>> FROM V_TRANSACTIONS v2
>> where v2.auto_id_customer = v1.auto_id_customer
>> and v2.INV_DATE > '01/01/2006') < 100000
>>
>> may be what you are looking for. Alternatively, the RowsSelected
>> property of IBO returns the number of rows selected once you get to
>> the end of your result set (at least).
>
> I tried this one already, but on a table with approx. 1000 records
> it take more then 20 secs. - not really handy ....
>
> Seems like Firebird does not support a
> FROM (SELECT ....) or any other kind of virtual tables
> besides of views - is this correct ?
>
>> Though why is this number interesting to know?
> I need a statistic of how many customers we have
> within certain turnover-ranges
>
>> Oh, and next time - ask such a question on the firebird-support
> list
> OK, noted
>
> THX