July 8, 2014 at 10:37 am
Can someone try to help me with this? I got this from my co-worker and he is having some issues when converting datetime into human readable format?
problem is in the WHERE clause
================
--Run time: 1 sec
================
select top 20 "dbo_HPD_Help_Desk"."First_Name" as "dbo_HPD_Help_Desk_First_Name",
"dbo_HPD_Help_Desk"."Assignee" as "dbo_HPD_Help_Desk_Assignee",
dbo.fn_cti_convertardatetime("dbo_HPD_Help_Desk"."Estimated_Resolution_Date") as "dbo_HPD_Help_Desk_Estimated_Resolution_Date_cf",
"dbo_HPD_Help_Desk"."Product_Name" as "dbo_HPD_Help_Desk_Product_Name",
"HPD_Priority"."value" as "HPD_Priority_value",
"dbo_HPD_Help_Desk"."Assigned_Group" as "dbo_HPD_Help_Desk_Assigned_Group",
dbo.fn_cti_convertardatetime("dbo_HPD_Help_Desk"."Reported_Date") as "dbo_HPD_Help_Desk_Reported_Date_cf",
"dbo_HPD_Help_Desk"."Incident_Number" as "dbo_HPD_Help_Desk_Incident_Number",
"HPD_AgeInformation"."ClosedDate" as "HPD_AgeInformation_ClosedDate",
"dbo_HPD_Help_Desk"."Site" as "dbo_HPD_Help_Desk_Site",
"HPD_ServiceType"."value" as "HPD_ServiceType_value",
"dbo_HPD_Help_Desk"."Last_Name" as "dbo_HPD_Help_Desk_Last_Name",
"HPD_Status"."value" as "HPD_Status_value",
"dbo_HPD_Help_Desk"."Categorization_Tier_2" as "dbo_HPD_Help_Desk_Categorization_Tier_2",
"dbo_HPD_Help_Desk"."Description" as "dbo_HPD_Help_Desk_Description",
"HPD_AgeInformation"."LastResolvedDate" as "HPD_AgeInformation_LastResolvedDate"
from "dbo"."HPD_Help_Desk" "dbo_HPD_Help_Desk"
left outer join (SELECT H.INCIDENT_NUMBER as "IncidentNumber", HPDStatus.Value as "Status", dbo.fn_cti_convertardatetime(h.reported_date) as "reported_date", dbo.fn_cti_datetime_diff(h.reported_date,0,'') as "Age", CAST(dbo.fn_cti_datetime_diff(h.reported_date,0,'N') as DECIMAL(20,2)) as "iAge", dbo.fn_cti_convertardatetime(h.last_resolved_date) as "LastResolvedDate", CASE WHEN h.last_resolved_date IS NOT NULL THEN dbo.fn_cti_datetime_diff(h.reported_date, h.last_resolved_date, '') ELSE NULL END as "TTR", CASE WHEN h.last_resolved_date IS NOT NULL THEN CAST(dbo.fn_cti_datetime_diff(h.reported_date, h.last_resolved_date, 'N') as DECIMAL(20,2)) ELSE NULL END as "iTTR", dbo.fn_cti_convertardatetime(closed_date) as "ClosedDate", CASE WHEN h.closed_date IS NOT NULL THEN dbo.fn_cti_datetime_diff(h.closed_date,0,'') ELSE NULL END as "TTC", CASE WHEN h.closed_date IS NOT NULL THEN CAST(dbo.fn_cti_datetime_diff(h.closed_date,0,'N') as DECIMAL(20,2)) ELSE NULL END as "iTTC", ACount.acount TimesAssigned FROM HPD_HELP_DESK H LEFT JOIN CTI_FieldValues HPDStatus ON HPDStatus.enumid = H.status AND HPDStatus.formname = 'HPD:Help Desk' AND HPDStatus.fieldname = 'Status' LEFT JOIN ( SELECT INCIDENT_NUMBER, COUNT(INCIDENT_NUMBER) AS acount FROM HPD_HELP_DESK_ASSIGNMENT_LOG where ASSIGNMENT_TYPE = 2000 /* Assigned */ GROUP BY INCIDENT_NUMBER ) ACOUNT ON H.Incident_Number=ACOUNT.Incident_Number) "HPD_AgeInformation" on ("dbo_HPD_Help_Desk"."Incident_Number" = "HPD_AgeInformation"."IncidentNumber")
left outer join (select enumid, value FROM CTI_FieldValues where formname = 'HPD:Help Desk' and fieldname = 'Priority') "HPD_Priority" on ("dbo_HPD_Help_Desk"."Priority" = "HPD_Priority"."enumid")
left outer join (select enumid, value FROM CTI_FieldValues where formname = 'HPD:Help Desk' and fieldname = 'Service Type') "HPD_ServiceType" on ("dbo_HPD_Help_Desk"."Service_Type" = "HPD_ServiceType"."enumid")
left outer join (select enumid, value FROM CTI_FieldValues where formname = 'HPD:Help Desk' and fieldname = 'Status') "HPD_Status" on ("dbo_HPD_Help_Desk"."Status" = "HPD_Status"."enumid")
where "HPD_Status"."value" in ('Assigned', 'Closed', 'In Progress', 'Pending', 'Resolved') and "HPD_ServiceType"."value" in ('User Service Request') and "dbo_HPD_Help_Desk"."Categorization_Tier_1" in ('Account/Access')
================
--Run time: 4 sec
================
select top 20 "dbo_HPD_Help_Desk"."First_Name" as "dbo_HPD_Help_Desk_First_Name",
"dbo_HPD_Help_Desk"."Assignee" as "dbo_HPD_Help_Desk_Assignee",
dbo.fn_cti_convertardatetime("dbo_HPD_Help_Desk"."Estimated_Resolution_Date") as "dbo_HPD_Help_Desk_Estimated_Resolution_Date_cf",
"dbo_HPD_Help_Desk"."Product_Name" as "dbo_HPD_Help_Desk_Product_Name",
"HPD_Priority"."value" as "HPD_Priority_value",
"dbo_HPD_Help_Desk"."Assigned_Group" as "dbo_HPD_Help_Desk_Assigned_Group",
dbo.fn_cti_convertardatetime("dbo_HPD_Help_Desk"."Reported_Date") as "dbo_HPD_Help_Desk_Reported_Date_cf",
"dbo_HPD_Help_Desk"."Incident_Number" as "dbo_HPD_Help_Desk_Incident_Number",
"HPD_AgeInformation"."ClosedDate" as "HPD_AgeInformation_ClosedDate",
"dbo_HPD_Help_Desk"."Site" as "dbo_HPD_Help_Desk_Site",
"HPD_ServiceType"."value" as "HPD_ServiceType_value",
"dbo_HPD_Help_Desk"."Last_Name" as "dbo_HPD_Help_Desk_Last_Name",
"HPD_Status"."value" as "HPD_Status_value",
"dbo_HPD_Help_Desk"."Categorization_Tier_2" as "dbo_HPD_Help_Desk_Categorization_Tier_2",
"dbo_HPD_Help_Desk"."Description" as "dbo_HPD_Help_Desk_Description",
"HPD_AgeInformation"."LastResolvedDate" as "HPD_AgeInformation_LastResolvedDate"
from "dbo"."HPD_Help_Desk" "dbo_HPD_Help_Desk"
left outer join (SELECT H.INCIDENT_NUMBER as "IncidentNumber", HPDStatus.Value as "Status", dbo.fn_cti_convertardatetime(h.reported_date) as "reported_date", dbo.fn_cti_datetime_diff(h.reported_date,0,'') as "Age", CAST(dbo.fn_cti_datetime_diff(h.reported_date,0,'N') as DECIMAL(20,2)) as "iAge", dbo.fn_cti_convertardatetime(h.last_resolved_date) as "LastResolvedDate", CASE WHEN h.last_resolved_date IS NOT NULL THEN dbo.fn_cti_datetime_diff(h.reported_date, h.last_resolved_date, '') ELSE NULL END as "TTR", CASE WHEN h.last_resolved_date IS NOT NULL THEN CAST(dbo.fn_cti_datetime_diff(h.reported_date, h.last_resolved_date, 'N') as DECIMAL(20,2)) ELSE NULL END as "iTTR", dbo.fn_cti_convertardatetime(closed_date) as "ClosedDate", CASE WHEN h.closed_date IS NOT NULL THEN dbo.fn_cti_datetime_diff(h.closed_date,0,'') ELSE NULL END as "TTC", CASE WHEN h.closed_date IS NOT NULL THEN CAST(dbo.fn_cti_datetime_diff(h.closed_date,0,'N') as DECIMAL(20,2)) ELSE NULL END as "iTTC", ACount.acount TimesAssigned FROM HPD_HELP_DESK H LEFT JOIN CTI_FieldValues HPDStatus ON HPDStatus.enumid = H.status AND HPDStatus.formname = 'HPD:Help Desk' AND HPDStatus.fieldname = 'Status' LEFT JOIN ( SELECT INCIDENT_NUMBER, COUNT(INCIDENT_NUMBER) AS acount FROM HPD_HELP_DESK_ASSIGNMENT_LOG where ASSIGNMENT_TYPE = 2000 /* Assigned */ GROUP BY INCIDENT_NUMBER ) ACOUNT ON H.Incident_Number=ACOUNT.Incident_Number) "HPD_AgeInformation" on ("dbo_HPD_Help_Desk"."Incident_Number" = "HPD_AgeInformation"."IncidentNumber")
left outer join (select enumid, value FROM CTI_FieldValues where formname = 'HPD:Help Desk' and fieldname = 'Priority') "HPD_Priority" on ("dbo_HPD_Help_Desk"."Priority" = "HPD_Priority"."enumid")
left outer join (select enumid, value FROM CTI_FieldValues where formname = 'HPD:Help Desk' and fieldname = 'Service Type') "HPD_ServiceType" on ("dbo_HPD_Help_Desk"."Service_Type" = "HPD_ServiceType"."enumid")
left outer join (select enumid, value FROM CTI_FieldValues where formname = 'HPD:Help Desk' and fieldname = 'Status') "HPD_Status" on ("dbo_HPD_Help_Desk"."Status" = "HPD_Status"."enumid")
where "HPD_Status"."value" in ('Assigned', 'Closed', 'In Progress', 'Pending', 'Resolved') and "HPD_ServiceType"."value" in ('User Service Request') and "dbo_HPD_Help_Desk"."Categorization_Tier_1" in ('Account/Access') and "dbo_HPD_Help_Desk".Last_Resolved_Date > 1394220011
================
--Resolved Date criteria specified using Column supplied function to convert datetime to human readable format
--Run time: > 4 min
================
select top 20 "dbo_HPD_Help_Desk"."First_Name" as "dbo_HPD_Help_Desk_First_Name",
"dbo_HPD_Help_Desk"."Assignee" as "dbo_HPD_Help_Desk_Assignee",
dbo.fn_cti_convertardatetime("dbo_HPD_Help_Desk"."Estimated_Resolution_Date") as "dbo_HPD_Help_Desk_Estimated_Resolution_Date_cf",
"dbo_HPD_Help_Desk"."Product_Name" as "dbo_HPD_Help_Desk_Product_Name",
"HPD_Priority"."value" as "HPD_Priority_value",
"dbo_HPD_Help_Desk"."Assigned_Group" as "dbo_HPD_Help_Desk_Assigned_Group",
dbo.fn_cti_convertardatetime("dbo_HPD_Help_Desk"."Reported_Date") as "dbo_HPD_Help_Desk_Reported_Date_cf",
"dbo_HPD_Help_Desk"."Incident_Number" as "dbo_HPD_Help_Desk_Incident_Number",
"HPD_AgeInformation"."ClosedDate" as "HPD_AgeInformation_ClosedDate",
"dbo_HPD_Help_Desk"."Site" as "dbo_HPD_Help_Desk_Site",
"HPD_ServiceType"."value" as "HPD_ServiceType_value",
"dbo_HPD_Help_Desk"."Last_Name" as "dbo_HPD_Help_Desk_Last_Name",
"HPD_Status"."value" as "HPD_Status_value",
"dbo_HPD_Help_Desk"."Categorization_Tier_2" as "dbo_HPD_Help_Desk_Categorization_Tier_2",
"dbo_HPD_Help_Desk"."Description" as "dbo_HPD_Help_Desk_Description",
"HPD_AgeInformation"."LastResolvedDate" as "HPD_AgeInformation_LastResolvedDate"
from "dbo"."HPD_Help_Desk" "dbo_HPD_Help_Desk"
left outer join (SELECT H.INCIDENT_NUMBER as "IncidentNumber", HPDStatus.Value as "Status", dbo.fn_cti_convertardatetime(h.reported_date) as "reported_date", dbo.fn_cti_datetime_diff(h.reported_date,0,'') as "Age", CAST(dbo.fn_cti_datetime_diff(h.reported_date,0,'N') as DECIMAL(20,2)) as "iAge", dbo.fn_cti_convertardatetime(h.last_resolved_date) as "LastResolvedDate", CASE WHEN h.last_resolved_date IS NOT NULL THEN dbo.fn_cti_datetime_diff(h.reported_date, h.last_resolved_date, '') ELSE NULL END as "TTR", CASE WHEN h.last_resolved_date IS NOT NULL THEN CAST(dbo.fn_cti_datetime_diff(h.reported_date, h.last_resolved_date, 'N') as DECIMAL(20,2)) ELSE NULL END as "iTTR", dbo.fn_cti_convertardatetime(closed_date) as "ClosedDate", CASE WHEN h.closed_date IS NOT NULL THEN dbo.fn_cti_datetime_diff(h.closed_date,0,'') ELSE NULL END as "TTC", CASE WHEN h.closed_date IS NOT NULL THEN CAST(dbo.fn_cti_datetime_diff(h.closed_date,0,'N') as DECIMAL(20,2)) ELSE NULL END as "iTTC", ACount.acount TimesAssigned FROM HPD_HELP_DESK H LEFT JOIN CTI_FieldValues HPDStatus ON HPDStatus.enumid = H.status AND HPDStatus.formname = 'HPD:Help Desk' AND HPDStatus.fieldname = 'Status' LEFT JOIN ( SELECT INCIDENT_NUMBER, COUNT(INCIDENT_NUMBER) AS acount FROM HPD_HELP_DESK_ASSIGNMENT_LOG where ASSIGNMENT_TYPE = 2000 /* Assigned */ GROUP BY INCIDENT_NUMBER ) ACOUNT ON H.Incident_Number=ACOUNT.Incident_Number) "HPD_AgeInformation" on ("dbo_HPD_Help_Desk"."Incident_Number" = "HPD_AgeInformation"."IncidentNumber")
left outer join (select enumid, value FROM CTI_FieldValues where formname = 'HPD:Help Desk' and fieldname = 'Priority') "HPD_Priority" on ("dbo_HPD_Help_Desk"."Priority" = "HPD_Priority"."enumid")
left outer join (select enumid, value FROM CTI_FieldValues where formname = 'HPD:Help Desk' and fieldname = 'Service Type') "HPD_ServiceType" on ("dbo_HPD_Help_Desk"."Service_Type" = "HPD_ServiceType"."enumid")
left outer join (select enumid, value FROM CTI_FieldValues where formname = 'HPD:Help Desk' and fieldname = 'Status') "HPD_Status" on ("dbo_HPD_Help_Desk"."Status" = "HPD_Status"."enumid")
where "HPD_Status"."value" in ('Assigned', 'Closed', 'In Progress', 'Pending', 'Resolved') and "HPD_ServiceType"."value" in ('User Service Request') and "dbo_HPD_Help_Desk"."Categorization_Tier_1" in ('Account/Access') and "HPD_AgeInformation"."LastResolvedDate" > '2014-03-07 14:40:20'
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 8, 2014 at 11:19 am
There are a few things that would make this easier to help with:
1. Execution plans for each query
2. The code for dbo.fn_cti_convertardatetime
3. The schema for the tables used. Especially indexes on the tables.
Off the top of my head I can tell you that the last one runs so long because the use of the scalar function to get LastResolvedDate. Because it is used in derived table and then that derived column is being used in the Where clause the query is essentially turned into a cursor because it has to process the entire result set to get the date in human readable format and then compare each row to the data value supplied. The best it can do is an index or table scan, there is no possibility of a seek.
You are much better off using the base column in the WHERE clause and then applying a function to the passed in date to convert it to the format stored in the base column. You can still use the function in the SELECT clause to return the date in human readable format, but don't use that in the WHERE clause.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply