Hi Lalit,
Its tooo long you posted this. I hope you may got the solution at this
time.
Insteed of using 'in' you can use LEFT JOIN.... But I dont know how
useful to your query.
__________________________________________________ ______________________

select convert(char(28),a.cod_cust_id) CUSTID,
a.nam_cust_shrt NAMECUSTSHRT,a.txt_cust_prefix PREFIX,
convert(char(8),a.dat_birth_cust,112) BIRTHDATE,
substring(a.txt_custadr_add1,1,35) ADD1,
substring(a.txt_custadr_add2,1,35) ADD2,
substring(a.txt_custadr_add3,1,20) ADD3,
substring(a.nam_custadr_city,1,25) CITY,
substring(a.txt_custadr_zip,1,10) ZIP,
substring(a.ref_cust_phone,1,20) RESIPHONE,
substring(a.ref_cust_phone_off,1,20) OFFPHONE,
substring(a.ref_cust_fax,1,20) FAX,
substring(a.nam_cust_spouse,1,20) SPOUSENAME
from cod_cust_id b LEFT JOIN ci_custmast a on (a.cod_cust_id =
b.cod_cust_id)
where
convert(char(8),a.dat_addr_change,112)= '20040108'
--convert(char,dateadd(dd,-8,getdate()),112) and
convert(char,getdate(),112)
and convert(char(8),a.dat_cust_open,112) < '20040108'
where flg_mnt_status='A' and flg_tb_new_req='Y')
and a.ctr_updat_srlno > 1
and a.flg_mnt_status = 'A'

__________________________________________________ ______________________

Regards,
Ram DSL,London.
www.dslsoft.net


From: Lalit (issues_sybase (AT) hotmail (DOT) com)
Subject: Query Tuning
This is the only article in this thread
View: Original Format
Newsgroups: comp.databases.sybase
Date: 2004-01-13 08:54:07 PST


Dear Friends,

How can we better tune this query.
ci_custmast (table size 6GB,datarows locking scheme)
in this query one subquery is used which is using datarows locking
scheme,
ubquery returns 4,38,000 rows out of 4,50,000 rows )


select convert(char(28),a.cod_cust_id) CUSTID,
a.nam_cust_shrt NAMECUSTSHRT,
a.txt_cust_prefix PREFIX,
convert(char(8),a.dat_birth_cust,112) BIRTHDATE,
substring(a.txt_custadr_add1,1,35) ADD1,
substring(a.txt_custadr_add2,1,35) ADD2,
substring(a.txt_custadr_add3,1,20) ADD3,
substring(a.nam_custadr_city,1,25) CITY,
substring(a.txt_custadr_zip,1,10) ZIP,
substring(a.ref_cust_phone,1,20) RESIPHONE,
substring(a.ref_cust_phone_off,1,20) OFFPHONE,
substring(a.ref_cust_fax,1,20) FAX,
substring(a.nam_cust_spouse,1,20) SPOUSENAME from
ci_custmast a
where
convert(char(8),a.dat_addr_change,112)= '20040108'
--convert(char,dateadd(dd,-8,getdate()),112) and
convert(char,getdate(),112)
and
convert(char(8),a.dat_cust_open,112) < '20040108'
and a.cod_cust_id in (select cod_cust_id from ci_direct_banking
where flg_mnt_status='A' and flg_tb_new_req='Y')
and a.ctr_updat_srlno > 1
and a.flg_mnt_status = 'A'
go

Kindly suggest creating indexes on which table will be best or how can
we reduce the overhead of a query