May 28, 2015 at 10:18 pm
Hi All,
I am tuning this query ( Please see the bottom query)
Duration : 27 sec
I attach the execution plan
Previously I added 1 nonclustered index :
CREATE nonCLUSTERED INDEX IX_Requestevent_PersonIDFrom
ON dbo.request_event (person_id_from)
include (request_id, event_type, event_dt, description)
here is the statistic Before adding the index:
(685287 row(s) affected)
Table 'request_contact'. Scan count 5, logical reads 1445, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'request_product'. Scan count 5, logical reads 1804, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'place'. Scan count 5, logical reads 130, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'request'. Scan count 5, logical reads 13955, physical reads 0, read-ahead reads 0, lob logical reads 102379, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 78008, logical reads 1682598, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'person'. Scan count 1, logical reads 59, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
[highlight=#ffff11]Table 'request_event'. Scan count 5, logical reads 22920, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.[/highlight]
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
After adding :
(685287 row(s) affected)
Table 'request_contact'. Scan count 5, logical reads 1445, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'request_product'. Scan count 5, logical reads 1804, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'place'. Scan count 5, logical reads 130, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'request'. Scan count 5, logical reads 13955, physical reads 0, read-ahead reads 0, lob logical reads 102379, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 78008, logical reads 1682598, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'person'. Scan count 1, logical reads 59, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
[highlight=#ffff11]Table 'request_event'. Scan count 5, logical reads 8459, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.[/highlight]
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
So I lower down logical read in Table request_event . But I really have no idea how to lower down Worktable which is VERY HiGH and logical read in Table Request is also high
The query is as below :
SELECT DISTINCT "Request_Details_with_Contact9"."Request_Id" AS "Request_Id",
"Request_Details_with_Contact9"."req_status" AS "req_status",
"Request_Details_with_Contact9"."Place_Name" AS "Place_Name",
"Request_Details_with_Contact9"."Request_Type" AS "Request_Type",
"Request_Details_with_Contact9"."Priority" AS "Priority",
"Request_Details_with_Contact9"."Logging_Method_Monitoring" AS "Logging_Method_Monitoring",
"Request_Details_with_Contact9"."Contractual_Override" AS "Contractual_Override",
"Request_Details_with_Contact9"."Additional_Problem_Notes" AS "Additional_Problem_Notes",
"Request_Details_with_Contact9"."First_Name" AS "First_Name",
"Request_Details_with_Contact9"."Name" AS "Name",
"Request_Details_with_Contact9"."Email_Address" AS "Email_Address",
"Request_Event10"."Event_Type" AS "Event_Type",
"Request_Event10"."Event_Datetime" AS "Event_Time",
"Request_Details_with_Contact9"."Request_Category" AS "Request_Category"
FROM
(
SELECT DISTINCT "Request_details7"."Request_Id" AS "Request_Id",
"Request_details7"."Model_Id" AS "Model_Id",
"Request_details7"."Serial_Id" AS "Serial_Id",
"Request_details7"."Product_Family" AS "Product_Family",
"Request_details7"."Product_Name" AS "Product_Name",
"Request_details7"."Request_Category" AS "Request_Category",
"Request_details7"."Problem_Group" AS "Problem_Group",
"Request_details7"."Problem_Description" AS "Problem_Description",
"Request_details7"."Request_Contract_Type" AS "Request_Contract_Type",
"Request_details7"."Problem_or_Change_Ref" AS "Problem_or_Change_Ref",
"Request_details7"."Additional_Problem_Notes" AS "Additional_Problem_Notes",
"Request_details7"."req_status" AS "req_status",
"Request_details7"."Place_Name" AS "Place_Name",
"Request_details7"."Request_Type" AS "Request_Type",
"Request_details7"."Problem_Group1" AS "Problem_Group1",
"Request_details7"."Problem_Code" AS "Problem_Code",
"Request_details7"."Priority" AS "Priority",
"Request_details7"."Logging_Method_Monitoring" AS "Logging_Method_Monitoring",
"Request_details7"."Contractual_Override" AS "Contractual_Override",
"Request_details7"."Service_Disruption_Type" AS "Service_Disruption_Type",
"Request_Contact8"."First_Name" AS "First_Name",
"Request_Contact8"."Name" AS "Name",
"Request_Contact8"."Email_Address" AS "Email_Address",
"Request_Contact8"."Phone" AS "Phone"
FROM
(
SELECT DISTINCT "request6"."request_id" AS "Request_Id",
"request_product"."model_id" AS "Model_Id",
"request_product"."serial_id" AS "Serial_Id",
"request_product"."product_family" AS "Product_Family",
"request_product"."product_name" AS "Product_Name",
"request6"."req_category" AS "Request_Category",
"request6"."user_def_lvl1" AS "Problem_Group",
"request6"."user_def_lvl2" AS "Problem_Description",
"request6"."req_contr_type" AS "Request_Contract_Type",
"request6"."user_def_1" AS "Problem_or_Change_Ref",
"request6"."cust_prob_descr" AS "Additional_Problem_Notes",
"request6"."req_status" AS "req_status",
"place___request"."name" AS "Place_Name",
"request6"."req_type" AS "Request_Type",
"request6"."user_def_lvl1" AS "Problem_Group1",
"request6"."problem_code" AS "Problem_Code",
"request6"."priority" AS "Priority",
"request6"."req_class" AS "Logging_Method_Monitoring",
"request6"."resolution_stts" AS "Contractual_Override",
"request6"."action_status" AS "Service_Disruption_Type"
FROM
(
(
SELECT "request_id",
"u_version",
"place_id",
"string_id",
"cross_reference",
"security_code",
"global_name",
"zippost",
"request_id_parent",
"origin",
"contract_id_noprd",
"contract_ver_nopr",
"contr_seq_noprd",
"contract_id_task",
"contract_ver_task",
"contr_seq_task",
"segment_code",
"req_contr_type",
"next_request_seq",
"prod_transactn_id",
"place_id_to_bill",
"parts_only",
"po_required",
"po",
"req_price_adj_pct",
"payment_method",
"payment_id",
"prepayment_amount",
"status",
"billing_status",
"billing_allowed",
"check_for_billing",
"posting_group",
"credit_terms_cd",
"approval_lvl_reqd",
"person_id_owner",
"person_id_apprv_by",
"approval_dt",
"approval_tm",
"req_type",
"req_group",
"req_class",
"req_category",
"repair_request",
"problem_code",
"problem_class",
"escalation_level",
"color_level",
"escalation_rte",
"escalate_flag",
"suspended",
"total_suspend_tm",
"priority",
"visibility",
"severity",
"comm_interval_min",
"resp_interval_min",
"action_status",
"needs_callback",
"interesting",
"obliged_calbk_dt",
"obliged_calbk_tm",
"obliged_arrive_dt",
"obliged_arrive_tm",
"check_performance",
"req_status",
"resolution_stts",
"times_printed",
"currency",
"language_code",
"linked",
"phy_svc_grp_a",
"phy_svc_grp_b",
"phy_svc_grp_c",
"phy_svc_grp_d",
"phy_svc_grp_e",
"sales_grp_a",
"sales_grp_b",
"sales_grp_c",
"sales_grp_d",
"sales_grp_e",
"contr_id_purch",
"contr_ver_purch",
"queue_status",
"place_id_sent_by",
"place_id_ship_to",
"place_id_req_issd",
"cust_debit_memo",
"debit_memo_ref_po",
"schedule_due_dt",
"schedule_ship_dt",
"payment_method_typ",
"credit_card_number",
"credit_card_type",
"ccard_issued_by",
"credit_card_exp_dt",
"card_holder_name",
"cc_approval_cd",
"freight_terms_rcv",
"freight_terms_shp",
"ship_via_rcv",
"ship_via_shp",
"proof_of_purchase",
"customs_id",
"status_condition",
"hold_condition",
"force_detail_print",
"user_def_1",
"user_def_2",
"user_def_3",
"user_def_d1",
"user_def_d2",
"user_def_n1",
"user_def_n2",
"user_def_lvl1",
"user_def_lvl2",
"user_def_lvl3",
"created_id",
"created_dt",
"created_tm",
"last_updated_id",
"last_updated_dt",
"last_updated_tm",
CAST( "cust_prob_descr" AS VARCHAR( 2000 ) ) AS "cust_prob_descr"
FROM "MetrixDatamart_T"."dbo"."request"
)
"request6"
INNER JOIN "MetrixDatamart_T"."dbo"."place" "place___request"
ON "request6"."place_id" = "place___request"."place_id"
)
LEFT OUTER JOIN "MetrixDatamart_T"."dbo"."request_product" "request_product"
ON "request6"."request_id" = "request_product"."request_id"
)
"Request_details7"
LEFT OUTER JOIN
(
SELECT DISTINCT "request_contact"."request_id" AS "Request_Id",
"request_contact"."first_name" AS "First_Name",
"request_contact"."name" AS "Name",
"request_contact"."email_address" AS "Email_Address",
"request_contact"."phone" AS "Phone"
FROM "MetrixDatamart_T"."dbo"."request_contact" "request_contact"
)
"Request_Contact8"
ON "Request_details7"."Request_Id" = "Request_Contact8"."Request_Id"
)
"Request_Details_with_Contact9",
(
SELECT DISTINCT "request_event"."request_id" AS "Request_Id",
"request_event"."event_type" AS "Event_Type",
"request_event"."event_dt" AS "Event_Datetime",
"request_event"."description" AS "Description",
"person___request_event"."name" AS "Person_Name"
FROM "MetrixDatamart_T"."dbo"."request_event" "request_event",
"MetrixDatamart_T"."dbo"."person" "person___request_event"
WHERE "request_event"."person_id_from" = "person___request_event"."person_id"
)
"Request_Event10"
WHERE "Request_Details_with_Contact9"."req_status" = 'COMPLETED'
AND ("Request_Details_with_Contact9"."Problem_Group" <> 'INTERNAL'
OR "Request_Details_with_Contact9"."Problem_Group" is null)
AND "Request_Details_with_Contact9"."Request_Id" = "Request_Event10"."Request_Id"
ORDER BY 1 asc ,
2 asc ,
3 asc ,
4 asc ,
5 asc ,
6 asc ,
7 asc ,
8 asc ,
9 asc ,
10 asc ,
11 asc ,
12 asc ,
13 asc
-------------------------------------------------------------------------------------------------
Please help how to optimize it .. Really appreciate it
Cheers
May 28, 2015 at 11:10 pm
You've been around enough to know about posting DLL, sample data, etc... You're also posting the estimated query plan. The actual query plan would be better.
Anyhow, from a birds-eye view...
That non-clustered index is obviously not helping much based on your stats. All those SELECT DISTINCTS are killing you; a sign of very bad database design. You're query plan is showing mostly Clustered Index Scans. A non-clustered index seek would probably be better. Again, due to bad data modeling and poor query design.
-- Itzik Ben-Gan 2001
May 29, 2015 at 2:33 am
WhiteLotus (5/28/2015)
Hi All,I am tuning this query ( Please see the bottom query)
<<snip>>
Please help how to optimize it .. Really appreciate it
Cheers
Optimising performance of queries is much easier if they are logically laid out and nicely formatted. This one isn't - it's awful. Below is the output from the Simple Talk prettifier with some extra white space and a couple of comments. Note that the ST prettifier couldn't quite figure it out - neither can I. I'd strongly recommend you invest some time in a rewrite of this junk into something more sensible. You may not even need to optimise it afterwards.
SELECT DISTINCT
Request_Details_with_Contact9.Request_Id AS Request_Id,
Request_Details_with_Contact9.req_status AS req_status,
Request_Details_with_Contact9.Place_Name AS Place_Name,
Request_Details_with_Contact9.Request_Type AS Request_Type,
Request_Details_with_Contact9.Priority AS Priority,
Request_Details_with_Contact9.Logging_Method_Monitoring AS Logging_Method_Monitoring,
Request_Details_with_Contact9.Contractual_Override AS Contractual_Override,
Request_Details_with_Contact9.Additional_Problem_Notes AS Additional_Problem_Notes,
Request_Details_with_Contact9.First_Name AS First_Name,
Request_Details_with_Contact9.Name AS Name,
Request_Details_with_Contact9.Email_Address AS Email_Address,
Request_Event10.Event_Type AS Event_Type,
Request_Event10.Event_Datetime AS Event_Time,
Request_Details_with_Contact9.Request_Category AS Request_Category
FROM ( -- Request_Details_with_Contact9
SELECT DISTINCT Request_details7.Request_Id AS Request_Id,
Request_details7.Model_Id AS Model_Id,
Request_details7.Serial_Id AS Serial_Id,
Request_details7.Product_Family AS Product_Family,
Request_details7.Product_Name AS Product_Name,
Request_details7.Request_Category AS Request_Category,
Request_details7.Problem_Group AS Problem_Group,
Request_details7.Problem_Description AS Problem_Description,
Request_details7.Request_Contract_Type AS Request_Contract_Type,
Request_details7.Problem_or_Change_Ref AS Problem_or_Change_Ref,
Request_details7.Additional_Problem_Notes AS Additional_Problem_Notes,
Request_details7.req_status AS req_status,
Request_details7.Place_Name AS Place_Name,
Request_details7.Request_Type AS Request_Type,
Request_details7.Problem_Group1 AS Problem_Group1,
Request_details7.Problem_Code AS Problem_Code,
Request_details7.Priority AS Priority,
Request_details7.Logging_Method_Monitoring AS Logging_Method_Monitoring,
Request_details7.Contractual_Override AS Contractual_Override,
Request_details7.Service_Disruption_Type AS Service_Disruption_Type,
Request_Contact8.First_Name AS First_Name,
Request_Contact8.Name AS Name,
Request_Contact8.Email_Address AS Email_Address,
Request_Contact8.Phone AS Phone
FROM (
SELECT DISTINCT
request6.request_id AS Request_Id,
request_product.model_id AS Model_Id,
request_product.serial_id AS Serial_Id,
request_product.product_family AS Product_Family,
request_product.product_name AS Product_Name,
request6.req_category AS Request_Category,
request6.user_def_lvl1 AS Problem_Group,
request6.user_def_lvl2 AS Problem_Description,
request6.req_contr_type AS Request_Contract_Type,
request6.user_def_1 AS Problem_or_Change_Ref,
request6.cust_prob_descr AS Additional_Problem_Notes,
request6.req_status AS req_status,
place___request.name AS Place_Name,
request6.req_type AS Request_Type,
request6.user_def_lvl1 AS Problem_Group1,
request6.problem_code AS Problem_Code,
request6.priority AS Priority,
request6.req_class AS Logging_Method_Monitoring,
request6.resolution_stts AS Contractual_Override,
request6.action_status AS Service_Disruption_Type
FROM (
(SELECT
request_id, -- followed by many other columns, most of which aren't referenced again
CAST( cust_prob_descr AS VARCHAR( 2000 ) ) AS cust_prob_descr
FROM MetrixDatamart_T.dbo.request
) request6
INNER JOIN MetrixDatamart_T.dbo.place place___request
ON request6.place_id = place___request.place_id
) -- missing alias???
LEFT OUTER JOIN MetrixDatamart_T.dbo.request_product request_product
ON request6.request_id = request_product.request_id
) Request_details7
LEFT OUTER JOIN (
SELECT DISTINCT request_contact.request_id AS Request_Id,
request_contact.first_name AS First_Name,
request_contact.name AS Name,
request_contact.email_address AS Email_Address,
request_contact.phone AS Phone
FROM MetrixDatamart_T.dbo.request_contact request_coontact
) Request_Contact8
ON Request_details7.Request_Id = Request_Contact8.Request_Id
) Request_Details_with_Contact9,
-- Old-style join???
(
SELECT DISTINCT request_event.request_id AS Request_Id,
request_event.event_type AS Event_Type,
request_event.event_dt AS Event_Datetime,
request_event.description AS Description,
person___request_event.name AS Person_Name
FROM MetrixDatamart_T.dbo.request_event request_event, -- Old-style join???
MetrixDatamart_T.dbo.person person___request_event
WHERE request_event.person_id_from = person___request_event.person_id
) Request_Event10
WHERE Request_Details_with_Contact9.req_status = 'COMPLETED'
AND (Request_Details_with_Contact9.Problem_Group <> 'INTERNAL'
OR Request_Details_with_Contact9.Problem_Group IS NULL)
AND Request_Details_with_Contact9.Request_Id = Request_Event10.Request_Id
ORDER BY 1 ASC ,
2 ASC ,
3 ASC ,
4 ASC ,
5 ASC ,
6 ASC ,
7 ASC ,
8 ASC ,
9 ASC ,
10 ASC ,
11 ASC ,
12 ASC ,
13 ASC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 29, 2015 at 6:39 am
You seem to get Model_Id from a window and pass it up to an outer window, and that value does not get used in a filter, join or select on the last outer window.
Not going to check all columns.
Also, you are distincting a distinct of a distinct left joined to a distinct ansi-89 join distinct inside which is a ansi89 join again.
try writing your joins first so that you can build the relationships between the tables.
Don't window a select onto a table and bring back all columns. Just do a join directly on the table in the outer query.
Windowing here looks to serve no purpose.
Are you using a tool to create this query?
June 1, 2015 at 10:05 pm
MadAdmin (5/29/2015)
You seem to get Model_Id from a window and pass it up to an outer window, and that value does not get used in a filter, join or select on the last outer window.Not going to check all columns.
Also, you are distincting a distinct of a distinct left joined to a distinct ansi-89 join distinct inside which is a ansi89 join again.
try writing your joins first so that you can build the relationships between the tables.
Don't window a select onto a table and bring back all columns. Just do a join directly on the table in the outer query.
Windowing here looks to serve no purpose.
Are you using a tool to create this query?
I am not sure who wrote this query really but it is my job now to tune it 🙁 ... will check it again
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply