Em 26/7/2011 13:48, Leyne, Sean escreveu:

> What happens if you try:
>
>> (B1) person query - slow (11-12 secs, ~2000 rows)
>> select
>> ca.id,cn.firstname,cn.lastname,cn.email1,cn.dateofbirth,
>> ca.kind,ca.category,ca.status,cn.name
>> from extendedperson ca
>> join person cn on ca.personid=cn.id
>> where (upper(cn.name) starting with 'S')
> Select
> ca.id,cn.firstname,cn.lastname,cn.email1,cn.dateofbirth, ca.kind,ca.category,ca.status,cn.name
> from person cn
> JOIN extendedperson ca on ca.personid=cn.id
> where (upper(cn.name) starting with 'S')
>
> Do you have an index on cn.name?
>
>
> Sean
>

Looking at the provided metadata I can see this index:

CREATE INDEX I_PERSON_NAME ON PERSON COMPUTED BY (UPPER(LASTNAME));

I think the diference in speed is just a matter of first returned rows, could the OP try to fetch all to see how long each one takes ?

On the first query (the fast one) two indices are used:
I_PERSON_NAME: ok for the filter starting with 'S'

I_PERSON_COMPANYPERSONID: but there is no filter on PERSON.COMPANYPERSONID so this index could not help to filter records out, that's the reason I think the OP is just seen a fast first rows query, but it he fetch all on both query the diference should not be to much

Another point.... the OP said:
"Both queries seem straightforward and are performing STARTING WITH tests on a base (person) table. There are 114k records. The surprising thing is that query with an extra join (A1) performs fine whereas the simpler query (B1/B2) is ~ 10x slower."

I dont think it is a fair comparison, since the tables in each query are not the same.

The third query

"(B2) person query - slightly quicker but still too slow (10 secs, ~2000 rows) uses additional clause on person table (and cn.extendedpersonid>0 ) to use I_PERSON_EXTENDEDPERSONID index" This criteria filters out 50% of the records, it's not a good indexed search, I can bet that every single data page was visited, so there is no reason for an indexed search.

How long does it take to run this:
select cn.firstname,cn.lastname,cn.email1,cn.dateofbirth,cn.name
person cn
where (upper(cn.name) starting with 'S')

?

see you !