I have what seems like a fairly simple query, but it's timing out. It runs with a smaller result set (15k) but it won't run with 90k or greater.

Here is the query

SELECT sdv.JobID,count(distinct sdv.SubscriberKey) as Sent,s3.Market as Market
FROM [Sent Data View] sdv with (NOLOCK)
JOIN [Stan Filter SendLog Test] s3 with (NOLOCK)
ON sdv.JobID = s3.JobID
WHERE sdv.EventDate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
AND sdv.EventDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
GROUP BY sdv.JobID, s3.Market

The first table [Sent Data View] has about 1.6 million records. The other table [Stan Filter SendLog Test] is a filtered Data Extension from the send log. I have put filters on that one to change the value anywhere from 15k to 20k to 90k to 120k to 300k. It only works when there are fewer records around 20k. It also works on the full 300k if I take out the count() select. Obviously this seems like a query optimization problem, but I'm hitting a wall.

I've also tried importing the 300k records to a standard data extension to see if that worked, but it did not.

I know I could get Market in a separate query, but this seems so simple, I'd really just like to have it all in one...

  • Ok UPDATE. I guess this is working now. I think maybe all I had to do was add distinct to the count? Can anyone explain why that worked? count(distinct sdv.SubscriberKey) – garek007 Aug 23 '17 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.