使用dateadd查询运行非常长 [英] Query using dateadd running VERY long

问题描述

我正在尝试显示未在x天内更新商品库存的商店。



区域经理想要输入一个数字（如90或365）并获得所有商店和他们订购的最后库存的清单。因此，如果90家商店在过去90天内没有订购任何商品，则需要显示商店名称和他们上次订购的所有商品。



如果我使用它：

I am trying to display stores that haven't updated their merchandise inventory in x number of days.

The District Manager wants to enter a number (like 90 or 365) and get a list of all the stores AND the last inventory they ordered. So, if 90 stores haven't ordered anything in the last 90 days, the store name and all the items they last ordered need to be displayed.

If I use this:

WHERE MerchLastShippedDate = DATEADD("d", -@Days, CONVERT(VARCHAR(10),Getdate(),101)) 

我的查询在很长一段时间内运行。但是，这并没有向我展示我需要的一切。当我使用下面的内容向我展示比我输入的天数更早的所有内容时，查询需要太长时间才能运行。



如何更正此问题并使我的查询运行得更快？



这是我的代码：

my query runs in an ok amount of time. But, that isn't showing me everything I need. It's when I use the below to show me everything that is older than the number of days I enter that the query takes too long to run.

How can I correct this and make my query run faster?

Here is my code:

IF object_id('tempdb..#ActiveStores') is not null
BEGIN
    DROP TABLE #ActiveStores
END

SELECT DISTINCT
	Storename,
	StoreId
	INTO #ActiveStores
FROM 
	StoresTable
Where Storestatus = 1
	and Storename <> '  '
Order by
	StoreID


IF object_id('tempdb..#MerchDays') is not null
BEGIN
    DROP TABLE #MerchDays
END
  

Create Table #MerchDays
(StoreID varchar(100),
StoreName varchar(100),
MerchID varchar(50),
Manager  varchar(100),
MerchLastShippedDate datetime,
MerchNumber varchar(100),
QtyMerchShipped float,
AmtMerchShipped float

)

INSERT INTO #MerchDays
(StoreID, StoreName, MerchId, Manager, MerchLastShippedDate, MerchNumber, QtyMerchShipped, AmtMerchShipped)
SELECT StoreID, StoreName, MerchId, Manager, MerchLastShippedDate, MerchNumber, QtyMerchShipped, AmtMerchShipped
FROM MainStoredInventoryMerchTbl 
 
WHERE MerchLastShippedDate <= DATEADD("d", -@Days, CONVERT(VARCHAR(10),Getdate(),101)) 
 GROUP BY StoreID, StoreName, MerchId, Manager, MerchLastShippedDate, MerchNumber, QtyMerchShipped, AmtMerchShipped
ORDER BY StoreID

</<pre lang="SQL">

What I have tried:

MSDN, Google search, stackoverflow

推荐答案

我的SQL有点生疏，但看起来你在循环内而不是在外面计算固定日期。即：计算一次而不是每次记录？

My SQL is a bit rusty but it looks like you are calculating a fixed date inside a loop instead of outside. ie: calculate once rather than for every record?

这篇关于使用dateadd查询运行非常长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！