SQL Server Performance

Performance degradation on Convert function

Discussion in 'Performance Tuning for DBAs' started by fbardi, Jan 11, 2008.

  1. fbardi New Member

    Hello, how can I optimize the performance of the function "CONVERT" in SQL Server 2000?
    My sentence "select (CONVERT (CHAR (10), effdt_field, 121))" which takes too long.
    if you run same script whitout CONVERT, consumes much less time.
    Thanks.
  2. ndinakar Member

    The slower performance is to be expected. Get the date as is from the table and do the formatting at the front end.
  3. Adriaan New Member

    As long as the convert function only occurs in the column list of the query, the execution time should not be so much different. Are you sure you're not using the convert function in a JOIN or WHERE or ORDER BY clause?
  4. fbardi New Member

    Hi, Thanks for your help. The convert function I'm using is in 3 list columns. Here I show you my full sql sentence
    SELECT FILL.EMPLID,FILL.ACAD_CAREER,FILL.INSTITUTION,FILL.TERM_DESCR30,FILL.STRM,FILL.ACAD_CAREER_DESCR,FILL.INSTITUTION_DESCR,(CONVERT(CHAR(10),FILL.TERM_BEGIN_DT,121)),(CONVERT(CHAR(10),FILL.TERM_END_DT,121)),(CONVERT(CHAR(10),FILL.SSR_SSENRLAVAIL_DT,121)),FILL.ACAD_PROG_PRIMARY,FILL.WITHDRAW_CODE FROM PS_SSR_STDNT_TERMS FILL
    where FILL.EMPLID='00000000005'
    and FILL.STRM=(select min(a.STRM) from PS_SSR_STDNT_TERMS a where a.EMPLID=FILL.EMPLID and a.ACAD_CAREER=FILL.ACAD_CAREER and a.INSTITUTION=FILL.INSTITUTION
    and a.TERM_BEGIN_DT<='2008-01-11'
    and a.TERM_END_DT>='2008-01-11'
    and (a.SSR_SSENRLAVAIL_DT>'2008-01-11' or a.SSR_SSENRLAVAIL_DT is NULL))
    Any idea of how to reduce the time?
  5. Adriaan New Member

    I assume that the term details are repeated on many rows (TERM_BEGIN_DT and TERM_END_DT) so you could normalize them into a Term table.
    Then in your query you could use a view (or a derived table) on the Term table to apply the date format.

Share This Page