Home DateAdd Query take long time to execute
Reply: 1

DateAdd Query take long time to execute

AlvinWi
1#
AlvinWi Published in 2018-02-14 04:38:08Z

I have query below which have DateAdd on the WHERE clause

SELECT d.INVOICE_NO, d.part_no, d.PART_ORDER_QTY, d.PART_SHIPPED_QTY, d.INV_ALLOC_ORIG_DOC, d.INV_ALLOC_BR_QTY_HIST, d.INV_ALLOC_QTY_HIST, pc.PopCode, pm.SOURCE_CODE, d.BSIT_STORE, I.CUST_NO, C.Sales_type
  FROM [BSIT_ERA_RAW_DATA].[dbo].[INVOICE_ERA_LINE_DTLS] D
  LEFT JOIN [BSIT_ERA_RAW_DATA].[dbo].[INVOICE] I ON D.INVOICE_NO = I.INVOICE_NO
  LEFT JOIN [BSIT_ERA_RAW_DATA].[dbo].[PROD_MASTER] PM ON D.PART_NO = PM.PARTS_MASTER_KEY AND PM.BSIT_STORE = 'STORE01'
  LEFT JOIN [MICQIF_PROD].[dbo].[PopCodes] PC ON right(d.PART_NO,len(d.part_no)-2) COLLATE SQL_Latin1_General_CP1_CI_AS = PC.ItemNo COLLATE SQL_Latin1_General_CP1_CI_AS
  LEFT JOIN [BSIT_ERA_RAW_DATA].[dbo].[CUSTOMER] C ON I.CUST_NO = C.CUSTOMER_ID AND C.BSIT_STORE = 'STORE01'
  WHERE D.PART_ISSUE_DATE >= CONVERT(DATE,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1 , 0))
  AND D.PART_ISSUE_DATE <= CONVERT(varchar(10), DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) , -1), 120)
  AND I.INVOICE_CLOSED_DATE <= CONVERT(varchar(10), DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) , -1), 120)

  AND D.INV_ALLOC_ORIG_DOC IS NULL
  AND D.INVOICE_NO NOT LIKE 'CM%'
  AND D.PART_NO IS NOT NULL
  and d.part_no like 'cp%'
  and INVOICE_VOID_DATE IS NULL
  AND I.CUST_NO NOT IN ('90102','90103','90104','90105','90201','90203','90204','90205','90401','90402','90403','90405','90501','90502','90503','90504','90301','90302','90304','90305')

This run for a very long time and in the end I could not finish the process.

However if change the date to a static date like

CONVERT(DATE,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1 , 0))

into '2018-01-01'

and CONVERT(varchar(10), DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) , -1), 120)

into '2018-01-31', the query will run smoothly.

Could anyone advise what could be the issue?

Cheers

tarheel
2#
tarheel Reply to 2018-02-14 04:51:45Z

An excerpt from an article called Avoid Using Functions in WHERE clause is as follows:

There are two reasons why you want to avoid having a function call in your WHERE clause and more specifically on the columns you are filtering on in your WHERE clause. [...The] function needs to be called for every record in the result set which can slow down your query performance. The second reason which can have even more impact on query performance is the fact that if there is a function surrounding the column you are trying to filter on, any indexes on that column can not be used.

Which boils down to the fact that your function is being calculated n times (where n is the number of records being considered). You can reduce the number of calculations down to 1 by assigning the calculated date to a variable, and then use that variable in the where clause. The resulting query would look something like this:

declare @part_issue_date_min date = CONVERT(DATE,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1 , 0))
    , @part_issue_date_max date = CONVERT(varchar(10), DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) , -1), 120)
    , @invoice_closed_date date = CONVERT(varchar(10), DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) , -1), 120)

SELECT d.INVOICE_NO, d.part_no, d.PART_ORDER_QTY, d.PART_SHIPPED_QTY, d.INV_ALLOC_ORIG_DOC, d.INV_ALLOC_BR_QTY_HIST, d.INV_ALLOC_QTY_HIST, pc.PopCode, pm.SOURCE_CODE, d.BSIT_STORE, I.CUST_NO, C.Sales_type
  FROM [BSIT_ERA_RAW_DATA].[dbo].[INVOICE_ERA_LINE_DTLS] D
  LEFT JOIN [BSIT_ERA_RAW_DATA].[dbo].[INVOICE] I ON D.INVOICE_NO = I.INVOICE_NO
  LEFT JOIN [BSIT_ERA_RAW_DATA].[dbo].[PROD_MASTER] PM ON D.PART_NO = PM.PARTS_MASTER_KEY AND PM.BSIT_STORE = 'STORE01'
  LEFT JOIN [MICQIF_PROD].[dbo].[PopCodes] PC ON right(d.PART_NO,len(d.part_no)-2) COLLATE SQL_Latin1_General_CP1_CI_AS = PC.ItemNo COLLATE SQL_Latin1_General_CP1_CI_AS
  LEFT JOIN [BSIT_ERA_RAW_DATA].[dbo].[CUSTOMER] C ON I.CUST_NO = C.CUSTOMER_ID AND C.BSIT_STORE = 'STORE01'
  WHERE D.PART_ISSUE_DATE >= @part_issue_date_min
  AND D.PART_ISSUE_DATE <= @part_issue_date_max
  AND I.INVOICE_CLOSED_DATE <= @invoice_closed_date

  AND D.INV_ALLOC_ORIG_DOC IS NULL
  AND D.INVOICE_NO NOT LIKE 'CM%'
  AND D.PART_NO IS NOT NULL
  and d.part_no like 'cp%'
  and INVOICE_VOID_DATE IS NULL
  AND I.CUST_NO NOT IN ('90102','90103','90104','90105','90201','90203','90204','90205','90401','90402','90403','90405','90501','90502','90503','90504','90301','90302','90304','90305')
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.63456 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO