Convert Function takes long time for execution in SQL Server

Question

We have a query which contains 15 convert function calls against different datetime columns, along with 20 other columns. The query takes nearly 5 minutes when executed with Convert functions, however when we comment out/remove convert functions it takes only 15 seconds.

CONVERT (VARCHAR (10), TABLE.[COLUMN NAME], 101)


SELECT
      T1.CONFLICT_ID,
      CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
      CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
      T1.LAST_UPD_BY,
      T1.CREATED_BY,
      T1.MODIFICATION_NUM,
      T1.ROW_ID,
      CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
      T1.ROW_ID,
      CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
      T1.LAST_UPD_BY,
      CONVERT (VARCHAR (10),T1.TODO_AFTER_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_AFTER_DT, 8),
      T1.APPT_REPT_TYPE,
      T27.ATTRIB_47,
      T1.ROW_STATUS,
      T24.OWN_INST_ID,
      T24.INTEGRATION_ID,
      T15.SR_TITLE,
      T27.ATTRIB_45,
      T1.APPT_ALARM_TM_MIN,
      T15.SR_NUM,
      T1.PR_PRDINT_ID,
      T1.PR_SR_ID,
      T1.PR_SYMPTOM_CD,
      CONVERT (VARCHAR (10),T13.PLAN_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T13.PLAN_END_DT, 8),
      CONVERT (VARCHAR (10),T13.PLAN_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T13.PLAN_START_DT, 8),
      T12.BL_CLASS_CD,
      T12.BL_CURCY_CD,
      T1.PROJ_ID,
      CONVERT (VARCHAR (10),T13.ACTL_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T13.ACTL_END_DT, 8),
      CONVERT (VARCHAR (10),T13.ACTL_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T13.ACTL_START_DT, 8),
      T9.NAME,
      T9.CURCY_CD,
      T1.OPTY_ID,
      T10.BASE_CURCY_CD,
      T1.TARGET_OU_ID,
      T10.LOC,
      T10.NAME,
      CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 8),
      T1.SRV_REGN_ID,
      T16.NAME,
      T18.AMS_ROUTE_TO_CD,
      T1.ME_EVT_ID,
      T1.ASGN_MANL_FLG,
      T20.MSTR_CASE_ID,
      T22.NUM_PAGE,
      T18.AMS_ACT_ID,
      T19.MAX_CAPABILITY_LVL,
      T19.MIN_CAPABILITY_LVL,
      T19.RTE_APPR_FLG,
      T1.OWNER_LOGIN,
      T2.BU_ID,
      T1.OWNER_PER_ID,
      T1.APPT_REPT_FLG,
      CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 8),
      T1.PR_ORDER_ID,
      T1.DO_NOT_ROUTE_FLG,
      T1.TARGET_OU_ADDR_ID,
      T1.ARREST_ID,
      T1.ASSET_ID,
      T1.ASGN_USR_EXCLD_FLG,
      T7.PR_POSTN_ID,
      T28.PIM_APPT_FLG,
      T28.SEBL_APPT_FLG,
      T28.UNSPRTD_REPT_FLG,
      T26.SRM_REQUEST_ID,
      T12.NAME,
      T12.PROJ_NUM,
      T1.X_ENTITY_TYPE,
      T1.XTRNL_ACCESS_FLG,
      T29.EVIDENCE_ID,
      T1.SRA_TYPE_CD,
      T1.COMM_ID,
      T1.COMMENTS_LONG,
      T1.COMMENTS,
      T10.PR_ADDR_ID,
      T1.PR_PROD_CAT_ID,
      T3.FST_NAME,
      T1.TARGET_PER_ID,
      T3.LAST_NAME,
      T1.OWNER_OU_ID,
      T1.COMM_ID,
      T1.CMPLTD_FLG,
      T20.CASE_NUM,
      T1.X_OFFENSE_ID,
      T27.ATTRIB_37,
      T1.PREV_ACT_ID,
      CONVERT (VARCHAR (10),T1.APPT_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_END_DT, 8),
      CONVERT (VARCHAR (10),T1.APPT_END_TM, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_END_TM, 8),
      T14.SCHED_ENGINE_CD,
      T6.ASSET_NUM,
      T11.NAME,
      T1.OWNER_POSTN_ID,
      T25.WEB_ACCESS_FLG,
      T1.SRC_ID,
      T1.X_CASE_ID,
      T20.NAME,
      CONVERT (VARCHAR (10),T1.TODO_DUE_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_DUE_DT, 8),
      T1.APPT_DURATION_MIN,
      CONVERT (VARCHAR (10),T18.TLR_EFFECTIVE_DT, 101) + ' ' + CONVERT (VARCHAR (10),T18.TLR_EFFECTIVE_DT, 8),
      T26.ACTIVE_FLG,
      T19.NAME,
      T1.SRA_DEFECT_ID,
      T1.SRA_SR_ID,
      T1.TMPL_PLANITEM_ID,
      T1.ACTIVITY_UID,
      T1.AGREEMENT_ID,
      T1.PYMNT_FLG,
      T1.ALARM_FLAG,
      T1.CREATOR_LOGIN,
      T1.ASGN_DNRM_FLG,
      T1.NAME,
      T18.TLR_INTG_RET_CD,
      T1.INCIDENT_ID,
      T8.X_SESSION_ID,
      T18.TLR_INTG_ID,
      T18.TLR_INTG_MSG,
      CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 8),
      T1.DONE_FLG,
      CONVERT (VARCHAR (10),T1.APPT_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_DT, 8),
      T4.POSTN_TYPE_CD,
      CONVERT (VARCHAR (10),T1.STATUS_UPD_TS, 101) + ' ' + CONVERT (VARCHAR (10),T1.STATUS_UPD_TS, 8),
      T10.DIVISION,
      T1.COMMENTS,
      T26.STAGE_ID,
      CONVERT (VARCHAR (10),T1.APPT_START_TM, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_TM, 8),
      CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 8),
      T1.ASSESS_7,
      T1.X_AUTO_FLG,
      T1.X_SEQUENCE,
      T23.FORMAT_TYPE_CD,
      T1.X_OFFENSE_ID,
      T5.EMP_ID,
      T21.SRV_REGN_ID,
      T2.FST_NAME,
      T2.LAST_NAME,
      T14.SCHED_PARAM_SET_ID,
      T1.PAR_EVT_ID,
      T1.BILLABLE_FLG,
      T1.APPT_REPT_APPT_ID,
      T1.APPT_REPT_REPL_CD,
      T1.TEMPLATE_FLG,
      T1.ASGN_SYS_FLG,
      T1.PCT_COMPLETE,
      T1.TARGET_PER_ADDR_ID,
      T18.SUB_TYPE_CD,
      T1.CL_SUBJ_ID,
      T1.CAL_DISP_FLG,
      CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 8),
      CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 8),
      T1.COST_CURCY_CD,
      T15.SR_STAT_ID,
      T1.SUBTYPE_CD,
      T1.TODO_CD,
      T20.X_CASE_MODE,
      T1.CAL_TYPE_CD,
      T1.EVT_STAT_CD,
      T30.ROW_ID,
      T31.ROW_STATUS,
      T33.AMS_CAPABILITY_LVL,
      T34.LOGIN,
      T32.ROW_ID,
      T36.GEOCD_VALID_FLG,
      T22.ROW_ID,
      T22.PAR_ROW_ID,
      T22.MODIFICATION_NUM,
      T22.CREATED_BY,
      T22.LAST_UPD_BY,
      CONVERT (VARCHAR (10),T22.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T22.CREATED, 8),
      CONVERT (VARCHAR (10),T22.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T22.LAST_UPD, 8),
      T22.CONFLICT_ID,
      T22.PAR_ROW_ID,
      T18.ROW_ID,
      T18.PAR_ROW_ID,
      T18.MODIFICATION_NUM,
      T18.CREATED_BY,
      T18.LAST_UPD_BY,
      CONVERT (VARCHAR (10),T18.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T18.CREATED, 8),
      CONVERT (VARCHAR (10),T18.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T18.LAST_UPD, 8),
      T18.CONFLICT_ID,
      T18.PAR_ROW_ID,
      T23.ROW_ID,
      T23.PAR_ROW_ID,
      T23.MODIFICATION_NUM,
      T23.CREATED_BY,
      T23.LAST_UPD_BY,
      CONVERT (VARCHAR (10),T23.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T23.CREATED, 8),
      CONVERT (VARCHAR (10),T23.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T23.LAST_UPD, 8),
      T23.CONFLICT_ID,
      T23.PAR_ROW_ID,
      T24.ROW_ID,
      T24.PAR_ROW_ID,
      T24.MODIFICATION_NUM,
      T24.CREATED_BY,
      T24.LAST_UPD_BY,
      CONVERT (VARCHAR (10),T24.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T24.CREATED, 8),
      CONVERT (VARCHAR (10),T24.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T24.LAST_UPD, 8),
      T24.CONFLICT_ID,
      T24.PAR_ROW_ID,
      T25.ROW_ID,
      T25.PAR_ROW_ID,
      T25.MODIFICATION_NUM,
      T25.CREATED_BY,
      T25.LAST_UPD_BY,
      CONVERT (VARCHAR (10),T25.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T25.CREATED, 8),
      CONVERT (VARCHAR (10),T25.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T25.LAST_UPD, 8),
      T25.CONFLICT_ID,
      T25.PAR_ROW_ID,
      T26.ROW_ID,
      T26.PAR_ROW_ID,
      T26.MODIFICATION_NUM,
      T26.CREATED_BY,
      T26.LAST_UPD_BY,
      CONVERT (VARCHAR (10),T26.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T26.CREATED, 8),
      CONVERT (VARCHAR (10),T26.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T26.LAST_UPD, 8),
      T26.CONFLICT_ID,
      T26.PAR_ROW_ID,
      T27.ROW_ID,
      T27.PAR_ROW_ID,
      T27.MODIFICATION_NUM,
      T27.CREATED_BY,
      T27.LAST_UPD_BY,
      CONVERT (VARCHAR (10),T27.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T27.CREATED, 8),
      CONVERT (VARCHAR (10),T27.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T27.LAST_UPD, 8),
      T27.CONFLICT_ID,
      T27.PAR_ROW_ID,
      T28.ROW_ID,
      T28.PAR_ROW_ID,
      T28.MODIFICATION_NUM,
      T28.CREATED_BY,
      T28.LAST_UPD_BY,
      CONVERT (VARCHAR (10),T28.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T28.CREATED, 8),
      CONVERT (VARCHAR (10),T28.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T28.LAST_UPD, 8),
      T28.CONFLICT_ID,
      T28.PAR_ROW_ID,
      T29.ROW_ID,
      T29.PAR_ROW_ID,
      T29.MODIFICATION_NUM,
      T29.CREATED_BY,
      T29.LAST_UPD_BY,
      CONVERT (VARCHAR (10),T29.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T29.CREATED, 8),
      CONVERT (VARCHAR (10),T29.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T29.LAST_UPD, 8),
      T29.CONFLICT_ID,
      T29.PAR_ROW_ID,
      T30.ROW_ID,
      T31.ROW_ID,
      T32.ROW_ID,
      T35.ROW_ID,
      T36.ROW_ID
      FROM 
       dbo.S_EVT_ACT T1 
          LEFT OUTER JOIN dbo.S_CONTACT T2 ON T1.OWNER_PER_ID = T2.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_CONTACT T3 ON T1.TARGET_PER_ID = T3.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_POSTN T4 ON T2.PR_HELD_POSTN_ID = T4.ROW_ID
          LEFT OUTER JOIN dbo.S_ACT_EMP T5 ON T1.ROW_ID = T5.ACTIVITY_ID
          LEFT OUTER JOIN dbo.S_ASSET T6 ON T1.ASSET_ID = T6.ROW_ID
          LEFT OUTER JOIN dbo.S_CONTACT T7 ON T1.TARGET_PER_ID = T7.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_INCIDENT T8 ON T1.INCIDENT_ID = T8.ROW_ID
          LEFT OUTER JOIN dbo.S_OPTY T9 ON T1.OPTY_ID = T9.ROW_ID
          LEFT OUTER JOIN dbo.S_ORG_EXT T10 ON T1.TARGET_OU_ID = T10.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_POSTN T11 ON T1.OWNER_POSTN_ID = T11.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_PROJ T12 ON T1.PROJ_ID = T12.ROW_ID
          LEFT OUTER JOIN dbo.S_PROJITEM T13 ON T1.PROJ_ITEM_ID = T13.ROW_ID
          LEFT OUTER JOIN dbo.S_SRV_REGN T14 ON T1.SRV_REGN_ID = T14.ROW_ID
          LEFT OUTER JOIN dbo.S_SRV_REQ T15 ON T1.SRA_SR_ID = T15.ROW_ID
          LEFT OUTER JOIN dbo.S_TIMEZONE T16 ON T14.TIME_ZONE_ID = T16.ROW_ID
          LEFT OUTER JOIN dbo.S_OPTY_POSTN T17 ON T1.OPTY_ID = T17.OPTY_ID AND T17.POSITION_ID = '1-D38P'
          LEFT OUTER JOIN dbo.S_EVT_ACT_FNX T18 ON T1.ROW_ID = T18.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_FN_APPR T19 ON T18.AMS_ACT_ID = T19.ROW_ID
          LEFT OUTER JOIN dbo.S_CASE T20 ON T1.X_CASE_ID = T20.ROW_ID
          LEFT OUTER JOIN dbo.S_EMP_PER T21 ON T1.OWNER_PER_ID = T21.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_EVT_ACT_LSX T22 ON T1.ROW_ID = T22.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_EVT_MAIL T23 ON T1.ROW_ID = T23.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_EVT_ACT_SS T24 ON T1.ROW_ID = T24.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_SUSP_ACT T25 ON T1.ROW_ID = T25.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_EVT_MKTG T26 ON T1.ROW_ID = T26.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_EVT_ACT_X T27 ON T1.ROW_ID = T27.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_EVT_CAL T28 ON T1.ROW_ID = T28.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_EVT_CASE T29 ON T1.ROW_ID = T29.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_PARTY T30 ON T1.TARGET_PER_ID = T30.ROW_ID
          LEFT OUTER JOIN dbo.S_ACT_EMP T31 ON T1.OWNER_PER_ID = T31.EMP_ID AND T1.ROW_ID = T31.ACTIVITY_ID
          LEFT OUTER JOIN dbo.S_PARTY T32 ON T31.EMP_ID = T32.ROW_ID
          LEFT OUTER JOIN dbo.S_CONTACT_FNX T33 ON T31.EMP_ID = T33.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_USER T34 ON T31.EMP_ID = T34.PAR_ROW_ID
          LEFT OUTER JOIN dbo.S_CON_ADDR T35 ON T1.TARGET_OU_ADDR_ID = T35.ADDR_PER_ID AND T1.TARGET_OU_ID = T35.ACCNT_ID
          LEFT OUTER JOIN dbo.S_ADDR_PER T36 ON T1.TARGET_OU_ADDR_ID = T36.ROW_ID
   WHERE 
      ((T1.APPT_REPT_REPL_CD IS NULL) AND
      ((T1.TEMPLATE_FLG != N'Y' AND T1.TEMPLATE_FLG != N'P' OR T1.TEMPLATE_FLG IS NULL) AND (T1.OPTY_ID IS NULL OR T9.SECURE_FLG = N'N' OR T17.OPTY_ID IS NOT NULL)) AND
      (T1.PRIV_FLG = 'N' OR T1.PRIV_FLG IS NULL OR T1.OWNER_PER_ID = '1-K6JK')) AND
      (T1.ROW_ID = '1-Y95E')

Are the convert invocations in your SELECT clause or in your WHERE clause? If the latter, they probably [make your query **non-sargable**](http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable). Please post a [mcve] of the SQL in question. — Heinzi, Aug 17 '16 at 08:57
Query takes long time to execute its select query which contains 15 convert functions its system generated so We can't modify anymore. — Sara, Aug 17 '16 at 08:58
@Sara - Please read http://stackoverflow.com/help/how-to-ask — Chris Pickford, Aug 17 '16 at 09:01
@Sara post the query. A conversion in the `select` clause doesn't take a long time. Besides, *why* are you trying to convert a datetime to some arbitrary string format? It's a *lot* easier for applications to use the actual datetime values. A conversion in `WHERE` on the other hand is a bug that hints at query problems — Panagiotis Kanavos, Aug 17 '16 at 09:27
@Sara PS no system generates such conversions. Programmers do, typically when they confuse date values with string literals that represent dates. `WHERE myTable.MyDate > @someDateTypedParameter` uses indexes and returns immediatelly. `WHERE CONVERT(VARCHAR(10),myTable.MyDate,101)="somestring"` forces a full table scan, and will simply break if `somestring` doesn't match the hard-coded format - ie everywhere outside the US — Panagiotis Kanavos, Aug 17 '16 at 09:29
@Sara even if you *have* to use strings (you don't), using the unseparated format allows you to compare directly against date fields without conversion issues, ie `WHERE myTable.MyDateField > '20160810')`. Why pass a string though when you can pass a date parameter? — Panagiotis Kanavos, Aug 17 '16 at 09:34

score 0 · Answer 1 · answered Aug 17 '16 at 09:17

You are asking if there is a way to speed up Conversions? No is the answer... Otherwise why would they not just make this faster way the default?

Why is it converting all the columns? You mention it is system generated... which system generates it? You could change the data types of the tables to be the correct type, then the conversion would not be needed.

score 0 · Answer 2 · answered Aug 20 '16 at 13:40

I don't believe the convert functions can be the reason. It of course requires some resources, but it should definitely take minutes.

You can end up with a different plan for some reason when the convert functions are there and that might it look like that's the reason.

I would suggest starting to look for what normally can cause slowness in the query:

Compare the query plan of the good and bad query. If there is a difference, then we might be able to help you.
Make sure there's no blocking happening
When comparing queries, look at statistics io output

In your case when you have such huge number of tables, plan creation can definitely time out. You should also check that from the leftmost operator in the plan. See a blog post by Grant Fritchey

If you compare just runtime, you might get totally wrong results for example because query plan already exists, data is cached etc. To be sure that just comparing time is correct, you should run checkpoint, dbcc dropcleanbuffers and dbcc freeprocache but in test environment only.

score 0 · Answer 3 · edited May 21 '20 at 15:00

0

I found a similar issue when I use the FORMAT function to convert DATETIME to a string. I tried to comment on the function, I found out the query is obviously faster than before. So I avoid using FORMAT function to convert DATETIME to string. In my case, I want to convert DATETIME to yyyyMM, so I used [YEAR(c_date) * 100 + MONTH(c_date)] instead. Base on my experience, if data is not much, it's okay to use FORMAT function, but if you have millions of records of data then you should avoid using FORMAT function.

edited May 21 '20 at 15:00

Denzil Soans

667
1
7
27

answered May 21 '20 at 11:00

johm.chamroeun

19
2

It will be very helpful if you can come up with statistics on performance on the topic you are mentioning of. – Chakri May 21 '20 at 12:42
I have a table rcp_master with 2,878, 000 records of data and rcp_date is a column that contains DATETIME without indexing. My goal want to query data that happened in 11/2019. - SQL1 : using FORMAT(rcp_date, 'yyyyMM') = '201911' - SQL2 : usign [YEAR(rcp_date) * 100 + MONTH(rcp_date)] = 201911 SQL1 took around 14s but SQL2 took only 1s. If you run wiht SQL Plan, you will see SQL1 have one step more than SQL2, that step is "Filter" step. – johm.chamroeun May 22 '20 at 04:51

Convert Function takes long time for execution in SQL Server

3 Answers3