February 16, 2010 at 4:24 pm
I'm running the following query:
IF OBJECT_ID('tempdb.dbo.##bcpOut') IS NOT NULL DROP TABLE ##bcpOut
SELECT
a.customerID
,a.orderdate
,(CASE WHEN a.order_rank = MAX(a.order_rank ) OVER(PARTITION BY a.shopper_id)
THEN 1 ELSE 0 END) AS isLastOrder
,ISNULL(DATEDIFF(dd,a.orderdate,b.orderdate),0) AS TimeBetweenOrders
,a.businessLine
,a.Order_rank
INTO ##bcpOut
FROM #ordersProduct a
LEFT JOIN #ordersProduct b
ON a.customerID= b.customerID
AND a.order_rank = b.order_rank - 1
The #ordersProduct temp table contains 128million records and the left join on itself used to calculate time between sequential orders isn't helping. I'm hoping someone may be able to provide a creative way for me to send messages as this query is running at specific time intervals showing how many records have been churned through without limiting performance and without using a loop. Is that possible?
February 17, 2010 at 9:36 am
So I assume this is either a dumb question or everyone is afraid of taking on the challenge.
February 17, 2010 at 10:31 am
The answer is no, not without breaking it into smaller batches.
You submit a query and then get no response back from SQL until it's performed the operation. It's possible you could get some indication from performing dirty reads on the table being inserted into, but it would impact performance by virtue of the overhead of counting the rows, it would also be inaccurate and possibly still locked when doing the reads.
You may be able to optimise that query though. Obviously this is 100% untested as you haven't provided sample data, but something like this may work out being faster depending on the indexes on the underlying table:
;WITH ordersProductCTE AS (
SELECT
customerID,
orderdate,
order_rank,
businessline,
RANK() OVER (PARTITION BY shopper_id ORDER BY order_rank DESC) AS order_rank_desc
)
SELECT
a.customerID
,a.orderdate
,(CASE WHEN a.order_rank_desc = 1 THEN 1 ELSE 0 END) AS isLastOrder
,ISNULL(DATEDIFF(dd,a.orderdate,b.orderdate),0) AS TimeBetweenOrders
,a.businessLine
,a.Order_rank
INTO ##bcpOut
FROM ordersProductCTE a
LEFT JOIN ordersProductCTE B ON a.customerID= b.customerID
AND a.order_rank = b.order_rank - 1
If order_rank is guaranteed elsewhere to be unique for a shopper_id or you only ever want 1 order for each shopper_id to be flagged as the last order, you could further improve the performance by substituting RANK() for ROW_NUMBER()
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply