September 27, 2018 at 11:05 am
SELECT
mp.deal_id,
(SELECT MAX(revs_no) FROM avt_deal_hdr (NOLOCK) WHERE deal_id = mp.deal_id) revs_no,
mp.product_id,
isnull(mp.version_id,1) version_id,
1 aa,
mp.material_product_id ,
pd.line_id,
isnull(pd.delivery_status_id, 4) delivery_status_id into #product_result
FROM avt_material_product mp (NOLOCK)
INNER JOIN #product_status ps ON mp.active_cd = ps.product_status
INNER JOIN #title_status_cd ts ON mp.title_status_cd = ts.title_status_cd
INNER JOIN avt_po_detail pd (NOLOCK) ON mp.material_product_id = pd.material_product_id
INNER JOIN avt_deal_line adl (NOLOCK) ON adl.deal_id=mp.deal_id AND adl.product_id=mp.product_id
AND adl.revs_no =(SELECT MAX(revs_no) FROM avt_deal_hdr WHERE deal_id = adl.deal_id)
WHERE
mp.deal_id > 0
and mp.product_id is not null
and isnull(mp.prep_dt, '1/1/1900') >= isnull(@prep_date_start, '1/1/1900') /* ASR-4573560 Included the isnull check*/
and isnull(mp.prep_dt, '1/1/1900') <= isnull(@prep_date_end, '1/1/3000')
and isnull(mp.delivery_dt, '1/1/1900') >= isnull(@delivery_date_start, '1/1/1900')
and isnull(mp.delivery_dt, '1/1/1900') <= isnull(@delivery_date_end, '1/1/3000')
and isnull(mp.lic_beg_dt, '1/1/1900') >= isnull(@lic_beg_start, '1/1/1900')
and isnull(mp.lic_beg_dt, '1/1/1900') <= isnull(@lic_beg_end, '1/1/3000')
and isnull(mp.lic_end_dt, '1/1/3000') >= isnull(@lic_end_start, '1/1/1900')
and isnull( mp.lic_end_dt, '1/1/3000') <= isnull(@lic_end_end, '1/1/3000')
AND ISNULL(adl.pin_letter_sent, '1/1/1900') >= ISNULL(@Pin_start_date, '1/1/1900') ---SRTS-2795--Pin sent date filter
AND ISNULL(adl.pin_letter_sent, '1/1/1900') <= ISNULL(@Pin_end_date, '1/1/3000') ---SRTS-2795--Pin sent date filter
--and mp.deal_id=119412
drop table #product_status
drop table #title_status_cd
drop table #product_result
--SQL2 starts--SQL2 starts
Delete pr
from #product_result pr Left outer join avt_po_detail pd (NOLOCK)
ON pr.deal_id = pd.deal_id
and ( (isnull(pd.due_dt,'1/1/1900') >= isnull(@due_dt_start ,'1/1/1900')
and isnull(pd.due_dt,'1/1/1900') <= isnull(@due_dt_end , '1/1/3000'))
OR (pd.due_dt is null and pd.due_dt_expr is not null and pd.vendor_due_date is not null and
(isnull(pd.vendor_due_date,'1/1/1900') >= isnull(@due_dt_start ,'1/1/1900')
and isnull(pd.vendor_due_date,'1/1/1900') <= isnull(@due_dt_end , '1/1/3000')))
OR (pd.due_dt is null and pd.vendor_due_date is null and pr.original_release_date is not null and
isnull(DATEADD(DAY,Convert(int,CASE WHEN IsNumeric(CONVERT(VARCHAR(20), pd.due_dt_expr )) = 1
THEN CONVERT(VARCHAR(20), pd.due_dt_expr)
ELSE 0 END), pr.original_release_date),'1/1/1900') >=isnull(@due_dt_start ,'1/1/1900')
and isnull(DATEADD(DAY,Convert(int,CASE WHEN IsNumeric(CONVERT(VARCHAR(20), pd.due_dt_expr )) = 1
THEN CONVERT(VARCHAR(20), pd.due_dt_expr)
ELSE 0 END), pr.original_release_date),'1/1/1900') <= isnull(@due_dt_end , '1/1/3000'))
OR @due_dt_cnt = 0)
--SQL2 ends
September 28, 2018 at 7:01 am
Enormous performance killer:and isnull(mp.prep_dt, '1/1/1900') >= isnull(@prep_date_start, '1/1/1900')
Compare the column to the variable/parameter. Running functions on the column (and on the parameter/variable) means that statistics and indexes can't be used and you MUST perform scans to find every single one of these values. You can't get any other behavior until this is eliminated. Read this article too.
This is an even more egregious example:
isnull(DATEADD(DAY,Convert(int,CASE WHEN IsNumeric(CONVERT(VARCHAR(20), pd.due_dt_expr )) = 1
THEN CONVERT(VARCHAR(20), pd.due_dt_expr)
ELSE 0 END), pr.original_release_date),'1/1/1900') >=isnull(@due_dt_start ,'1/1/1900')
"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
September 28, 2018 at 8:44 am
Also search for articles that mention "sargable / sargability". This is a way of referring to the sort of problem that Grant has mentioned.
For example, https://sqlinthewild.co.za/index.php/2016/09/13/what-is-a-sargable-predicate/
or http://blogs.lobsterpot.com.au/2010/01/22/sargable-functions-in-sql-server/
are a couple of articles you should read.
Thomas Rushton
blog: https://thelonedba.wordpress.com
September 28, 2018 at 9:59 am
What part is taking the most time? Can you split it up to see?
Try replacing the first SQL with something like this:
SET @prep_date_start = COALESCE(@prep_date_start, '1/1/1900');
SET @prep_date_end = COALESCE(@prep_date_end, '1/1/3000');
SET @delivery_date_start = COALESCE(@delivery_date_start, '1/1/1900');
SET @delivery_date_end = COALESCE(@delivery_date_end, '1/1/3000');
SET @lic_beg_start = COALESCE(@lic_beg_start, '1/1/1900');
SET @lic_beg_end = COALESCE(@lic_beg_end, '1/1/3000');
SET @lic_end_start = COALESCE(@lic_end_start, '1/1/1900');
SET @lic_end_end = COALESCE(@lic_end_end, '1/1/3000');
SET @Pin_start_date = COALESCE(@Pin_start_date, '1/1/1900');
SET @Pin_end_date = COALESCE(@Pin_end_date, '1/1/3000');
WITH avt_material_product_CTE AS
(
SELECT deal_id, product_id, version_id, material_product_id,
active_cd, title_status_cd, material_product_id,
COALESCE(prep_dt, '1/1/1900') AS prep_dt,
COALESCE(delivery_dt, '1/1/1900') AS delivery_dt,
COALESCE(lic_beg_dt, '1/1/1900') AS lic_beg_dt,
COALESCE(lic_end_dt, '1/1/3000') AS lic_end_dt
FROM avt_material_product
)
,
avt_deal_line_CTE
(
SELECT deal_id, product_id, revs_no,
COALESCE(pin_letter_sent, '1/1/1900') AS pin_letter_sent
FROM avt_deal_line
)
SELECT mp.deal_id,
--(SELECT MAX(revs_no)
-- FROM avt_deal_hdr --(NOLOCK)
-- WHERE deal_id = mp.deal_id) AS revs_no,
adl.revs_no,
mp.product_id,
COALESCE(mp.version_id,1) AS version_id,
1 AS aa,
mp.material_product_id,
pd.line_id,
COALESCE(pd.delivery_status_id, 4) AS delivery_status_id
INTO #product_result
FROM avt_material_product_CTE AS mp --(NOLOCK)
INNER JOIN #product_status AS ps
ON mp.active_cd = ps.product_status
INNER JOIN #title_status_cd AS ts
ON mp.title_status_cd = ts.title_status_cd
INNER JOIN avt_po_detail AS pd --(NOLOCK)
ON mp.material_product_id = pd.material_product_id
INNER JOIN avt_deal_line_CTE AS adl --(NOLOCK)
ON adl.deal_id = mp.deal_id
AND adl.product_id = mp.product_id
AND adl.revs_no = (SELECT MAX(revs_no)
FROM avt_deal_hdr
WHERE deal_id = adl.deal_id)
WHERE mp.deal_id > 0
AND mp.product_id IS NOT NULL
AND mp.prep_dt >= @prep_date_start /* ASR-4573560 Included the COALESCE check*/
AND mp.prep_dt <= @prep_date_end
AND mp.delivery_dt >= @delivery_date_start
AND mp.delivery_dt <= @delivery_date_end
AND mp.lic_beg_dt >= @lic_beg_start
AND mp.lic_beg_dt <= @lic_beg_end
AND mp.lic_end_dt >= @lic_end_start
and mp.lic_end_dt <= @lic_end_end
AND adl.pin_letter_sent >= @Pin_start_date ---SRTS-2795--Pin sent date filter
AND adl.pin_letter_sent <= @Pin_end_date ---SRTS-2795--Pin sent date filter
--and mp.deal_id=119412
;
DROP TABLE #product_status;
DROP TABLE #title_status_cd;
DROP TABLE #product_result;
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply