SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need some help with the query:


Need some help with the query:

Author
Message
New Born DBA
New Born DBA
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2008 Visits: 931
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]
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79002 Visits: 14972
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search