SQL Server Performance

database performance degrade (proper useof index)

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by akshayt, Jan 10, 2007.

  1. akshayt New Member

    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
  2. mmarovic Active Member

    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.
  3. alzdba Member

Share This Page