query optimization and index optimazation issue | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

query optimization and index optimazation issue

hi everbody,
right now i am working on an erp system in which we are facing performance inssue as our data increases.i have tried lot of things reindexing,fragmentation of index and i am also using profiler for bad queries . i also deleted the demo alerts that are firing frequently.i also try sp_updatestats
about my database
sql server 2000
database size 3.50 gb
the table in which i am getting problem are havey transactional and if i remove some index
it also degrade the performance
my questions are
1.effect of do not automatically recompute stastics(i checked this in enterprise manager)
2. if i reindex indexes of a table then the fill factor of all the indexes become same
90%
3.there are some query on which i want some suggestion how to fast is there is any alternative way to write such queries
1.select cast(a.gc_no as varchar)gc_no, convert(datetime,cast(a.gc_dt as varchar(12)),110) gc_dt , m.prt_desc + ‘ | ‘ + M.DIVISION as prt_desc ,a.contracted,c.city_name FromCity,d.city_name Tocity,R.prt_desc + ‘ | ‘ + R.DIVISION AS CONSIGNER ,E.prt_desc + ‘ | ‘ + E.DIVISION AS CONSIGNEE,f_delete as Deleted ,sp_instr,O.Org_name Booking_Office, tot_pk_val [CN Amount],rebooking,inv_no,inv_value,prp_by,print_sr_no from t_gc_mst a ,m_city c ,m_city d ,m_org O ,m_prospect M ,m_prospect R ,m_prospect E where C.city_cd = a.st_city_cd AND :confused:rg_cd= a.branch_cd and a.prt_cd = M.prt_cd and a.cnsr_prt_cd = R.prt_cd and a.cnse_prt_cd = E.prt_cd And d.city_cd = a.end_city_cd and a.branch_cd=154 and cast(convert(varchar(30),gc_dt,101)as DatetIme) between ’01/Apr/2006′ and ’11/Jan/2007′
in this query i used the cast,convert and + and it is used frequently
and generally it is taking lots of time 2.
select gc_no from t_gc_mst where (cnsr_prt_cd=117000003 OR cnse_prt_cd=117000003 OR prt_cd=117000003) AND f_delete=’N’ AND e1_type=’N’ AND gc_no not in( select gc_no from t_bill_dtl0 D,t_bill_mst M where M.bill_no=D.bill_no and M.f_delete<>’Y’ and D.gc_amount > 0 ) order by gc_no
in this query is taking so much time so i want to know how to create index in such type of query should it be the composite index of all the field in where clause or single index on each field
– It is in you best interest to adapt to JOIN syntax !
This way you easily separate join from filter predicate !
e.g.
from t_gc_mst a
INNER JOIN m_city c
ON C.city_cd = a.st_city_cd
INNER JOIN m_city d
ON d.city_cd = a.end_city_cd
INNER JOIN m_org O
ON :confused:rg_cd= a.branch_cd
INNER JOIN m_prospect M
ON a.prt_cd = M.prt_cd
INNER JOIN m_prospect R
ON a.cnsr_prt_cd = R.prt_cd
INNER JOIN m_prospect E
ON a.cnse_prt_cd = E.prt_cd
where a.branch_cd=154
REPLACE WITH and cast(convert(varchar(30),gc_dt,101)as DatetIme) between ’01/Apr/2006′ and ’11/Jan/2007′
AND gc_dt between ‘2006-04-01’ and ‘2007-01-11’
– don’t perform a function on a tablecolumn in a comparisson if you can convert the @variable or a hardcoded value ! Functions will demote the usage of a possible index !
– you may want to replace your ‘not in’ with a correlated not existsAND not EXISTS ( select *
from t_bill_dtl0 D
INNER JOINt_bill_mst M
on M.bill_no=D.bill_no
and M.f_delete<>’Y’
and D.gc_amount > 0
WHERE D.gc_no = a.gc_no )

]]>