January 30, 2009 at 4:57 am
Hi everyone,
I Have a big report that is run via crystal reports that currently takes aorund 7-10 minutes to run depending on the predicate values. I have been asked to help improve the speed as sometimes when it is run in the application, the report times out.
[Code]
SELECT "Customer"."URN_text",
"Customer"."Name",
"Customer"."Customer_ID",
"Work_List"."Long_name",
"Work_List_Entry"."Customer_ID",
"Reason_Code_Canvass_Status"."Reason_ID",
"Operator"."Log_on_name",
"vTMG_Work_List_Customer_Sales"."Last_Six_Weeks_Net_Price",
"Event"."Event_timestamp",
"Event"."Event_ID",
"Event"."Event_description",
"Reason_Code_Canvass_Detail"."Long_name",
"Work_List_Entry"."Rejector_ID",
"Diary_Entry"."Creation_timestamp",
"Diary_Entry"."Action_status",
"Reason_Code_Canvass_Status"."Long_name",
"Diary_Entry"."Action_date",
"Diary_Entry_Type"."Long_name",
"Diary_Entry"."Diary_Entry_ID",
"Reason_Code_Event_Diary_Entry"."Long_name",
"vTMG_Work_List_Customer_Sales"."Last_Order_Insert_Date",
"vTMG_Work_List_Customer_Sales"."Last_Order_Net_Price",
"Business_Subset"."Business_Subset_ID",
"Diary_Entry"."Description",
"Event_Diary_Entry"."Event_description"
FROM
"Customer" "Customer"
INNER JOIN "Business_Subset" "Business_Subset" ON "Customer"."Business_Subset_ID"="Business_Subset"."Business_Subset_ID"
INNER JOIN "Customer_Canvasser" "Customer_Canvasser" ON "Customer"."Customer_ID"="Customer_Canvasser"."Customer_ID"
INNER JOIN "Reason_Code" "Reason_Code_Canvass_Status" ON "Customer_Canvasser"."Canvass_Status_ID"="Reason_Code_Canvass_Status"."Reason_ID"
INNER JOIN "Operator" "Operator" ON "Customer_Canvasser"."Operator_ID"="Operator"."Operator_ID"
INNER JOIN "TMG_Work_List_Entry" "TMG_Work_List_Entry" ON "Customer"."Customer_ID"="TMG_Work_List_Entry"."Customer_ID"
INNER JOIN "Work_List" "Work_List" ON "TMG_Work_List_Entry"."Work_List_ID"="Work_List"."Work_List_ID"
INNER JOIN "vTMG_Work_List_Customer_Sales" "vTMG_Work_List_Customer_Sales" ON "TMG_Work_List_Entry"."Work_List_ID"="vTMG_Work_List_Customer_Sales"."Work_List_ID"
AND "TMG_Work_List_Entry"."Work_List_Entry_ID"="vTMG_Work_List_Customer_Sales"."Work_List_Entry_ID"
AND "TMG_Work_List_Entry"."Customer_ID"="vTMG_Work_List_Customer_Sales"."Customer_id"
LEFT OUTER JOIN "Event" "Event" ON "TMG_Work_List_Entry"."Customer_ID"="Event"."Customer_ID"
AND "Work_List"."Long_name"="Event"."Work_list_name"
LEFT OUTER JOIN "Work_List_Entry" "Work_List_Entry" ON "TMG_Work_List_Entry"."Work_List_ID"="Work_List_Entry"."Work_List_ID"
AND "TMG_Work_List_Entry"."Work_List_Entry_ID"="Work_List_Entry"."Work_List_Entry_ID"
AND "TMG_Work_List_Entry"."Customer_ID"="Work_List_Entry"."Customer_ID"
LEFT OUTER JOIN "vTMG_Work_List_Diary_Entries" "vTMG_Work_List_Diary_Entries" ON "TMG_Work_List_Entry"."Work_List_ID"="vTMG_Work_List_Diary_Entries"."Work_List_ID"
AND "TMG_Work_List_Entry"."Work_List_Entry_ID"="vTMG_Work_List_Diary_Entries"."Work_List_Entry_ID"
AND "TMG_Work_List_Entry"."Customer_ID"="vTMG_Work_List_Diary_Entries"."Customer_id"
LEFT OUTER JOIN "Diary_Entry" "Diary_Entry" ON "vTMG_Work_List_Diary_Entries"."Diary_Entry_ID"="Diary_Entry"."Diary_Entry_ID"
LEFT OUTER JOIN "Event" "Event_Diary_Entry" ON "vTMG_Work_List_Diary_Entries"."Diary_Entry_ID"="Event_Diary_Entry"."Diary_Entry_ID"
LEFT OUTER JOIN "Diary_Entry_Type" "Diary_Entry_Type" ON "Diary_Entry"."Diary_Entry_Type_ID"="Diary_Entry_Type"."Diary_Entry_Type_ID"
LEFT OUTER JOIN "Canvass_Detail" "Canvass_Detail_Event_Diary_Entry" ON "Event_Diary_Entry"."Event_ID"="Canvass_Detail_Event_Diary_Entry"."Event_ID"
LEFT OUTER JOIN "Reason_Code" "Reason_Code_Event_Diary_Entry" ON "Canvass_Detail_Event_Diary_Entry"."Reason_ID"="Reason_Code_Event_Diary_Entry"."Reason_ID"
LEFT OUTER JOIN "Canvass_Detail" "Canvass_Detail" ON "Event"."Event_ID"="Canvass_Detail"."Event_ID"
LEFT OUTER JOIN "Reason_Code" "Reason_Code_Canvass_Detail" ON "Canvass_Detail"."Reason_ID"="Reason_Code_Canvass_Detail"."Reason_ID"
WHERE "Business_Subset"."Business_Subset_ID"=5000024
AND "Reason_Code_Canvass_Status"."Reason_ID"=6025384
AND "Work_List"."Long_name" LIKE 'Tele to TOLA%'
ORDER BY "Operator"."Log_on_name", "Customer"."Customer_ID", "Diary_Entry"."Creation_timestamp"
[/Code]
[Code]
CREATE view [dbo].[vTMG_Work_List_Customer_Sales] as
SELECT
Work_List.Work_List_ID,
TMG_Work_List_Entry.Work_List_Entry_ID,
TMG_Work_List_Entry.Customer_id,
-- sales for 6 weeks prior to work list creation
COALESCE((
SELECT
SUM(Order_Insert.Insert_Net_Price)
FROM
Order_Record INNER JOIN
Order_Row ON Order_Record.Order_ID = Order_Row.Order_ID INNER JOIN
Order_Insert ON Order_Row.Order_ID = Order_Insert.Order_ID
AND Order_Row.Order_Row_ID = Order_Insert.Order_Row_ID
WHERE
order_record.customer_id = TMG_work_list_entry.customer_id
AND Order_Insert.Insert_Date >= Work_List.Create_Time - 36
AND Order_Insert.Insert_Date <= Work_List.Create_Time
GROUP BY
order_record.customer_id),0) AS Last_Six_Weeks_Net_Price,
--Last Order Insert Date
(SELECT DISTINCT
last_live_insert_date
FROM
vTMG_Customer_Last_Order
WHERE
vTMG_Customer_Last_Order.Customer_Id = TMG_Work_List_Entry.Customer_id) AS Last_Order_Insert_Date,
--Last Order Value
COALESCE((
SELECT
SUM(Order_Insert.Insert_Net_Price)
FROM
vTMG_Customer_Last_Order INNER JOIN
Order_Insert ON vTMG_Customer_Last_Order.Order_Id = Order_Insert.Order_ID AND
vTMG_Customer_Last_Order.Last_live_insert_date = Order_Insert.Insert_date
WHERE
vTMG_Customer_Last_Order.customer_id = TMG_work_list_entry.customer_id
GROUP BY
vTMG_Customer_Last_Order.customer_id),0) AS Last_Order_Net_Price
FROM
Work_List INNER JOIN
TMG_Work_List_Entry ON Work_List.Work_List_ID = TMG_Work_List_Entry.Work_List_ID --INNER JOIN
[/Code]
[Code]
CREATE VIEW [dbo].[vTMG_Customer_Last_Order]
AS
SELECT
Customer_ID,
Order_Id,
COALESCE(Last_live_insert_date, Last_booked_insert_date) AS Last_live_insert_date
FROM
Order_Record o
WHERE
COALESCE(Last_live_insert_date, Last_booked_insert_date) =
(
SELECT
MAX(COALESCE(Last_live_insert_date, Last_booked_insert_date)) AS Last_live_insert_date
FROM
Order_Record orc
WHERE
orc.customer_id = o.customer_id)
[/Code]
[Code]
CREATE VIEW [dbo].[vTMG_Customer_Last_Order]
AS
SELECT
Customer_ID,
Order_Id,
COALESCE(Last_live_insert_date, Last_booked_insert_date) AS Last_live_insert_date
FROM
Order_Record o
WHERE
COALESCE(Last_live_insert_date, Last_booked_insert_date) =
(
SELECT
MAX(COALESCE(Last_live_insert_date, Last_booked_insert_date)) AS Last_live_insert_date
FROM
Order_Record orc
WHERE
orc.customer_id = o.customer_id)
[/Code]
[Code]
SELECT
w.*
FROM
work_list_entry w LEFT OUTER JOIN
TMG_Work_list_Entry t ON w.work_list_id = t.work_list_id
andw.work_list_entry_id = t.work_list_entry_id
and w.customer_id = t.customer_id
WHERE
t.customer_id IS NULL
[/Code]
I Will explain the problem in the next post and the investigation i have done so far..
January 30, 2009 at 5:14 am
Things like this will lead to table scans, which will kill your performance:
WHERE
COALESCE(Last_live_insert_date, Last_booked_insert_date)
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 30, 2009 at 5:17 am
Indexes have been added where needed but the main issue is the number of physical reads the report is having request.
[Code]
(1062 row(s) affected)
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.
Table 'Canvass_Detail'. Scan count 21, logical reads 140, physical reads 0, read-ahead reads 52, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Event'. Scan count 14, logical reads 881, physical reads 155, read-ahead reads 32, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Diary_Entry'. Scan count 1055, logical reads 3759, physical reads 831, read-ahead reads 110, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TMG_Work_List_Entry'. Scan count 12, logical reads 6474, physical reads 0, read-ahead reads 21, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Work_List'. Scan count 1, logical reads 2142, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Work_List_Entry'. Scan count 0, logical reads 3239, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Order_Insert'. Scan count 17086, logical reads 68581, physical reads 11765, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Order_Record'. Scan count 7295, logical reads 22487, physical reads 679, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Order_Row'. Scan count 26, logical reads 161, physical reads 1, read-ahead reads 40, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customer'. Scan count 0, logical reads 7021, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Operator'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customer_Canvasser'. Scan count 1269, logical reads 7962, physical reads 0, read-ahead reads 12, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Business_Subset'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Reason_Code'. Scan count 2, logical reads 10, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Diary_Entry_Type'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 2187 ms, elapsed time = 427431 ms.
[/Code]
When checking sys.dm_exec_requests the wait type is PAGEIOLATCH_SH which i assume is because the pages cannot be sent to data cache quickly enough.
I will be defragging the tables that are involved in this query which should help reduce the number of physical reads as well as the scan count.
January 30, 2009 at 5:35 am
I think the bottleneck is the first view. All these inline select statements generally slow down a lot. If you move these to LEFT OUTER JOINs, at least your query becomes a lot better readable, and probably will solve your problem too.
I tried to change the query to your needs, but without the table definitions, it's hard to check myself. Hope you at least understand a bit what I try to do. CREATE view [dbo].[vTMG_Work_List_Customer_Sales] as
SELECT Work_List.Work_List_ID, TMG_Work_List_Entry.Work_List_Entry_ID, TMG_Work_List_Entry.Customer_id,
COALESCE(O_R.Insert_Net_Price,0) AS Last_Six_Weeks_Net_Price, Last_Order_Insert_Date,
COALESCE(TMG.Last_Order_Net_Price,0) Last_Order_Net_Price
FROM Work_List
INNER JOIN TMG_Work_List_Entry ON Work_List.Work_List_ID = TMG_Work_List_Entry.Work_List_ID
LEFT OUTER JOIN
(
SELECT Insert_Date, Customer_ID, SUM(Order_Insert.Insert_Net_Price) Insert_Net_Price
FROM Order_Record
INNER JOIN Order_Row ON Order_Record.Order_ID = Order_Row.Order_ID
INNER JOIN Order_Insert ON Order_Row.Order_ID = Order_Insert.Order_ID AND Order_Row.Order_Row_ID = Order_Insert.Order_Row_ID
WHERE
GROUP BY order_record.customer_id
) O_R ON O_R.customer_id = TMG_work_list_entry.customer_id
AND O_R.Insert_Date >= Work_List.Create_Time - 36
AND O_R.Insert_Date <= Work_List.Create_Time
LEFT OUTER JOIN vTMG_Customer_Last_Order ON vTMG_Customer_Last_Order.Customer_Id = TMG_Work_List_Entry.Customer_id
LEFT OUTER JOIN
(
SELECT SUM(Order_Insert.Insert_Net_Price) Last_Order_Net_Price
FROM vTMG_Customer_Last_Order
INNER JOIN Order_Insert ON vTMG_Customer_Last_Order.Order_Id = Order_Insert.Order_ID AND vTMG_Customer_Last_Order.Last_live_insert_date = Order_Insert.Insert_date
GROUP BY vTMG_Customer_Last_Order.customer_id
) TMG ON vTMG_Customer_Last_Order.customer_id = TMG_work_list_entry.customer_id
Next step can be to put all the JOINs together in one statement.
Let us know if this approach gives any improvement on performance.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
January 30, 2009 at 5:39 am
Can you post the execution plans?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 30, 2009 at 5:44 am
January 30, 2009 at 5:50 am
Usually save it as a .sqlplan file and then zip it up and post it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 30, 2009 at 7:04 am
January 30, 2009 at 7:37 am
It looks largely pretty good. There are several key lookups and the four index scans at the top. For example one of the key lookups is against Customer_Canvasser and it's only returning one column. You could add that column to the IX_Customer_Canvasser as an INCLUDE which would eliminate the key lookup. My understanding is that a key lookup is worth 3 additional reads per lookup, which is what makes them so expensive.
Except for the scans & the lookups, nothing is jumping out, but I'd focus on those areas.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 30, 2009 at 1:08 pm
You don't have an actual plan do you? It's possible that the stats are off somewhere, and with a plan this complex a small difference in one node could make a large difference to the plan as a whole.
February 2, 2009 at 2:54 pm
HI guys,
ive attached the actual execution plan, as well as the estimated plan and statistics io log from a LIVE report run.
The customer_sales_work_list view has also had some predicates added to it
CREATE view [dbo].[vTMG_Work_List_Customer_Sales] as
SELECT
Work_List.Work_List_ID,
TMG_Work_List_Entry.Work_List_Entry_ID,
TMG_Work_List_Entry.Customer_id,
-- sales for 6 weeks prior to work list creation
COALESCE((
SELECT
SUM(Order_Insert.Insert_Net_Price)
FROM
Order_Record INNER JOIN
Order_Insert ON Order_Record.Order_ID = Order_Insert.Order_ID
WHERE
order_record.customer_id = TMG_work_list_entry.customer_id
AND Order_Insert.Insert_Date >= Work_List.Create_Time - 36
AND Order_Insert.Insert_Date <= Work_List.Create_Time
GROUP BY
order_record.customer_id),0) AS Last_Six_Weeks_Net_Price,
--Last Order Insert Date
(SELECT DISTINCT
last_live_insert_date
FROM
vTMG_Customer_Last_Order
WHERE
vTMG_Customer_Last_Order.Customer_Id = TMG_Work_List_Entry.Customer_id) AS Last_Order_Insert_Date,
--Last Order Value
COALESCE((
SELECT
SUM(Order_Insert.Insert_Net_Price)
FROM
vTMG_Customer_Last_Order INNER JOIN
Order_Insert ON vTMG_Customer_Last_Order.Order_Id = Order_Insert.Order_ID AND
vTMG_Customer_Last_Order.Last_live_insert_date = Order_Insert.Insert_date
WHERE
vTMG_Customer_Last_Order.customer_id = TMG_work_list_entry.customer_id
GROUP BY
vTMG_Customer_Last_Order.customer_id),0) AS Last_Order_Net_Price
FROM
Work_List INNER JOIN
TMG_Work_List_Entry ON Work_List.Work_List_ID = TMG_Work_List_Entry.Work_List_ID
WHERE
(Work_List.Long_name Like 'Tele to TOLA%'
OR Work_List.Long_name Like 'Tele to Field%'
OR Work_List.Long_name Like 'Tele to Tele%'
OR Work_List.Long_name Like 'Field to Tele^')
CREATE view [dbo].[vTMG_Work_List_Diary_Entries] as
SELECT
Work_List.Work_List_ID,
TMG_Work_List_Entry.Work_List_Entry_ID,
TMG_Work_List_Entry.Customer_id,
Diary_Entry_ID
FROM
Work_List INNER JOIN
TMG_Work_List_Entry ON Work_List.Work_List_ID = TMG_Work_List_Entry.Work_List_ID INNER JOIN
Diary_Entry ON tmg_Work_list_Entry.customer_id = diary_entry.customer_id
WHERE
(Work_List.Long_name Like 'Tele to TOLA%'
OR Work_List.Long_name Like 'Tele to Field%'
OR Work_List.Long_name Like 'Tele to Tele%'
OR Work_List.Long_name Like 'Field to Tele%')
AND Creation_timestamp >= Work_List.Create_time
this has been done to help narrow the result set.
February 3, 2009 at 5:34 am
I can't seem to open the new zip attachment. Sorry.
Looking at the query some more, you've got correlated sub-queries in the select list. This is effectively RBAR (row-by-agonizing-row) processing. Those queries are run once for each row in the outer query (sort of). I'd work on making them into derived tables and put them in the JOIN. This is the part I'm referring to:
... COALESCE((
SELECT
SUM(Order_Insert.Insert_Net_Price)
FROM
vTMG_Customer_Last_Order INNER JOIN
Order_Insert ON vTMG_Customer_Last_Order.Order_Id = Order_Insert.Order_ID AND
vTMG_Customer_Last_Order.Last_live_insert_date = Order_Insert.Insert_date
WHERE
vTMG_Customer_Last_Order.customer_id = TMG_work_list_entry.customer_id
GROUP BY
vTMG_Customer_Last_Order.customer_id),0) AS Last_Order_Net_Price
FROM
Work_List INNER JOIN
...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2009 at 6:01 am
There's another possible angle here. Where is the code running from? Is it a query placed in a command object in Crystal or from a stored procedure? How many rows come across to Crystal (it's shown bottom right during data refresh) and how many rows are actually used in the report?
If the rowcount returned to the client is high then consider setting up a subreport. The main report collects the parameters and passes them to a subreport which executes a parameterised stored procedure containing your Huge Query.
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
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply