More efficient version?
Hi,
I have this query but it seems to take a really long time to complete. is there any modifications I could do that could perhaps make it a little more efficient? I was envisaging for a year and a halfs worth of data, perhaps taking a few minutes, but I stopped it before at 23 minutes.
I have this query but it seems to take a really long time to complete. is there any modifications I could do that could perhaps make it a little more efficient? I was envisaging for a year and a halfs worth of data, perhaps taking a few minutes, but I stopped it before at 23 minutes.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
declare @fromdate date
declare @todate date
set @fromdate = '2017-01-01'
set @todate = '2018-05-05'
SELECT
c.de_number
,client.cl_number
,c.de_status
,Max(CASE WHEN a1.co_type = '2' THEN a1.co_description ELSE c.de_status END) Prev_Stat
,c.de_type
,c.de_collector
,c.de_salesman
,c.de_listed_date
,c.de_owned_by_debtor
,c.de_principal
,c.de_principal + c.de_adjustments Netprin
,c.de_paid
,c.de_last_payment_date
,c.de_last_worked_date
,c.de_active
,c.de_poe
,c.de_zip
,CASE WHEN c.de_cell_phone IS NOT NULL THEN 'X'END Good_cell
,CASE WHEN c.de_home_phone IS NOT NULL THEN 'X'END Good_landline
,CASE WHEN c.de_email IS NOT NULL THEN 'X'END Good_Email
,Max(case when (co.co_description = 'DMOT Request for payment letter' OR co.co_description = 'DMOT Small Balance Letter') AND co.co_type = '0' then co.co_date end) first_demand_letter
,Max(case when co.co_description = '1st Demand Email' AND co.co_type = '0' then co.co_date end) first_demand_email
,Max(case when co.co_description = 'DMOT 48 Hour Notice' AND co.co_type = '0' then co.co_date end) 'DMOT-48hour'
,Max(case when co.co_description = '48 Hour -Email' AND co.co_type = '0' then co.co_date end) '48Hour Email'
,Max(case when co.co_description = '48 Hour 2nd Referral -Email' AND co.co_type = '0' then co.co_date end) '48hour_email-2nd Ref'
,Max(case when co.co_description = 'Credit Default Email' AND co.co_type = '0' then co.co_date end) credit_default_email
,Max(case when co.co_description = 'DMOT Solicitors Demand Letter' AND co.co_type = '0' then co.co_date end) DMOT_SOL
,Max(case when co.co_description = 'Vodafone Credit Listing Pending Email' AND co.co_type = '0' then co.co_date end) Voda_Pending_Credit_default
,Max(case when co.co_description = 'Legal Proceedings -Email' AND co.co_type = '0' then co.co_date end) Legal_Preceding
,Max(case when (co.co_description = 'Solicitors Demand Email - Vodafone' OR co.co_description = 'Solicitors Demand Email - Optus' or co.co_who_from = 'VODSOLE' OR
co.co_who_from = 'OPTSOLE' OR co.co_who_from = 'VIRSOLE') AND co.co_type = '0' then co.co_date end) Sol_Demand_Email
,Max(case when co.co_description = 'Optus 50% Settlement Email' AND co.co_type = '0' then co.co_date end) '50% Settlement Email'
,Max(CASE WHEN co.co_type = '3' THEN co.co_description END) Prom_desc
,Max(CASE WHEN co.co_type = '3' THEN co.co_amount END) Prom_amt
,Max(CASE WHEN co.co_type = '3' THEN co.co_date END) Prom_dd
,Max(CASE WHEN co.co_type = '3' AND co.co_repeat = '0' THEN 'No Repeat' WHEN co.co_type = '3' AND co.co_repeat = '2' THEN 'Weekly' WHEN co.co_type = '3' AND co.co_repeat = '3' THEN 'Fortnightly' WHEN co.co_type = '3' AND co.co_repeat = '4' THEN 'Monthly'END) Prom_Freq
,Max(case when co.co_completed = '0' then co.co_date end) 'future_contact'
,Max(case when co.co_description = 'Contact Made' OR co.co_description = 'Spoke with Third Party' OR co.co_description = 'Debtor Rang In' OR co.co_description = 'Rang debtor left message to call back' OR co.co_description = 'Account Viewed' OR co.co_description = 'Spoke with debtor' OR co.co_description = 'Payment arrangement reviewed' OR co.co_description = 'Dialler Called' then co.co_date end) Last_collector_date
,Max(CASE WHEN vedas.co_type = '2' then vedas.co_description END) vedas_desc
,Max(phonecnt.phonecount)
FROM debtor c
LEFT JOIN (SELECT contact.co_rowid_debtor, contact.co_type, contact.co_date, contact.co_description, Row_Number() OVER (PARTITION BY contact.co_rowid_debtor, contact.co_type ORDER BY contact.co_date DESC) AS rn FROM contact) a1 ON c.de_rowid = a1.co_rowid_debtor
LEFT JOIN contact co ON c.de_rowid = co.co_rowid_debtor
LEFT JOIN client ON c.de_rowid_client = client.cl_rowid
LEFT JOIN contact vedas ON c.de_rowid = vedas.co_rowid_debtor AND vedas.co_type = '2' AND c.de_listed_date > '2008-06-25'
LEFT JOIN (select
co_rowid_debtor
,Sum(CASE when co_description = 'Spoke with debtor' or co_description = 'Rang debtor left message to call back' then 1
when co_type = 22 and co_class != 2 and co_class != 3 then 1
when co_type = 1 and co_user_1 != 'Invalid' and co_user_1 != 'No Answer' and co_user_1 != 'busy' then 1
when co_type = 0 then 1
when co_type = 19 then 1
else 0 END) as phonecount
from contact
where co_date between @fromdate and @todate
group by co_rowid_debtor) as phonecnt ON phonecnt.co_rowid_debtor = c.de_rowid
where c.de_listed_date between @fromdate and @todate
GROUP BY
c.de_number
,client.cl_number
,c.de_status
,c.de_type
,c.de_collector
,c.de_salesman
,c.de_listed_date
,c.de_owned_by_debtor
,c.de_principal
,c.de_principal + c.de_adjustments
,c.de_paid
,c.de_last_payment_date
,c.de_last_worked_date
,c.de_active
,c.de_poe
,c.de_zip
,c.de_cell_phone
,c.de_home_phone
,c.de_emailASKER
Hi,
I tried that, and also worked out the execution plan which I have attached.
query-execution-plan-18.06.2018.sqlplan
I tried that, and also worked out the execution plan which I have attached.
query-execution-plan-18.06.2018.sqlplan
Try creating this missing index, run the query, check the performance and then send another execution plan for further improvements..
USE [cv_masterdb]
GO
CREATE INDEX IX_Contact_perf ON [dbo].[contact] ([co_date])
INCLUDE ([co_rowid_debtor],[co_class],[co_type],[co_description],[co_user_1])
GO
You need to get rid of that massive GROUP BY if at all possible. Best way is, if possible, to move aggregates back to their original tables, something like below. Don't have sample data so I couldn't test it, but it should at least be very close.
SELECT
c.de_number
,client.cl_number
,c.de_status
,CASE WHEN a1.co_description > c.de_status THEN a1.co_description ELSE c.de_status END Prev_Stat
,c.de_type
,c.de_collector
,c.de_salesman
,c.de_listed_date
,c.de_owned_by_debtor
,c.de_principal
,c.de_principal + c.de_adjustments Netprin
,c.de_paid
,c.de_last_payment_date
,c.de_last_worked_date
,c.de_active
,c.de_poe
,c.de_zip
,CASE WHEN c.de_cell_phone IS NOT NULL THEN 'X' END Good_cell
,CASE WHEN c.de_home_phone IS NOT NULL THEN 'X' END Good_landline
,CASE WHEN c.de_email IS NOT NULL THEN 'X' END Good_Email
,co.first_demand_letter
,co.first_demand_email
,co.[DMOT-48hour]
,co.[48Hour Email]
,co.[48hour_email-2nd Ref]
,co.credit_default_email
,co.DMOT_SOL
,co.Voda_Pending_Credit_default
,co.Legal_Preceding
,co.Sol_Demand_Email
,co.[50% Settlement Email]
,co.Prom_desc
,co.Prom_amt
,co.Prom_dd
,co.Prom_Freq
,co.[future_contact]
,co.Last_collector_date
,vedas.vedas_desc
,phonecnt.phonecount
FROM debtor c
LEFT JOIN (SELECT contact.co_rowid_debtor, contact.co_type, max(contact.co_description) as co_description FROM contact WHERE contact.co_type = '2' GROUP BY contact.co_rowid_debtor, contact.co_type) a1 ON c.de_rowid = a1.co_rowid_debtor
LEFT JOIN (
SELECT
co2.co_rowid_debtor
,Max(case when (co2.co_description = 'DMOT Request for payment letter' OR co2.co_description = 'DMOT Small Balance Letter') AND co2.co_type = '0' then co2.co_date end) first_demand_letter
,Max(case when co2.co_description = '1st Demand Email' AND co2.co_type = '0' then co2.co_date end) first_demand_email
,Max(case when co2.co_description = 'DMOT 48 Hour Notice' AND co2.co_type = '0' then co2.co_date end) 'DMOT-48hour'
,Max(case when co2.co_description = '48 Hour -Email' AND co2.co_type = '0' then co2.co_date end) '48Hour Email'
,Max(case when co2.co_description = '48 Hour 2nd Referral -Email' AND co2.co_type = '0' then co2.co_date end) '48hour_email-2nd Ref'
,Max(case when co2.co_description = 'Credit Default Email' AND co2.co_type = '0' then co2.co_date end) credit_default_email
,Max(case when co2.co_description = 'DMOT Solicitors Demand Letter' AND co2.co_type = '0' then co2.co_date end) DMOT_SOL
,Max(case when co2.co_description = 'Vodafone Credit Listing Pending Email' AND co2.co_type = '0' then co2.co_date end) Voda_Pending_Credit_default
,Max(case when co2.co_description = 'Legal Proceedings -Email' AND co2.co_type = '0' then co2.co_date end) Legal_Preceding
,Max(case when (co2.co_description = 'Solicitors Demand Email - Vodafone' OR co2.co_description = 'Solicitors Demand Email - Optus' or co2.co_who_from = 'VODSOLE' OR
co2.co_who_from = 'OPTSOLE' OR co2.co_who_from = 'VIRSOLE') AND co2.co_type = '0' then co2.co_date end) Sol_Demand_Email
,Max(case when co2.co_description = 'Optus 50% Settlement Email' AND co2.co_type = '0' then co2.co_date end) '50% Settlement Email'
,Max(CASE WHEN co2.co_type = '3' THEN co2.co_description END) Prom_desc
,Max(CASE WHEN co2.co_type = '3' THEN co2.co_amount END) Prom_amt
,Max(CASE WHEN co2.co_type = '3' THEN co2.co_date END) Prom_dd
,Max(CASE WHEN co2.co_type = '3' AND co2.co_repeat = '0' THEN 'No Repeat' WHEN co2.co_type = '3' AND co2.co_repeat = '2' THEN 'Weekly' WHEN co2.co_type = '3' AND co2.co_repeat = '3' THEN 'Fortnightly' WHEN co2.co_type = '3' AND co2.co_repeat = '4' THEN 'Monthly'END) Prom_Freq
,Max(case when co2.co_completed = '0' then co2.co_date end) 'future_contact'
,Max(case when co2.co_description = 'Contact Made' OR co2.co_description = 'Spoke with Third Party' OR co2.co_description = 'Debtor Rang In' OR co2.co_description = 'Rang debtor left message to call back' OR co2.co_description = 'Account Viewed' OR co2.co_description = 'Spoke with debtor' OR co2.co_description = 'Payment arrangement reviewed' OR co2.co_description = 'Dialler Called' then co2.co_date end) Last_collector_date
FROM contact co2
GROUP BY co2.co_rowid_debtor
) AS co ON c.de_rowid = co.co_rowid_debtor
LEFT JOIN client ON c.de_rowid_client = client.cl_rowid
LEFT JOIN (
select vedas2.co_rowid_debtor
,Max(CASE WHEN vedas.co_type = '2' then vedas.co_description END) vedas_desc
from contact vedas2
where vedas2.co_type = '2'
group by vedas2.co_rowid_debtor
) AS vedas ON c.de_rowid = vedas.co_rowid_debtor AND c.de_listed_date > '2008-06-25'
LEFT JOIN (select
co_rowid_debtor
,Sum(CASE when co_description = 'Spoke with debtor' or co_description = 'Rang debtor left message to call back' then 1
when co_type = 22 and co_class != 2 and co_class != 3 then 1
when co_type = 1 and co_user_1 != 'Invalid' and co_user_1 != 'No Answer' and co_user_1 != 'busy' then 1
when co_type = 0 then 1
when co_type = 19 then 1
else 0 END) as phonecount
from contact
where co_date between @fromdate and @todate
group by co_rowid_debtor
) as phonecnt ON phonecnt.co_rowid_debtor = c.de_rowid
where c.de_listed_date between @fromdate and @todateASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window