Hello Helen,

I tried with join and i removed the upper from the query.Now it
takes 2 mins 40 second for fetching 27019 records.
I haven't created the index on Cust_nam field yet.For creating index
i have to get the permission from DBA.Is there any way to improve the
performance without creating the index on cust_nam field?

If i give order by <field_name>,that field has to be indexed or what??

Regards,

Sivaraman

--- In ib-support@yahoogroups.com, Helen Borrie <helebor@t...> wrote:
> At 10:23 AM 8/06/2003 +0000, you wrote:
> >Hello,
> >
> >This is my query.
> >
> >select
> >a.cust_id,
> >b.lbl,
> >c.lbl,
> >a.cust_nam
> >from
> >cust_v a,
> >source_v b,
> >ctyp_v c
> >where
> >a.source_id=b.source_id and
> >a.ctyp_id=c.ctyp_id and
> >upper(a.source_id)='SAB'
> >order by a.cust_nam
> >
> >This query takes 2 minutes 50 second to fetch 27019
> >records.CUST_V,SOURCE_V and CTYP are views.i have the index for
> >cust_id in the CUST Table, source_id in SOURCE table and ctyp_ix in
> >CTYP Table.Foreign key is not defined and there is no index for
> >source_id and ctyp_id in CUST table.
> >
> >how to improve the performance of this query???
> >Should i create the index for source_id and ctyp_id in CUST
table.Is
> >it must?
>
> If these columns hav low distribution (few values over many
records) then a
> single-column index on it would make the query slower, not faster.
I think
> the main sources of delay here are the upper() expression in the
WHERE
> clause and the lack of an index on cust_nam.
>
> I would want to tidy this query up to use the SQL-92 explicit join
syntax, too.
>
> So - minimum - index cust_nam. And I'm wondering why you haven't
enforced
> upper-case on source_id, if it needs to be used for searching. If
it's not
> too late, I would want to fix that up:
>
> update cust_v
> set source_id = upper (source_id)
> where source_id is not null;
> commit;
>
> set term ^ ;
> create trigger bi_cust_v_1 for cust_v
> active before insert position 1
> as
> begin
> if (new.source_id is not null) then
> new.source_id = upper(new.source_id);
> end ^
> commit ^
> create trigger bu_cust_v_1 for cust_v
> active before update position 1
> as
> begin
> if (new.source_id is not null) then
> new.source_id = upper(new.source_id);
> end ^
> commit ^
> set term ; ^
>
> New query (after mods and cust_name indexed):
> select
> a.cust_id,
> b.lbl,
> c.lbl,
> a.cust_nam
> from cust_v a
> join source_v b on a.source_id=b.source_id
> join ctyp_v c on a.ctyp_id=c.ctyp_id
> where a.source_id = 'SAB'
> order by a.cust_nam
>
> heLen