database performance degrade (proper useof index) | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

database performance degrade (proper useof index)

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
about queries: 1st:
cast(convert(varchar(30),gc_dt,101)as DateTime) between ’01/Apr/2006′ and ’11/Jan/2007′ my guess it should be a.gc_dt. Don’t use cast and convert. Read articlehttp://www.sql-server-performance.com/fk_datetime.asp 2nd: Instead of ors use union with 3 branches.
see my reply at your xpost[B)]http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=18829
]]>