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...