Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need some help with the query: Expand / Collapse
Author
Message
Posted Tuesday, July 8, 2014 10:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 26, 2014 8:31 AM
Points: 220, Visits: 594
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'




[i]"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]
Post #1590431
Posted Tuesday, July 8, 2014 11:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 10,205, Visits: 13,149
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1590445
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse