firebird database, any way to speed this up?

This table "type143" is in the firebird database The table has about
3.5 
million records.

select substring(phone from 1 for 3) as AC, count(*) as cnt from type143 
group by substring(phone from 1 for 3)

In DBF world, this takes two steps because the BDE won't allow for inline 
sbustring group by.

So I tried going to FB expecting a huge increase in speed but I am not 
seeing it yet?

Thanks in advance,

Greg Hill

I forgot to say that the query is taking about 2 full minutes right now on 
my P4, 500mb ram

gh
"Greg Hill" <ghill123@hotmail.com> wrote in message 
news:nGqR3lsNIHA.328@news-server...
> This table "type143" is in the firebird database The table has
about 3.5 
> million records.
>
> select substring(phone from 1 for 3) as AC, count(*) as cnt from type143 
> group by substring(phone from 1 for 3)
>
> In DBF world, this takes two steps because the BDE won't allow for inline 
> sbustring group by.
>
> So I tried going to FB expecting a huge increase in speed but I am not 
> seeing it yet?
>
> Thanks in advance,
>
> Greg Hill
>
>
>
> 

Just added a new column for AREACODE only and updated that column (6 
minutes) later, I tried the group by statement on the newly created column 
and got decreased performance.
Just indexed the column and I believe I am getting even worse results in 
performance.
So needless to say I am discouraged at this point, but leaving some hope 
that I can do something to speed things up.

By the way I am working directly in the IBExpert with these test.

Greg Hill



"Greg Hill" <ghill123@hotmail.com> wrote in message 
news:nGqR3lsNIHA.328@news-server...
> This table "type143" is in the firebird database The table has
about 3.5 
> million records.
>
> select substring(phone from 1 for 3) as AC, count(*) as cnt from type143 
> group by substring(phone from 1 for 3)
>
> In DBF world, this takes two steps because the BDE won't allow for inline 
> sbustring group by.
>
> So I tried going to FB expecting a huge increase in speed but I am not 
> seeing it yet?
>
> Thanks in advance,
>
> Greg Hill
>
>
>
> 

"*Lysander*" <nobody@nowhere.com> wrote in message 
news:qsB7QawNIHA.1444@news-server...
> Greg Hill schrieb:
>> Just added a new column for AREACODE only and updated that column (6 
>> minutes) later, I tried the group by statement on the newly created 
>> column and got decreased performance.
>
> putting the areacode into a seperate field is a good idea.
> indexing it was a bad idea. Too many dupes.
>
> COUNTing 3.5 million records is a VERY bad idea with any sqlserver.
>
> selecting 3.5 million records at once without any filtering
>  is a VERY bad idea with ANY sqlserver.
>
> No human being can work with 3.5 million records at once.
> There must be any criteria to divide it into portions to work with.
> Apply this criteria to the select-statement.

Ok, I was under the impression that a DBMS could in fact handle extremely 
large tables gracefully and effieciently compared to DBF's
I have noticed extreme improvements with FireBird regarding relationships 
with multiple tables some large some small

I could of course break it up by areacodes or something but I need to 
consider how the table is populated and how much work would be involved with 
maintaining hundreds a large amount of tables.

I am also curious of how or what people like google use.  They return search 
results of 5,000,000 in less than a second.  Are these precounted selects?

Greg Hill

Greg Hill schrieb:
> Just added a new column for AREACODE only and updated that column (6 
> minutes) later, I tried the group by statement on the newly created column

> and got decreased performance.

putting the areacode into a seperate field is a good idea.
indexing it was a bad idea. Too many dupes.

COUNTing 3.5 million records is a VERY bad idea with any sqlserver.

selecting 3.5 million records at once without any filtering
  is a VERY bad idea with ANY sqlserver.

No human being can work with 3.5 million records at once.
There must be any criteria to divide it into portions to work with.